• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 767
  • 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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

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