| Unable to take backup of a table [message #685135] |
Thu, 28 October 2021 00:57  |
 |
BalajiDBA
Messages: 132
Registered: October 2018
Location: India
|
Senior Member |
|
|
Hi,
There is a request to delete entire table. Before deleting the records, when I try to backup the table using expdp
I found
Starting "USER_DBA"."SYS_EXPORT_TABLE_01": USER_DBA/******** tables=M_UNITY_DYR_GG."demo_model_exceptions" directory=DMPDIR dumpfile=M_UNITY_DYR_GG."demo_model_exceptions"_table.dmp
logfile=M_UNITY_DYR_GG."demo_model_exceptions"_table.log
ORA-39166: Object M_UNITY_DYR_GG."demo_model_exceptions was not found or could not be exported or imported.
ORA-31655: no data or metadata objects selected for job
Job "USER_DBA"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at Wed Oct 27 07:35:01 2021 elapsed 0 00:00:20
SQL> select count(*) from M_UNITY_DYR_GG."demo_model_exceptions";
COUNT(*)
----------
126180
Could someone please suggest how to backup this table.
This is a prod database.
This is standalone database
Regards,
Balaji
|
|
|
|
|
|
|
|
| Re: Unable to take backup of a table [message #685139 is a reply to message #685135] |
Thu, 28 October 2021 05:40  |
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
Unfortunately Oracle DataPump always uppercases command line parameter values regardless if you enclose them in double-quotes or not. You MUST use parameter file when using case-sensitive paremeter values (and in general using DataPump parameter files is allways a good idea):
SQL> create table "demo_model_exceptions"(n number);
Table created.
SQL> exit
Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
I:\>echo tables=SCOTT."demo_model_exceptions" > c:\temp\demo_model_exceptions.par
I:\>expdp scott@pdb1sol122 directory=temp dumpfile=demo_model_exceptions.dmp logfile=demo_model_exceptions parfile=c:\temp\demo_model_exceptions.par
Export: Release 12.2.0.1.0 - Production on Thu Oct 28 06:27:57 2021
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/********@pdb1sol122 directory=temp dumpfile=demo_model_exceptions.dmp logfile=demo_model_exceptions parfile=c:\temp\demo_model_exceptions.par
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "SCOTT"."demo_model_exceptions" 0 KB 0 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
C:\TEMP\DEMO_MODEL_EXCEPTIONS.DMP
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Thu Oct 28 06:28:20 2021 elapsed 0 00:00:18
I:\>
|
|
|
|
|
|
| Re: Unable to take backup of a table [message #685142 is a reply to message #685140] |
Thu, 28 October 2021 15:39 |
Solomon Yakobson
Messages: 3212
Registered: January 2010
Location: Connecticut, USA
|
Senior Member |
|
|
EdStevens wrote on Thu, 28 October 2021 10:28And are we supposed to guess at exactly what the full expdp command was?
No need to guess. DataPump repeats command line parameters in line Starting:
Starting "USER_DBA"."SYS_EXPORT_TABLE_01": USER_DBA/******** tables=M_UNITY_DYR_GG."demo_model_exceptions" directory=DMPDIR dumpfile=M_UNITY_DYR_GG."demo_model_exceptions"_table.dmp
logfile=M_UNITY_DYR_GG."demo_model_exceptions"_table.log
|
|
|
|
|
|
|
|
| Re: Unable to take backup of a table [message #685148 is a reply to message #685142] |
Fri, 29 October 2021 00:43 |
 |
Michel Cadot
Messages: 68418
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Solomon Yakobson wrote on Thu, 28 October 2021 22:39EdStevens wrote on Thu, 28 October 2021 10:28And are we supposed to guess at exactly what the full expdp command was?
No need to guess. DataPump repeats command line parameters in line Starting:
Solomon, you assume DataPump has no bugs in this part of its code.
It could be this line hides the actual command because there is some bug and misleads in the diagnose.
Giving the command is ALWAYS necessary, at least to reproduce the case in the same way.
It is not necessary to use a parameter file, you can do it in the command line:
E:\>expdp michel/michel dumpfile=test.dmp tables=\"test\"
Export: Release 11.2.0.4.0 - Production on Ven. Oct. 29 07:28:40 2021
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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MICHEL"."SYS_EXPORT_TABLE_01": michel/******** dumpfile=test.dmp tables="test"
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
>>> DBMS_AW_EXP: Ignoring SH.CAL_MONTH_SALES_AW
ORA-39166: Object MICHEL.TEST was not found.
ORA-31655: no data or metadata objects selected for job
Job "MICHEL"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at Ven. Oct. 29 07:28:44 2021 elapsed 0 00:00:03
E:\>expdp michel/michel dumpfile=test.dmp "tables=\"test\""
Export: Release 11.2.0.4.0 - Production on Ven. Oct. 29 07:29:01 2021
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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MICHEL"."SYS_EXPORT_TABLE_01": michel/******** dumpfile=test.dmp tables="test"
Estimate in progress using BLOCKS method...
Total estimation using BLOCKS method: 0 KB
>>> DBMS_AW_EXP: Ignoring SH.CAL_MONTH_SALES_AW
ORA-39166: Object MICHEL.TEST was not found.
ORA-31655: no data or metadata objects selected for job
Job "MICHEL"."SYS_EXPORT_TABLE_01" completed with 2 error(s) at Ven. Oct. 29 07:29:05 2021 elapsed 0 00:00:03
E:\>expdp michel/michel dumpfile=test.dmp tables='\"test\"'
Export: Release 11.2.0.4.0 - Production on Ven. Oct. 29 07:29:27 2021
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 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "MICHEL"."SYS_EXPORT_TABLE_01": michel/******** dumpfile=test.dmp tables='"test"'
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 0 KB
>>> DBMS_AW_EXP: Ignoring SH.CAL_MONTH_SALES_AW
Processing object type TABLE_EXPORT/TABLE/TABLE
>>> DBMS_AW_EXP: Ignoring SH.CAL_MONTH_SALES_AW
. . exported "MICHEL"."test" 0 KB 0 rows
Master table "MICHEL"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for MICHEL.SYS_EXPORT_TABLE_01 is:
E:\ORACLE\SAVE\TEST.DMP
Job "MICHEL"."SYS_EXPORT_TABLE_01" successfully completed at Ven. Oct. 29 07:29:33 2021 elapsed 0 00:00:06
|
|
|
|
] 
Search
Help
Members
Register
Login
Home
