Script: controlfiles.sql

    -- -----------------------------------------------------------------------------------
-- Description  : Displays information about controlfiles.
-- Requirements : Access to the V$ views.
-- Call Syntax  : @controlfiles
-- -----------------------------------------------------------------------------------

SET LINESIZE 100
COLUMN name FORMAT A80

SELECT name,
       status
FROM   v$controlfile
ORDER BY name;

SET LINESIZE 80

Read More

Script: max_extents.sql

-- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays all tables and indexes nearing their MAX_EXTENTS setting.
    -- Requirements : Access to the DBA views.
    -- Call Syntax  : @max_extents
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000
    SET VERIFY OFF

    PROMPT
    PROMPT Tables and Indexes nearing MAX_EXTENTS
    PROMPT **************************************
    SELECT e.owner,
           e.segment_type,
           Substr(e.segment_name, 1, 30) segment_name,
           Trunc(s.initial_extent/1024) "INITIAL K",
           Trunc(s.next_extent/1024) "NEXT K",
           s.max_extents,
           Count(*) as extents
    FROM   dba_extents e,
           dba_segments s
    WHERE  e.owner        = s.owner
    AND    e.segment_name = s.segment_name
    AND    e.owner        NOT IN ('SYS', 'SYSTEM')
    GROUP BY e.owner, e.segment_type, e.segment_name, s.initial_extent, s.next_extent, s.max_extents
    HAVING Count(*) > s.max_extents - 10
    ORDER BY e.owner, e.segment_type, Count(*) DESC;

Read More

Script: lru_latch_ratio.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays current LRU latch ratios.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @lru_latch_hit_ratio
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000
    COLUMN "Ratio %" FORMAT 990.00
    
    PROMPT
    PROMPT Values greater than 3% indicate contention.

    SELECT a.child#,
           (a.SLEEPS / a.GETS) * 100 "Ratio %"
    FROM   v$latch_children a
    WHERE  a.name      = 'cache buffers lru chain'
    ORDER BY 1;


    SET PAGESIZE 14

Read More

Script: longops.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays information on all long operations.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @longops
        -- -----------------------------------------------------------------------------------

    COLUMN sid FORMAT 999
    COLUMN serial# FORMAT 9999999
    COLUMN machine FORMAT A30
    COLUMN progress_pct FORMAT 99999999.00
    COLUMN elapsed FORMAT A10
    COLUMN remaining FORMAT A10

    SELECT s.sid,
           s.serial#,
           s.machine,
           ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
           ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
           ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
    FROM   v$session s,
           v$session_longops sl
    WHERE  s.sid     = sl.sid
    AND    s.serial# = sl.serial#;

Read More

Script: logfiles.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays information about redo log files.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @logfiles
        -- -----------------------------------------------------------------------------------

    SET LINESIZE 200
    COLUMN member FORMAT A50
    COLUMN first_change# FORMAT 99999999999999999999
    COLUMN next_change# FORMAT 99999999999999999999

    SELECT l.thread#,
           lf.group#,
           lf.member,
           TRUNC(l.bytes/1024/1024) AS size_mb,
           l.status,
           l.archived,
           lf.type,
           lf.is_recovery_dest_file AS rdf,
           l.sequence#,
           l.first_change#,
           l.next_change#  
    FROM   v$logfile lf
           JOIN v$log l ON l.group# = lf.group#
    ORDER BY l.thread#,lf.group#, lf.member;

    SET LINESIZE 80

Read More

Script: locked_objects_internal.sql

-- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Lists all locks on the specific object.
    -- Requirements : Access to the DBA views.
    -- Call Syntax  : @locked_objects_internal (object-name)
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 1000 VERIFY OFF

    COLUMN lock_type FORMAT A20
    COLUMN mode_held FORMAT A10
    COLUMN mode_requested FORMAT A10
    COLUMN lock_id1 FORMAT A50
    COLUMN lock_id2 FORMAT A30

    SELECT li.session_id AS sid,
           s.serial#,
           li.lock_type,
           li.mode_held,
           li.mode_requested,
           li.lock_id1,
           li.lock_id2
    FROM   dba_lock_internal li
           JOIN v$session s ON li.session_id = s.sid
    WHERE  UPPER(lock_id1) LIKE '%&1%';

    SET VERIFY ON

Read More

Script: locked_objects.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Lists all locked objects.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @locked_objects
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000
    SET VERIFY OFF

    COLUMN owner FORMAT A20
    COLUMN username FORMAT A20
    COLUMN object_owner FORMAT A20
    COLUMN object_name FORMAT A30
    COLUMN locked_mode FORMAT A15

    SELECT lo.session_id AS sid,
           s.serial#,
           NVL(lo.oracle_username, '(oracle)') AS username,
           o.owner AS object_owner,
           o.object_name,
           Decode(lo.locked_mode, 0, 'None',
                                 1, 'Null (NULL)',
                                 2, 'Row-S (SS)',
                                 3, 'Row-X (SX)',
                                 4, 'Share (S)',
                                 5, 'S/Row-X (SSX)',
                                 6, 'Exclusive (X)',
                                 lo.locked_mode) locked_mode,
           lo.os_user_name
    FROM   v$locked_object lo
           JOIN dba_objects o ON o.object_id = lo.object_id
           JOIN v$session s ON lo.session_id = s.sid
    ORDER BY 1, 2, 3, 4;

    SET PAGESIZE 14
    SET VERIFY ON

Read More

Script: license.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays session usage for licensing purposes.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @license
    -- 
    -- -----------------------------------------------------------------------------------
    SELECT *
    FROM   v$license;

Read More

Script: library_cache.sql

-- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays library cache statistics.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @library_cache
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000
    SET VERIFY OFF

    SELECT a.namespace "Name Space",
           a.gets "Get Requests",
           a.gethits "Get Hits",
           Round(a.gethitratio,2) "Get Ratio",
           a.pins "Pin Requests",
           a.pinhits "Pin Hits",
           Round(a.pinhitratio,2) "Pin Ratio",
           a.reloads "Reloads",
           a.invalidations "Invalidations"
    FROM   v$librarycache a
    ORDER BY 1;

    SET PAGESIZE 14
    SET VERIFY ON

Read More

Script: latches.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays information about all current latches.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @latches
       -- -----------------------------------------------------------------------------------
    SET LINESIZE 200

    SELECT l.latch#,
           l.name,
           l.gets,
           l.misses,
           l.sleeps,
           l.immediate_gets,
           l.immediate_misses,
           l.spin_gets
    FROM   v$latch l
    ORDER BY l.name;

Read More

Script: latch_holders.sql

-- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays information about all current latch holders.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @latch_holders
        -- -----------------------------------------------------------------------------------
    SET LINESIZE 200

    SELECT l.name "Latch Name",
           lh.pid "PID",
           lh.sid "SID",
           l.gets "Gets (Wait)",
           l.misses "Misses (Wait)",
           l.sleeps "Sleeps (Wait)",
           l.immediate_gets "Gets (No Wait)",
           l.immediate_misses "Misses (Wait)"
    FROM   v$latch l,
           v$latchholder lh
    WHERE  l.addr = lh.laddr
    ORDER BY l.name;

Read More

Script: latch_hit_ratios.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays current latch hit ratios.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @latch_hit_ratios
        -- -----------------------------------------------------------------------------------
    SET LINESIZE 200

    COLUMN latch_hit_ratio FORMAT 990.00
    
    SELECT l.name,
           l.gets,
           l.misses,
           ((1 - (l.misses / l.gets)) * 100) AS latch_hit_ratio
    FROM   v$latch l
    WHERE  l.gets   != 0
    UNION
    SELECT l.name,
           l.gets,
           l.misses,
           100 AS latch_hit_ratio
    FROM   v$latch l
    WHERE  l.gets   = 0
    ORDER BY 4 DESC;

Read More

Script: jobs_running.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays information about all jobs currently running.
    -- Requirements : Access to the DBA views.
    -- Call Syntax  : @jobs_running
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000
    SET VERIFY OFF

    SELECT a.job "Job",
           a.sid,
           a.failures "Failures",      
           Substr(To_Char(a.last_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "Last Date",     
           Substr(To_Char(a.this_date,'DD-Mon-YYYY HH24:MI:SS'),1,20) "This Date"            
    FROM   dba_jobs_running a;

    SET PAGESIZE 14
    SET VERIFY ON

Read More

Script: jobs.sql

-- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays information about all scheduled jobs.
    -- Requirements : Access to the DBA views.
    -- Call Syntax  : @jobs
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 1000 PAGESIZE 1000

    COLUMN log_user FORMAT A15
    COLUMN priv_user FORMAT A15
    COLUMN schema_user FORMAT A15
    COLUMN interval FORMAT A40
    COLUMN what FORMAT A50
    COLUMN nls_env FORMAT A50
    COLUMN misc_env FORMAT A50

    SELECT a.job,           
           a.log_user,      
           a.priv_user,    
           a.schema_user,   
           To_Char(a.last_date,'DD-MON-YYYY HH24:MI:SS') AS last_date,     
           --To_Char(a.this_date,'DD-MON-YYYY HH24:MI:SS') AS this_date,     
           To_Char(a.next_date,'DD-MON-YYYY HH24:MI:SS') AS next_date,     
           a.broken,        
           a.interval,      
           a.failures,      
           a.what,
           a.total_time,    
           a.nls_env,       
           a.misc_env         
    FROM   dba_jobs a;

    SET LINESIZE 80 PAGESIZE 14

Read More

Script: invalid_objects.sql

-- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Lists all invalid objects in the database.
    -- Call Syntax  : @invalid_objects
    -- Requirements : Access to the DBA views.
       -- -----------------------------------------------------------------------------------
    COLUMN object_name FORMAT A30
    SELECT owner,
           object_type,
           object_name,
           status
    FROM   dba_objects
    WHERE  status = 'INVALID'
    ORDER BY owner, object_type, object_name;

Read More

Script: index_usage.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Shows the usage for the specified table indexes.
    -- Call Syntax  : @index_usage (table-name) (index-name or all)
        -- -----------------------------------------------------------------------------------
    SET VERIFY OFF
    SET LINESIZE 200

    SELECT table_name,
           index_name,
           used,
           start_monitoring,
           end_monitoring
    FROM   v$object_usage
    WHERE  table_name = UPPER('&1')
    AND    index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));

Read More

Script: index_partitions.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays partition information for the specified index, or all indexes.
    -- Requirements : Access to the DBA views.
    -- Call Syntax  : @index_patitions (index_name or all) (schema-name)
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000
    SET FEEDBACK OFF
    SET VERIFY OFF

    SELECT a.index_name,
           a.partition_name,
           a.tablespace_name,
           a.initial_extent,
           a.next_extent,
           a.pct_increase,
           a.num_rows
    FROM   dba_ind_partitions a
    WHERE  a.index_name  = Decode(Upper('&&1'),'ALL',a.index_name,Upper('&&1'))
    AND    a.index_owner = Upper('&&2')
    ORDER BY a.index_name, a.partition_name
    /

    PROMPT
    SET PAGESIZE 14
    SET FEEDBACK ON

Read More

Script: index_monitoring_status.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Shows the monitoring status for the specified table indexes.
    -- Call Syntax  : @index_monitoring_status (schema) (table-name or all)
        -- -----------------------------------------------------------------------------------
    SET VERIFY OFF

    SELECT table_name,
           index_name,
           monitoring
    FROM   v$object_usage
    WHERE  table_name = UPPER('&1')
    AND    index_name = DECODE(UPPER('&2'), 'ALL', index_name, UPPER('&2'));

Read More

Script: index_extents.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays number of extents for all indexes belonging to the specified table, or all tables.
    -- Requirements : Access to the DBA views.
    -- Call Syntax  : @index_extents (table_name or all) (schema-name)
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000
    SET VERIFY OFF

    SELECT i.index_name,
           Count(e.segment_name) extents,
           i.max_extents,
           t.num_rows "ROWS",
           Trunc(i.initial_extent/1024) "INITIAL K",
           Trunc(i.next_extent/1024) "NEXT K",
           t.table_name
    FROM   all_tables t,
           all_indexes i,
           dba_extents e
    WHERE  i.table_name   = t.table_name
    AND    i.owner        = t.owner
    AND    e.segment_name = i.index_name
    AND    e.owner        = i.owner
    AND    i.table_name   = Decode(Upper('&&1'),'ALL',i.table_name,Upper('&&1'))
    AND    i.owner        = Upper('&&2')
    GROUP BY t.table_name,
             i.index_name,
             i.max_extents,
             t.num_rows,
             i.initial_extent,
             i.next_extent
    HAVING   Count(e.segment_name) > 5
    ORDER BY Count(e.segment_name) DESC;

    SET PAGESIZE 18
    SET VERIFY ON

Read More

Script: identify_trace_file.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays the name of the trace file associated with the current session.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @identify_trace_file
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 100
    COLUMN trace_file FORMAT A60

    SELECT s.sid,
           s.serial#,
           pa.value || '/' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||   
           '_ora_' || p.spid || '.trc' AS trace_file
    FROM   v$session s,
           v$process p,
           v$parameter pa
    WHERE  pa.name = 'user_dump_dest'
    AND    s.paddr = p.addr
    AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');

Read More

Script: hot_blocks.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Detects hot blocks.
    -- Call Syntax  : @hot_blocks
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 200
    SET VERIFY OFF

    SELECT *
    FROM   (SELECT name,
                   addr,
                   gets,
                   misses,
                   sleeps
            FROM   v$latch_children
            WHERE  name = 'cache buffers chains'
            AND    misses > 0
            ORDER BY misses DESC)
    WHERE  rownum < 11;

    ACCEPT address PROMPT "Enter ADDR: "

    COLUMN owner FORMAT A15
    COLUMN object_name FORMAT A30
    COLUMN subobject_name FORMAT A20

    SELECT *
    FROM   (SELECT o.owner,
                   o.object_name,
                   o.subobject_name,
                   bh.tch,
                   bh.obj,
                   bh.file#,
                   bh.dbablk,
                   bh.class,
                   bh.state
            FROM   x$bh bh,
                   dba_objects o
            WHERE  o.data_object_id = bh.obj
            AND    hladdr = '&address'
            ORDER BY tch DESC)
    WHERE  rownum < 11;

Read More

Script: hidden_parameters.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays a list of one or all the hidden parameters.
    -- Requirements : Access to the v$ views.
    -- Call Syntax  : @hidden_parameters (parameter-name or all)
    -- -----------------------------------------------------------------------------------
    SET VERIFY OFF
    COLUMN parameter      FORMAT a37
    COLUMN description    FORMAT a30 WORD_WRAPPED
    COLUMN session_value  FORMAT a10
    COLUMN instance_value FORMAT a10
    
    SELECT a.ksppinm AS parameter,
           a.ksppdesc AS description,
           b.ksppstvl AS session_value,
           c.ksppstvl AS instance_value
    FROM   x$ksppi a,
           x$ksppcv b,
           x$ksppsv c
    WHERE  a.indx = b.indx
    AND    a.indx = c.indx
    AND    a.ksppinm LIKE '/_%' ESCAPE '/'
    AND    a.ksppinm = DECODE(LOWER('&1'), 'all', a.ksppinm, LOWER('&1'))
    ORDER BY a.ksppinm;

Read More

Script: high_water_mark.sql

    -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays the High Water Mark for the specified table, or all tables.
    -- Requirements : Access to the Dbms_Space.
    -- Call Syntax  : @high_water_mark (table_name or all) (schema-name)
    -- 
    -- -----------------------------------------------------------------------------------
    SET SERVEROUTPUT ON
    SET VERIFY OFF

    DECLARE
      CURSOR cu_tables IS
        SELECT a.owner,
               a.table_name
        FROM   all_tables a
        WHERE  a.table_name = Decode(Upper('&&1'),'ALL',a.table_name,Upper('&&1'))
        AND    a.owner      = Upper('&&2');

      op1  NUMBER;
      op2  NUMBER;
      op3  NUMBER;
      op4  NUMBER;
      op5  NUMBER;
      op6  NUMBER;
      op7  NUMBER;
    BEGIN

      Dbms_Output.Disable;
      Dbms_Output.Enable(1000000);
      Dbms_Output.Put_Line('TABLE                             UNUSED BLOCKS     TOTAL BLOCKS  HIGH WATER MARK');
      Dbms_Output.Put_Line('------------------------------  ---------------  ---------------  ---------------');
      FOR cur_rec IN cu_tables LOOP
        Dbms_Space.Unused_Space(cur_rec.owner,cur_rec.table_name,'TABLE',op1,op2,op3,op4,op5,op6,op7);
        Dbms_Output.Put_Line(RPad(cur_rec.table_name,30,' ') ||
                             LPad(op3,15,' ')                ||
                             LPad(op1,15,' ')                ||
                             LPad(Trunc(op1-op3-1),15,' '));
      END LOOP;

    END;
    /

    SET VERIFY ON

Read More

Script: fks.sql

-- -----------------------------------------------------------------------------------
      -- Author       : Pavan Yennampelli
    -- Description  : Displays the constraints on a specific table and those referencing it.
    -- Call Syntax  : @fks (table-name) (schema)
    -- 
    -- -----------------------------------------------------------------------------------
    PROMPT
    SET VERIFY OFF
    SET FEEDBACK OFF
    SET LINESIZE 255
    SET PAGESIZE 1000

    PROMPT
    PROMPT Constraints Owned By Table
    PROMPT ==========================
    SELECT c.constraint_name "Constraint",
           Decode(c.constraint_type,'P','Primary Key',
                                    'U','Unique Key',
                                    'C','Check',
                                    'R','Foreign Key',
                                    c.constraint_type) "Type",
           c.r_owner "Ref Table",
           c.r_constraint_name "Ref Constraint"
    FROM   all_constraints c
    WHERE  c.table_name = Upper('&&1')
    AND    c.owner      = Upper('&&2');


    PROMPT
    PROMPT Constraints Referencing Table
    PROMPT =============================
    SELECT c1.table_name "Table",
           c1.constraint_name "Foreign Key",
           c1.r_constraint_name "References"
    FROM   all_constraints c1
    WHERE  c1.owner      = Upper('&&2')
    AND    c1.r_constraint_name IN (SELECT c2.constraint_name
                                    FROM   all_constraints c2
                                    WHERE  c2.table_name = Upper('&&1')
                                    AND    c2.owner      = Upper('&&2')
                                    AND    c2.constraint_type IN ('P','U'));

    SET VERIFY ON
    SET FEEDBACK ON
    SET PAGESIZE 1000
    PROMPT

Read More

Script: health.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Lots of information about the database so you can asses the general health of the system.
    -- Requirements : Access to the V$ & DBA views and several other monitoring scripts.
    -- Call Syntax  : @health (username/password@service)
    -- 
    -- -----------------------------------------------------------------------------------
    SPOOL Health_Checks.txt
    conn &1
    @db_info
    @sessions
    @ts_full
    @max_extents

    SPOOL OFF

Read More

Script: free_space.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays space usage for each datafile.
    -- Requirements : Access to the DBA views.
    -- Call Syntax  : @free_space
        -- -----------------------------------------------------------------------------------
    SET PAGESIZE 100
    SET LINESIZE 265

    COLUMN tablespace_name FORMAT A20
    COLUMN file_name FORMAT A50

    SELECT df.tablespace_name,
           df.file_name,
           df.size_mb,
           f.free_mb,
           df.max_size_mb,
           f.free_mb + (df.max_size_mb - df.size_mb) AS max_free_mb,
           RPAD(' '|| RPAD('X',ROUND((df.max_size_mb-(f.free_mb + (df.max_size_mb - df.size_mb)))/max_size_mb*10,0), 'X'),11,'-') AS used_pct
    FROM   (SELECT file_id,
                   file_name,
                   tablespace_name,
                   TRUNC(bytes/1024/1024) AS size_mb,
                   TRUNC(GREATEST(bytes,maxbytes)/1024/1024) AS max_size_mb
            FROM   dba_data_files) df,
           (SELECT TRUNC(SUM(bytes)/1024/1024) AS free_mb,
                   file_id
            FROM dba_free_space
            GROUP BY file_id) f
    WHERE  df.file_id = f.file_id (+)
    ORDER BY df.tablespace_name,
             df.file_name;

    PROMPT
    SET PAGESIZE 14

Read More

Script: code_dep_distinct.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays a tree of dependencies of specified object.
    -- Call Syntax  : @code_dep_distinct (schema-name) (object-name) (object_type or all)
    -- 
    -- -----------------------------------------------------------------------------------
    SET VERIFY OFF
    SET LINESIZE 255
    SET PAGESIZE 1000
    COLUMN referenced_object FORMAT A50
    COLUMN referenced_type FORMAT A20
    COLUMN referenced_link_name FORMAT A20

    SELECT DISTINCT a.referenced_owner || '.' || a.referenced_name AS referenced_object,
           a.referenced_type,
           a.referenced_link_name
    FROM   all_dependencies a
    WHERE  a.owner NOT IN ('SYS','SYSTEM','PUBLIC')
    AND    a.referenced_owner NOT IN ('SYS','SYSTEM','PUBLIC')
    AND    a.referenced_type != 'NON-EXISTENT'
    AND    a.referenced_type = DECODE(UPPER('&3'), 'ALL', a.referenced_type, UPPER('&3'))
    START WITH a.owner = UPPER('&1')
    AND        a.name  = UPPER('&2')
    CONNECT BY a.owner = PRIOR a.referenced_owner
    AND        a.name  = PRIOR a.referenced_name
    AND        a.type  = PRIOR a.referenced_type;

    SET VERIFY ON
    SET PAGESIZE 22

Read More

Script: dispatchers.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays dispatcher statistics.
    -- Requirements : Access to the v$ views.
    -- Call Syntax  : @dispatchers
      -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000
    SET VERIFY OFF

    SELECT a.name "Name",
           a.status "Status",
           a.accept "Accept",
           a.messages "Total Mesgs",
           a.bytes "Total Bytes",
           a.owned "Circs Owned",
           a.idle "Total Idle Time",
           a.busy "Total Busy Time",
           Round(a.busy/(a.busy + a.idle),2) "Load"
    FROM   v$dispatcher a
    ORDER BY 1;

    SET PAGESIZE 14
    SET VERIFY ON

Read More

Script: fk_columns.sql

-- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays information on all FKs for the specified schema and table.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @fk_columns (schema-name or all) (table-name or all)
     -- -----------------------------------------------------------------------------------
    SET VERIFY OFF
    SET LINESIZE 1000
    COLUMN column_name FORMAT A30
    COLUMN r_column_name FORMAT A30

    SELECT c.constraint_name,
           cc.table_name,
           cc.column_name,
           rcc.table_name AS r_table_name,
           rcc.column_name AS r_column_name,
           cc.position
    FROM   dba_constraints c
           JOIN dba_cons_columns cc ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
           JOIN dba_cons_columns rcc ON c.owner = rcc.owner AND c.r_constraint_name = rcc.constraint_name AND cc.position = rcc.position
    WHERE  c.owner      = DECODE(UPPER('&1'), 'ALL', c.owner, UPPER('&1'))
    AND    c.table_name = DECODE(UPPER('&2'), 'ALL', c.table_name, UPPER('&2'))
    ORDER BY c.constraint_name, cc.table_name, cc.position;

Read More

Script: file_io.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays the amount of IO for each datafile.
    -- Requirements : Access to the v$ views.
    -- Call Syntax  : @file_io
    -- Last Modified: 15-JUL-2000
    -- -----------------------------------------------------------------------------------
    SET PAGESIZE 1000

    SELECT Substr(d.name,1,50) "File Name",
           f.phyblkrd "Blocks Read",
           f.phyblkwrt "Blocks Writen",
           f.phyblkrd + f.phyblkwrt "Total I/O"
    FROM   v$filestat f,
           v$datafile d
    WHERE  d.file# = f.file#
    ORDER BY f.phyblkrd + f.phyblkwrt DESC;

    SET PAGESIZE 18

Read More

Script: explain.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays a tree-style execution plan of the specified statement after it has been explained.
    -- Requirements : Access to the plan table.
    -- Call Syntax  : @explain (statement-id)
    -- -----------------------------------------------------------------------------------
    SET PAGESIZE 100
    SET LINESIZE 200
    SET VERIFY OFF

    COLUMN plan             FORMAT A50
    COLUMN object_name      FORMAT A30
    COLUMN object_type      FORMAT A15
    COLUMN bytes            FORMAT 9999999999
    COLUMN cost             FORMAT 9999999
    COLUMN partition_start  FORMAT A20
    COLUMN partition_stop   FORMAT A20

    SELECT LPAD(' ', 2 * (level - 1)) ||
           DECODE (level,1,NULL,level-1 || '.' || pt.position || ' ') ||
           INITCAP(pt.operation) ||
           DECODE(pt.options,NULL,'',' (' || INITCAP(pt.options) || ')') plan,
           pt.object_name,
           pt.object_type,
           pt.bytes,
           pt.cost,
           pt.partition_start,
           pt.partition_stop
    FROM   plan_table pt
    START WITH pt.id = 0
      AND pt.statement_id = '&1'
    CONNECT BY PRIOR pt.id = pt.parent_id
      AND pt.statement_id = '&1';

Read More

Script: error_stack.sql

    -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays contents of the error stack.
    -- Call Syntax  : @error_stack
    -- 
    -- -----------------------------------------------------------------------------------
    SET SERVEROUTPUT ON
    DECLARE
      v_stack  VARCHAR2(2000);
    BEGIN
      v_stack := Dbms_Utility.Format_Error_Stack;
      Dbms_Output.Put_Line(v_stack);
    END;
    /

Read More

Script: directories.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays information about all directories.
    -- Requirements : Access to the DBA views.
    -- Call Syntax  : @directories
     -- -----------------------------------------------------------------------------------
    COLUMN owner FORMAT A20
    COLUMN directory_name FORMAT A25
    COLUMN directory_path FORMAT A50

    SELECT *
    FROM   dba_directories
    ORDER BY owner, directory_name;

Read More

Script: df_free_space.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays free space information about datafiles.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @df_free_space.sql
    -- -----------------------------------------------------------------------------------

    SET LINESIZE 120
    COLUMN file_name FORMAT A60

    SELECT a.file_name,
           ROUND(a.bytes/1024/1024) AS size_mb,
           ROUND(a.maxbytes/1024/1024) AS maxsize_mb,
           ROUND(b.free_bytes/1024/1024) AS free_mb,
           ROUND((a.maxbytes-a.bytes)/1024/1024) AS growth_mb,
           100 - ROUND(((b.free_bytes+a.growth)/a.maxbytes) * 100) AS pct_used
    FROM   (SELECT file_name,
                   file_id,
                   bytes,
                   GREATEST(bytes,maxbytes) AS maxbytes,
                   GREATEST(bytes,maxbytes)-bytes AS growth
            FROM   dba_data_files) a,
           (SELeCT file_id,
                   SUM(bytes) AS free_bytes
            FROM   dba_free_space
            GROUP BY file_id) b
    WHERE  a.file_id = b.file_id
    ORDER BY file_name;

Read More

Script: db_properties.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays all database property values.
    -- Call Syntax  : @db_properties
    -- -----------------------------------------------------------------------------------
    COLUMN property_value FORMAT A50

    SELECT property_name,
           property_value
    FROM   database_properties
    ORDER BY property_name;

Read More

Script: db_info.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays general information about the database.
    -- Requirements : Access to the v$ views.
    -- Call Syntax  : @db_info
    -- 
    -- -----------------------------------------------------------------------------------
    SET PAGESIZE 1000
    SET LINESIZE 100
    SET FEEDBACK OFF

    SELECT *
    FROM   v$database;

    SELECT *
    FROM   v$instance;

    SELECT *
    FROM   v$version;

    SELECT a.name,
           a.value
    FROM   v$sga a;

    SELECT Substr(c.name,1,60) "Controlfile",
           NVL(c.status,'UNKNOWN') "Status"
    FROM   v$controlfile c
    ORDER BY 1;

    SELECT Substr(d.name,1,60) "Datafile",
           NVL(d.status,'UNKNOWN') "Status",
           d.enabled "Enabled",
           LPad(To_Char(Round(d.bytes/1024000,2),'9999990.00'),10,' ') "Size (M)"
    FROM   v$datafile d
    ORDER BY 1;

    SELECT l.group# "Group",
           Substr(l.member,1,60) "Logfile",
           NVL(l.status,'UNKNOWN') "Status"
    FROM   v$logfile l
    ORDER BY 1,2;

    PROMPT
    SET PAGESIZE 14
    SET FEEDBACK ON

Read More

Script: db_links_open.sql

-- -----------------------------------------------------------------------------------
     -- Author       : Pavan Yennampelli
    -- Description  : Displays information on all open database links.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @db_links_open
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 200

    COLUMN db_link FORMAT A30

    SELECT db_link,
           owner_id,
           logged_on,
           heterogeneous,
           protocol,
           open_cursors,
           in_transaction,
           update_sent,
           commit_point_strength
    FROM   v$dblink
    ORDER BY db_link;

    SET LINESIZE 80

Read More

Script: db_links.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays information on all database links.
    -- Requirements : Access to the DBA views.
    -- Call Syntax  : @db_links
       -- -----------------------------------------------------------------------------------
    SET LINESIZE 150

    COLUMN db_link FORMAT A30
    COLUMN host FORMAT A30

    SELECT owner,
           db_link,
           username,
           host
    FROM   dba_db_links
    ORDER BY owner, db_link;

Read More

Script: db_cache_advice.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Predicts how changes to the buffer cache will affect physical reads.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @db_cache_advice
       -- -----------------------------------------------------------------------------------

    COLUMN size_for_estimate          FORMAT 999,999,999,999 heading 'Cache Size (MB)'
    COLUMN buffers_for_estimate       FORMAT 999,999,999 heading 'Buffers'
    COLUMN estd_physical_read_factor  FORMAT 999.90 heading 'Estd Phys|Read Factor'
    COLUMN estd_physical_reads        FORMAT 999,999,999,999 heading 'Estd Phys| Reads'

    SELECT size_for_estimate,
           buffers_for_estimate,
           estd_physical_read_factor,
           estd_physical_reads
    FROM   v$db_cache_advice
    WHERE  name          = 'DEFAULT'
    AND    block_size    = (SELECT value
                            FROM   v$parameter
                            WHERE  name = 'db_block_size')
    AND    advice_status = 'ON';

Read More

Script: datafiles.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays information about datafiles.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @datafiles
        -- -----------------------------------------------------------------------------------

    SET LINESIZE 200
    COLUMN file_name FORMAT A70

    SELECT file_id,
           file_name,
           ROUND(bytes/1024/1024/1024) AS size_gb,
           ROUND(maxbytes/1024/1024/1024) AS max_size_gb,
           autoextensible,
           increment_by,
           status
    FROM   dba_data_files
    ORDER BY file_name;

Read More

Script: column_defaults.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays the default values where present for the specified table.
    -- Call Syntax  : @column_defaults (table-name)
    -- 
    -- -----------------------------------------------------------------------------------
    SET LINESIZE 100
    SET VERIFY OFF

    SELECT a.column_name "Column",
           a.data_default "Default"
    FROM   all_tab_columns a
    WHERE  a.table_name = Upper('&1')
    AND    a.data_default IS NOT NULL
    /

Read More

Script: code_dep_tree.sql

-- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays a tree of dependencies of specified object.
    -- Call Syntax  : @code_dep_tree (schema-name) (object-name)
    -- 
    -- -----------------------------------------------------------------------------------
    SET VERIFY OFF
    SET LINESIZE 255
    SET PAGESIZE 1000

    COLUMN referenced_object FORMAT A50
    COLUMN referenced_type FORMAT A20
    COLUMN referenced_link_name FORMAT A20

    SELECT RPAD(' ', level*2, ' ') || a.referenced_owner || '.' || a.referenced_name AS referenced_object,
           a.referenced_type,
           a.referenced_link_name
    FROM   all_dependencies a
    WHERE  a.owner NOT IN ('SYS','SYSTEM','PUBLIC')
    AND    a.referenced_owner NOT IN ('SYS','SYSTEM','PUBLIC')
    AND    a.referenced_type != 'NON-EXISTENT'
    START WITH a.owner = UPPER('&1')
    AND        a.name  = UPPER('&2')
    CONNECT BY a.owner = PRIOR a.referenced_owner
    AND        a.name  = PRIOR a.referenced_name
    AND        a.type  = PRIOR a.referenced_type;

    SET VERIFY ON
    SET PAGESIZE 22

Read More

Script: code_dep_on.sql

-- -----------------------------------------------------------------------------------
     -- Author       : Pavan Yennampelli
    -- Description  : Displays all objects dependant on the specified object.
    -- Call Syntax  : @code_dep_on (schema-name or all) (object-name)
    -- 
    -- -----------------------------------------------------------------------------------
    SET VERIFY OFF
    SET LINESIZE 255
    SET PAGESIZE 1000
    BREAK ON type SKIP 1
    COLUMN owner FORMAT A20
    SELECT a.type,
           a.owner,
           a.name
    FROM   all_dependencies a
    WHERE  a.referenced_owner = DECODE(UPPER('&1'), 'ALL', a.referenced_owner, UPPER('&1'))
    AND    a.referenced_name  = UPPER('&2')
    ORDER BY 1,2,3;
    SET PAGESIZE 22
    SET VERIFY ON

Read More

Script: code_dep.sql

 -- -----------------------------------------------------------------------------------
       -- Author       : Pavan Yennampelli
    -- Description  : Displays all dependencies of specified object.
    -- Call Syntax  : @code_dep (schema-name or all) (object-name)
    -- 
    -- -----------------------------------------------------------------------------------
    SET VERIFY OFF
    SET LINESIZE 255
    SET PAGESIZE 1000
    BREAK ON referenced_type SKIP 1
    COLUMN referenced_type FORMAT A20
    COLUMN referenced_owner FORMAT A20
    COLUMN referenced_name FORMAT A40
    COLUMN referenced_link_name FORMAT A20
    SELECT a.referenced_type,
           a.referenced_owner,
           a.referenced_name,
           a.referenced_link_name
    FROM   all_dependencies a
    WHERE  a.owner = DECODE(UPPER('&1'), 'ALL', a.referenced_owner, UPPER('&1'))
    AND    a.name  = UPPER('&2')
    ORDER BY 1,2,3;
    SET VERIFY ON
    SET PAGESIZE 22

Read More

Script: call_stack.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays the current call stack.
    -- Requirements : Access to DBMS_UTILITY.
    -- Call Syntax  : @call_stack
    -- 
    -- -----------------------------------------------------------------------------------
    SET SERVEROUTPUT ON
    DECLARE
      v_stack  VARCHAR2(2000);
    BEGIN
      v_stack := Dbms_Utility.Format_Call_Stack;
      Dbms_Output.Put_Line(v_stack);
    END;
    /

Read More

Script: cache_hit_ratio.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays cache hit ratio for the database.
    -- Comments     : The minimum figure of 89% is often quoted, but depending on the type of system this may not be possible.
    -- Requirements : Access to the v$ views.
    -- Call Syntax  : @cache_hit_ratio
       -- -----------------------------------------------------------------------------------
    PROMPT
    PROMPT Hit ratio should exceed 89%
    SELECT Sum(Decode(a.name, 'consistent gets', a.value, 0)) "Consistent Gets",
           Sum(Decode(a.name, 'db block gets', a.value, 0)) "DB Block Gets",
           Sum(Decode(a.name, 'physical reads', a.value, 0)) "Physical Reads",
           Round(((Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
             Sum(Decode(a.name, 'db block gets', a.value, 0)) -
             Sum(Decode(a.name, 'physical reads', a.value, 0))  )/
               (Sum(Decode(a.name, 'consistent gets', a.value, 0)) +
                 Sum(Decode(a.name, 'db block gets', a.value, 0))))
                 *100,2) "Hit Ratio %"
    FROM   v$sysstat a;

Read More

Script: active_sessions.sql

 -- -----------------------------------------------------------------------------------
        -- Author       : Pavan Yennampelli
    -- Description  : Displays information on all active database sessions.
    -- Requirements : Access to the V$ views.
    -- Call Syntax  : @active_sessions
        -- -----------------------------------------------------------------------------------
    SET LINESIZE 500
    SET PAGESIZE 1000

    COLUMN username FORMAT A15
    COLUMN machine FORMAT A25
    COLUMN logon_time FORMAT A20

    SELECT NVL(s.username, '(oracle)') AS username,
           s.osuser,
           s.sid,
           s.serial#,
           p.spid,
           s.lockwait,
           s.status,
           s.module,
           s.machine,
           s.program,
           TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
    FROM   v$session s,
           v$process p
    WHERE  s.paddr  = p.addr
    AND    s.status = 'ACTIVE'
    ORDER BY s.username, s.osuser;

    SET PAGESIZE 14

Read More

Refresh is a scenario where we migrate data from one database to another database or from one schema to another schema.

Generally , Refreshes are done at:

1. Table level Refrtesh
2. Schema Level Refresh
3. Database Level Refresh

We perform table level and schema level refreshes using export/import utility or Datapump Technology.

Data pump is a new feature in Oracle10g that provides fast parallel data load. With direct path and parallel execution, data pump is several times faster then the traditional exp/imp. Traditional exp/imp runs on client side. But impdp/expdp runs on server side. So we have much control on expdp/expdp compared to traditional exp/imp. When compared to exp/imp, data pump startup time is longer. Because, it has to setup the jobs, queues, and master table. Also at the end of the export operation the master table data is written to the dump file set, and at the beginning of the import job the master table is located and loaded in the schema of the user.

Following are the process involved in the data pump operation:Client Process : This process is initiated by client utility. This process makes a call to the data pump API. Once the data pump is initiated, this process is not necessary for the progress of the job.Shadow Process : When client log into the database, foreground process is created. It services the client data pump API requests. This process creates the master table and creates Advanced queuing queues used for communication. Once client process ends, shadow process also go away.

Master Control Process : MCP controls the execution of the data pump job. There is one MCP per job. MCP divides the data pump job into various metadata and data load or unload jobs and hands them over to the worker processes.Worker Process : MCP creates worker process based on the valule of the PARALLEL parameter. The worker process performs the task requested by MCP.Advantage of Data pump

1. We can perform export in parallel. It can also write to multiple files on different disks. (Specify parameters PARALLEL=2 and the two directory names with file specification DUMPFILE=ddir1:/file1.dmp, DDIR2:/file2.dmp)

2. Has ability to attach and detach from job, monitor the job progress remotely.

3. Has more option to filter metadata objects. Ex, EXCLUDE, INCLUDE

4. ESTIMATE_ONLY option can be used to estimate disk space requirements before performs the job

5. Data can be exported from remote database by using Database link

6. Explicit DB version can be specified, so only supported object types are exported.

7. During impdp, we can change the target file names, schema, and tablespace. Ex, REMAP_SCHEMA, REMAP_DATAFILES, REMAP_TABLESPACE

8. Has the option to filter data rows during impdp. Traditional exp/imp, we have this filter option only in exp. But here we have filter option on both impdp, expdp.

9. Data can be imported from one DB to another without writing to dump file, using NETWORK_LINK parameter.

10. Data access methods are decided automatically. In traditional exp/imp, we specify the value for the parameter DIRECT. But here, it decides where direct path can not be used , conventional path is used.

11. Job status can be queried directly from data dictionary(For example, dba_datapump_jobs, dba_datapump_sessions etc)

Exp & Expdp common parameters: These below parameters exists in both traditional exp and expdp utility.

FILESIZE
FLASHBACK_SCN
FLASHBACK_TIME
FULL
HELP
PARFILE
QUERY
TABLES
TABLESPACES
TRANSPORT_TABLESPACES(exp value is Y/N, expdp value is name of the tablespace)

Comparing exp & expdp parameters: These below parameters are equivalent parameters between exp & expdp. Exp and corresponding Expdp parameters...

FEEDBACK => STATUS
FILE => DUMPFILE
LOG => LOGFILE
OWNER => SCHEMAS
TTS_FULL_CHECK => TRANSPROT_FULL_CHECK

New parameters in expdp Utility

ATTACH Attach the client session to existing data pump jobs

CONTENT Specify what to export(ALL, DATA_ONLY, METADATA_ONLY)

DIRECTORY Location to write the dump file and log file.

ESTIMATE Show how much disk space each table in the export job consumes.

ESTIMATE_ONLY It estimate the space, but does not perform export

EXCLUDE List of objects to be excluded

INCLUDE List of jobs to be included

JOB_NAME Name of the export job

KEEP_MASTER Specify Y not to drop the master table after export

NETWORK_LINK Specify dblink to export from remote database

NOLOGFILE Specify Y if you do not want to create log file

PARALLEL Specify the maximum number of threads for the export job

VERSION DB objects that are incompatible with the specified version will not be exported.

ENCRYPTION_PASSWORD The table column is encrypted, then it will be written as clear text in the dump file set when the password is not specified. We can define any string as a password for this parameter.

COMPRESSION Specifies whether to compress metadata before writing to the dump file set. The default is METADATA_ONLY. We have two values(METADATA_ONLY,NONE). We can use NONE if we want to disable during the expdp.

SAMPLE - Allows you to specify a percentage of data to be sampled and unloaded from the source database. The sample_percent indicates the probability that a block of rows will be selected as part of the sample.

Imp & Impdp common parameters: These below parameters exist in both traditional imp and impdp utility.

FULL
HELP
PARFILE
QUERY
SKIP_UNUSABLE_INDEXES
TABLES
TABLESPACESComparing imp & impdp parameters: These below parameters are equivalent parameters between imp & impdp. imp and corresponding impdp parameters...

DATAFILES => TRANSPORT_DATAFILES
DESTROY =>REUSE_DATAFILES
FEEDBACK =>STATUS
FILE =>DUMPFILE
FROMUSER =>SCHEMAS, REMAP_SCHEMAS
IGNORE =>TABLE_EXISTS_ACTION(SKIP,APPEND,TRUNCATE,REPLACE)
INDEXFILE, SHOW=>SQLFILE
LOG =>LOGFILE
TOUSER =>REMAP_SCHEMANew parameters in impdp Utility

FLASHBACK_SCN Performs import operation that is consistent with the SCN specified from the source database. Valid only when NETWORK_LINK parameter is used.

FLASHBACK_TIME Similar to FLASHBACK_SCN, but oracle finds the SCN close to the time specified.

NETWORK_LINK Performs import directly from a source database using database link name specified in the parameter. The dump file will be not be created in server when we use this parameter. To get a consistent export from the source database, we can use the FLASHBACK_SCN or FLASHBACK_TIME parameters. These two parameters are only valid when we use NETWORK_LINK parameter.

REMAP_DATAFILE Changes name of the source DB data file to a different name in the target.

REMAP_SCHEMA Loads objects to a different target schema name.

REMAP_TABLESPACE Changes name of the source tablespace to a different name in the target.

TRANSFORM We can specify that the storage clause should not be generated in the DDL for import. This is useful if the storage characteristics of the source and target database are different. The valid values are SEGMENT_ATTRIBUTES, STORAGE. STORAGE removes the storage clause from the CREATE statement DDL, whereas SEGMENT_ATTRIBUTES removes physical attributes, tablespace, logging, and storage attributes.

TRANSFORM = name:boolean_value[:object_type], where boolean_value is Y or N.

For instance, TRANSFORM=storage:N:table

ENCRYPTION_PASSWORD It is required on an import operation if an encryption password was specified on the export operation.

CONTENT, INCLUDE, EXCLUDE are same as expdp utilities.
Steps for Table Level :

For Example consider a below scenario:

Source Database: Prod
Schema: Scott
Table:Emp

Target Database:Stage
Schema:  Scott
Table: Emp

Now perform below steps on source database:

SQL>select count(*) from scott.emp;
SQL>select sum(bytes)/1024/1024/1024 from dba_segments where segment_name='Emp' and owner='scott';

$exp file=exp_prod_scott_emp.dmp log=exp_prod_scott_emp.log tables=scott.emp buffer=102400 consistent=y
 copy the dump to target database location.

Now perform below steps on Target database:

 SQL>select count(*) from scott.emp;
SQL>select sum(bytes)/1024/1024/1024 from dba_segments where segment_name='Emp' and owner='scott';

$exp file=exp_stage_scott_emp.dmp log=exp_stage_scott_emp.log tables=scott.emp buffer=102400 consistent=y

SQL>drop table scott.emp;

$imp file=exp_prod_scott_emp.dmp log=imp_stage_scott_emp.log fromuser=scott touser=scott

now crosscheck the data in both the  databases.

SQL>select count(*) from scott.emp;
SQL>select sum(bytes)/1024/1024/1024 from dba_segments where segment_name='Emp' and owner='scott';

The o/p of above queries must be same in both the databases after import.
 

Read More