dba:oracle:docs:import_and_export
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revision | Last revisionBoth sides next revision | ||
dba:oracle:import_and_export [2022/02/11 11:12] – ↷ Page moved from oracle:import_and_export to dba:oracle:import_and_export dodger | dba:oracle:docs:import_and_export [2023/01/31 08:22] – removed - external edit (Unknown date) 127.0.0.1 | ||
---|---|---|---|
Line 1: | Line 1: | ||
- | ====== [DOC] IMPDP/EXPDP ====== | ||
- | |||
- | ===== External Docs ===== | ||
- | |||
- | * [[https:// | ||
- | |||
- | |||
- | |||
- | ====== DB querys ====== | ||
- | ===== List running jobs ===== | ||
- | <code sql> | ||
- | COL OWNER_NAME FORMAT A35 | ||
- | COL JOB_NAME FORMAT A40 | ||
- | COL OPERATION FORMAT A50 | ||
- | COL JOB_MODE FORMAT A50 | ||
- | COL STATE FORMAT A30 | ||
- | SELECT owner_name, job_name, operation, job_mode, state FROM dba_datapump_jobs; | ||
- | </ | ||
- | |||
- | |||
- | ===== List job completion progress ===== | ||
- | <code sql> | ||
- | col opname format a30 | ||
- | SELECT | ||
- | opname, | ||
- | sid, | ||
- | serial#, | ||
- | context, | ||
- | sofar, | ||
- | totalwork, | ||
- | round(sofar / totalwork * 100, 2) " | ||
- | FROM | ||
- | v$session_longops | ||
- | WHERE | ||
- | opname IN ( | ||
- | SELECT | ||
- | d.job_name | ||
- | FROM | ||
- | v$session | ||
- | v$process | ||
- | dba_datapump_sessions | ||
- | WHERE | ||
- | p.addr = s.paddr | ||
- | AND s.saddr = d.saddr | ||
- | ) | ||
- | AND opname NOT LIKE ' | ||
- | AND totalwork != 0 | ||
- | AND sofar <> totalwork; | ||
- | </ | ||
- | |||
- | ===== List what is doing each job ===== | ||
- | |||
- | <code sql> | ||
- | col username format a32 | ||
- | col job_name format a30 | ||
- | col program format a50 | ||
- | COL ACTUALDATE FORMAT A20 | ||
- | COL SPID FORMAT A10 | ||
- | COL SID_SERIAL FORMAT A20 | ||
- | SELECT TO_CHAR(SYSDATE, | ||
- | | ||
- | s.sid || ',' | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | p.pid | ||
- | FROM V$SESSION s, V$PROCESS p, DBA_DATAPUMP_SESSIONS d | ||
- | WHERE p.addr = s.paddr AND s.saddr = d.saddr; | ||
- | </ | ||
- | Same but with sqltext | ||
- | <code sql> | ||
- | |||
- | col program format a50 | ||
- | COL ACTUALDATE FORMAT A20 | ||
- | COL SPID FORMAT A10 | ||
- | col username format a20 | ||
- | SELECT s.program, | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | | ||
- | FROM V$SESSION s, V$PROCESS p, DBA_DATAPUMP_SESSIONS d, V$SQLAREA SQA | ||
- | WHERE p.addr = s.paddr AND s.saddr = d.saddr | ||
- | AND S.SQL_ID=SQA.SQL_ID; | ||
- | </ | ||
- | |||
- | |||
- | and: | ||
- | <code sql> | ||
- | col event format a100 | ||
- | select program, event | ||
- | from | ||
- | where program like ' | ||
- | </ | ||
- | |||
- | ===== Available Export Objects ===== | ||
- | This will list all available objects for an INCLUDE/ | ||
- | <code sql> | ||
- | COL HET_TYPE FORMAT A30 | ||
- | COL OBJECT_PATH FORMAT A120 | ||
- | SELECT HET_TYPE, OBJECT_PATH FROM DBA_EXPORT_OBJECTS ; | ||
- | </ | ||
- | |||
- | |||
- | |||
- | ===== create a datapump user ===== | ||
- | |||
- | This is a user for impdp/ | ||
- | Specially useful with '' | ||
- | <code sql> | ||
- | CREATE TABLESPACE SYSDP DATAFILE ' | ||
- | size 1G | ||
- | EXTENT MANAGEMENT LOCAL AUTOALLOCATE | ||
- | segment space management auto; | ||
- | |||
- | create user SYSDP identified by SUPERDBA default tablespace SYSDP quota unlimited on SYSDP; | ||
- | |||
- | grant create table to SYSDP; | ||
- | grant datapump_exp_full_database to SYSDP; | ||
- | grant datapump_imp_full_database to SYSDP; | ||
- | grant read, write on directory EXPDP_DIR to SYSDP; | ||
- | </ | ||
- | |||
- | ====== Killing jobs ====== | ||
- | |||
- | |||
- | ===== Stopping a job ===== | ||
- | |||
- | < | ||
- | </ | ||
- | Then | ||
- | < | ||
- | Kill | ||
- | </ | ||
- | select '' | ||
- | |||
- | |||
- | ===== Killing sessions from oracle ===== | ||
- | |||
- | Will show only dp sessions with '' | ||
- | <code sql> | ||
- | col username format a32 | ||
- | col job_name format a30 | ||
- | col program format a50 | ||
- | COL ACTUALDATE FORMAT A20 | ||
- | COL SPID FORMAT A10 | ||
- | COL SID_SERIAL FORMAT A20 | ||
- | SELECT 'ALTER SYSTEM KILL SESSION ''' | ||
- | FROM V$SESSION s, V$PROCESS p, DBA_DATAPUMP_SESSIONS d | ||
- | WHERE p.addr = s.paddr AND s.saddr = d.saddr | ||
- | -- AND D.STATE = 'NOT RUNNING' | ||
- | ; | ||
- | </ | ||
- | |||
- | |||
- | Full information: | ||
- | <code sql> | ||
- | SELECT T1.SID_SERIAL, | ||
- | T1.OSPID, | ||
- | T1.SQLID, | ||
- | T1.USERNAME, | ||
- | T1.OSUSER, | ||
- | T1.HOSTNAME, | ||
- | T1.PROGRAM, | ||
- | T1.CLIENT_INFO | ||
- | from ( | ||
- | SELECT ses.sid || ',' | ||
- | p.spid AS OSPID, | ||
- | SES.SQL_ID SQLID, | ||
- | SES.USERNAME USERNAME, | ||
- | SES.OSUSER OSUSER, | ||
- | SES.MACHINE HOSTNAME, | ||
- | SES.PROGRAM PROGRAM, | ||
- | SES.CLIENT_INFO | ||
- | FROM V$SESSION SES, V$PROCESS P | ||
- | WHERE SES.paddr = p.addr | ||
- | ORDER BY SES.MACHINE | ||
- | ) t1, | ||
- | ( | ||
- | SELECT s.sid || ',' | ||
- | FROM V$SESSION s, V$PROCESS p, DBA_DATAPUMP_SESSIONS d | ||
- | WHERE p.addr = s.paddr AND s.saddr = d.saddr | ||
- | ) t2 | ||
- | where t1.SID_SERIAL=t2.SID_SERIAL | ||
- | / | ||
- | </ | ||
- | |||
- | |||
- | ===== Drop table associated to a DP job ===== | ||
- | After killing a job | ||
- | <code sql> | ||
- | SELECT 'DROP TABLE ' || owner_name || ' | ||
- | </ | ||
- | |||
- | |||
- | ====== do not remove ====== | ||
- | |||
- | |||
- | <code sql> | ||
- | </ | ||
- | <code sql> | ||
- | </ | ||
- | <code sql> | ||
- | </ | ||
dba/oracle/docs/import_and_export.txt · Last modified: 2023/01/31 08:22 by dodger