2020年11月30日 星期一

Oracle expdp impdp CONTENT=METADATA_ONLY testing

 

新增兩個USER,創建表,賦與權限。實驗看看哪個復原方式可以達到我要的效果。

CREATE USER HR3

  IDENTIFIED BY VALUES 'S:78DE8200DFBDE7C8A683934A5AF43E2FE8614C357C0BBA8DBC7C7898D824;9F1BFB4526C62B14'

  DEFAULT TABLESPACE USERS

  TEMPORARY TABLESPACE TEMP

  PROFILE DEFAULT

  ACCOUNT UNLOCK;


-- 3 Roles for HR3 

GRANT CONNECT TO HR3;

GRANT RESOURCE TO HR3;

GRANT SELECT_CATALOG_ROLE TO HR3;

ALTER USER HR3 DEFAULT ROLE ALL;


-- 5 System Privileges for HR3 

GRANT ALTER SYSTEM TO HR3;

GRANT CREATE DATABASE LINK TO HR3;

GRANT CREATE MATERIALIZED VIEW TO HR3;

GRANT ON COMMIT REFRESH TO HR3;

GRANT UNLIMITED TABLESPACE TO HR3;



CREATE USER HR4

  IDENTIFIED BY VALUES 'S:78DE8200DFBDE7C8A683934A5AF43E2FE8614C357C0BBA8DBC7C7898D824;9F1BFB4526C62B14'

  DEFAULT TABLESPACE TEST_TBS

  TEMPORARY TABLESPACE TEMP

  PROFILE DEFAULT

  ACCOUNT UNLOCK;


-- 3 Roles for HR4 

GRANT CONNECT TO HR4;

GRANT RESOURCE TO HR4;

GRANT SELECT_CATALOG_ROLE TO HR4;

ALTER USER HR4 DEFAULT ROLE ALL;


-- 5 System Privileges for HR4 

GRANT ALTER SYSTEM TO HR4;

GRANT CREATE DATABASE LINK TO HR4;

GRANT CREATE MATERIALIZED VIEW TO HR4;

GRANT ON COMMIT REFRESH TO HR4;

GRANT UNLIMITED TABLESPACE TO HR4;



SQL> create table hr3.EMPLOYEES_TEST as select * from HR1.EMPLOYEES_TEST;


Table created.


SQL> grant select on hr3.EMPLOYEES_TEST to hr4;


Grant succeeded.














[oracle@testdb89 dpdump]$ expdp DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR3 CONTENT=METADATA_ONLY EXCLUDE=statistics dumpfile=hr3_20201130.dmp


Export: Release 11.2.0.4.0 - Production on Mon Nov 30 12:42:39 2020


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


Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR3 CONTENT=METADATA_ONLY EXCLUDE=statistics dumpfile=hr3_20201130.dmp

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  /u01/app/oracle/admin/DB11G/dpdump/hr3_20201130.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 30 12:42:55 2020 elapsed 0 00:00:10



[oracle@testdb89 dpdump]$ expdp DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR4 CONTENT=METADATA_ONLY EXCLUDE=statistics dumpfile=hr4_20201130.dmp


Export: Release 11.2.0.4.0 - Production on Mon Nov 30 12:44:47 2020


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

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR4 CONTENT=METADATA_ONLY EXCLUDE=statistics dumpfile=hr4_20201130.dmp

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  /u01/app/oracle/admin/DB11G/dpdump/hr4_20201130.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 30 12:45:02 2020 elapsed 0 00:00:07


[oracle@testdb89 dpdump]$ expdp DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR3,HR4 CONTENT=METADATA_ONLY EXCLUDE=statistics dumpfile=hr34_20201130.dmp


Export: Release 11.2.0.4.0 - Production on Mon Nov 30 12:45:17 2020


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

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** DIRECTORY=DATA_PUMP_DIR SCHEMAS=HR3,HR4 CONTENT=METADATA_ONLY EXCLUDE=statistics dumpfile=hr34_20201130.dmp

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

>>> DBMS_AW_EXP: SYS.AW$EXPRESS: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWMD: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWCREATE10G: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWXML: OLAP not enabled

>>> DBMS_AW_EXP: SYS.AW$AWREPORT: OLAP not enabled

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  /u01/app/oracle/admin/DB11G/dpdump/hr34_20201130.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 30 12:45:30 2020 elapsed 0 00:00:07


[oracle@testdb89 dpdump]$ expdp include=user SCHEMAS=HR4 DIRECTORY=DATA_PUMP_DIR dumpfile=userhr4_20201130.dmp


Export: Release 11.2.0.4.0 - Production on Mon Nov 30 13:36:29 2020


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

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01":  system/******** include=user SCHEMAS=HR4 DIRECTORY=DATA_PUMP_DIR dumpfile=userhr4_20201130.dmp

Estimate in progress using BLOCKS method...

Total estimation using BLOCKS method: 0 KB

Processing object type SCHEMA_EXPORT/USER

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

  /u01/app/oracle/admin/DB11G/dpdump/userhr4_20201130.dmp

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at Mon Nov 30 13:36:41 2020 elapsed 0 00:00:04



SQL> DROP USER HR4 CASCADE;


[oracle@testdb89 dpdump]$ impdp DIRECTORY=DATA_PUMP_DIR DUMPFILE=userhr4_20201130.dmp


Import: Release 11.2.0.4.0 - Production on Mon Nov 30 13:37:24 2020


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

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** DIRECTORY=DATA_PUMP_DIR DUMPFILE=userhr4_20201130.dmp

Processing object type SCHEMA_EXPORT/USER

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Mon Nov 30 13:38:40 2020 elapsed 0 00:00:02








用expdp include=user 的export file 做impdp,只會create user。沒有相關system, object, role 等權限。

沒有權限,連登入資料庫都會失敗。

[oracle@testdb89 dpdump]$ sqlplus hr4/hr


SQL*Plus: Release 11.2.0.4.0 Production on Mon Nov 30 13:41:44 2020


Copyright (c) 1982, 2013, Oracle.  All rights reserved.


ERROR:

ORA-01045: user HR4 lacks CREATE SESSION privilege; logon denied

Enter user-name:




[oracle@testdb89 dpdump]$ sqlplus / as sysdba

SQL> DROP USER HR4 CASCADE;

[oracle@testdb89 dpdump]$ impdp DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr4_20201130.dmp











缺 -- 1 Object Privilege for HR4 

GRANT SELECT ON HR3.EMPLOYEES_TEST TO HR4;




[oracle@testdb89 dpdump]$ sqlplus / as sysdba

SQL> DROP USER HR4 CASCADE;

[oracle@testdb89 dpdump]$ impdp DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr34_20201130.dmp


Import: Release 11.2.0.4.0 - Production on Mon Nov 30 12:47:47 2020


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

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr34_20201130.dmp

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"HR3" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39151: Table "HR3"."EMPLOYEES_TEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 2 error(s) at Mon Nov 30 12:47:56 2020 elapsed 0 00:00:02












缺 -- 1 Object Privilege for HR4 

GRANT SELECT ON HR3.EMPLOYEES_TEST TO HR4;

因為 ORA-39151: Table "HR3"."EMPLOYEES_TEST" exists. All dependent metadata and data will be skipped due to table_exists_action of skip


[oracle@testdb89 dpdump]$ sqlplus / as sysdba

SQL> DROP USER HR3 CASCADE;

SQL> DROP USER HR4 CASCADE;

[oracle@testdb89 dpdump]$ impdp DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr34_20201130.dmp

Import: Release 11.2.0.4.0 - Production on Mon Nov 30 12:49:58 2020


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

Connected to: Oracle Database 11g Release 11.2.0.4.0 - 64bit Production

Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** DIRECTORY=DATA_PUMP_DIR DUMPFILE=hr34_20201130.dmp

Processing object type SCHEMA_EXPORT/USER

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT

Job "SYSTEM"."SYS_IMPORT_FULL_01" successfully completed at Mon Nov 30 12:50:09 2020 elapsed 0 00:00:02


將HR3, HR4 都完整drop,才會恢復的完整。 HR4 有 GRANT SELECT ON HR3.EMPLOYEES_TEST TO HR4;













SQL> set long 64000

SQL> select dbms_metadata.get_ddl('USER','HR3') from dual;

SQL> select dbms_metadata.get_ddl('USER','HR4') from dual;

DBMS_METADATA.GET_DDL('USER','HR4')

--------------------------------------------------------------------------------


   CREATE USER "HR4" IDENTIFIED BY VALUES 'S:78DE8200DFBDE7C8A683934A5AF43E2FE86

14C357C0BBA8DBC7C7898D824;9F1BFB4526C62B14'

      DEFAULT TABLESPACE "TEST_TBS"

      TEMPORARY TABLESPACE "TEMP"


如果是想在開發機,設定和正式機相同的密碼。


ALTER USER "HR4" IDENTIFIED BY VALUES 'S:78DE8200DFBDE7C8A683934A5AF43E2FE86

14C357C0BBA8DBC7C7898D824;9F1BFB4526C62B14'






2020年11月24日 星期二

Oracle Row Chaining and Migration 圖示

 

The Secrets of Oracle Row Chaining and Migration

https://www.akadia.com/services/ora_chained_rows.html



Row Migration

We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the other data that exists there currently).  A migration means that the entire row will move and we just leave behind the «forwarding address». So, the original block just has the rowid of the new block and the entire row is moved.






Row Chaining

A row is too large to fit into a single database block. For example, if you use a 4KB blocksize for your database, and you need to insert a row of 8KB into it, Oracle will use 3 blocks and store the row in pieces. Some conditions that will cause row chaining are: Tables whose rowsize exceeds the blocksize. Tables with LONG and LONG RAW columns are prone to having chained rows. Tables with more then 255 columns will have chained rows as Oracle break wide tables up into pieces. So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks.





"alter table move tablespace" tips

http://www.dba-oracle.com/t_alter_table_move_index_constraint.htm


reorg


Identifying Oracle Tables with Migrated/Chained Rows

http://www.dba-oracle.com/t_identify_chained_rows.htm


資料鏈結(Chained Row)有兩種型態
http://dbtim.blogspot.com/2016/05/1-3.html


可用TOAD Rebuild Table 或是 Repair Chained Rows (好用)來做 優化


analyze table TableName compute statistics;