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
Quick check :
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
SQL> select count(*) from ENGARDE_NORTH1REP.ALLOCATIONS;
COUNT(*)
----------
12463222
Example of exporting tables with a parameter file
expdp system/secret_password parfile=expdp_TL_BACK_par
$ cat ./expdp_TL_BACK_par
DIRECTORY=dpump
DUMPFILE=expdp_TL_BACK_%U.dmp
LOGFILE=expdp_TL_BACK.log
FILESIZE=1000M
TABLES=nwgis.TL_BUILD
nwgis.TL_HANT
nwgis.TL_STRUC
nwgis.TL_WATER
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
No comments:
Post a Comment