Home » SQL & PL/SQL » SQL & PL/SQL » Unable to take backup of a table (Oracle, 18.0.0.0.0, CentOS Linux release 7.8.2003 (Core))
Unable to take backup of a table [message #685135] Thu, 28 October 2021 00:57 Go to next message
BalajiDBA
Messages: 139
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
Table name is M_UNITY_DYR_GG.demo_model_exceptions
SQL> select count(*) from M_UNITY_DYR_GG."demo_model_exceptions";
  COUNT(*)
----------
    126180
Table size is 170 MB

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 #685137 is a reply to message #685135] Thu, 28 October 2021 01:23 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why is this posted in SQL & PL/SQL forum?
If, after 15 years here, you are even unable to post in the correct forum, there is no hope.

Re: Unable to take backup of a table [message #685138 is a reply to message #685137] Thu, 28 October 2021 01:34 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
Could you move it to correct forum as I have posted it on wrong forum by mistake.
Re: Unable to take backup of a table [message #685139 is a reply to message #685135] Thu, 28 October 2021 05:40 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
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:\>
SY.
Re: Unable to take backup of a table [message #685140 is a reply to message #685139] Thu, 28 October 2021 09:28 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
And are we supposed to guess at exactly what the full expdp command was?
Re: Unable to take backup of a table [message #685142 is a reply to message #685140] Thu, 28 October 2021 15:39 Go to previous messageGo to next message
Solomon Yakobson
Messages: 3269
Registered: January 2010
Location: Connecticut, USA
Senior Member
EdStevens wrote on Thu, 28 October 2021 10:28
And 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
SY.
Re: Unable to take backup of a table [message #685143 is a reply to message #685142] Thu, 28 October 2021 16:15 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
Solomon - yep, I missed that, and odd I'd never noticed it before. I guess that in all of the times I've looked at dpump logs, I've been looking at the tail end and never had a reason to closely examine the head of the log.
Re: Unable to take backup of a table [message #685146 is a reply to message #685135] Thu, 28 October 2021 23:29 Go to previous messageGo to next message
BalajiDBA
Messages: 139
Registered: October 2018
Location: India
Senior Member
Thanks Soloman. Will test it and come back if I see any errors again.

Regards,
Balaji
Re: Unable to take backup of a table [message #685148 is a reply to message #685142] Fri, 29 October 2021 00:43 Go to previous message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Solomon Yakobson wrote on Thu, 28 October 2021 22:39
EdStevens wrote on Thu, 28 October 2021 10:28
And 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
And indeed this "Starting" line is misleading as it does not show the options as they actually are handled but as they are received.

Previous Topic: transpose rows into columns
Next Topic: Long query with WITH not responding
Goto Forum:
  


Current Time: Thu Mar 28 10:01:58 CDT 2024