SQL to find reference constraint to specific table

SELECT TABLE_NAME, CONSTRAINT_NAME, STATUS, OWNER
FROM ALL_CONSTRAINTS
WHERE R_OWNER = 'SCOTT'
AND CONSTRAINT_TYPE = 'R'
AND R_CONSTRAINT_NAME IN
(
SELECT CONSTRAINT_NAME FROM ALL_CONSTRAINTS
WHERE CONSTRAINT_TYPE IN ('P', 'U')
AND TABLE_NAME = 'SCOTT'
AND OWNER = 'EMPLOYEE'
)
ORDER BY TABLE_NAME, CONSTRAINT_NAME

EXPDP Command in Action

Create dump directory and give grants

 CREATE OR REPLACE DIRECTORY DUMP_DIRAS '/u02/live_dump'
 GRANT READ, WRITE ON DIRECTORY importme TO dba_user;


Export Full Schema

expdp directory=DUMP_DIRE  dumpfile=scott_test.dmp logfile=scott_test.log schemas=scott flashback_time=\"to_timestamp\(to_char\(sysdate,\'yyyy-mm-dd hh24:mi:ss\'\),\'yyyy-mm-dd hh24:mi:ss\'\)\"


Export Tables

expdp directory=DUMP_DIRE  dumpfile=scott_test_table.dmp logfile=scott_test_table.log tables=scott.enployees flashback_time=\"to_timestamp\(to_char\(sysdate,\'yyyy-mm-dd hh24:mi:ss\'\),\'yyyy-mm-dd hh24:mi:ss\'\)\"


Export Schema without specific tables

expdp directory=DUMP_DIRE  dumpfile=scott_test.dmp logfile=scott_test.log schemas=scott flashback_time=\"to_timestamp\(to_char\(sysdate,\'yyyy-mm-dd hh24:mi:ss\'\),\'yyyy-mm-dd hh24:mi:ss\'\)\" EXCLUDE=TABLE:\"LIKE \'AUDIT%\'\"

Get dump file size Estimate

expdp directory=DUMP_DIRE  schemas=scott flashback_time=\"to_timestamp\(to_char\(sysdate,\'yyyy-mm-dd hh24:mi:ss\'\),\'yyyy-mm-dd hh24:mi:ss\'\)\" ESTIMATE_ONLY=Y


Export data with where condition 

create file as backup.par with following details

DIRECTORY= DUMP_DIRE
DUMPFILE= scott_table.dmp
LOGFILE= scott_table.log
TABLES=scott_employee
QUERY="where JOB_TYPE = 'ACC' and SALARY >= 10000"
EXCLUDE=statistics

then execute expdp command

expdp parfile=backup.par

Gather Database Statistics

Database Stat Gather
EXEC DBMS_STATS.gather_database_stats;
EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);

Schema stat gather
EXEC DBMS_STATS.gather_schema_stats('TEST');
EXEC DBMS_STATS.gather_schema_stats('TEST', estimate_percent => 15);

Table Stat gather
EXEC DBMS_STATS.gather_table_stats('TEST', 'EMPLOYEES');
EXEC DBMS_STATS.gather_table_stats('TEST', 'EMPLOYEES', estimate_percent => 15);

Index Stat gather
EXEC DBMS_STATS.gather_index_stats('TEST', 'EMPLOYEES_IDX_01');
EXEC DBMS_STATS.gather_index_stats('TEST', 'EMPLOYEES_IDX_01', estimate_percent => 15);