A) CREATE STAGING TABLE
First Create the staging table which will hold sql profiles to be moved over.
SQL> exec DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF (table_name=>’SQL_STG_TAB’,schema_name=>’SYS’);
(This will create a table SQL_STG_TAB, which will be able to hold the required sql profiles, the structure of this table is exactly same as that of DBA_SQL_PROFILES table)
Leave a CommentOracle Database supports the export and import of SQL plan baselines using the Oracle Data Pump Import and Export utilities. Use the DBMS_SPM package to define a staging table, which you can use to pack and unpack SQL plan baselines.
To import a set of SQL plan baselines from one system to another:
10 CommentsSometime DBAs need to create SQL Baseline for particular SQL_ID for plan stabilization
1) VERIFY CURRENTLY EXISTING SQL PLAN BASELINES
select * from dba_sql_plan_baselines;
7 Comments
Below are some of the simple but frequently used database tablespace related queries used by DBAs:
Check Temporary tablespace temp files size
set lines 132 col FILE_NAME format a60 select TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024,AUTOEXTENSIBLE,MAXBYTES/1024/1024 from dba_temp_files;Leave a Comment
Sometimes it is required to flush the BAD SQL_PLAN from shared_pool so that new (or old) better execution plan can be picked by SQL_ID
1) FIND ADDRESS AND HASH_VALUE OF SQL_ID
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like '7yc%'; ADDRESS HASH_VALUE ---------------- ---------- 000000085FD77CF0 808321886
5 Comments
Finding the SQL_ID for a SQL Statement is straight forward task. You can use query like below to find the SQL_ID
SQL> SELECT sql_id, hash_value, SUBSTR(sql_text,1,40) Text FROM v$sql WHERE sql_text LIKE '%&An_Identifiable_String%';
To Determine the SQL_ID of a SQL Statement in a PL/SQL Block below steps can be followed:
2 CommentsOracle provides a utility called FNDCPASS that allows you to change passwords in batch mode. It is highly advisable to back up the FND_ORACLE_USERID table…
2 CommentsCouple of Oracle Application Concurrent Manager related scripts which we use frequently. A) UNDER WHICH MANAGER THE REQUEST WAS RUN SELECT b.user_concurrent_queue_name FROM fnd_concurrent_processes a…
2 CommentsBelow are some of the useful scripts related to Oracle Applications database
–FIND DATABASE SIZE
select 'DATA_n_INDEX: ' || sum(bytes)/1024/1024/1024 || ' GBytes' DATABASE_SIZE from dba_data_files union select 'TEMP: ' || sum(bytes)/1024/1024/1024 || ' GBytes' from dba_temp_files union select 'REDO LOGS: ' || sum(bytes)/1024/1024/1024 || ' GBytes' from v$log union select 'CONTROLFILE: ' || sum(FILE_SIZE_BLKS*BLOCK_SIZE)/1024/1024 || ' MBytes' from v$controlfile;3 Comments
Below are some of the frequently used Physical Standby Data Guard related SQL queries 1) Basic information of database (primary or standby) SQL> SELECT DATABASE_ROLE,…
6 Comments