set lines 400;
col owner_name for a10;
col job_name for a20;
col operation for a40;
col job_mode for a20;
col state for a20 ;
SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs
where state='EXECUTING';
==================================================
select sid, serial#, sofar, totalwork,
dp.owner_name, dp.state, dp.job_mode
from gv$session_longops sl, gv$datapump_job dp
where sl.opname = dp.job_name and sofar != totalwork;
======================================================
+++++++++++++++ Shows waiting status of data pump ++++++++++++++
set lines 400;
col module for a20;
col state for a10;
col event for a40;
col sql_text for a80;
select s.sid, s.module, s.state,
substr(s.event, 1, 21) as event,
s.seconds_in_wait as secs,
substr(sql.sql_text, 1, 30) as sql_text
from v$session s
join v$sql sql on sql.sql_id = s.sql_id
where s.module like 'Data Pump%'
order by s.module, s.sid;
****************** showing percentage completed ocptechnology *************************
set lines 400;
col opname for a20;
col username for a20;
SELECT OPNAME, SID, SERIAL#, username,CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS WHERE OPNAME in
( select d.job_name from v$session s, v$process p, dba_datapump_sessions d where p.addr=s.paddr and s.saddr=d.saddr )
AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;
***************** DBAGENISIs****************************
SELECT SID, SERIAL#, USERNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR <> TOTALWORK;
==========================================================
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024 * 1024) "Size (GB)",
SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
order by 5 desc;
---------------------------------------------------------------------------------------------
alter tablespace JBBOS_DAT add datafile '+DATA' size 30g;
--------------------------------------------------------------------------------------
select a.tablespace_name tablespace,
d.TEMP_TOTAL_GB,
sum (a.used_blocks * d.block_size) / 1024 / 1024 / 1024 TEMP_USED_GB,
d.TEMP_TOTAL_GB - sum (a.used_blocks * d.block_size) / 1024 / 1024 / 1024 TEMP_FREE_GB
from gv$sort_segment a,
(
select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 / 1024 TEMP_TOTAL_GB
from gv$tablespace b, gv$tempfile c
where b.ts#= c.ts#
group by b.name, c.block_size
) d
where a.tablespace_name = d.name
group by a.tablespace_name, d.TEMP_TOTAL_GB;
col owner_name for a10;
col job_name for a20;
col operation for a40;
col job_mode for a20;
col state for a20 ;
SELECT owner_name, job_name, operation, job_mode, state
FROM dba_datapump_jobs
where state='EXECUTING';
==================================================
select sid, serial#, sofar, totalwork,
dp.owner_name, dp.state, dp.job_mode
from gv$session_longops sl, gv$datapump_job dp
where sl.opname = dp.job_name and sofar != totalwork;
======================================================
+++++++++++++++ Shows waiting status of data pump ++++++++++++++
set lines 400;
col module for a20;
col state for a10;
col event for a40;
col sql_text for a80;
select s.sid, s.module, s.state,
substr(s.event, 1, 21) as event,
s.seconds_in_wait as secs,
substr(sql.sql_text, 1, 30) as sql_text
from v$session s
join v$sql sql on sql.sql_id = s.sql_id
where s.module like 'Data Pump%'
order by s.module, s.sid;
****************** showing percentage completed ocptechnology *************************
set lines 400;
col opname for a20;
col username for a20;
SELECT OPNAME, SID, SERIAL#, username,CONTEXT, SOFAR, TOTALWORK, ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
FROM V$SESSION_LONGOPS WHERE OPNAME in
( select d.job_name from v$session s, v$process p, dba_datapump_sessions d where p.addr=s.paddr and s.saddr=d.saddr )
AND OPNAME NOT LIKE '%aggregate%' AND TOTALWORK != 0 AND SOFAR <> TOTALWORK;
***************** DBAGENISIs****************************
SELECT SID, SERIAL#, USERNAME, CONTEXT, SOFAR, TOTALWORK,ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"FROM V$SESSION_LONGOPS WHERE TOTALWORK != 0 AND SOFAR <> TOTALWORK;
==========================================================
SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024 * 1024) "Size (GB)",
SUM(fs.bytes) / (1024 * 1024 * 1024) "Free (GB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
order by 5 desc;
---------------------------------------------------------------------------------------------
alter tablespace JBBOS_DAT add datafile '+DATA' size 30g;
--------------------------------------------------------------------------------------
select a.tablespace_name tablespace,
d.TEMP_TOTAL_GB,
sum (a.used_blocks * d.block_size) / 1024 / 1024 / 1024 TEMP_USED_GB,
d.TEMP_TOTAL_GB - sum (a.used_blocks * d.block_size) / 1024 / 1024 / 1024 TEMP_FREE_GB
from gv$sort_segment a,
(
select b.name, c.block_size, sum (c.bytes) / 1024 / 1024 / 1024 TEMP_TOTAL_GB
from gv$tablespace b, gv$tempfile c
where b.ts#= c.ts#
group by b.name, c.block_size
) d
where a.tablespace_name = d.name
group by a.tablespace_name, d.TEMP_TOTAL_GB;
--------------------------------------------------------------------------------
select
srt.tablespace,
srt.segfile#,
srt.segblk#,
srt.blocks,
a.sid,
a.serial#,
a.username,
a.osuser,
a.status
from
v$session a,
v$sort_usage srt
where
a.saddr = srt.session_addr
order by
srt.tablespace, srt.segfile#, srt.segblk#,
srt.blocks;
===================================================================
select tablespace_name,file_name,bytes/(1024*1024*1024) "size_GB" from dba_temp_files;
alter tablespace TEMP add tempfile '+DATA' size 30G;
=========================================================================
select
srt.tablespace,
srt.segfile#,
srt.segblk#,
srt.blocks,
a.sid,
a.serial#,
a.username,
a.osuser,
a.status
from
v$session a,
v$sort_usage srt
where
a.saddr = srt.session_addr
order by
srt.tablespace, srt.segfile#, srt.segblk#,
srt.blocks;
===================================================================
select tablespace_name,file_name,bytes/(1024*1024*1024) "size_GB" from dba_temp_files;
alter tablespace TEMP add tempfile '+DATA' size 30G;
=========================================================================
Notes: expdp and impdp
If the Target database’s release is higher than the source, the VERSION parameter typically does not have to be specified because all objects in the source database will be compatible with the higher target release.
The VERSION parameter should be used when the Source database’s release is higher than the Target database.
expdp can be done --> from lower version to higher version
expdp can be need (version=oracle version) ---> from higher version to lower version
=================================================================================================
[user@hostname]$ cat schema_export_prod_<date-time>.par
DIRECTORY=DATA_PUMP
SCHEMAS=<schema_name>
DUMPFILE=exp_prod_01132023_%U.dmp
LOGFILE=exp_prod_schema_01132023.log
PARALLEL=6
FILESIZE=10G
EXCLUDE=STATISTICS
COMPRESSION=ALL
CLUSTER=N
STATUS=30
JOB_NAME=exp_prod_schema_01132023
[user@hostname]$ cat schema_prod_expdp_table_tablename.par
DIRECTORY=DATA_PUMP
SCHEMAS=<schema_name>
DUMPFILE=exp_prod_<schema_name>_<table_name>_%U.dmp
LOGFILE=exp_prod_<schema_name>_<table_name>.log
PARALLEL=12
FILESIZE=15G
COMPRESSION=ALL
CLUSTER=N
STATUS=30
#JOB_NAME=exp_prod_<schema_name>_<table_name>_01132023
INCLUDE=TABLE:"IN('<table_name>')"
[user@hostname]$ cat imp_<schema_name>.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=exp_prod_<schema_name>_01282023_%U.dmp
LOGFILE=IMP_<schema_name>_<table_name>_02022023.log
TABLE_EXISTS_ACTION=REPLACE
#o#JOB_NAME=<schema_name>_<table_name>
EXCLUDE=VIEW,PROCEDURE,PACKAGE,FUNCTION,TRIGGER
#schemas=<schema_name>
EXCLUDE=SCHEMA:"IN('<schema1_name>','<schema2_name>')"
PARALLEL=32
CLUSTER=N
FULL=Y
[user@hostname]$ cat <schema_name>_<host_name>.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=<schema_name>_<table_name>_%U.dmp
LOGFILE=IMP_<schema_name>_<table_name>_10022018.log
EXCLUDE=SCHEMA:"IN('<schema_name>','<schema_name>')"
SCHEMAS=<schema_name>
##SCHEMAS=<schema_name>
parallel=25
TABLE_EXISTS_ACTION=REPLACE
CLUSTER=N
[user@hostname]$ cat <schema_name>.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=<schema_name>_<table_name>_%U.dmp
LOGFILE=IMP_<schema_name>_<table_name>.log
EXCLUDE=SCHEMA:"IN('<schema_name>','<schema_name>')"
SCHEMAS=<schema_name>
##SCHEMAS=<schema_name>
parallel=25
TABLE_EXISTS_ACTION=REPLACE
CLUSTER=N
[user@hostname]$ cat <database>_FULL_EXP.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=Full_database_%U.dmp
LOGFILE=Full_<schema_name>_<table_name>.log
##JOB_NAME=03282016_IMP_EXPORT_ST1_NEW
FILESIZE=10G
CLUSTER=N
PARALLEL=4
full=y
EXCLUDE=SCHEMA:"IN('<schema_name>',...,'<schema_name>)"
[user@hostname]$ cat <database>_FULL_EXP.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=Full_DATABASE_exp_%U.dmp
LOGFILE=Full_database_exp.log
##JOB_NAME=<full_database_exp>
FILESIZE=10G
CLUSTER=N
PARALLEL=4
full=y
EXCLUDE=SCHEMA:"IN('<schema_name>',...,'<schema_name>')"
[user@hostname]$ cat DATABASE_TABLE_INDEX_CREATE_SCRIPT.par
DIRECTORY=DATA_PUMP_DIR
DUMPFILE=NAME_%U.dmp
LOGFILE=database_INDEX.log
JOB_NAME=database_INDEX
CLUSTER=N
PARALLEL=24
INCLUDE=INDEX
sqlfile=DATABASE_INDEXES_CREATE.sql
[user@hostname]$ cat <schema_name>_export_prod.par
DIRECTORY=DATA_PUMP_DIR
SCHEMAS=<schema_name>
DUMPFILE=exp_prod_<schema_name>_%U.dmp
LOGFILE=exp_prod_<schema_name>.log
PARALLEL=12
FILESIZE=15G
EXCLUDE=STATISTICS
COMPRESSION=ALL
CLUSTER=N
STATUS=30
EXCLUDE=TABLE:"IN ('<table_name>',...,'<table_name>')"
JOB_NAME=exp_prod_<schema_name>_<table_name>
########### Most Important Note ######################
Always take expdp backup for schemas before dropping its objects
------------>> Take expdp schema backup before dropping schema objects <<---------------
or take restore point or rman backup before doing same if asked.
