Experience: is what you get soon after you need it.

Experience: is what you get soon after you need it.

****************I am authoring a Book on Oracle Database Cloud Services.......More details to follow.***************

Title : Oracle Database Cloud Revealed
Publisher : Apress
Release Date : Jan-2019

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


Rasul Allah (sal Allahu alaihi wa sallam) said: "Restore the trusts of those who trust you, and deal not falsely with him who deals falsely with you." [Abu Dawud, Tirmidhi]


My Cloud Certifications:
Oracle Cloud Infrastructure 2018 Certified Architect Associate.
Oracle Cloud Infrastructure Classic 2018 Certified Architect Associate.
Oracle Database Cloud Administrator Certified Professional.
Oracle Database Cloud Service Operations Certified Associate.

Search This Blog

Tuesday, August 11, 2015

Oracle remap schema and remap tablespace

Remap_schema & remap_tablespace:

Below we will load the contents of "hr" schema into "hr_test" schema and from tablespace "USERS" to "hr_test"
SQL> create directory data_dir as '/home/oracle/sshaik';

Directory created.

SQL> create directory log_dir as '/home/oracle/sshaik/log';

Directory created.

SQL> grant all on directory data_dir to system;

Grant succeeded.

SQL> grant all on directory log_dir to system;

Grant succeeded.


SQL> create tablespace hr_test datafile '+DATA' size 100m autoextend on next 10m;

Tablespace created.

SQL>create user hr_test identified by hr_test default tablespace hr_test;

User created.
SQL> !

[oracle@collabn1 sshaik]$
expdp \"/ as sysdba\" dumpfile=data_dir:hr.dmp logfile=log_dir:hr_exp.log schemas=hr

Export: Release 11.2.0.1.0 - Production on Wed Aug 5 15:37:29 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" dumpfile=data_dir:hr.dmp logfile=log_dir:hr_exp.log schemas=hr
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 512 KB
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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "HR"."COUNTRIES"                            6.367 KB      25 rows
. . exported "HR"."DEPARTMENTS"                          7.007 KB      27 rows
. . exported "HR"."EMPLOYEES"                            16.81 KB     107 rows
. . exported "HR"."JOBS"                                 6.992 KB      19 rows
. . exported "HR"."JOB_HISTORY"                          7.054 KB      10 rows
. . exported "HR"."LOCATIONS"                            8.273 KB      23 rows
. . exported "HR"."REGIONS"                              5.476 KB       4 rows
. . exported "HR"."REGIONS2"                             5.476 KB       4 rows
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
 /home/oracle/sshaik/hr.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01" successfully completed at 15:38:18


[oracle@collabn1 sshaik]$
impdp system/XXXXXX dumpfile=data_dir:hr.dmp logfile=log_dir:hr_imp.log remap_schema=hr:hr_test remap_tablespace=hr:hr_test

Import: Release 11.2.0.1.0 - Production on Wed Aug 5 15:43:29 2015

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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_FULL_01":  system/******** dumpfile=data_dir:hr.dmp logfile=log_dir:hr_imp.log remap_schema=hr:hr_test remap_tablespace=hr:hr_test
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"HR_TEST" 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/SEQUENCE/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
. . imported "HR_TEST"."COUNTRIES"                       6.367 KB      25 rows
. . imported "HR_TEST"."DEPARTMENTS"                     7.007 KB      27 rows
. . imported "HR_TEST"."EMPLOYEES"                       16.81 KB     107 rows
. . imported "HR_TEST"."JOBS"                            6.992 KB      19 rows
. . imported "HR_TEST"."JOB_HISTORY"                     7.054 KB      10 rows
. . imported "HR_TEST"."LOCATIONS"                       8.273 KB      23 rows
. . imported "HR_TEST"."REGIONS"                         5.476 KB       4 rows
. . imported "HR_TEST"."REGIONS2"                        5.476 KB       4 rows
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/VIEW/VIEW
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/TRIGGER
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SYSTEM"."SYS_IMPORT_FULL_01" completed successfully at 15:43:38


Verify the data is loaded into the new schema.


SQL> drop user hr_test cascade;

User dropped.

SQL> drop tablespace hr_test including contents;

Tablespace dropped.

No comments: