TEKMEN's Oracle World

Partitioning on W_GL_OTHER_F - 1

2011-02-27T17:30:00.002+01:00

In our database, W_GL_OTHER_F table has more then 70 million records, and reports have already started hitting bad performance. Considering we will go live on more countries in our global OBIEE project, the number of records is getting higher day by day. According to Oracle Business Intelligence Applications Version 7.9.6 Performance Recommendations (BI_Apps796_Perf_Tech_Note_V5), we have decided to go on partitiopning over W_GL_OTHER_F table. I will try to write the all steps that we followed to help other people who are trying to do the same, as we had many questions before starting this process, and we could not find quick answers by just googling.

We are implementing the range partitioning with ACCT_PERIOD_END_DT_WID and also partitioning the data by quarterly. The first problem that we hit was how to partition the data by quarterly while the ACCT_PERIOD_END_DT_WID is a number field having populated by concatenating 3 values

M_CAL_CAL_WID || ACCT_PERIOD_END_DT|| IIF(ADJUSTMENT_FLG='Y', '999', '000')

M_CAL_CAL_WID is an ID of calendar being used, in our case it is 1003. And the last 3 digits has not much impact on partitioning. Like if accounting period end is 20101031 (31 Oct 2010) and if it is adjustment record then ACCT_PERIOD_END_DT_WID would be 100320101031999. Therefore we came up with the solution that we will be loading this data into 4th Quarter of 2010, which can be defined as "partition PART_2010Q4 values less than (100320101232000)", see the below create table script for more detail, but itis simply that we do not need to comply with date formatting, it is a number in this column anyway, so we can play with it as we want.

Steps to be done in Datawarehouse Database
Rename the original table

rename W_GL_OTHER_F to W_GL_OTHER_F_ORIG;

Before running the create table ddl, execute the below query in database and change the partition range value accordingly.

SELECT row_wid
FROM w_mcal_cal_d
WHERE mcal_cal_name = 'YOUR CALENDAR'

Please use the value from the above query while defining the partition range. As in our environment the value is 1003 we have used 1003**** and replace this with the query result.

Create the partitioned table, using range partitioning by quarter;

Create table W_GL_OTHER_F partition by range (ACCT_PERIOD_END_DT_WID)
(
partition PART_MIN values less than (100320061232000),
partition PART_2007Q1 values less than (100320070332000),
partition PART_2007Q2 values less than (100320070632000),
partition PART_2007Q3 values less than (100320070932000),
partition PART_2007Q4 values less than (100320071232000),
partition PART_2008Q1 values less than (100320080332000),
partition PART_2008Q2 values less than (100320080632000),
partition PART_2008Q3 values less than (100320080932000),
partition PART_2008Q4 values less than (100320081232000),
partition PART_2009Q1 values less than (100320090332000),
partition PART_2009Q2 values less than (100320090632000),
partition PART_2009Q3 values less than (100320090932000),
partition PART_2009Q4 values less than (100320091232000),
partition PART_2010Q1 values less than (100320100332000),
partition PART_2010Q2 values less than (100320100632000),
partition PART_2010Q3 values less than (100320100932000),
partition PART_2010Q4 values less than (100320101232000),
partition PART_2011Q1 values less than (100320110332000),
partition PART_2011Q2 values less than (100320110632000),
partition PART_2011Q3 values less than (100320110932000),
partition PART_2011Q4 values less than (100320111232000),
partition PART_2012Q1 values less than (100320120332000),
partition PART_2012Q2 values less than (100320120632000),
partition PART_2012Q3 values less than (100320120932000),
partition PART_2012Q4 values less than (100320121232000),
partition PART_MAX values less than (maxvalue)
)
TABLESPACE XXXDW_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          4M
            NEXT             4M
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
nologging parallel
enable row movement
as (select * from W_GL_OTHER_F_ORIG);
/

Rename indexes on renamed table

ALTER INDEX W_GL_OTHER_F_71 rename to W_GL_OTHER_F_71_ORIG;
ALTER INDEX W_GL_OTHER_F_72 rename to W_GL_OTHER_F_72_ORIG;
ALTER INDEX W_GL_OTHER_F_73 rename to W_GL_OTHER_F_73_ORIG;
ALTER INDEX W_GL_OTHER_F_F59 rename to W_GL_OTHER_F_F59_ORIG;
ALTER INDEX W_GL_OTHER_F_F25 rename to W_GL_OTHER_F_F25_ORIG;
ALTER INDEX W_GL_OTHER_F_F36 rename to W_GL_OTHER_F_F36_ORIG;
ALTER INDEX W_GL_OTHER_F_F40 rename to W_GL_OTHER_F_F40_ORIG;
ALTER INDEX W_GL_OTHER_F_F5 rename to W_GL_OTHER_F_F5_ORIG;
ALTER INDEX W_GL_OTHER_F_M2 rename to W_GL_OTHER_F_M2_ORIG;
ALTER INDEX W_GL_OTHER_F_C70 rename to W_GL_OTHER_F_C70_ORIG;
ALTER INDEX W_GL_OTHER_F_F13 rename to W_GL_OTHER_F_F13_ORIG;
ALTER INDEX W_GL_OTHER_F_F11 rename to W_GL_OTHER_F_F11_ORIG;
ALTER INDEX W_GL_OTHER_F_F60 rename to W_GL_OTHER_F_F60_ORIG;
ALTER INDEX W_GL_OTHER_F_F61 rename to W_GL_OTHER_F_F61_ORIG;
ALTER INDEX W_GL_OTHER_F_U1 rename to W_GL_OTHER_F_U1_ORIG;

Create Global and Local indexes. Run the below statements to create indexes

CREATE BITMAP INDEX W_GL_OTHER_F_C70 ON W_GL_OTHER_F (LOC_CURR_CODE ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_72 ON W_GL_OTHER_F (X_REFERENCE_8 ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE UNIQUE INDEX W_GL_OTHER_F_U1 ON W_GL_OTHER_F (INTEGRATION_ID ASC,DATASOURCE_NUM_ID ASC) tablespace XXXDW_DATA GLOBAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_73 ON W_GL_OTHER_F (DOC_CURR_CODE ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_71 ON W_GL_OTHER_F (DOC_STATUS_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F11 ON W_GL_OTHER_F (COMPANY_ORG_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F13 ON W_GL_OTHER_F (COST_CENTER_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F25 ON W_GL_OTHER_F (GL_ACCOUNT_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F36 ON W_GL_OTHER_F (POSTED_ON_DT_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F40 ON W_GL_OTHER_F (PROFIT_CENTER_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F5 ON W_GL_OTHER_F (BUSN_AREA_ORG_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE INDEX W_GL_OTHER_F_F59 ON W_GL_OTHER_F (ACCT_PERIOD_END_DT_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F60 ON W_GL_OTHER_F (LEDGER_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_F61 ON W_GL_OTHER_F (MCAL_CAL_WID ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;
CREATE BITMAP INDEX W_GL_OTHER_F_M2 ON W_GL_OTHER_F (DELETE_FLG ASC) tablespace XXXDW_DATA LOCAL NOLOGGING;

And finally compute statistics on partitioned table

BEGIN
dbms_stats.Gather_table_stats(
NULL,
tabname => 'W_GL_OTHER_F',
CASCADE => true,
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO');
END;

Database part is done, we need to do DAC changes that I will try to post later. Actually for DAC part, the document from Oracle is more than enough, but I will repeat some steps to finalize the document. My team from offshore has done good job on delivering this solution, a big thanks to them.

Vertical Text Title in OBIEE Answers

2011-02-10T05:49:00.000+01:00

We had a requirement that report needs to show column titles vertically in the Answers report. We used to have similar thing for downloaded excel reports by using "mso-rotate:90" in "Use Custom CSS Style" before. But this time requirement is for answer reports. Similarly by adding the following statement into column heading formatting part, we achieved to have vertically aligned heading in the report as seen below;

"{writing-mode: tb-rl;filter: flipv fliph;}"

Override Session Variable in Answers

2011-01-29T05:49:00.000+01:00

If you wanted to override a session variable and disable cache hits, you would use a syntax like the following in Answers Advanced Tab
SET VARIABLE LOGLEVEL=4, DISABLE_CACHE_HIT = 1;

SET VARIABLE OU_ORG=128;

Performance Tuning Tips for OBIEE

2011-01-25T08:01:00.000+01:00

1. implement caching mechanism
2. use aggregates
3. use aggregate navigation
4. limit the number of initialisation blocks
5. turn off logging
6. carry out calculations in database
7. use materialized views if possible
8. use database hints
9. alter the NQSONFIG.ini parameters

http://www.oraclebidwh.com/2010/02/performance-tuning-in-obiee/

OBIEE is not an ETL tool!

2011-01-23T05:18:00.000+01:00

just a reminder for whom using OBIEE as Excel Extraction tool;

"OBIEE is not an ETL tool. It is NOT designed to handle large amounts of non aggregated data. It’s designed to handle dimensional structured data with sufficient aggregation tables."

Changing the Connection Pool from Answers

2011-01-21T11:18:00.000+01:00

Keeping Leading Zero's in Answers

2011-01-04T11:11:00.000+01:00

Nice way to keep leading zero’s when exporting to excel from within OBIEE:

Add “mso-number-format:\@” to the Custom CSS Style Options of the appropriate column in the OBIEE report.

Excel rotating style

mso-rotate:90

UDDI Connection in JDeveloper

2010-12-04T17:47:00.000+01:00

In the Connection Wizard, provide a connection name and specify the UDDI inquiry endpoint URL. The syntax of this URL is:

http://ohs_host:ohs_Port/registry_context/uddi/inquiry

ohs_host and ohs_Port have the following definitions:

· ohs_host is the address of the Oracle Application Server host machine.; for example, server07.company.com

· ohs_Port is the HTTP listener port assigned to OHS

registry_context is context root used to access the target registry instance, such as "registry" or "registrypub"

How to export Security Group to create UDML

2010-11-02T15:35:00.001+01:00

Here is a command to extract security objects from an OBIEE repository using the UDML command, mind the “-S” in the end, it handles to export Security part only;

C:\oracle\OBIEE\OracleBI\server\Bin\nQUDMLGen.exe -U Administrator -P xxx123 -R D:\ALL\repository\temps\yaz\050310_XXX_BAW_CS.rpd -O D:\ALL\repository\temps\yaz\tekin.udml -S

C:\OracleBI\server\bin\nqudmlgen -U Administrator -P Administrator -R C:\OracleBI\server\Sample\samplesales\samplesales.rpd -O C:\OracleBI\server\Sample\samplesales\samplesales.udml -S

nqudmlgen: This is the actual export command
-U: This is the user flag, in my case Administrator
-P: This is the password flag, in my case Administrator since I'm using sample sales
-R: Is the source repository. I am sourcing from samplesales.rpd on a Windows box
-O: Is the output file. I am populating a file called samplesales.udml in the same folder as the source
-S: Exports only security objects

Here is the command to import the objects

nqudmlexec -U Administrator -P Administrator -I C:\OracleBI\server\Sample\samplesales\samplesales.udml -B C:\OracleBI\server\Sample\samplesales\samplesales.rpd -O C:\OracleBI\server\Sample\samplesales\samplesales2.rpd

nqudmlexec : This is the actual import command
-U: This is the user flag, in my case Administrator
-P: This is the password flag, in my case Administrator since I'm using sample sales
-I: This is the input script. In this case, the input to this is the output from the first command
-B: This is the base repository. In this case, your base repository will be your target repository, the one where you want to migrate the users to.
-O: This is the output repository. This command makes a copy and applies the changes in the UDML to that copy. So you need to specify an output file. This is the file that will have the changes. The base file will not have the new users since it is just used to make this copy.

Example;

EXPORT;

C:\oracle\OBIEE\OracleBI\server\Bin\nQUDMLGen.exe -U Administrator -P xxx-R D:\ALL\repository\temps\yaz\050310_XXX_BAW_CS.rpd -O D:\ALL\repository\temps\yaz\tekin.udml

IMPORT;

C:\oracle\OBIEE\OracleBI\server\Bin\nQUDMLexec.exe -U Administrator -P xxx -I D:\ALL\repository\temps\yaz\tekin.udml -B D:\ALL\repository\temps\yaz\050310_BAW.rpd -O D:\ALL\repository\temps\yaz\050310_BAW.rpd

How to delete item by UDML

2010-10-05T15:32:00.002+02:00

How to delete item by UDML

We are complaining about “UDML works great but we cannot delete item by using UDML”. Here is how to delete;

1. Copy the item you want to delete as UDML

DECLARE FOLDER ATTRIBUTE "XXX - BAW Customer Service".."- Task Created By"."Created By User Name" AS "Created By User Name" UPGRADE ID 2160852698 LOGICAL ATTRIBUTE "Core"."Dim - Created By"."User Name" OVERRIDE LOGICAL NAME

ALIASES ("User Name")

PRIVILEGES ( READ);

2. And change it as below with DELETE command, save it.

DELETE FOLDER ATTRIBUTE "XXX - BAW Customer Service".."- Task Created By"."Created By User Login";

3. Run nQUDMLexec.exe, that’s it!

Length Semantics in DB Profile

2010-09-25T14:23:00.003+02:00

If we describe any table in OBIEE environment, we can find that all the string column sizes are 4 times the actual size.

For e.g.; In table W_SUPPLIER_ACCOUNT_D

PAY_TERMS_CODE VARCHAR (200)

PAY_TERMS_NAME VARCHAR (320)

If we refer the same columns in Informatica and Oracle BI Repository, the column sizes would be VARCHAR (50) and VARCHAR (80) respectively.

The reason behind this anomaly is that DB profile is set with Length Semantics to BYTE instead of CHAR.

i.e.; PAY_TERMS_CODE VARCHAR (200) = VARCHAR (200 BYTE) = VARCHAR (50 CHAR)

So going forward whenever you extend any base table for adding a string column, specify the syntax as CHAR explicitly.

i.e.; alter table W_SUPPLIER_ACCOUNT_D

add X_VAT_CODE VARCHAR2(50 CHAR);

Purging RPD Cache

2010-08-04T17:47:00.004+02:00

We can purge RPD cache without opening rpd online mode using “Oracle BI Presentation Services Administration”

TO_NUMBER in OBIEE

2010-01-06T16:13:00.000+01:00

You can use CAST function to convert from one datatype to another datatype. and you can create a new logical column in your RPD , that use this function;

cast(MONTH("Fixed Assets Depreciation".DEPRN_RUN_DATE) as char)

Technorati Tags: OBIEE

iBot Job Scheduler Exit Code

2009-12-10T12:02:00.003+01:00

We created conditional iBot request, and schedule it to run every morning. But when we examine the Job Scheduler log file, we noticed that there is “Exit Code” column, which is sometimes different from one run to another. Exit Code column apparently shows how many successful deliveries being done with that run.

The ExitCode of an instance is set to the number of successful deliveries. The count corresponds to the number of successful deliveries to devices, and there may be more than one device for each recipient of an iBot.

Oracle Null

2009-07-20T10:58:00.001+02:00

These are not necessarily unexplainable idiosyncrasies. Rather, this is a list
of Null usage cases that may surprise me personally. Note: Null value is
spelled "Null" in here.

(1) Null doesn't count in aggregate function.
create table testnull (a number);
insert into testnull values (1);
insert into testnull values (2);
insert into testnull values (null);
select count(*) from testnull; <-- returns 3
select count(a) from testnull; <-- returns 2

create table test (name varchar2(10), value number);
insert into test values ('xx', 12);
insert into test values ('xx', null);
insert into test values ('yy', 123);
select name, count(*) from test group by name;
select name, count(value) from test group by name;

NAME COUNT(VALUE)
---------- ------------
xx 1 <-- would be 2 if select name, count(*) ...
yy 1

(2) Inserted null string converted to Null.
create table testnull (a varchar2(10));
insert into testnull values (null);
insert into testnull values ('');
insert into testnull values ('' || 'Hello');
insert into testnull values (null || 'Hello');
select dump(a) from testnull;

DUMP(A)
---------------------------------------------
NULL
NULL
Typ=1 Len=5: 72,101,108,108,111
Typ=1 Len=5: 72,101,108,108,111

(3) Where can Null be compared?
select decode(null, null, 'Null equals Null in DECODE') from dual;

DECODE(NULL,NULL,'NULLEQUA
--------------------------
Null equals Null in DECODE

Oracle SQL Reference says "In a DECODE function, Oracle considers two nulls to
be equivalent. If expr is null, then Oracle returns the result of the first
search that is also null."

Another place where Null can be compared is in range partition definition,
where MAXVALUE is greater than Null (Ref. J. Lewis "Practical Oracle8i",
p.241).

(4) [Related to (3)] Unique constraints.
create table test (a number);
create unique index unq_test on test (a);
insert into test values (null);
insert into test values (null); <-- No error.
You *are* able to insert another Null without getting ORA-1 (unique constraint
violated).

create table test (a varchar2(1), b varchar2(1));
create unique index unq_test on test (a, b);
insert into test values ('A', null);
insert into test values ('A', null); <-- Get ORA-1
truncate table test;
insert into test values (null, null);
insert into test values (null, null); <-- No error
So if all columns are null, the unique constraint will not be violated. If one
or more columns have non-null values, the constraint takes effect.

(5) Unknown OR True returns True, Unknown AND False returns False.
create table test (a number, b number, c number);
insert into test values (3, 4, null);
select 'Got it' from test where b < c or a < b; <-- returns 'Got it'
select 'Got it' from test where not (b > c and a > b); <-- returns 'Got it'

Source : http://yong321.freeshell.org/computer/OracleNull.txt

Action Link for OBIEE

2009-06-17T11:38:00.003+02:00

Prerequisite : The security setup to reach OBIEE from E-Business Suite must be completed before creating Action Link (Action link screens enable to drill back into the transactional application screen from an Oracle BI request or dashboard).

Source : Metalink Note - 552735.1

In order to generate an Action Link you will first have to do the following:

Identify the Oracle E-Business Suite Application page/function that you want to link to. Obtain the function_id of that page and identify the querystring parameters required by that page. This will have to be done by going through Oracle E-Business Suite documentation.
Identify the Oracle E-Business Suite table that will support the parameters needed for the Oracle E-Business Suite function (page) that you want to build an Action link to and create this physical table in the Oracle E-Business Suite OLTP schema as an opaque view.

Here the OE_ORDER_HEADERS_ALL table was chosen because it is at the grain of the Order Header and supplies the HEADER_ID that we can use to join to the warehouse tables that contain the Order Header information. The function_id of the Oracle E-Business Suite page for Order Details and the parameters supported by that page were also identified from Oracle E-Business Suite documentation.

The Action Link URL is generated by calling the FND_RUN_FUNCTION.GET_RUN_FUNCTION_URL() function in the Oracle E-Business Suite Database Schema. For example:

SELECT HEADER_ID, fnd_run_function.get_run_function_url( CAST(fnd_function.get_function_id('ISC_ORDINF_DETAILS_PMV') AS NUMBER), CAST( VALUEOF(NQ_SESSION.OLTP_EBS_RESP_APPL_ID) AS NUMBER), CAST( VALUEOF(NQ_SESSION.OLTP_EBS_RESP_ID) AS NUMBER), CAST( VALUEOF(NQ_SESSION.OLTP_EBS_SEC_GROUP_ID) AS NUMBER), 'HeaderId='||HEADER_ID||'&pFunctionName=ISC_ORDINF_DETAILS_PMV&pMode=NO&pageFunctionName=ISC_ORDINF_DETAILS_PMV', NULL) as ORDER_HEADER_ACTION_LINK_URL FROM OE_ORDER_HEADERS_ALL

The parameters to the function are:

p_function_id in number,
p_resp_appl_id in number,
p_resp_id in number,
p_security_group_id in number,
p_parameters in varchar2 default null,
p_override_agent in varchar2 default null

Here p_function_id is the function_id of the page that you want to navigate to, the next three parameters pass the security context to Oracle E-Business Suite. The value of these session variables will be set by the initialization block described in sub-section "Creating Init Block for setting Oracle E-Business Suite Context". The fourth parameter is optional and is used if the page you are navigating to accepts parameters. In many cases if you want to navigate to a particular record on the page you are navigating to (Action Links typically do this), you will need to supply those querystring parameters here. The function call returns a URL to the desired function with encrypted querystring parameters.

The next step is joining this opaque view to the base fact table in the Data Warehouse schema. This join represents a join of tables in different database schemas and will therefore happen in the Oracle BI Server.

IMPORTANT: Ensure sufficient filters are applied when requesting any columns from this opaque view so that a small data set is returned to the Oracle BI Server to join with the results from the warehouse schema. For demo environments, where the EBS table contains only thousands of rows, filters can be ignored. However, customer implementations will typically contain millions of rows so appropriate filters are required.

Map into the logical and presentation layers

Map the URL column from the opaque view into the logical star where you want to create the Action Link. Then expose this logical column in the appropriate presentation catalog. Also ensure that the user who will be logging into Oracle E-Business Suite Applications is assigned access to the appropriate presentation catalogs. For example:

Oracle BI Answers Configuration

When including this Action Link column in a report, edit the column properties to indicate this is of type ‘Hyperlink’. That will automatically make this a clickable link in an Answers report. Further customization can be done to embed an image instead of the text.

Oracle BI Presentation Catalog Configuration

Ensure the user who will be logging into Oracle E-Business Suite Applications is set up in the presentation catalog as a user with the appropriate permissions. You can make the dashboard that you want to embed into Oracle E-Business Suite the default dashboard for that user. This will take the user directly to that dashboard when they click on the hyperlink in Oracle E-Business Suite.

Calling Bursting from XML Template

2009-05-18T11:00:00.003+02:00

With the standalone BI Publisher, we have useful Bursting tool in order to split and send the reports to relevant people via email. However, when you are using standalone BI Publisher, you may need to connect E-Business Suite and need to initialize the session for multi-org, VPD, etc. to get the relevant data. In order to do that, you will need to use report triggeres to cal a PLSQL wrapper to initialize the session. With the help of data templates, we can easily call PLSQL functions.

There are 3 steps you need to follow up to be successfull on calling PLSQL function;

1. Add a defaultPackage declaration to the Data Template definition, like -
" Version="1.0">
2. Create a package with above name and add a boolean function to it. For my work I gave the function name as beforeReport.
3. The parameters used in the Data Template should be declared (same name/datatype) in the package specification.

<datatemplate name="DATA" defaultpackage="XX_OBIEE_UTIL" version="1.0">
<dataquery>
<sqlstatement name="ROW">

</sqlstatement>
</dataquery>
<datastructure>
<group name="ROW" source="ROW">
<element name="RUNDATE" value="RUNDATE">
<element name="xxEMAIL" value="xxEMAIL">
<element name="CUSTOMER_NUMBER" value="CUSTOMER_NUMBER">
<element name="CUSTOMER_NAME" value="CUSTOMER_NAME">
<element name="PRODUCT" value="PRODUCT">
<element name="REVENUE_TYPE" value="REVENUE_TYPE">
<element name="REGION" value="REGION">
<element name="DM" value="DM">
<element name="CAE" value="CAE">
<element name="SECTOR" value="SECTOR">
<element name="CURRENT_NET_REVENUE" value="CURRENT_NET_REVENUE">
<element name="PREVIOUS_NET_REVENUE" value="PREVIOUS_NET_REVENUE">
<element name="VARIANCE" value="VARIANCE">
<datatrigger name="beforeReport" source="XX_OBIEE_UTIL.beforeReport()">
</datatrigger>
/datastructure></datatemplate>

XSL Formatting Objects

2009-03-19T11:26:00.001+01:00

When you design BI Publisher Template for the reports, you will need XSL Formatting Objects to make nice reports, like making the cell red as you can see from the picture. The following link will give you many formatting properties that you can make use of for your design.

XSL Formatting Objects

Math and XSLT

2009-02-27T11:36:00.000+01:00

The following link is for understanding the Math and XSLT that you may need to focus on when you build your BI Publisher templates.

Math and XSLT

DBMS_XMLGEN

2009-01-17T10:56:00.002+01:00

DBMS XMLGEN is a PL/SQL package that allows programmers to extract XML data from Oracle database tables. It might be useful when you need a XML file to create XML publisher layouts. Here are 2 examples on how to use it;

SELECT DBMS_XMLGEN.getXML('SELECT * FROM emp') FROM dual;

or
DECLARE
ctx DBMS_XMLGEN.ctxHandle;
xml CLOB;
BEGIN
ctx := dbms_xmlgen.newcontext('select * from emp');
dbms_xmlgen.setrowtag(ctx, 'MY-ROW-START-HERE');
xml := dbms_xmlgen.getxml(ctx);
dbms_output.put_line(substr(xml,1,255));
END;
/

MultiOrg setting for Concurrent Request in R12

2008-12-26T20:26:00.004+01:00

While you were upgrading to R12, if you will not implement MOAC, you should make relevant changes for Multi Org concept. In order your custom reports to be run properly, make them "Single" org to continue using without implementing MOAC.

Login into application with 'System Administration' responsibility
Click on 'Programs' under 'Concurrent' menu.
Query for short name of the concurrent program (i.e. XXSIPRNT_CA ).
Click on Update pencil icon of your program under result region
Under 'Update Concurrent Program' region, select Request tab
Under 'Request Setting' region, select 'Single' from the drop down of 'Operating Unit Mode' field
Save changes by clicking on 'Apply' button.

update FND_CONCURRENT_PROGRAMS
set multi_org_category='S'
where concurrent_program_id=45718;

Enabling OAF Personalization

2008-12-16T20:41:00.000+01:00

To enable Personalization:

FND: Diagnostics should be set to “Yes”. This will allow the “About this Page” link to be displayed on the OA pages.
Personalize Self-Service Defn should be set to “Yes”. This will allow the “Personalize Page” link to be displayed on the OA pages.
FND: Personalization Region Link Enabled should be set to “Yes”. This will allow the Personalize Region links to be displayed at the region level.
FND: Developer Mode = YES (Optional for debugging)

Use Functional Administrator -> Core Services -> Profiles to change this.

R12 and JRE 1.5.10

2008-10-26T20:16:00.000+01:00

In order to run R12, use JRE 1.5.10 which is located under the http:///OA_HTML/oaj2se.exe

After installing this, Internet Explorer and Firefox can run the R12 forms screens properly.

You may need to make adjustments on the Java Runtime settings in order your browser can use the proper version of Java. Check the following settings;

Settings -> Control Panel Java -> Java -> Java Application Runtime Settings

The "Show Log" feature in R12

2008-09-06T20:31:00.000+02:00

The "Show Log" feature is available only to the System Administrator responsibility by default. Access is controlled using Function Security. Users can be granted access by including one or more of the following submenus or functions on a user's responsibility:

Log Search: OAM_BF_SYSLOG_READ_ONLY_MENU (Log Search: Read Only) Menu
Log Delete: OAM_BF_SYSLOG_DELETE (Log Delete) function
Log Setup: OAM_BF_SYSLOG_CONFIG (Log Setup) function
Full access (Search/Configure/Delete): OAM_BF_SYSLOG_ALL_MENU (Log Search: All Functions) Menu.

NOTE: After making these changes please ensure that you bounce Apache.

OAF JDeveloper Version

2008-07-06T14:44:00.000+02:00

How to find the correct version of JDeveloper to use with E-Business Suite 11i or Release 12 - Use Metalink Note:416708.1

In order to find the information regarding your installation use the link of your EBusiness Installation;
http://host:port/OA_HTML/OAInfo.jsp

NOTE : Starting from 12.0.4 the page http://host:port/OA_HTML/OAInfo.jsp is not supported anymore, instead we need to login to Applications and select “About this Page” link. It will show us the OAF level and much more information.

Java class header Info

2008-06-26T17:47:00.000+02:00

Sometimes, we may need to find the version of the class file for OAF pages. The following shell commend will help to find out relevant information about class file;

strings -a ArCusOvrCO.class | grep '$Header'

and, the output will look like;

Timezone in APPS

2008-05-13T20:34:00.001+02:00

For time zone support to be activated, the system administrator must ensure that each of the following is true:

Oracle E-Business Suite version 11i10 or higher
Oracle RDBMS 9i or higher
The database must be configured to use the time zone file 'timezlrg.dat' rather than the 'timezone.dat' file
The database must be started in the standard corporate time zone
Profile 'Server Timezone' (SERVER_TIMEZONE_ID) must be set at the Site level, and must be set to the same standard corporate time zone as the database
Profile 'Client Timezone' (CLIENT_TIMEZONE_ID) must be set at the user level
Profile 'Enable Timezone Conversions' (ENABLE_TIMEZONE_CONVERSIONS) must be set to 'Yes' ('Y') at the Site level.
Profile 'Concurrent: Multiple Time Zones' (CONC_MULTI_TZ) should be set to 'No' (N) at the Site level

HRMS - fnd_session tip

2008-04-16T20:13:00.001+02:00

Almost all the HRMS views have a join to fnd_sessions table. When user changes their date-track value, it gets reflected in fnd_sessions table. Use the SQL below to create a default record for your SQL*Plus session;

INSERT INTO fnd_sessions
(session_id
,effective_date)
(SELECT userenv('sessionid')
,SYSDATE
FROM dual
WHERE NOT EXISTS (SELECT 'c'
FROM fnd_sessions s1
WHERE userenv('sessionid') = s1.session_id));

Getting the Dependencies

2008-03-26T20:10:00.000+01:00

This is a very easy task, in fact: Oracle maintains the relationships automatically and exposes them through three views. As such, we do not need to rely on reverse engineering DDL scripts.

USER_DEPENDENCIES returns the dependencies of the objects owned by the current user.
ALL_DEPENDENCIES returns the dependencies of the objects visible to the current user (so it's a superset of the former)
DBA_DEPENDENCIES returns the dependencies of the all database objects. It requires SYSDBA privileges.

The most useful fields are returned by the following query:

select name, type, referenced_owner, referenced_name, referenced_type
from user_dependencies

whose results can be read as "The object name of type type depends on the object referenced_name of type referenced_type owned by referenced_owner (as implied by the use of user_dependencies, the owner of the name object is the current user). The referenced_owner column is useful to filter out various Oracle objects such as DUAL.

APPEND into tables

2008-02-26T13:56:00.000+01:00

By using the APPEND hint, you ensure that Oracle always grabs "fresh" data blocks by raising the high-water-mark for the table. If you are doing parallel insert DML, the Append mode is the default and you don't need to specify an APPEND hint. Also, putting the table into NOLOGGING mode will allow Oracle to avoid almost all redo logging.

insert /*+ append */ into customer select (. . .) from customer_xx;

Selecting sample data from table

2008-01-21T19:52:00.001+01:00

Sometimes you may need to select a percentage of data from the table to create test data. The following statement will select approximately 0.2 percent of the data in the table;

select * from RA_CUSTOMER_TRX_ALL sample(0.2);

Commit, commit_form and forms_ddl

2008-01-06T08:04:00.000+01:00

commit
this is db command (only), not FORMS and if:

form has upPOSTed statements they may be ignored
form not fire PRE, POST, ON - commit triggers

commit execute as client side statement

forms_ddl(commit)

this is dynamic sql - commit and commit execute as server side. And form doesn't know about commit. it commits the all the DML transactions if any executed and pendig for commit .

commit_form

It fires commit for all the database blocks on the form
it commits the all the DML transactions if any executed and pendig for commit .

STRING_TO_DATE

2007-12-23T20:54:00.000+01:00

For the concurrent request parameters, if you are sending a VARCHAR2 type of parameter as a date variable, I would suggest to use STRING_TO_DATE function for conversion; See below;

g_DateUB := NVL(Fnd_Conc_Date.STRING_TO_DATE (p_dateUB), SYSDATE);

Call a pl/sql API from forms personalization

2007-11-26T20:57:00.001+01:00

To call a pl/sql API from forms personalization, do this

Personalize the form
Create an action of type "BuiltIn"
BuiltIn Type for Action should be "Execute a Procedure"
Argument should be as below

='declare
v_field_value VARCHAR2(200) ;
begin
xx_proc ('''||${item.BLOCKNAME.FIELDNAME.value}||''');
end'

Note the syntax, after =, entire declare begin end is within single quote. Also, there is no semi colon after "end"

You can pass field values as

'''||${item.BLOCKNAME.FIELDNAME.value}||'''

Utilities:Diagnostics

2007-09-26T20:36:00.000+02:00

Making "Profile – Utilities:Diagnostics" YES will give enduser the ability to use Diagnostic without entering the APPS Password.

FORMAT_ERROR_STACK

2007-08-26T20:52:00.002+02:00

Replace SQLERRM with the much better DBMS_UTILITY.format_error_stack. Now you will see the full error stack information -- but if the string exceeds 255 characters, you get an ERROR!

DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.format_error_stack);

FORMAT_ERROR_STACK

2007-08-26T20:52:00.001+02:00

Replace SQLERRM with the much better DBMS_UTILITY.format_error_stack. Now you will see the full error stack information -- but if the string exceeds 255 characters, you get an ERROR!

DBMS_OUTPUT.PUT_LINE (DBMS_UTILITY.format_error_stack);

MSCA Turkish

2007-02-20T14:50:00.000+01:00

After successful installation of Mobile Supply Chain Applications (MSCA) of Oracle Applications Release 11.5.10, we discovered Telnet client can not display characters for the Turkish language.

Telnet Protocol; Mobile Applications Framework's Presentation Manager Layer can send data to telnet clients in multi-byte. However, for telnet clients (whether it be a telnet client software on Microsoft Windows, Unix, etc. or a telnet client on a mobile device) to display characters for the specified language, system administrator's have to set the CHARACTER_SET parameter in their default_key.ini file (or whatever ini file they use for this in $MWA_TOP/secure directory) to the proper character encoding value.

For example, for West European character sets (like French ), CHARACTER_SET has to be set to WE8ISO8859P1. When this fails for you, try using the encoding scheme format for your characterset as referenced from java character codes:

For example, we tried using 'Cp1254' (case sensitive) for Turkish charactersets as the value in CHARACTER_SET.

Also check CLASSPATH. Some charactersets are available by default in the Basic Encoding set (rt.jar) file, while others are available in the Extended Encoding set (i18n.jar or characters.jar in the upper versions of Java). Therefore, CLASSPATH needs to include the correct file.

Tip : Oracle Home

2006-11-17T14:33:00.000+01:00

When you have several oracle_home, here is a way to find which is the oracle home for a particular instance:

1) get the pid of a background process:
ps -edf | grep [o]ra_smon_ORAPRD01
oracle 307694 1 0 Jan 24 - 1:43 ora_smon_ORAPRD01

2) get its current working directory from /proc:
ls -l /proc/307694/cwd
lr-x------ 2 oracle dba 0 Feb 28 16:00 /proc/307694/cwd -> /app/oracle/product/9.2.0/dbs/

Here oracle_home is /app/oracle/product/9.2.0

Tip : dbms_java

2006-11-17T14:29:00.000+01:00

By executing the following 2 statements in your session :

SET SERVEROUTPUT ON SIZE 1000000;
CALL DBMS_JAVA.SET_OUTPUT (1000000);

All the output of the System.out.println command in your stored procedures will be shown in your serveroutput. This gives you simple and fast way to debug your procedure like any other pl/sql procedure. It's jus like you would use the dbms_output.put_line proc.

Tip : 10g Problem

2006-05-13T14:30:00.000+02:00

When you start to work with 10g, you will notice some "new" behavior that you didn't expect.
After install on a unix environment you aren't able to run sqlplus, tnsping from sthat server if you aren't the user or if you don't belong to DBA group.

It looks like that Oracle is not able to get the securirity right.
Their solution is close it for everybody (as in the big, bad world) and your save.
Now you, as a DBA, has to open it for the normal (non DBA) users and now YOU are responsable!
That's an easy solution of Mr. Oracle.

You need to run $ORACLE_HOME/install/changePerm.sh to lift the restrictions.
Note some directories are kep to restrictive, you need to execute in the ORACLE_HOME:
$ chmod O+rx lib lib2 ldap ldap/admin srvm/lib32

(Or better: Checkout Metalink note 363721.1 and download patch 5087548.)

Another effect is that the CONNECT role is changed (is more restrictive in 10g).
Restoring the role CONNECT of 10gR2 as it was in 9i:

SQL> @$ORACLE_HOME/rdbms/admin/rstrconn.sql

Tip : Easy Connect String

2006-02-27T12:10:00.000+01:00

EZCONNECT - Easy Connect String
You can avoid having to use a TNSNAMES.ORA file by specifying the full connect string at the command prompt. The hostname is the only mandatory piece.

sqlplus username/password@ [//] hostname [:port] [/service_name]

Eg. sqlplus scott/tiger@10gserver:1521/ORCL

Ex :sqlplus scott/tiger@:1521/

Set up EZCONNECT in the sqlnet.ora file as follows:

NAMES.DIRECTORY_PATH = (TNSNAMES, EZCONNECT)

This gives us easy and secure way of connecting to databases from client machine without leaving the DB information in TNSNAMES.ORA

This information is taken from http://www.dba-village.com/

Oracle Firefox Search Plugins

2006-02-21T12:48:00.000+01:00

Mozilla Firefox

General Oracle Search Plugins

Install oracle-base-plugin - Searches the ORACLE-BASE.com site.
Install otn-plugin - Searches the Oracle Technology Network site.
Install oracle-metalink-plugin - Searches the Oracle Metalink site.

Oracle Error Search Plugins

Install oracle817-errors-plugin - Searches the Oracle 8.1.7 error messages manual.
Install oracle901-errors-plugin - Searches the Oracle 9.0.1 error messages manual.
Install oracle920-errors-plugin - Searches the Oracle 9.2.0 error messages manual.
Install oracle101-errors-plugin - Searches the Oracle 10.1.0 error messages manual.
Install oracle102-errors-plugin - Searches the Oracle 10.2.0 error messages manual.

Oracle Documentation Search Plugins

Install oracle817-docs-plugin - Searches the Oracle 8.1.7 manuals.
Install oracle901-docs-plugin - Searches the Oracle 9.0.1 manuals.
Install oracle920-docs-plugin - Searches the Oracle 9.2.0 manuals.
Install oracle101-docs-plugin - Searches the Oracle 10.1.0 manuals.
Install oracle102-docs-plugin - Searches the Oracle 10.2.0 manuals.

This information is taken from http://www.oracle-base.com

Standalone Reports Server on 10g R2 Developer

2006-02-14T15:10:00.000+01:00

If you tried to install report server as NT service in 10gAS R2(10.1.2.0.2) , when you ran following command at command prompt;

rwserver -install autostart=yes

you will get the error message saying that "Please consult the installation guides for how to setup and run this program"

Beginning with Oracle Reports 10g Release 2 (10.1.2), running Reports Server as a Windows service is no longer supported ( rwserver -install server_name). As a result, the related command line keywords INSTALL and UNINSTALL are also obsolete.

Start or stop a Reports Server registered with Oracle Enterprise Manager 10g only through Oracle Enterprise Manager 10g/OPMN. OPMN automatically restarts Reports Server if it stops responding for some reason. On Windows, OPMN itself is run as a Windows service. Start the Reports Server as a standalone server on Windows using the following command:

rwserver server=server_name

Add the BATCH command line keyword to start up the server without displaying dialog boxes or messages.

rwserver server=server_name batch=yes

J2EE vs .NET: Where Is Application Development Going?

2006-02-13T10:17:00.000+01:00

J2EE vs .NET: Where Is Application Development Going?
by Duncan Mills Outlines The Rise and Rise of the Meta-Framework

Where is application development going? What's the next cool thing? You may have answers to these questions, your answers may be the same or different to mine or anyone else's. The point is we just don't really know, and that's a problem. Saying to the manager of enterprise development shops "Oh yes just standardize on J2EE and everything will be fine" is not going to cut it. These folks are savvy enough to know that J2EE is a minefield of choice in standards and APIs. They need and deserve more direction than that. So you can make a suggestion as to a good set of technologies to use in a particular scenario - let's say Toplink, Lucene, Struts and JSP, as a random example - but of course there's a catch. You've just flagged a whole bunch of different technologies and APIs, each with a learning curve, each with different download locations or vendors and possibly conflicts in the APIs they consume. This is, I think, why .NET presses a lot of the right buttons. It's a Meta-Framework - a one stop shop. Say to a development manager you just have this one thing to do everything you need, and of course it's going to be attractive, irrespective of what the reality might be under the covers. There is no doubt that there are a lot of fantastic point solutions and frameworks out there in the J2EE world, but as standalone islands of functionality they have a much harder sell in the corporate market. If we look for frameworks that have been successful and widely adopted and examine them to see what gives them the edge what will we find? Take Struts for instance (love it or hate it). I'd be hard pressed to call Struts a meta-framework by my current thinking, but for it's time, it was. It wasn't just a collection of taglibs, that's what everyone was doing, it was taglibs plus a front controller, and it evolved to encompass validation as well. Struts became a worthwhile skill because with that one notch in you belt you can tackle a good chunk of an applications development.

What Defines a Meta-Framework today?

Broad Scope - the framework needs to cover everything from UI creation and page flow controller functionality to integration with multiple service providers including EJB, Web services, POJO and so on. It's not just a vertical slice through. Pluggability - the flexibility within the meta framework to incorporate choice into the stack. There is no reason at all that a meta-framework cannot encapsulate existing best of breed service providers, this is particularly true in an area like O/R mapping where I might want to use EJB, I might want to use a POJO based Toplink solution, or something totally new might come along. Just give me the choice (but feel free to offer best practice solutions).

Coexistence - Given that it's unlikely that a meta-framework will be able to implement everything itself it's going to be in turn a consumer of service frameworks - this is implicit in the pluggability argument. This in turn implies that the coupling between services within the framework has to be loose otherwise the pluggability dream cannot be fulfilled. Also, however, it imposes a degree of responsibility in the provider of the meta-framework.

Someone has to test all this stuff together, are there classloading problems for instance, do all these components share the same version of key APIs and so on. If you construct you own bespoke architecture this is something you'd have to worry about. If you consume a meta-framework one of the things you should be getting is this certification. Of course that might mean that components within a meta-framework are not absolute cutting edge within a specific genre, but do developers want cutting edge or do they want assured working?

Abstraction - where you have choice you need abstraction. If I want to swap out my O/R mapping layer I don't want to have to adopt a whole new set of APIs at the binding or transactional glue points. The meta framework needs to add value here, standards such as the common databinding proposed by JSR 227, are ideally placed to provide this type of plumbing.

I not dumb enough to suppose that swapping out is actually common within an individual application, but the point is the same skillset can be reused between projects or where a project has a heterogeneous set of providers. Abstraction generally is where meta-frameworks can add the most value because it leads into the next point - longevity.

Longevity - APIs change, this is a fact of life, Frameworks provide a level of abstraction on top of the base platform APIs and a degree of insulation from that change as a result. But frameworks change too with time, particularly active community driven ones. Meta-frameworks can add another layer of abstraction and programmer insulation on top of this shifting morass. You code to the Meta-framework and the plumbing in is handled for you, as the sands shift, the meta-framework adapts to that on your behalf. Can this work in reality? Well yes, certainly in the world of propriety frameworks we have environments like Oracle Forms which have persisted and evolved for almost 20 years, bringing code forward from VT terminals, through Windows GUIs to the Web, essentially unchanged, although perhaps enhanced as more capabilities appeared.

This then is the major carrot that meta-frameworks can offer to enterprise development shops - stability, but without stagnation. A meta-framework has to evolve and offer it's consumers the latest and greatest whilst at the same time maintaining a rock solid foundation for existing applications.

Tooling - Meta-frameworks will often be based around both coded abstraction layers and a large amount of in-metadata configuration. As such having tools support is an important part of the picture. Tooling can add yet another layer of abstraction through alternative visualizations such as diagramming or GUI screen designers. This helps with the whole future proofing issue..
But Why Now?

Why should you believe that meta-frameworks have any traction? We've not really seen any SOA-like marketing buzz around such frameworks, no great vendor splashes. Well, I think now is the time because it's happening in a stealthy and underhand way in any case. If we ignore the vendors for a second and just look at the standards and trends: What's the big trend at the moment? - well POJOs and IOC, think EJB 3.0, think Spring, you name it - loose coupling in other words.

I also think that the JavaServer Faces (JSF) standard is also a key player here. JSF offers an abstracted UI definition and event handling model that can be run across multiple devices. That's a large chunk of meta-framework right there. If I learn JSF I can code for mainstream browsers, handhelds and even industrial telnet devices with a single skill set. That works for me!

Where Are The Meta-Frameworks?

We've see that Microsoft can do it with .NET, but they have the luxury of almost total control. Are fully fledged meta-frameworks possible in the open standards J2EE space?

Well yes I think it can be done, many frameworks aspire, but most of those that do so do not have the scope of a true meta-framework. Maybe they only support one UI technology, or only allow EJBs for persistence. That's not good enough, a framework must be adaptable and willing to evolve to drink the soup du jour, but of course do that in a balanced and supportable way. To date I'd say that to varying degrees, the Oracle ADF framework and the Spring framework are closest in exhibiting most of the essential meta-framework attributes. Keel is also out there in this space but is lacking traction and is unlikely to be that attractive to large enterprises.

Meta-frameworks then, have the potential to offer exactly what the large enterprise shops need: a certified technology stack with the flexibility to meet the majority of requirements, and the promise of a lifetime that matches the application being built with it. I think it's inevitable that meta-frameworks are in the the domain of the commercial vendors (and I include in the grouping the vendors operating on a Service basis for open source as well as the paid-for-product vendors). Maintaining a meta-framework is a long term and expensive commitment, it's going to have to be paid for, either through license costs on the framework itself, or through support/service costs. It's also got to be backed by companies that stand a chance of being around for the required timescales.
The vendors though, are out there and ready to jump into this space. The meta-frameworks are coming...

Close all forms with one button

2006-02-12T12:00:00.000+01:00

When you have multiform application with several open Form modules they are organized within one MDI parent runform window. It's standard behavior on Win32 that when user press 'Close' button on MDI parent window all open child windows starts to close. However, pressing 'Close' button on MDI parent Runform window will cause only the current form to be closed. In order to achieve similar functionality to standard one in MDI Runform session to we need to have following code in every involved form module;

WHEN-NEW-FORM-INSTANCE trigger:

default_value('false','global.closing');

WHEN-WINDOW-ACTIVATED trigger:

if :global.closing='true' then

exit_form;

end if;

Now, it is up to Forms developer from where he/she triggers this closing process. A special toolbar button or menu item for this purpose can be used with code:

:global.closing := 'true';

exit_form;

and all open forms within the current Forms MDI Runform parent window starts to close.

Using the COPY and NAME_IN built-in

2006-02-10T11:57:00.000+01:00

Using the COPY and NAME_IN built-in functions in Forms isn't explained that well in the documentation. But they can be very useful in making your forms more generic by enabling you to build up field names dynamically and subsequently set and/or get the field values. In addition with the COPY function its possible to programatically insert non-numeric characters such as '%' into numeric fields, thus allowing wild-card searches to be performed.

NAME_IN

The name_in function allows you to get the value of a variable which itself is held as a variable name. Consider a form with two text fields on it - field1 and field2. Now into field1 enter the string 'Hello' and into field2 enter the string 'field1'. Now if you do a message(name_in(:field2)) it will display the string 'Hello'. As another example suppose you want to know what the value contained in the current form item is.
You look at the help and see that there is system variable called current_item. Great, I'll just message this out, however you'll soon discover that system.current_item is the name of the current item - not what it contains. To get at the value of the current item just enclose it within the name_in built-in - name_in(:system.current_item)

COPY

COPY is the complement of the NAME_IN function in that it allows you to set the value of a variable which itself is held as a variable. For example suppose you wish to set the value of the current item to the string 'Hello' - assuming the current item will be a text field. You can't simply do

:system.current_item:='Hello' ;

as Forms disallows this. But you can do a

COPY('Hello',:system.current_item);

Often you may have to dynamically create variables holding the name of fields on your form and set them to some value. Say you have 5 blocks - block1, block2 etc… all containing a text field of the same name - stock_id. To set the value of the stock_id field that the cursor is currently on you might use code like:-

COPY(' IBM',:system.current_block||'.stock_id');

One other use that COPY has is to place non-numeric characters into numeric fields programmatically during enter-query mode. Why would you want to do that? Mostly to allow the placing of wildcard characters such as '%'. You'll find that if you simply try:-

:num_field := '123%'

Forms will issue an error message

However doing COPY('123%',:num_field) works OK.

This only works during entry-query mode and you only need to use this if you have to enter the characters programmatically. You can just type in such characters normally if required.

Oracle Technical Interview Questions Answered

2006-02-10T11:08:00.000+01:00

1. Explain the difference between a hot backup and a cold backup and the benefits associated with each.

A hot backup is basically taking a backup of the database while it is still up and running and it must be in archive log mode. A cold backup is taking a backup of the database while it is shut down and does not require being in archive log mode. The benefit of taking a hot backup is that the database is still available for use while the backup is occurring and you can recover the database to any point in time. The benefit of taking a cold backup is that it is typically easier to administer the backup and recovery process. In addition, since you are taking cold backups the database does not require being in archive log mode and thus there will be a slight performance gain as the database is not cutting archive logs to disk.

2. You have just had to restore from backup and do not have any control files. How would you go about bringing up this database?

I would create a text based backup control file, stipulating where on disk all the data files where and then issue the recover command with the using backup control file clause.

3. How do you switch from an init.ora file to a spfile?

Issue the create spfile from pfile command.

4. Explain the difference between a data block, an extent and a segment.

A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

5. Give two examples of how you might determine the structure of the table DEPT.

Use the describe command or use the dbms_metadata.get_ddl package.

6. Where would you look for errors from the database engine?

In the alert log.

7. Compare and contrast TRUNCATE and DELETE for a table.

Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.

8. Give the reasoning behind using an index.

Faster access to data blocks in a table.

9. Give the two types of tables involved in producing a star schema and the type of data they hold.

Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.

10. . What type of index should you use on a fact table?

A Bitmap index.

11. Give two examples of referential integrity constraints.

A primary key and a foreign key.

12. A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?

Disable the foreign key constraint to the parent, drop the table, re-create the table, enable the foreign key constraint.

13. Explain the difference between ARCHIVELOG mode and NOARCHIVELOG mode and the benefits and disadvantages to each.

ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time. NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.

14. What command would you use to create a backup control file?

Alter database backup control file to trace.

15. Give the stages of instance startup to a usable state where normal users may access it.

STARTUP NOMOUNT - Instance startup

STARTUP MOUNT - The database is mounted

STARTUP OPEN - The database is opened

16. What column differentiates the V$ views to the GV$ views and how?

The INST_ID column which indicates the instance in a RAC environment the information came from.

17. How would you go about generating an EXPLAIN plan?

Create a plan table with utlxplan.sql.

Use the explain plan set statement_id = 'tst1' into plan_table for a SQL statement

Look at the explain plan with utlxplp.sql or utlxpls.sql

18. How would you go about increasing the buffer cache hit ratio?

Use the buffer cache advisory over a given workload and then query the v$db_cache_advice table. If a change was necessary then I would use the alter system set db_cache_size command.

19. Explain an ORA-01555

You get this error when you get a snapshot too old within rollback. It can usually be solved by increasing the undo retention or increasing the size of rollbacks. You should also look at the logic involved in the application getting the error message.

20. Explain the difference between $ORACLE_HOME and $ORACLE_BASE.

ORACLE_BASE is the root directory for oracle. ORACLE_HOME located beneath ORACLE_BASE is where the oracle products reside.

Well, we have gone through the first 25 questions as I would answer them during an interview. Please feel free to add your personal experiences to the answers as it will always improve the process and add your particular touch. As always remember these are "core" DBA questions and not necessarily related to the Oracle options that you may encounter in some interviews. Take a close look at the requirements for any job and try to come up with questions that the interviewer may ask. Next time we will tackle the rest of the questions. Until then, good luck with the process.

Solaris Logadm

2006-02-10T10:51:00.000+01:00

Solaris 9 has added a new command called logadm which is useful in managing Oracle log files as well as any other log files that are used on your system. I don't know whether other flavours of UNIX have similar commands or not.

The command is '/usr/sbin/logadm'. The default configuration file is '/etc/logadm.conf', but a user configuration file can be specified.

The way I have used this is to create a cron job as below. This uses an Oracle specific config file.
====================
0 01 * * * "/usr/sbin/logadm -f /opt/apps/oracle/utils/logadm/logadm.conf"
====================

Then I created the config file as below. The configuration file must be left writable, as the logadm updates the command with the -P option to keep track of last processed timestamps
====================
#
/oracle/admin/GANDALF/bdump/alert_GANDALF1.log -C 3 -c -p 1w -z 1
/oracle/product/10.1.0/db/network/log/listener.log -C 3 -c -p 1w -z 1
/oracle/product/10.1.0/db/network/log/sqlnet.log -C 3 -c -p 1w -z 1
====================
Using the switch '-c' means that 'logadm' is able to process the listener.ora file without problems due to locking.

For further details consult the 'logadm' man page.

Rollback Segments

2006-02-09T16:11:00.000+01:00

Each time Oracle makes a change to schema data it records the information required to undo that change in a special type of database area called a rollback segment. This information is always kept at least until the transaction making the change has committed, but as soon as the transaction is complete its rollback or undo data can be overwritten. How soon this happens depends on how much undo space is available and how quickly current and future transactions create new undo records. Within a few seconds, or minutes, or hours the undo information will be overwritten or, in some cases, simply discarded. Since the introduction of Oracle Version 6 in 1988 the allocation of rollback segment space has been a major concern for Oracle DBA's who have had to decide both how many rollback segments an instance should have and how large each one should be. Resolving this issue has typically required a number of compromises that are outside the scope of this post.

Oracle9i supports the traditional rollback segment management features that have evolved over the past 13 years, but also introduces Automatic Undo Management. In this mode the DBA only has to create an "undo tablespace", tell Oracle to use this tablespace, and specify for how many seconds each undo record must be retained. The records will, of course, be kept for longer if the transaction that creates them does not commit within the time interval. In Oracle9i the following three instance parameters will guarantee that all undo entries will remain available for 15 minutes:

undo_management = AUTO
undo_retention = 900 # seconds
undo_tablespace = UNDOTBS

However a potentially unwanted side effect is that the Oracle server will not retain the data for much longer than the time specified even if the instance is running with a relatively light updating load i.e. even if there is no great demand to write new undo information. This contrasts markedly with traditional rollback segment management, where under light updating loads undo entries could (and would) remain available for several hours to generate read consistent data sometimes required by long running reports. Fortunately the instance parameter undo_retention can be altered dynamically using alter system set and this may become necessary at sites which have long report runs take place and cannot completely prevent update from occurring while these reports are running.

ADF JClient: Creating monolithic jar files to run JClient applications

2006-02-08T12:45:00.000+01:00

The following information will save mylife :) I do not know where I got it (therefore sorry for not referencing it), but it would be useful if you think to make your code as an standalone jar file, having lots of different types of external libraries.

Within JDeveloper 10g it doesn't seem to be obvious on how to deploy a JClient application within a monolithic Java archive file that then can be used to run the JClient application on the client using java -jar The assumption that is made by the default deployment setting is that all dependency libraries are part of the classpath on the deployment platform, which means that only the application specific classes need to be deployed in the application's archive file. This assumption makes sense if you have more than one ADF JClient application that runs on the local client. To deploy JClient application so they run stand alone out of a jar file, with no additional setup required on the client machine, you need to add all the dependency classes to the application deployment jar, which quickly can become 17 MB in size due to this. Do as follows:

1) In the ADF JClient project, create a new JAR File deployment profile.

2) In the profile settings, for the JAR options, specify the name of the runnable class (to make a runnable jar file)

3) Create a new "Dependency Analysis" File group.

4) Select all the libraries from the Libraries tab on the Contributors page.

5) Make sure "Include Contents in Output" is checked. Otherwise the jar files of this libraries will be added, which is of no help at runtime.

6) Deploy the application to the jar file

7) Run the jar file, e.g: java -jar archive1.jar

Helpful hints for Designer

2006-02-06T11:03:00.000+01:00

Process Modeler

To display the Flow Name automatically, right click in the swim lane, select Customize Graphics, and click the Display Flow Name (On Create) checkbox.
To increase/decrease the width of the swim lane, select the organizational unit, press shift + down arrow or shift + up arrow respectively.
To change a process to a decision point and denote by using a diamond, right click in the swim lane, select customize graphics, select Decision Point from the drop down list, select Enhanced Symbol from the Mode group box. You will need to change each Process Modeler Diagram that will have a decision point.
After a process, etc. is created, it is automatically added to the repository even if you have not saved the diagram. Therefore, if you make a mistake and the process, etc. should not be included, you must Delete it from the repository. If you Cut a process, etc. from a diagram, this merely removes it from the diagram, it does not delete it from the repository. Be careful how you use Cut and Delete.

Entity Relationship Diagram

If your diagram is large and you would like to view only the entities and their relationships, you can opt not to display the attributes by selecting, Options> Customize from the menu bar. Click the Attributes checkbox in the View group box to remove the checkmark.
When creating a relationship, to straighten the line between them, click the line and then use the up and down arrows or left and right arrows depending upon if the line is vertical or horizontal. Another option is to select Options>Customize from the menu bar and click the Snap checkbox in the Grid group box. You can also display the grid by clicking the Display checkbox in the Grid group box.
To improve the readability and understanding of your relationships, you may need to create dog-legs or angled lines. After the initial relationship is created between two entities, you can click on intermediate points to create angled lines. To get the desired angle, you hold down the shift key and click the middle of the line to create a drawing point. You can then drag this drawing point to the desired spot. To remove the drawing point, press the shift key and click on the point again.

Repository Object Navigator (RON)

To insert documents such as Word or email, from the RON navigator expand Reference Data Definition heading, highlight Documents and click the + (Create Object) button on the left side. The Document Properties window will open. Enter a document name, author, type, comment, and other information as necessary. Under the Documentation heading, single click the yellow icon located to the left of Document Text, a text pad window opens. Type information or copy and paste from another document and then save it.

Repository Reports

To generate a report with an html format that can be viewed by a client or non-Designer type person via a browser, select the required report in the navigator and then the Parameters Palette window will open. Set the Destination Type to be File, enter the LAN path into the Destination Name with .htm as the extension, change the Destination Format to html, and the Mode should be bitmap. Run the report.

Setup UNIX Sendmail to Access SMTP Gateway

2006-02-03T10:56:00.000+01:00

The steps below are relevant to Sun SOLARIS Servers running Solaris 2.6 or 2.8, consult your System Administration manual for details on how to perform this for other hardware vendors / operating systems.

1. If the sendmail daemon is currently running on your system, terminate it with the following command:

/etc/init.d/sendmail stop

2. Copy /etc/mail/main.cf to /etc/mail/sendmail.cf

3. Edit /etc/hosts and place an entry here for the SMTP gateway machine, e.g:

1.2.3.4 mailhost

4. To test connectivity to the SMTP machine called mailhost, enter the following command:

telnet mailhost 25

This will initiate a telnet session with the mailhost machine on port 25, which is the port that the SMTP daemon listens for incoming messages.

5. Edit the /etc/mail/sendmail.cf file and edit the following entries:

Change Dmsmartuucp to Dmether

This changes the mailer program for remote mail delivery from uucp to the smtp mailer.

Change DR ddn-gateway to DR mailhost

Change CR ddn-gateway to CR mailhost

This changes the behavior of the sendmail daemon to route all remote mail generated from this server to be directed at the SMTP host you defined in /etc/hosts

6. Save the sendmail.cf configuration file

7. Start the sendmail daemon by issuing the following command:

/etc/init.d/sendmail start

8. Run $POM_TOP/bin/MasterScript.sh stop

9. Add /usr/lib to the PATH in the .profile file and activate it

10. Run $POM_TOP/bin/MasterScript.sh start apps/apps

tip : dbms_random

2006-02-01T13:00:00.000+01:00

SELECT dbms_random.string('U', 2)||TRUNC(dbms_random.VALUE(1000, 9999))
FROM dual;

Output : au3910

SELECT dbms_random.string('U', 2)||TRUNC(dbms_random.VALUE(1, 9))||
dbms_random.string('U', 2)||TRUNC(dbms_random.VALUE(1,9))||
dbms_random.string('U', 2)
FROM dual;

Output : AI7KU4EE

Meaning of Oracle Release Numbers

2006-01-30T16:48:00.000+01:00

Have you know what is the meaning of oracle 9.2.0.1.0 or 9.2.0.4.0?

Oracle release numbers consist of 5 digits - for example Oracle9i.9.2.0.1.0 means First digit is the major version number (8), second is the release number (1), third is the maintenance release number (7), fourth is the generic patch number (3) and fifth is the platform specific patch set number (2).

Report changes from alert.log file (unix server only)

2006-01-29T16:17:00.000+01:00

In the morning yo want to check the alert.log file, but only for the changes. There is some work required to determine when udid you last time look at the file, where to start reading etc. Also on Monday you want to report since Friday etc.

The following commands will extract only the changes since the last time you have run the report:

# get the log filename
log=$ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_${ORACLE_SID}.log

if [[ -f $log.last ]] then
#get the last reported line
today=`cat $log.last`
else
#find the last date in the file
lastdt=`tail $log|grep :|grep 200|tail -1|cut -c-11`

#get the first occurrence of that date
today=`grep -n "$lastdt" $log|head -1|tr ':' '\012'|head -1`
fi

#remember the last line:

echo `wc -l $log`>$log.last

#today's file
newlog=/tmp/mon_alert_${ORACLE_SID}.log

#extract only today's data
sed -n $today,'$'p $log|tr '\09' ' '>$newlog

# the rest is up to you.

Alternatively, you could run a cron job to get the number of lines:

# get the log filename
log=$ORACLE_BASE/admin/$ORACLE_SID/bdump/alert_${ORACLE_SID}.log
echo `wc -l $log`>$log.last

#In the morning you just report data from the file:
#today's file
newlog=/tmp/mon_alert_${ORACLE_SID}.log

#extract only today's data
sed -n $today,'$'p $log|tr '\09' ' '>$newlog

Look for anything of interest in the $newlog file

Unix Core Files

2006-01-20T16:20:00.000+01:00

When you are running a program on a UNIX machine that terminates abnormally, possibly because it crashed, the operating system creates a core file in the current working directory. The system dumps information into the core file about what the program was doing at the time it crashed, e.g., which subroutine it was calling or what memory address it was accessing. The format of the information in this file is pretty cryptic, but the file can be post-processed by an appropriate debugging tool to give application developers useful information. If you are not trying to diagnose a bug in an application, the file contains no other useful information and can be safely deleted. In fact, it should be deleted because often it is quite large (>100 MB).

How can you prevent the core file from getting so large? One option is to create a directory named core in your current working directory. When the application crashes, the operating system cannot write the core file because a directory with the name already exists. A second option is to set a limit on how big the core file is allowed to grow using the command

limit coredumpize xxxx

xxxx is the maximum size in kB that the core file is allowed to be. In theory, the core file will never exceed the limit. However, in practice, some operating systems ignore it. This command has been incorporated into the standard NML .cshrc file.

oraenv and coraenv Utilities

2006-01-17T16:03:00.000+01:00

The oraenv and coraenv utilities both aid in setting the Oracle environment on UNIX systems (other utilities exist on Windows platform that enable the Oracle Home to be set.) The coraenv utility is appropriate for the UNIX C Shell; oraenv should be used with either the Bourne or Korn shells.

Database operations require the ORACLE_HOME to be set before the user may access the database. If ORACLE_HOME is not set, commands such as sqlplus, exp, or any other utility for that matter, will not be found.

Both utilities are shell scripts that do the same thing in the different UNIX shells. They will prompt for a SID of the database unless ORAENV_ASK is set to N. The utility will also append the ORACLE_HOME value to the path, marking the location of the utility.

The oraenv command will prompt for the SID of the database that you wish $ORACLE_HOME to access.

$ . oraenv
ORACLE_SID = [] ? ASG920

The dbhome utility can now be used to verify that $ORACLE_HOME is correct.

$ dbhome
/usr/oracle/9.2.0

The “dot space” part of the command is required to make the environment change with the parent shell, as opposed to entering a command without it which would only affect the subshell running that process.

These commands can be used to avoid specifying the network service name when issuing commands. For instance, without using oraenv, an sqlplus command would look like:

$ sqlplus system/manager@nameofservice as sysdba

whereas after oraenv has been executed, the following command would work:

$ sqlplus system/manager as sysdba

Starting and Stopping on Windows

2006-01-14T16:05:00.000+01:00

The dbstart and dbstop shell scripts do not exist on Windows platforms. Consequently Oracle database startup and shutdown is implemented completely differently. The oradim utility is used on the Windows platform to perform these tasks.

C:\oracle9i\bin\oradim -startup -sid ORCL92 –usrpwd manager
-starttype SRVC,INST -pfile C:\oracle9i\admin\ORCL92\pfile\init.ora

startup — Indicates that the specified instance should be started.
sid — The SID of the database to start.
usrpwd — The password for the database user.
starttype — Specifies whether to start the instance, the service, or both (SRVC, INST).

The following command can be used to shutdown the instance with oradim:

C:\oracle9i\bin\oradim -shutdown -sid ORCL92 -shutttype SRVC,INST
–shutmode A

Notice that no password is needed to perform this task.

The shuttype parameter specifies what is to be stopped – the service (SRVC), the instance (INST), or both (SRVC, INST). The shutmode specifies the method that should perform the shutdown – (A)bort, (I)mmediate, or (N)ormal.

Each operation, regardless of success, is logged in the oradim log file (ORACLE_HOME\database\OraDim.Log). This file should be checked for errors after each oradim command is executed.

The oradim utility provides more than just the ability to start and stop Windows databases. oradim can create and edit databases. It also allows DBAs to configure script-based installation mechanisms, bypassing the Oracle Database Configuration Assistant’s graphical user interface (GUI).

For a reference of all oradim commands, use the oradim–help command.

Automatic Startup & Shutdown

2006-01-12T20:01:00.000+01:00

The following list summarizes the functions performed by the different Oracle startup and shutdown scripts (which are invoked during system startup and shutdown, respectively).

Script	Description
$ORACLE_HOME/bin/dbstart	Ensures a clean startup of database instance(s), even after system failure
$ORACLE_HOME/bin/dbshut	Ensures a clean shutdown for database instances
/var/opt/oracle/oratab	Contains a field that specifies whether a particular database instance should be brought up/down at system startup /shutdown time by specifying "Y" in this field, the "dbstart" and "dbshut" scripts bring this database and instance up or down.
/etc/inittab		Controls the initialization process

Relevant Files	Description
/etc/init.d/dbstart	Script to call $ORACLE_HOME/bin/dbstart
/etc/init.d/dbshut	Script to call $ORACLE_HOME/bin/dbshut
/etc/rc2.d/S99dbstart	Link to the script /etc/init.d/dbstart
/etc/rc0.d/K01dbshut	Link to the script /etc/init.d/dbshut
$ORACLE_HOME/lsnr_start.sh	Script to start listener
$ORACLE_HOME/lsnr_stop.sh	Script to stop listener

System V initialization scripts are contained in /etc/rc.d directories where "n" is the run-level value of the script. A run-level of 0 usually signifies power shutdown mode, while a run-level of 2 signifies multi-user mode. The directories contain initialization scripts such as "S75cron" and "K30tcp". These scripts are named using the following method:

[K or S][two-digit number][descriptive filename]

Names starting with "S" indicate scripts that are called at startup; names starting with "K" indicate scripts that are called at shutdown time.

Scripts containing larger numbers in their names are executed after those with lower numbers. Oracle startup scripts typically contain larger numbers in their names, such as "S99oracle", indicating that the script should be run after the system has been started up. Oracle shutdown script names, on the other hand, usually contain smaller numbers, such as "K01oracle" indicating that the script should be run before system shutdown.

Every Oracle installation has an 'oratab' file which contains an entry for every database in the system. You will need 'root' privileges to accomplish the following:

STEPS

1. Edit the 'oratab' file (/var/opt/oracle/oratab):

mlive:/export/home/app/oracle/product/8.1.6:Y

2. As oracle user, create the following shell script files “lsnr_start.sh” and “lsnr_stop.sh” in the $ORACLE_HOME directory and ensure that both files are executable by the oracle userid. Since all files “lsnrctl” needs are not in the default directory, those scripts that include the needed information have to be used to start listener:

lsnr_start :

PATH=/export/home/app/oracle/product/9.2.0.1.0/bin:.;

export PATH

ORATAB=/var/opt/oracle/oratab;

export ORATAB

ORACLE_HOME=/export/home/app/oracle/product/9.2.0.1.0;

export ORACLE_HOME

lsnrctl start

lsnr_stop :

PATH=/export/home/app/oracle/product/9.2.0.1.0/bin:.;

export PATH

ORATAB=/var/opt/oracle/oratab;

export ORATAB

ORACLE_HOME=/export/home/app/oracle/product/9.2.0.1.0;

export ORACLE_HOME

lsnrctl stop

3. As root, create the following OS script files dbstart and dbshut in the /etc/init.d directory and ensure that both files are executable by the oracle userid:

dbstart :

su oracle -c /export/home/app/oracle/product/9.2.0.1.0/bin/dbstart

su oracle -c /export/home/app/oracle/product/9.2.0.1.0/lsnr_start.sh

dbshut :

su oracle -c /export/home/app/oracle/product/9.2.0.1.0/bin/dbshut

su oracle -c /export/home/app/oracle/product/9.2.0.1.0/lsnr_stop.sh

4. As a root, link the dbshut and dbstart to /etc/rc*.d directory:

# ln -s /etc/init.d/dbshut /etc/rc0.d/K01dbshut

# ln -s /etc/init.d/dbstart /etc/rc2.d/S99dbstart

Relevant Oracle Metalink Documents

1005041.6 How to Automatically Start the Listener On UNIX

61333.1 Automatic startup and shutdown of oracle instances

105957.1 How to setup Oracle to startup automatically if system is restarted

1068670.6 Listener will not start autostart at boot time

1031930.6 Solaris RC&RC.D startup&shutdown scripts

91815.1 The Annotated dbstart Script

Availability of the DB and listener

2006-01-05T20:10:00.000+01:00

Under the “/export/tmscs/db_cron” folder, “db_check.sh” script seen below is created.

db_check.sh

#!/bin/sh
#
############################
# FUNCTION: IsListenerRunning
############################
#
IsListenerRunning()
{
ps -ef | egrep '/bin/tnslsnr' | egrep -v 'grep' > /dev/null 2>&1
}

############################

# FUNCTION: IsListenerUp

############################

#

IsListenerUp()

{

lsnrctl status > /dev/null 2>&1

}

############################

# MAIN PROGRAM

############################

#

CHECK_LISTENER=1

CHECK_LISTENER_IS_UP=1

SPOOLFILE=/export/tmscs/db_cron/db.log

#

# check if database is running

#

if [ `echo "select dummy||'OK' from dual;" | sqlplus -s system/manager | grep XOK` ]

then

IS_OK="XOK"

else

IS_OK="FALSE"

fi

#

# check if listener is running

#

IsListenerRunning || CHECK_LISTENER=0

#

# check if listener is up#

IsListenerUp || CHECK_LISTENER_IS_UP=0

date +"Date is %D %nTime is %T" > $SPOOLFILE

if [ $IS_OK = "XOK" ]

then

echo "SELECT 'Database has been up and running for '|| round(24*(SYSDATE-logon_time),1) || ' hour(s)' FROM sys.V_\$SESSION WHERE sid=1;" | sqlplus

-s system/manager | grep running >> $SPOOLFILE

else

echo "Database is down" >> $SPOOLFILE

fi

if [ $CHECK_LISTENER = 0 ]

then

echo "Listener is not Running" >> $SPOOLFILE

fi

if [ $CHECK_LISTENER_IS_UP = 0 ]

then

echo "Listener is down" >> $SPOOLFILE

lsnrctl start && echo "Listener is started succesfully" >> $SPOOLFILE

else

echo "Listener is up" >> $SPOOLFILE

fi
If listener is not running, script will start it. But if database is down, script will do nothing.

The crontab jobs executing this script is created under the user ROOT, and since ROOT user has already several crontab jobs, the new job line is added to the crontab file and then this file is submitted to the system (/var/spool/cron/crontabs/root);

15,30,45,01 * * * * su - oracle -c "/export/tmscs/db_cron/db_check.sh" > "/export/tmscs/db_cron/err.log"

And then to submit the file;

crontab crontab_filename

Essentially, root is the super user and can thus impersonate oracle without a password. su oracle will enable root to become oracle. The -c means 'execute the following command as oracle'. The extra '-' between su and oracle is crucial - because of this, the script is being executed as if the user oracle had logged in himself. In other words all environment variables like $ORACLE_HOME, $ORACLE_SID and $PATH are set correctly.

This job runs every fifteen minutes.

After running the script, the crontab job will produce 2 files (db.log and err.log) under the folder “/export/tmscs/db_cron”

db.log

It gives information about database and listener.

If database and listener are running;

Date is 01/07/03

Time is 10:12:57

Database has been up and running for 119.4 hour(s)

Listener is up

If database is down and listener is running;

Date is 01/07/03

Time is 13:53:43

Database is down

Listener is up

If database is up and listener is down;

Date is 01/07/03

Time is 13:54:37

Database has been up and running for 0 hour(s)

Listener is up

If both database and listener is down;

Date is 01/07/03

Time is 13:45:01

Database is down

Listener is not Running

Listener is down

err.log

$ more err.log

PATH is /usr/bin:

PATH is

/export/home/app/oracle/product/

9.2.0.1.0/bin:.:/usr/bin:.:/usr/local/bin

Sun Microsystems Inc. SunOS 5.7 Generic October 1998

You have mail.

Running Oracle .login ...

It shows the log of last process and if an error occurs, what the error is can be seen in this file;

Note that when the folder (/export/tmscs/db_cron) is changed both crontab and db_check.sh script files must be corrected.

Configuration :

Variable SPOOLFILE in db_check.sh file must be configured (now SPOOLFILE=/export/tmscs/db_cron/db.log)
Given Password for system user in db_check.sh file must be corrected (now system/manager)
Given path for db_clean.sql and err.log files must be corrected (now /export/tmscs/db_cron/)
All created scripts and folders must be owned by oracle:dba with the rights of “711”

Server and Database Naming

2006-01-05T19:48:00.000+01:00

As servers and databases are deployed for global applications, it becomes critical that server and database names are standardized globally. This document provides the guidelines that should be followed to name servers and databases for global applications. The naming conventions detailed in this document are provided as a formula. Certain components of the formula have been standardized so that they can be utilized very easily. However, there will be exceptions where the naming convention cannot be applied to a global application. It is then up to the GEOs to get together and agree on a naming convention that works for the specific application and that can be implemented on a global basis.

Server Naming Formula: application + function . domain

Database Naming Formula: application + function + geo specification

The following tables provide the recommended abbreviation standards to be used in naming servers and databases for global applications.

Examples :

FUNCTION ABBREVIATION
Disaster Recovery database DR
Disaster Recovery server DR or DBDR
Test database TST
Test server TST or TEST
Development DEV
Training TRN
Release REL
Test Reporting TRP
DBnet (off SWAN) DBX
Middle tier MT Middle tier (partner) MTP
Web server WS

GEO DOMAIN
APAC singapore; japan
EMEA holland; turkey
Americas central; east

Notes:

Oracle database names (SIDs) typically should be uppercase.
There is no hard limit to the length of a server name. It is recommended that server names no more than 8-14 characters, if possible.
Server names typically should be lower case.
Server names can include a hyphen to separate the application and function components of the server name. However, it has been discovered that a hyphen in the server name causes a problem when the Oracle database init.ora parameter global_names is set to TRUE and the db_domain parameter is set to the server name. To avoid this problem, do not use a hyphen in the server name.
The domain component of the server name will always be separated with a period ('.') from the application and function components.

Oracle database names (SIDs) are limited to 8 charactersApplication component of Oracle database name can be up to 3 characters

Application component of Oracle database name can be up to 3 characters
Function component of Oracle database name can be up to 3 characters
GEO specification is limited to 2 characters

Database Interview Questions

2005-12-29T10:55:00.000+01:00

   What are the different types of joins?

   Explain normalization with examples.

   What cursor type do you use to retrieve multiple record sets?

   Difference between a "where" clause and a "having" clause

   What is the difference between "procedure" and "function"?

   How will you copy the structure of a table without copying the data?

   How to find out the database name from SQL*PLUS command prompt?

   Takeoffs with having indexes

   Talk about "Exception Handling" in PL/SQL?

   What is the difference between "NULL in C" and "NULL in Oracle?"

   What is Pro*C? What is OCI?

   Give some examples of Analytical functions.

   What is the difference between "translate" and "replace"?

   What is DYNAMIC SQL method 4?

   How to remove duplicate records from a table?

   What is the use of analyzing the tables?

   How to run SQL script from a Unix Shell?

   What is a "transaction"? Why are they necessary?

   Explain Normalization and Demoralization with examples.

   When do you get constraint violation? What are the types of constraints?

   How to convert RAW data type into TEXT?

   Difference - Primary Key and Aggregate Key

   How functional dependency is related to database table design?

   What is a "trigger"?

   Why can a "group by" or "order by" clause be expensive to process?

   What are "HINTS"? What is "index covering" of a query?

   What is a VIEW? How to get script for a view?

   What are the Large object types supported by Oracle?

   What is SQL*Loader?

   Difference between "VARCHAR" and "VARCHAR2" data types.

   What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table?

   Difference between "ORACLE" and "MICROSOFT ACCESS" databases.

   How to create a database link?

A Brief History of Oracle

2005-12-06T13:13:00.000+01:00

Way back in June 1970, Dr E F Codd published a paper entitled A Relational Model of Data for Large Shared Data Banks. This relational model, sponsored by IBM, then came to be accepted as the definitive model for relational database management systems – RDBMS. The language developed by IBM to manipulate the data stored within Codd's model was originally called Structured English Query Language, or SEQUEL, with the word 'English' later being dropped in favor Structured Query Language – SQL.

In 1979 a company called Relational Software, Inc. released the first commercially available implementation of SQL. Relational Software later came to be known as Oracle Corporation.

Today, the Oracle DBMS is supported on over 80 different operating environments, ranging from IBM mainframes, DEC VAX minicomputers, UNIX-based minicomputers, Windows NT and several proprietary hardware-operating system platforms, and is clearly the world's largest RDBMS vendor.

Oracle employs more than 42,000 professionals in 93 countries around the world. Their expenditure for research and development is approximately 13% of their revenues.

Lawrence Joseph (Larry) Ellison (Born: 1944, Chicago) is president and CEO of Oracle corporation.

He's the Oracle worlds hero, and he should be. Oracle Corporation, the company he founded with Robert N. (Bob) Miner and Edward A. (Ed) Oates back in 1977, has emerged as the world's largest vendor of software that helps large corporations and governments better manage their information.

(Below is taken from ORAFAQ)

So, who is Scott?

Bruce Scott was one of the first employees at Oracle (then Software Development Laboratories). He co-founded Gupta Technology (now known as Centura Software) in 1984 with Umang Gupta, and later became CEO and founder of PointBase, Inc.

Bruce was co-author and co-architect of Oracle V1, V2 and V3. The SCOTT schema (EMP and DEPT tables), with password TIGER, was created by him. Tiger was the name of his cat.

Where did the word Oracle originate from?

The word Oracle means:

"Prophecy or prediction; answer to a question believed to come from the gods; a statement believed to be infallible and authoritative; a shrine at which these answers are given"

There is, however, more to the word Oracle: used for the name of the database engine, and then later for the company itself. Larry Ellison and Bob Miner were working on a consulting project for the CIA (Central Intelligence Agency in USA) where the CIA wanted to use this new SQL language that IBM had written a white paper about. The code name for the project was Oracle (the CIA saw this as the system to give all answers to all questions or something such ;-).

The project eventually died (of sorts) but Larry and Bob saw the opportunity to take what they had started and market it. So they used that project's codename of Oracle to name their new RDBMS engine. Funny thing is, that one of Oracle's first customers was the CIA...

How fast is the Oracle database?

The standard Oracle license agreement normally prevents users from publishing benchmark results. The best source for performance related data is the Transaction Processing Performance Council (TPC). Visit their Home Page for database benchmark results.

Start

2005-11-30T10:19:00.000+01:00

This blog totaly focuses on Oracle Technologies (Database, APPS, Developer, JDeveloper, . . .). I am gonna try to write all tips, problems, ideas I face with. Also there will be various articles, quotation gathered from other oracle releated sites (by referencing them). Hope, this blog will be used as a reference among all oracle lovers like me:) I am also waiting for your cooperations, experts.

Love Oracle . . .