Query to Monitor IMPDP

Following query can be used to monitor the progress of impdp.

select
   substr(sql_text,instr(sql_text,'into "'),30) table_name,
   rows_processed, round((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes,
   trunc(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
from  
   sys.v_$sqlarea
where 
   sql_text like 'insert %into "%' and command_type = 2 and open_versions > 0; 

DDL of Indexes without Storage Parameters

Taking DDL's of indexes is always a good habit before any export/import activity. But we get lot of parameters along with create statements, like storage parameters etc which we may not need them to run after the completion of import. So we can remove them before running the import by running the following statements.


EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',FALSE);
EXECUTE DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);

How to check user objects created in SYSTEM tablespace not owned by SYS user

Query to check objects created in SYSTEM tablespace and not owned by SYS or SYSTEM user.
It is very important not to have user related objects in SYSTEM tablespace.


select owner, segment_name, segment_type
from dba_segments
where tablespace_name = 'SYSTEM'
and owner not in ('SYS','SYSTEM');

Checking if the database is using pfile or spfile

Use the following SQL query to see what parameter file is your database using currently. If it is PFILE or SPFILE.


SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type" FROM sys.v_$parameter WHERE name = 'spfile';

Determining longest query length in oracle database

Following is the query to determine the longest query length in oracle database, this query can be very useful in setting up UNDO_RETENTION parameter.

SQL> select max(maxquerylen) from V$undostat;

Make sure this is atleast equal to the value of UNDO_RETENTION to prevent a famous ORA-01555