Durham Oracle
Example of importing with sysdba when you don’t have any schema passwords — you could create yourself a user and give yourself dba privs but this is easier.
The expdp was just the reverse — putting this in a post as I have used this in the past but took an age to find it in my notes and the syntax is a bit odd to remember.
[oracle@db301] $ impdp \’/ as sysdba\’ SCHEMAS=ENGARDE_NORTH1REP DIRECTORY=DATA_PUMP_DIR DUMPFILE=ENGARDE_NORTH1REP.dmp LOGFILE=ENGARDE_NORTH1REP_import.log
Import: Release 12.1.0.2.0 — Production on Fri May 28 14:04:55 2021
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 — 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
Master table «SYS».»SYS_IMPORT_SCHEMA_01″ successfully loaded/unloaded
Starting «SYS».»SYS_IMPORT_SCHEMA_01″: «/******** AS SYSDBA» SCHEMAS=ENGARDE_NORTH1REP DIRECTORY=DATA_PUMP_DIR DUMPFILE=ENGARDE_NORTH1REP.dmp LOGFILE=ENGARDE_NORTH1REP_import.log
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_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/TABLE_DATA
. . imported «ENGARDE_NORTH1REP».»FSM_NW_ACTIVITY» 988.7 MB 1952353 rows
. . imported «ENGARDE_NORTH1REP».»ALLOCATIONS» 623.4 MB 12463222 rows
. . imported «ENGARDE_NORTH1REP».»EVENTS» 539.0 MB 3761100 rows
. . imported «ENGARDE_NORTH1REP».»JOBDESC» 499.4 MB 8609289 rows
. . imported «ENGARDE_NORTH1REP».»ACTIVITIES» 252.1 MB 5394085 rows
. . imported «ENGARDE_NORTH1REP».»ASSETS» 23.13 MB 313412 rows
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
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/STATISTICS/TABLE_STATISTICS
Processing object type SCHEMA_EXPORT/STATISTICS/MARKER
Job «SYS».»SYS_IMPORT_SCHEMA_01″ successfully completed at Fri May 28 14:29:22 2021 elapsed 0 00:24:26
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 — 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select count(*) from ENGARDE_NORTH1REP.ALLOCATIONS;
Example of exporting tables with a parameter file
expdp system/secret_password parfile=expdp_TL_BACK_par
Need to know export size —
$ expdp \’/ as sysdba\’ FULL=y ESTIMATE_ONLY=y NOLOGFILE=y
Export: Release 11.2.0.4.0 — Production on Mon May 22 15:16:42 2023
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 — 64bit Production
With the Real Application Testing option
Starting «SYS».»SYS_EXPORT_FULL_01″: «/******** AS SYSDBA» FULL=y ESTIMATE_ONLY=y NOLOGFILE=y
Estimate in progress using BLOCKS method.
Processing object type DATABASE_EXPORT/SCHEMA/TABLE/TABLE_DATA
. estimated «LYODBA».»TARCCNF» 26.12 GB
. estimated «LYODBA».»TREPOS_TRANSC_ITEM» 22.46 GB
Total estimation using BLOCKS method: 365.6 GB
Job «SYS».»SYS_EXPORT_FULL_01″ successfully completed at Mon May 22 15:17:40 2023 elapsed 0 00:00:57