• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 812
  • Last Modified:

Cannot export tables with 0 records

Hi i am using the following command on Oracle 11.2.0.1 and exporting a small schma. but the export is not exporting tables with 0 records. What am i doing wrong or what is the fix?

i tried to export it as system , sys and schema owner itself, no luck.

Please advice!

thanks!

@EXP SYSTEM/oracle@STARDB
FILE=STARWAR.dmp
LOG=STARWAR_exp.log
GRANTS=N
ROWS=Y
INDEXES=Y
TRIGGERS=Y
COMPRESS=Y
OWNER=STARWAR
0
crishna1
Asked:
crishna1
  • 4
  • 4
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
The @ in front of the exp command bothers me. Is that a typo?  '@' is a sqlplus script execute command and exp is an executable from the command line.

What makes you think it doesn't export?
What is the error?
0
 
crishna1Author Commented:
i am running it as a batch file. no error, i have tables missing in the new schema, which made me verify the import log and export log. the tables are missing in the export log . then i verified the source schema, the only tables that are missing are the tables with 0 records.
0
 
slightwv (䄆 Netminder) Commented:
Looks like a 'new' feature in 11gr2 and the old exp utility.

If the table has never had rows, it will not be exported.

Either use the new datapump, expdp or set deferred_segment_creation=false in thje spfile.

If you Google it, there is a lot out there on it.

I used the terms: oracle 11gr2 exp empty table
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
schwertnerCommented:
Classic Export utility is depricated in 11g. Only classic Import stays dor compatibility.

Use data pump because export created in 11g can only be imported in 11g (or future release 12c).

So Data Pump is the only option.

From other hand try to connect to 11g from 10g client and do the export from it.
Possibly the previous version will do classic export.
0
 
crishna1Author Commented:
exported from a server with 10g same result. i guess data pump is the only option.

Thanks!
0
 
crishna1Author Commented:
alos, setting deferred_segment_creation=false in the spfile also didnt work. data_pump it is then, will try and post  the update.
0
 
slightwv (䄆 Netminder) Commented:
>>exported from a server with 10g same result.

what exp version?  I just created a brand new table with 0 rows that exported just fine.

C:\>exp bob/bob tables=tab3

Export: Release 10.2.0.3.0 - Production on Tue Nov 1 13:35:42 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Produc
tion
With the Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                           TAB3          0 rows exported
Export terminated successfully without warnings.

C:\>

Open in new window

0
 
crishna1Author Commented:
Client Oracle 10.2.0.3, database server 11.2.0.1. looks like you are connect to a 10.2 using a 10.2?
0
 
slightwv (䄆 Netminder) Commented:
>>Client Oracle 10.2.0.3, database server 11.2.0.1

That would still be the 'old exp' and 11gR2 mentioned in http:#a37060383

Just because exp came with 11g, it is still considered the 'old' one.

Actually I would not expect the 10g export to understand an 11g database.

Here is an 11.2.0.2 complete test.  It might be an 11.2.0.1 bug.
C:\>sqlplus bud/bud

SQL*Plus: Release 11.2.0.2.0 Production on Tue Nov 1 13:55:57 2011

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> drop table tab1 purge;
drop table tab1 purge
           *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> create table tab1(col1 char(1));

Table created.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64
bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

C:\>exp bud/bud tables=tab1

Export: Release 11.2.0.2.0 - Production on Tue Nov 1 13:56:25 2011

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


Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit
Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                           TAB1          0 rows exported
Export terminated successfully without warnings.

C:\>

Open in new window

0
 
schwertnerCommented:
What happens if you set by export row=n ? Do the DDLs of the empty tables arise in the export?

I am asking because if they are in you can first import the DDLs and after that try to use the 'append' option with the rows in the nonempty tables.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

  • 4
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now