目录
Exporting and Importing Between PDBs
在pdb之间传输数据
实验：将pdb1 全库导入到pdb2，pdb2最好是新建的全新的数据库。
查看一下整个cdb数据库（pdb1,pdb2,pdbseed）在进行导入前的情况
pdb1是我在创建cdb数据库的时候创建的，pdb2是我后面永sql语句创建的。
[oracle@19c ~]$ cd /u01/app/oracle/oradata
[oracle@19c oradata]$ ll -ls
total 0
0 drwxr-x---. 5 oracle oinstall 239 Jan 10 17:54 CDB
[oracle@19c oradata]$ cd CDB
[oracle@19c CDB]$ ll -ls
total 2439084
18288 -rw-r-----. 1 oracle oinstall 18726912 Jan 10 18:02 control01.ctl
18288 -rw-r-----. 1 oracle oinstall 18726912 Jan 10 18:02 control02.ctl
0 drwxr-x---. 2 oracle oinstall 104 Jan 10 16:35 pdb1
0 drwxr-x---. 2 oracle oinstall 111 Jan 10 17:54 pdb2
0 drwxr-x---. 2 oracle oinstall 111 Jan 10 16:24 pdbseed
204804 -rw-r-----. 1 oracle oinstall 209715712 Jan 10 18:01 redo01.log
204804 -rw-r-----. 1 oracle oinstall 209715712 Jan 10 16:34 redo02.log
204804 -rw-r-----. 1 oracle oinstall 209715712 Jan 10 16:34 redo03.log
532488 -rw-r-----. 1 oracle oinstall 545267712 Jan 10 18:00 sysaux01.dbf
911368 -rw-r-----. 1 oracle oinstall 933240832 Jan 10 17:59 system01.dbf
1184 -rw-r-----. 1 oracle oinstall 33562624 Jan 10 16:34 temp01.dbf
337928 -rw-r-----. 1 oracle oinstall 346038272 Jan 10 18:00 undotbs01.dbf
5128 -rw-r-----. 1 oracle oinstall 5251072 Jan 10 16:34 users01.dbf
[oracle@19c CDB]$ cd pdb2
[oracle@19c pdb2]$ ll -ls
total 716880
337928 -rw-r-----. 1 oracle oinstall 346038272 Jan 10 18:00 sysaux01.dbf
276488 -rw-r-----. 1 oracle oinstall 283123712 Jan 10 18:00 system01.dbf
56 -rw-r-----. 1 oracle oinstall 37756928 Jan 10 17:55 temp012024-01-10_16-24-28-422-PM.dbf
102408 -rw-r-----. 1 oracle oinstall 104865792 Jan 10 18:00 undotbs01.dbf
[oracle@19c pdb2]$ cd pdbseed
-bash: cd: pdbseed: No such file or directory
[oracle@19c pdb2]$ cd -
/u01/app/oracle/oradata/CDB
[oracle@19c CDB]$ cd pdbseed
[oracle@19c pdbseed]$ ll -ls
total 716880
337928 -rw-r-----. 1 oracle oinstall 346038272 Jan 10 16:34 sysaux01.dbf
276488 -rw-r-----. 1 oracle oinstall 283123712 Jan 10 16:34 system01.dbf
56 -rw-r-----. 1 oracle oinstall 37756928 Jan 10 16:27 temp012024-01-10_16-24-28-422-PM.dbf
102408 -rw-r-----. 1 oracle oinstall 104865792 Jan 10 16:34 undotbs01.dbf
[oracle@19c pdbseed]$ cd -
/u01/app/oracle/oradata/CDB
[oracle@19c CDB]$ cd pdb1
[oracle@19c pdb1]$ ll -ls
total 742640
358408 -rw-r-----. 1 oracle oinstall 367009792 Jan 10 19:40 sysaux01.dbf
276488 -rw-r-----. 1 oracle oinstall 283123712 Jan 10 20:10 system01.dbf
208 -rw-r-----. 1 oracle oinstall 37756928 Jan 10 17:52 temp01.dbf
102408 -rw-r-----. 1 oracle oinstall 104865792 Jan 10 20:10 undotbs01.dbf
5128 -rw-r-----. 1 oracle oinstall 5251072 Jan 10 17:43 users01.dbf
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jan 10 17:39:26 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. ?All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show pdbs;----首先我们进入到cdb$root看下整体的数据文件情况
? ? CON_ID CON_NAME?? ??? ??? ? ?OPEN MODE ?RESTRICTED
---------- ------------------------------ ---------- ----------
?? ? 2 PDB$SEED?? ??? ??? ? ?READ ONLY ?NO
?? ? 3 PDB1 ?? ??? ??? ? ?READ WRITE NO
SQL> select property_name ,property_value from database_properties;----这里是看字符集和语言，在导出导入的时候都需要先声明
PROPERTY_NAME?? ??? ??? ??? ??? ? ? PROPERTY_VALUE
-------------------------------------------------- --------------------------------------------------
MAX_PDB_SNAPSHOTS?? ??? ??? ??? ? ? 8
CON_VSN ?? ??? ??? ??? ??? ? ? 4
DICTIONARY_ENDIAN_TYPE?? ??? ??? ??? ? ? LITTLE
LOCAL_UNDO_ENABLED?? ??? ??? ??? ? ? TRUE
OLS_OID_STATUS?? ??? ??? ??? ??? ? ? 0
GLOBAL_DB_NAME?? ??? ??? ??? ??? ? ? CDB
MAX_STRING_SIZE ?? ??? ??? ??? ? ? STANDARD
NO_USERID_VERIFIER_SALT ?? ??? ??? ? ? AC198494D2611286E6E4DF94A6FCE0F3
WORKLOAD_REPLAY_MODE
WORKLOAD_CAPTURE_MODE
NLS_RDBMS_VERSION?? ??? ??? ??? ? ? 19.0.0.0.0
PROPERTY_NAME?? ??? ??? ??? ??? ? ? PROPERTY_VALUE
-------------------------------------------------- --------------------------------------------------
NLS_NCHAR_CONV_EXCP?? ??? ??? ??? ? ? FALSE
NLS_LENGTH_SEMANTICS?? ??? ??? ??? ? ? BYTE
NLS_COMP?? ??? ??? ??? ??? ? ? BINARY
NLS_DUAL_CURRENCY?? ??? ??? ??? ? ? $
NLS_TIMESTAMP_TZ_FORMAT ?? ??? ??? ? ? DD-MON-RR HH.MI.SSXFF AM TZR
NLS_TIME_TZ_FORMAT?? ??? ??? ??? ? ? HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_FORMAT?? ??? ??? ??? ? ? DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_FORMAT ?? ??? ??? ??? ? ? HH.MI.SSXFF AM
NLS_SORT?? ??? ??? ??? ??? ? ? BINARY
NLS_DATE_LANGUAGE?? ??? ??? ??? ? ? AMERICAN
NLS_DATE_FORMAT ?? ??? ??? ??? ? ? DD-MON-RR
PROPERTY_NAME?? ??? ??? ??? ??? ? ? PROPERTY_VALUE
-------------------------------------------------- --------------------------------------------------
NLS_CALENDAR?? ??? ??? ??? ??? ? ? GREGORIAN
NLS_NUMERIC_CHARACTERS?? ??? ??? ??? ? ? .,
NLS_NCHAR_CHARACTERSET?? ??? ??? ??? ? ? AL16UTF16
NLS_CHARACTERSET?? ??? ??? ??? ? ? AL32UTF8
NLS_ISO_CURRENCY?? ??? ??? ??? ? ? AMERICA
NLS_CURRENCY?? ??? ??? ??? ??? ? ? $
NLS_TERRITORY?? ??? ??? ??? ??? ? ? AMERICA
NLS_LANGUAGE?? ??? ??? ??? ??? ? ? AMERICAN
DEFAULT_TBS_TYPE?? ??? ??? ??? ? ? SMALLFILE
DST_SECONDARY_TT_VERSION?? ??? ??? ? ? 0
DST_PRIMARY_TT_VERSION?? ??? ??? ??? ? ? 32
PROPERTY_NAME?? ??? ??? ??? ??? ? ? PROPERTY_VALUE
-------------------------------------------------- --------------------------------------------------
DST_UPGRADE_STATE?? ??? ??? ??? ? ? NONE
DBTIMEZONE?? ??? ??? ??? ??? ? ? 00:00
TDE_MASTER_KEY_ID
EXPORT_VIEWS_VERSION?? ??? ??? ??? ? ? 8
Flashback Timestamp TimeZone?? ??? ??? ? ? GMT
DEFAULT_EDITION ?? ??? ??? ??? ? ? ORA$BASE
DEFAULT_PERMANENT_TABLESPACE?? ??? ??? ? ? USERS
DEFAULT_TEMP_TABLESPACE ?? ??? ??? ? ? TEMP
DICT.BASE?? ??? ??? ??? ??? ? ? 2
42 rows selected.
SQL> select tablespace_name,block_size,status,logging,extent_management,segment_space_management from dba_tablespaces;----查看表空间信息
TABLESPACE_NAME ?? ? ? ? ? BLOCK_SIZE STATUS ? ?LOGGING ? EXTENT_MAN SEGMEN
------------------------------ ---------- --------- --------- ---------- ------
SYSTEM?? ??? ??? ??? ? ? ? 8192 ONLINE ? ?LOGGING ? LOCAL?? ? MANUAL
SYSAUX?? ??? ??? ??? ? ? ? 8192 ONLINE ? ?LOGGING ? LOCAL?? ? AUTO
UNDOTBS1?? ??? ??? ? ? ? 8192 ONLINE ? ?LOGGING ? LOCAL?? ? MANUAL
TEMP?? ??? ??? ??? ? ? ? 8192 ONLINE ? ?NOLOGGING LOCAL?? ? MANUAL
USERS?? ??? ??? ??? ? ? ? 8192 ONLINE ? ?LOGGING ? LOCAL?? ? AUTO
SQL> select tablespace_name,file_name from dba_temp_files;-----查看临时表空间数据文件
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEMP
/u01/app/oracle/oradata/CDB/temp01.dbf
SQL> alter session set container=pdb1;-----切换到pdb1
Session altered.
SQL> select name from v$datafile;-----查看数据文件信息，
NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB/pdb1/system01.dbf
/u01/app/oracle/oradata/CDB/pdb1/sysaux01.dbf
/u01/app/oracle/oradata/CDB/pdb1/undotbs01.dbf
/u01/app/oracle/oradata/CDB/pdb1/users01.dbf
SQL> select tablespace_name,block_size,status,logging,extent_management,segment_space_management from dba_tablespaces;----查看表空间信息
TABLESPACE_NAME ?? ? ? ? ? BLOCK_SIZE STATUS ? ?LOGGING ? EXTENT_MAN SEGMEN
------------------------------ ---------- --------- --------- ---------- ------
SYSTEM?? ??? ??? ??? ? ? ? 8192 ONLINE ? ?LOGGING ? LOCAL?? ? MANUAL
SYSAUX?? ??? ??? ??? ? ? ? 8192 ONLINE ? ?LOGGING ? LOCAL?? ? AUTO
UNDOTBS1?? ??? ??? ? ? ? 8192 ONLINE ? ?LOGGING ? LOCAL?? ? MANUAL
TEMP?? ??? ??? ??? ? ? ? 8192 ONLINE ? ?NOLOGGING LOCAL?? ? MANUAL
USERS?? ??? ??? ??? ? ? ? 8192 ONLINE ? ?LOGGING ? LOCAL?? ? AUTO
SQL> select tablespace_name,file_name from dba_temp_files;-----查看临时表空间的信息
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
TEMP
/u01/app/oracle/oradata/CDB/pdb1/temp01.dbf
SQL> create directory dump_dir as '/home/oracle/scripts';--------创建一个目录用于存放导出的文件
Directory created.
SQL> grant read,write,execute on directory dump_dir to system;------给system用户赋予使用目录的权限
Grant succeeded.
SQL> create user nice quota 5m on users;------创建一个实验用户
SQL> grant dba,resource,connect to nice; -----给用户赋权
Grant succeeded.
-------然后用nice用户登录pdb1创建一张test表，并且给test表插入一条数据
--------以下我们开始创建pdb2
SQL> show pdbs
?
? ? CON_ID CON_NAME?? ??? ??? ? ?OPEN MODE ?RESTRICTED
---------- ------------------------------ ---------- ----------
?? ? 2 PDB$SEED?? ??? ??? ? ?READ ONLY ?NO
?? ? 3 PDB1 ?? ??? ??? ? ?READ WRITE NO
SQL> create pluggable database pdb2 admin user admin1 identified by oracle
? 2 ?FILE_NAME_CONVERT=('pdbseed','pdb2');
?
Pluggable database created.
?
SQL> alter session set container=pdb2;
?
?
SQL> startup;
Pluggable Database opened.
SQL> select tablespace_name,block_size,status,logging,extent_management,segment_space_management from dba_tablespaces;?
?
TABLESPACE_NAME ?? ? ? ? ? BLOCK_SIZE STATUS ? ?LOGGING ? EXTENT_MAN SEGMEN
------------------------------ ---------- --------- --------- ---------- ------
SYSTEM?? ??? ??? ??? ? ? ? 8192 ONLINE ? ?LOGGING ? LOCAL?? ? MANUAL
SYSAUX?? ??? ??? ??? ? ? ? 8192 ONLINE ? ?LOGGING ? LOCAL?? ? AUTO
UNDOTBS1?? ??? ??? ? ? ? 8192 ONLINE ? ?LOGGING ? LOCAL?? ? MANUAL
TEMP?? ??? ??? ??? ? ? ? 8192 ONLINE ? ?NOLOGGING LOCAL?? ? MANUAL
?
SQL> select name from v$datafile;
?
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/CDB/pdb2/system01.dbf
/u01/app/oracle/oradata/CDB/pdb2/sysaux01.dbf
/u01/app/oracle/oradata/CDB/pdb2/undotbs01.dbf
?
SQL> select tablespace_name,file_name from dba_temp_files;
?
TABLESPACE_NAME
------------------------------
FILE_NAME
--------------------------------------------------------------------------------
TEMP
/u01/app/oracle/oradata/CDB/pdb2/temp012024-01-10_16-24-28-422-PM.dbf
SQL> create directory dump_dir as '/home/oracle/scripts';----创建一个目录，这个目录我们可以和pdb1创建成同一个目录，因为文件本来就在同一个位置
Directory created.
SQL> grant read,write,execute on directory dump_dir to system;
Grant succeeded.
[oracle@19c ~]$ export NLS_LANG=AMERICAN_AMERICA.AL32UTF8#声明字符集和语言
[oracle@19c ~]$ expdp system/oracle@pdb1 full=y dumpfile=cai.dmp directory=dump_dir
Export: Release 19.0.0.0.0 - Production on Wed Jan 10 17:48:19 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Starting "SYSTEM"."SYS_EXPORT_FULL_01": system/********@pdb1 full=y dumpfile=cai.dmp directory=dump_dir
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SCHEMA/USER
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
. . exported "SYS"."KU$_USER_MAPPING_VIEW" 6.078 KB 38 rows
. . exported "SYSTEM"."REDO_DB" 25.59 KB 1 rows
. . exported "WMSYS"."WM$WORKSPACES_TABLE$" 12.10 KB 1 rows
. . exported "WMSYS"."WM$HINT_TABLE$" 9.984 KB 97 rows
. . exported "LBACSYS"."OLS$INSTALLATIONS" 6.960 KB 2 rows
. . exported "WMSYS"."WM$WORKSPACE_PRIV_TABLE$" 7.078 KB 11 rows
. . exported "SYS"."DAM_CONFIG_PARAM$" 6.531 KB 14 rows
. . exported "SYS"."TSDP_SUBPOL$" 6.328 KB 1 rows
. . exported "WMSYS"."WM$NEXTVER_TABLE$" 6.375 KB 1 rows
. . exported "LBACSYS"."OLS$PROPS" 6.234 KB 5 rows
. . exported "WMSYS"."WM$ENV_VARS$" 6.015 KB 3 rows
. . exported "SYS"."TSDP_PARAMETER$" 5.953 KB 1 rows
. . exported "SYS"."TSDP_POLICY$" 5.921 KB 1 rows
. . exported "WMSYS"."WM$VERSION_HIERARCHY_TABLE$" 5.984 KB 1 rows
. . exported "WMSYS"."WM$EVENTS_INFO$" 5.812 KB 12 rows
. . exported "LBACSYS"."OLS$AUDIT_ACTIONS" 5.757 KB 8 rows
. . exported "LBACSYS"."OLS$DIP_EVENTS" 5.539 KB 2 rows
. . exported "AUDSYS"."AUD$UNIFIED":"AUD_UNIFIED_P0" 0 KB 0 rows
. . exported "AUDSYS"."AUD$UNIFIED":"SYS_P221" 108.9 KB 112 rows
. . exported "LBACSYS"."OLS$AUDIT" 0 KB 0 rows
. . exported "LBACSYS"."OLS$COMPARTMENTS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$DIP_DEBUG" 0 KB 0 rows
. . exported "LBACSYS"."OLS$GROUPS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$LAB" 0 KB 0 rows
. . exported "LBACSYS"."OLS$LEVELS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POL" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLICY_ADMIN" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$POLT" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROFILE" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROFILES" 0 KB 0 rows
. . exported "LBACSYS"."OLS$PROG" 0 KB 0 rows
. . exported "LBACSYS"."OLS$SESSINFO" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_COMPARTMENTS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_GROUPS" 0 KB 0 rows
. . exported "LBACSYS"."OLS$USER_LEVELS" 0 KB 0 rows
. . exported "SYS"."AUD$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_EVENTS$" 0 KB 0 rows
. . exported "SYS"."DAM_CLEANUP_JOBS$" 0 KB 0 rows
. . exported "SYS"."TSDP_ASSOCIATION$" 0 KB 0 rows
. . exported "SYS"."TSDP_CONDITION$" 0 KB 0 rows
. . exported "SYS"."TSDP_FEATURE_POLICY$" 0 KB 0 rows
. . exported "SYS"."TSDP_PROTECTION$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_DATA$" 0 KB 0 rows
. . exported "SYS"."TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
. . exported "SYS"."TSDP_SOURCE$" 0 KB 0 rows
. . exported "SYSTEM"."REDO_LOG" 0 KB 0 rows
. . exported "WMSYS"."WM$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows
. . exported "WMSYS"."WM$CONSTRAINTS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$CONS_COLUMNS$" 0 KB 0 rows
. . exported "WMSYS"."WM$LOCKROWS_INFO$" 0 KB 0 rows
. . exported "WMSYS"."WM$MODIFIED_TABLES$" 0 KB 0 rows
. . exported "WMSYS"."WM$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$NESTED_COLUMNS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_LOCKING_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$RIC_TRIGGERS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_DISPATCH_PROCS$" 0 KB 0 rows
. . exported "WMSYS"."WM$UDTRIG_INFO$" 0 KB 0 rows
. . exported "WMSYS"."WM$VERSION_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$VT_ERRORS_TABLE$" 0 KB 0 rows
. . exported "WMSYS"."WM$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 rows
. . exported "MDSYS"."RDF_PARAM$" 6.515 KB 3 rows
. . exported "SYS"."AUDTAB$TBS$FOR_EXPORT" 5.953 KB 2 rows
. . exported "SYS"."DBA_SENSITIVE_DATA" 0 KB 0 rows
. . exported "SYS"."DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
. . exported "SYS"."FGA_LOG$FOR_EXPORT" 0 KB 0 rows
. . exported "SYS"."NACL$_ACE_EXP" 0 KB 0 rows
. . exported "SYS"."NACL$_HOST_EXP" 6.914 KB 1 rows
. . exported "SYS"."NACL$_WALLET_EXP" 0 KB 0 rows
. . exported "SYS"."SQL$TEXT_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQL$_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$AUXDATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$DATA_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$PLAN_DATAPUMP" 0 KB 0 rows
. . exported "SYS"."SQLOBJ$_DATAPUMP" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_JOB_ARGS" 0 KB 0 rows
. . exported "SYSTEM"."SCHEDULER_PROGRAM_ARGS" 0 KB 0 rows
. . exported "WMSYS"."WM$EXP_MAP" 7.718 KB 3 rows
. . exported "WMSYS"."WM$METADATA_MAP" 0 KB 0 rows
. . exported "HR"."EMPLOYEES" 17.08 KB 107 rows
. . exported "HR"."LOCATIONS" 8.437 KB 23 rows
. . exported "HR"."JOB_HISTORY" 7.195 KB 10 rows
. . exported "HR"."JOBS" 7.109 KB 19 rows
. . exported "HR"."DEPARTMENTS" 7.125 KB 27 rows
. . exported "HR"."COUNTRIES" 6.367 KB 25 rows
. . exported "HR"."REGIONS" 5.546 KB 4 rows
. . exported "NICE"."TEST" 5.046 KB 1 rows
Master table "SYSTEM"."SYS_EXPORT_FULL_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:
/home/oracle/scripts/cai.dmp
Job "SYSTEM"."SYS_EXPORT_FULL_01" successfully completed at Wed Jan 10 17:52:51 2024 elapsed 0 00:04:24
[oracle@19c ~]$ cd scripts
[oracle@19c scripts]$ ll -ls
total 3932
3920 -rw-r-----. 1 oracle oinstall 4014080 Jan 10 17:52 cai.dmp
12 -rw-r-----. 1 oracle oinstall 10910 Jan 10 17:52 export.log
注意导入的时候有一个致命的报错会导致我导入失败，这里是说我的这个users表空间不能在pdb2添加数据文件，因为这个表空间不存在，所以我们需要在pdb2添加一个user表空间。
ORA-39083: Object type TABLESPACE:"USERS" failed to create with error:
ORA-01537: cannot add file '/u01/app/oracle/oradata/CDB/pdb1/users01.dbf' - file already part of database
Failing sql is:
CREATE TABLESPACE "USERS" DATAFILE '/u01/app/oracle/oradata/CDB/pdb1/users01.dbf' SIZE 5242880 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT ?NOCOMPRESS ?SEGMENT SPACE MANAGEMENT AUTO
[oracle@19c scripts]$ impdp system/oracle@pdb2 full=y dumpfile=cai.dmp directory=dump_dir
Import: Release 19.0.0.0.0 - Production on Wed Jan 10 18:09:35 2024
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01": system/********@pdb2 full=y dumpfile=cai.dmp directory=dump_dir
Processing object type DATABASE_EXPORT/PRE_SYSTEM_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/PRE_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/TABLESPACE
ORA-31684: Object type TABLESPACE:"UNDOTBS1" already exists
ORA-31684: Object type TABLESPACE:"TEMP" already exists
ORA-39083: Object type TABLESPACE:"USERS" failed to create with error:
ORA-01537: cannot add file '/u01/app/oracle/oradata/CDB/pdb1/users01.dbf' - file already part of database
Failing sql is:
CREATE TABLESPACE "USERS" DATAFILE '/u01/app/oracle/oradata/CDB/pdb1/users01.dbf' SIZE 5242880 AUTOEXTEND ON NEXT 1310720 MAXSIZE 32767M LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO
Processing object type DATABASE_EXPORT/PROFILE
Processing object type DATABASE_EXPORT/SCHEMA/USER
ORA-39083: Object type USER:"PDBADMIN" failed to create with error:
ORA-00959: tablespace 'USERS' does not exist
Failing sql is:
CREATE USER "PDBADMIN" IDENTIFIED BY VALUES 'S:0FD8910250DF92415DD93D3E082CC82885EBBA9289EA6861277EEE0B1F9E;T:5F7CE0CB625FFC6DB5523F4439B9F4074F1FB245360EA1EA7BE58C52CE58CFD419550182D1E3E58B4C3CF7380AE72BB70E1380B1FBF10D7EC3333094884B3D2213C332E14F040EED0098EFF620653F26' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"
ORA-39083: Object type USER:"NICE" failed to create with error:
ORA-00959: tablespace 'USERS' does not exist
Failing sql is:
CREATE USER "NICE" IDENTIFIED BY VALUES 'S:66AE62815E49BA1A1AC18B5EF026C2D5E87BC3291033FAA6BC763E4C94DD;T:0E120B656CA2344867C0FD8E768C1EDD24B05CAEC2AE6FA31E3618A1B1689AFDBD399BE16B6E83B65F911EA6DEE0FCFE2A20098DB9F4D96C0E4CCE91D571730C9AE60C873A2538A747CF56E7288EC777' DEFAULT TABLESPACE "USERS" TEMPORARY TABLESPACE "TEMP"
Processing object type DATABASE_EXPORT/RADM_FPTM
Processing object type DATABASE_EXPORT/GRANT/SYSTEM_GRANT/PROC_SYSTEM_GRANT
Processing object type DATABASE_EXPORT/SCHEMA/GRANT/SYSTEM_GRANT
ORA-39083: Object type SYSTEM_GRANT failed to create with error:
ORA-01917: user or role 'NICE' does not exist
Failing sql is:
GRANT UNLIMITED TABLESPACE TO "NICE"
Processing object type DATABASE_EXPORT/SCHEMA/ROLE_GRANT
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01917: user or role 'PDBADMIN' does not exist
Failing sql is:
GRANT "PDB_DBA" TO "PDBADMIN" WITH ADMIN OPTION
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01917: user or role 'NICE' does not exist
Failing sql is:
GRANT "CONNECT" TO "NICE"
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01917: user or role 'NICE' does not exist
Failing sql is:
GRANT "DBA" TO "NICE"
ORA-39083: Object type ROLE_GRANT failed to create with error:
ORA-01917: user or role 'NICE' does not exist
Failing sql is:
GRANT "RESOURCE" TO "NICE"
Processing object type DATABASE_EXPORT/SCHEMA/DEFAULT_ROLE
ORA-39083: Object type DEFAULT_ROLE:"PDBADMIN" failed to create with error:
ORA-01918: user 'PDBADMIN' does not exist
Failing sql is:
ALTER USER "PDBADMIN" DEFAULT ROLE ALL
ORA-39083: Object type DEFAULT_ROLE:"NICE" failed to create with error:
ORA-01918: user 'NICE' does not exist
Failing sql is:
ALTER USER "NICE" DEFAULT ROLE ALL
Processing object type DATABASE_EXPORT/SCHEMA/ON_USER_GRANT
ORA-39083: Object type ON_USER_GRANT failed to create with error:
ORA-31625: Schema NICE is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Failing sql is:
GRANT INHERIT PRIVILEGES ON USER "NICE" TO "PUBLIC"
Processing object type DATABASE_EXPORT/SCHEMA/TABLESPACE_QUOTA
ORA-39083: Object type TABLESPACE_QUOTA:"NICE"."USERS" failed to create with error:
ORA-01918: user 'NICE' does not exist
Failing sql is:
DECLARE TEMP_COUNT NUMBER; SQLSTR VARCHAR2(200); BEGIN SQLSTR := 'ALTER USER "NICE" QUOTA 1048576 ON "USERS"'; EXECUTE IMMEDIATE SQLSTR;EXCEPTION WHEN OTHERS THEN IF SQLCODE = -30041 THEN SQLSTR := 'SELECT COUNT(*) FROM USER_TABLESPACES WHERE TABLESPACE_NAME = ''USERS'' AND CONTENTS = ''TEMPORARY'''; EXECUTE IMMEDIATE SQLSTR INTO TEMP_COUNT; IF TEMP_COUNT = 1 THEN RETURN; ELSE RAISE; END IF; ELSE RAISE; END IF;END;
Processing object type DATABASE_EXPORT/RESOURCE_COST
Processing object type DATABASE_EXPORT/TRUSTED_DB_LINK
Processing object type DATABASE_EXPORT/SCHEMA/SEQUENCE/SEQUENCE
Processing object type DATABASE_EXPORT/DIRECTORY/DIRECTORY
ORA-31684: Object type DIRECTORY:"DUMP_DIR" already exists
Processing object type DATABASE_EXPORT/DIRECTORY/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PRE_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/PROCOBJ
Processing object type DATABASE_EXPORT/SYSTEM_PROCOBJACT/POST_SYSTEM_ACTIONS/PROCACT_SYSTEM
Processing object type DATABASE_EXPORT/SCHEMA/PROCACT_SCHEMA
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-31625: Schema PDBADMIN is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Failing sql is:
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'PDB1', inst_scn=>'2155177');COMMIT; END;
ORA-39083: Object type PROCACT_SCHEMA failed to create with error:
ORA-31625: Schema NICE is needed to import this object, but is unaccessible
ORA-01435: user does not exist
Failing sql is:
BEGIN
sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'PDB1', inst_scn=>'2155177');COMMIT; END;
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/EARLY_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported "SYS"."KU$_EXPORT_USER_MAP" 6.078 KB 38 rows
Processing object type DATABASE_EXPORT/EARLY_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/TABLE_DATA
. . imported "SYSTEM"."REDO_DB_TMP" 25.59 KB 1 rows
. . imported "WMSYS"."E$WORKSPACES_TABLE$" 12.10 KB 1 rows
. . imported "WMSYS"."E$HINT_TABLE$" 9.984 KB 97 rows
. . imported "WMSYS"."E$WORKSPACE_PRIV_TABLE$" 7.078 KB 11 rows
. . imported "SYS"."AMGT$DP$DAM_CONFIG_PARAM$" 6.531 KB 14 rows
. . imported "SYS"."DP$TSDP_SUBPOL$" 6.328 KB 1 rows
. . imported "WMSYS"."E$NEXTVER_TABLE$" 6.375 KB 1 rows
. . imported "WMSYS"."E$ENV_VARS$" 6.015 KB 3 rows
. . imported "SYS"."DP$TSDP_PARAMETER$" 5.953 KB 1 rows
. . imported "SYS"."DP$TSDP_POLICY$" 5.921 KB 1 rows
. . imported "WMSYS"."E$VERSION_HIERARCHY_TABLE$" 5.984 KB 1 rows
. . imported "WMSYS"."E$EVENTS_INFO$" 5.812 KB 12 rows
. . imported "AUDSYS"."AMGT$DP$AUD$UNIFIED":"AUD_UNIFIED_P0" 0 KB 0 rows
. . imported "AUDSYS"."AMGT$DP$AUD$UNIFIED":"SYS_P221" 108.9 KB 112 rows
. . imported "LBACSYS"."OLS_DP$OLS$AUDIT" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$COMPARTMENTS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$GROUPS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$LAB" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$LEVELS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$POL" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$POLS" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$POLT" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$PROFILE" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$PROG" 0 KB 0 rows
. . imported "LBACSYS"."OLS_DP$OLS$USER" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$AUD$" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_EVENTS$" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$DAM_CLEANUP_JOBS$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_ASSOCIATION$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_CONDITION$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_FEATURE_POLICY$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_PROTECTION$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_SENSITIVE_DATA$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_SENSITIVE_TYPE$" 0 KB 0 rows
. . imported "SYS"."DP$TSDP_SOURCE$" 0 KB 0 rows
. . imported "SYSTEM"."REDO_LOG_TMP" 0 KB 0 rows
. . imported "WMSYS"."E$BATCH_COMPRESSIBLE_TABLES$" 0 KB 0 rows
. . imported "WMSYS"."E$CONSTRAINTS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$CONS_COLUMNS$" 0 KB 0 rows
. . imported "WMSYS"."E$LOCKROWS_INFO$" 0 KB 0 rows
. . imported "WMSYS"."E$MODIFIED_TABLES$" 0 KB 0 rows
. . imported "WMSYS"."E$MP_GRAPH_WORKSPACES_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$MP_PARENT_WORKSPACES_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$NESTED_COLUMNS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RESOLVE_WORKSPACES_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RIC_LOCKING_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RIC_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$RIC_TRIGGERS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$UDTRIG_DISPATCH_PROCS$" 0 KB 0 rows
. . imported "WMSYS"."E$UDTRIG_INFO$" 0 KB 0 rows
. . imported "WMSYS"."E$VERSION_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$VT_ERRORS_TABLE$" 0 KB 0 rows
. . imported "WMSYS"."E$WORKSPACE_SAVEPOINTS_TABLE$" 0 KB 0 rows
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE
Processing object type DATABASE_EXPORT/NORMAL_OPTIONS/VIEWS_AS_TABLES/TABLE_DATA
. . imported "MDSYS"."RDF_PARAM$TBL" 6.515 KB 3 rows
. . imported "SYS"."AMGT$DP$AUDTAB$TBS$FOR_EXPORT" 5.953 KB 2 rows
. . imported "SYS"."DP$DBA_SENSITIVE_DATA" 0 KB 0 rows
. . imported "SYS"."DP$DBA_TSDP_POLICY_PROTECTION" 0 KB 0 rows
. . imported "SYS"."AMGT$DP$FGA_LOG$FOR_EXPORT" 0 KB 0 rows
. . imported "SYS"."NACL$_ACE_IMP" 0 KB 0 rows
. . imported "SYS"."NACL$_HOST_IMP" 6.914 KB 1 rows
. . imported "SYS"."NACL$_WALLET_IMP" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQL$TEXT" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQL$" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$AUXDATA" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$DATA" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$PLAN" 0 KB 0 rows
. . imported "SYS"."DATAPUMP$SQLOBJ$" 0 KB 0 rows
. . imported "SYSTEM"."SCHEDULER_JOB_ARGS_TMP" 0 KB 0 rows
. . imported "SYSTEM"."SCHEDULER_PROGRAM_ARGS_TMP" 0 KB 0 rows
. . imported "WMSYS"."E$EXP_MAP" 7.718 KB 3 rows
. . imported "WMSYS"."E$METADATA_MAP" 0 KB 0 rows
Processing object type DATABASE_EXPORT/NORMAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE
ORA-39083: Object type TABLE:"NICE"."TEST" failed to create with error:
ORA-00959: tablespace 'USERS' does not exist
Failing sql is:
CREATE TABLE "NICE"."TEST" ("ID" NUMBER) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS"
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. . imported "HR"."EMPLOYEES" 17.08 KB 107 rows
. . imported "HR"."LOCATIONS" 8.437 KB 23 rows
. . imported "HR"."JOB_HISTORY" 7.195 KB 10 rows
. . imported "HR"."JOBS" 7.109 KB 19 rows
. . imported "HR"."DEPARTMENTS" 7.125 KB 27 rows
. . imported "HR"."COUNTRIES" 6.367 KB 25 rows
. . imported "HR"."REGIONS" 5.546 KB 4 rows
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/COMMENT
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/PROCEDURE/ALTER_PROCEDURE
Processing object type DATABASE_EXPORT/SCHEMA/VIEW/VIEW
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/INDEX
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type DATABASE_EXPORT/STATISTICS/MARKER
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TRIGGER
Processing object type DATABASE_EXPORT/FINAL_POST_INSTANCE_IMPCALLOUT/MARKER
Processing object type DATABASE_EXPORT/AUDIT_UNIFIED/AUDIT_POLICY_ENABLE
Processing object type DATABASE_EXPORT/POST_SYSTEM_IMPCALLOUT/MARKER
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed with 18 error(s) at Wed Jan 10 18:12:45 2024 elapsed 0 00:03:09
SQL> show parameter create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
create_bitmap_area_size integer 8388608
create_stored_outlines string
db_create_file_dest string
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
SQL> alter system set db_create_file_dest='/u01/app/oracle/oradata/CDB/pdb2';-----我将pdb2设置成OMF管理模式，然后创建一个users表空间
System altered.
SQL> select tablespace_name,block_size,status,logging,extent_management,segment_space_management from dba_tablespaces;
TABLESPACE_NAME BLOCK_SIZE STATUS LOGGING EXTENT_MAN SEGMEN
------------------------------ ---------- --------- --------- ---------- ------
SYSTEM 8192 ONLINE LOGGING LOCAL MANUAL
SYSAUX 8192 ONLINE LOGGING LOCAL AUTO
UNDOTBS1 8192 ONLINE LOGGING LOCAL MANUAL
TEMP 8192 ONLINE NOLOGGING LOCAL MANUAL
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB2 READ WRITE NO
SQL> create tablespace users ;
Tablespace created.
--------导入成功后查看用户NICE和test表都已经存在了
SQL> select username from all_users;
USERNAME
--------------------------------------------------------------------------------
SYS
AUDSYS
SYSTEM
SYSBACKUP
SYSDG
SYSKM
SYSRAC
OUTLN
DBSFWUSER
XS$NULL
GSMADMIN_INTERNAL
USERNAME
--------------------------------------------------------------------------------
GSMUSER
DIP
REMOTE_SCHEDULER_AGENT
DBSNMP
ORACLE_OCM
SYS$UMF
APPQOSSYS
GSMCATUSER
GGSYS
XDB
ANONYMOUS
USERNAME
--------------------------------------------------------------------------------
WMSYS
OJVMSYS
CTXSYS
ORDSYS
ORDDATA
ORDPLUGINS
SI_INFORMTN_SCHEMA
MDSYS
OLAPSYS
MDDATA
DVSYS
USERNAME
--------------------------------------------------------------------------------
LBACSYS
DVF
ADMIN1
HR
PDBADMIN
NICE
39 rows selected.
SQL> select * from nice.test;
ID
----------
1
SQL>
SQL>
SQL>
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
4 PDB2 READ WRITE NO
SQL>
SQL> show pdbs;-----导入完成后查看整个CDB的情况，最后一个文件就是pdb2中OMF管理的users表空间文件
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 PDB1 READ WRITE NO
4 PDB2 READ WRITE NO
SQL> SELECT file_name, tablespace_name FROM cdb_data_files;
FILE_NAME TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/u01/app/oracle/oradata/CDB/system01.dbf SYSTEM
/u01/app/oracle/oradata/CDB/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/CDB/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/CDB/users01.dbf USERS
/u01/app/oracle/oradata/CDB/pdb1/system01.dbf SYSTEM
/u01/app/oracle/oradata/CDB/pdb1/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/CDB/pdb1/undotbs01.dbf UNDOTBS1
/u01/app/oracle/oradata/CDB/pdb1/users01.dbf USERS
/u01/app/oracle/oradata/CDB/pdb2/system01.dbf SYSTEM
/u01/app/oracle/oradata/CDB/pdb2/sysaux01.dbf SYSAUX
/u01/app/oracle/oradata/CDB/pdb2/undotbs01.dbf UNDOTBS1
FILE_NAME TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/u01/app/oracle/oradata/CDB/pdb2/CDB/0E956A9C5E244C1CE06302F USERS
4A8C003A7/datafile/o1_mf_users_lsx0ml7s_.dbf
?
?