Oracle 常用指令 & SQL - Datapump 篇 (datapump section)

3/01/2022 12:54:00 下午

datapump 相關

＊查詢目前datapump job 狀態

set line 200
col owner_name format a20
col job_name format a30
col operation format a20
col job_mode format a20
col state format a20
select * from DBA_DATAPUMP_JOBS;

OWNER_NAME           JOB_NAME                       OPERATION            JOB_MODE             STATE                    DEGREE ATTACHED_SESSIONS DATAPUMP_SESSIONS
-------------------- ------------------------------ -------------------- -------------------- -------------------- ---------- ----------------- -----------------
SYSTEM               SYS_EXPORT_TABLE_03            EXPORT               TABLE                EXECUTING                     8                 1                11
SYSTEM               SYS_EXPORT_TABLE_01            EXPORT               TABLE                NOT RUNNING                   0                 0                 0


SYSTEM               SYS_EXPORT_TABLE_02            EXPORT               TABLE                NOT RUNNING                   0                 0                 0

＊查詢目前datapump job 開啟的 session

select * from DBA_DATAPUMP_SESSIONS;

OWNER_NAME           JOB_NAME                          INST_ID SADDR            SESSION_TYPE
-------------------- ------------------------------ ---------- ---------------- ----------------------------
SYSTEM               SYS_EXPORT_TABLE_03                     1 070000055CCA1AC8 DBMS_DATAPUMP
SYSTEM               SYS_EXPORT_TABLE_03                     1 0700000554F34648 MASTER
SYSTEM               SYS_EXPORT_TABLE_03                     1 0700000564AAF750 WORKER
SYSTEM               SYS_EXPORT_TABLE_03                     1 0700000558BAFF40 WORKER
SYSTEM               SYS_EXPORT_TABLE_03                     1 0700000564CCA370 EXTERNAL TABLE
SYSTEM               SYS_EXPORT_TABLE_03                     1 0700000550E2A108 EXTERNAL TABLE
SYSTEM               SYS_EXPORT_TABLE_03                     1 0700000550E5CAA8 EXTERNAL TABLE
SYSTEM               SYS_EXPORT_TABLE_03                     1 0700000560B55DC8 EXTERNAL TABLE
SYSTEM               SYS_EXPORT_TABLE_03                     1 0700000550EE29C8 EXTERNAL TABLE
SYSTEM               SYS_EXPORT_TABLE_03                     1 0700000564AC44B0 EXTERNAL TABLE
SYSTEM               SYS_EXPORT_TABLE_03                     1 0700000558DE5800 EXTERNAL TABLE

＊attach 查看目前的 job 狀態 (只能登入正在執行的帳號，無法利用sys登入看別人的job)

$ impdp '"/ as sysdba"' attach=SYS_IMPORT_FULL_01
Import> status

$ expdp system attach=SYS_EXPORT_TABLE_03

Export: Release 11.2.0.2.0 - Production on Tue Mar 22 13:00:13 2022

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
Password: 

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

Job: SYS_EXPORT_TABLE_03
  Owner: SYSTEM                         
  Operation: EXPORT                         
  Creator Privs: TRUE                           
  GUID: DAC873E6AFF3021EE0530A510524021E
  Start Time: Tuesday, 22 March, 2022 12:57:45
  Mode: TABLE                          
  Instance: ORCL
  Max Parallelism: 8
  EXPORT Job Parameters:
  Parameter Name      Parameter Value:
     CLIENT_COMMAND        system/******** parfile=expdp_dt.par    
     COMPRESSION           ALL                                     
     INCLUDE_METADATA      0
  State: EXECUTING                      
  Bytes Processed: 0
  Current Parallelism: 8
  Job Error Count: 0
  Dump File: /dmp/expdp_DT_2022M02_%u.dmp
  Dump File: /dmp/expdp_DT_2022M02_01.dmp
    bytes written: 4,096
  Dump File: /dmp/expdp_DT_2022M02_02.dmp
    bytes written: 4,096
  Dump File: /dmp/expdp_DT_2022M02_03.dmp
    bytes written: 4,096
  Dump File: /dmp/expdp_DT_2022M02_04.dmp
    bytes written: 4,096
  Dump File: /dmp/expdp_DT_2022M02_05.dmp
    bytes written: 4,096
  Dump File: /dmp/expdp_DT_2022M02_06.dmp
    bytes written: 4,096
  Dump File: /dmp/expdp_DT_2022M02_07.dmp
    bytes written: 4,096
  
Worker 1 Status:
  Process Name: DW00
  State: EXECUTING                      
  Object Schema: MGR
  Object Name: SAVE_DT
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 1
  Completed Rows: 151,382,533
  Worker Parallelism: 7
  
Worker 2 Status:
  Process Name: DW01
  State: WORK WAITING

Export> status

Job: SYS_EXPORT_TABLE_03
  Operation: EXPORT                         
  Mode: TABLE                          
  State: EXECUTING                      
  Bytes Processed: 0
  Current Parallelism: 8
  Job Error Count: 0
  Dump File: /dmp/expdp_DT_2022M02_%u.dmp
  Dump File: /dmp/expdp_DT_2022M02_01.dmp
    bytes written: 4,096
  Dump File: /dmp/expdp_DT_2022M02_02.dmp
    bytes written: 4,096
  Dump File: /dmp/expdp_DT_2022M02_03.dmp
    bytes written: 4,096
  Dump File: /dmp/expdp_DT_2022M02_04.dmp
    bytes written: 4,096
  Dump File: /dmp/expdp_DT_2022M02_05.dmp
    bytes written: 4,096
  Dump File: /dmp/expdp_DT_2022M02_06.dmp
    bytes written: 4,096
  Dump File: /dmp/expdp_DT_2022M02_07.dmp
    bytes written: 4,096
  
Worker 1 Status:
  Process Name: DW00
  State: EXECUTING                      
  Object Schema: MGR
  Object Name: SAVE_DT
  Object Type: TABLE_EXPORT/TABLE/TABLE_DATA
  Completed Objects: 1
  Total Objects: 1
  Completed Rows: 394,463,832
  Worker Parallelism: 7
  
Worker 2 Status:
  Process Name: DW01
  State: WORK WAITING

＊impdp 如果出現 tablespace not exists時，帶入參數 TRANSFORM=SEGMENT_ATTRIBUTES:n，會直接使用 schema default tablespace

impdp sys/password schemas=SCHEMANAME dumpfile=schema.dmp logfile=impdp.log TRANSFORM=SEGMENT_ATTRIBUTES:n

＊利用dumpfile導出sqlfile

impdp system directory=dmp dumpfile=expdp_schema_%U.dmp logfile=schema_ddl.log sqlfile=schema_ddl.sql

＊開啟trace

impdp system directory=dmp dumpfile=expdp_schema_%U.dmp logfile=schema_ddl.log trace=480300

＊匯出Special Partition Table

expdp system directory=dmp dumpfile=expdp_schema_%U.dmp logfile=schema_ddl.log tables=schema.table:partition_name

＊指定Table匯出篩選資料dump

SQL> select * from dba_directories where directory_name ='DMP';
OWNER                          DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ------------------------------ --------------------------------------------------
SYS                            DMP                            /ARCHIVE/dmp


CREATE TABLE SCHEMA.DEST_TABLE_20221026_1028 
ORGANIZATION EXTERNAL 
  ( TYPE ORACLE_DATAPUMP 
    DEFAULT DIRECTORY dmp 
    ACCESS PARAMETERS (COMPRESSION ENABLED)  
    LOCATION ('TABLE_20221026_1028_01.dmp',
              'TABLE_20221026_1028_02.dmp',
              'TABLE_20221026_1028_03.dmp',
              'TABLE_20221026_1028_04.dmp',
              'TABLE_20221026_1028_05.dmp',
              'TABLE_20221026_1028_06.dmp',
              'TABLE_20221026_1028_07.dmp',
              'TABLE_20221026_1028_08.dmp')
  ) AS 
SELECT /*+ parallel(8) */* 
FROM SCHEMA.SOURCE_TABLE PARTITION (2022M10)  
WHERE pdate >= to_timestamp('20221026 214000' , 'yyyymmdd hh24:mi:ss') 
  AND pdate <= to_timestamp('20221028 180000' , 'yyyymmdd hh24:mi:ss');
  

oracle@dba2/ARCHIVE/dmp>ls -l *.dmp
-rw-r-----    1 oracle   oinstall  432570368 Dec 06 09:38 TABLE_20221026_1028_02.dmp
-rw-r-----    1 oracle   oinstall  479686656 Dec 06 09:38 TABLE_20221026_1028_05.dmp
-rw-r-----    1 oracle   oinstall  274616320 Dec 06 09:38 TABLE_20221026_1028_04.dmp
-rw-r-----    1 oracle   oinstall  466444288 Dec 06 09:38 TABLE_20221026_1028_01.dmp
-rw-r-----    1 oracle   oinstall  483336192 Dec 06 09:38 TABLE_20221026_1028_07.dmp
-rw-r-----    1 oracle   oinstall  695775232 Dec 06 09:38 TABLE_20221026_1028_03.dmp
-rw-r-----    1 oracle   oinstall  665026560 Dec 06 09:38 TABLE_20221026_1028_06.dmp
-rw-r-----    1 oracle   oinstall  482201600 Dec 06 09:38 TABLE_20221026_1028_08.dmp

＊利用獨立table dumpfile還原建立成table

CREATE TABLE SCHEMA.DEST_TABLE_20221026_1028 (
 PK_ID VARCHAR2(24),
 C1 VARCHAR2(8),
 C2 VARCHAR2(10),
 PDATE DATE
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_DATAPUMP
  DEFAULT DIRECTORY dmp
  LOCATION ('TABLE_20221026_1028_01.dmp',
            'TABLE_20221026_1028_02.dmp',
            'TABLE_20221026_1028_03.dmp',
            'TABLE_20221026_1028_04.dmp',
            'TABLE_20221026_1028_05.dmp',
            'TABLE_20221026_1028_06.dmp',
            'TABLE_20221026_1028_07.dmp',
            'TABLE_20221026_1028_08.dmp')  
);
JohnsonJ

張貼者： JohnsonJ

張貼留言

0 留言