Oracle 11gR2 exp does not export some tables

exportoracle

I have an Oracle 11g (11.2.0.1) Database running on Linux (x64). Within the database I have a schema and 33 tables for it (all in the same tablespace). When I log in via sqlplus I can list all the tables via

SELECT OBJECT_NAME FROM USER_OBJECTS WHERE OBJECT_TYPE = 'TABLE';

But when I export the Tablespace using

exp ... BUFFER=65536 FULL=N COMPRESS=N CONSISTENT=Y TABLESPACES=... FILE=...

Then it only exports 24 of the 33 tables.
I have tried to export the missing tables via

exp ... TABLES=<missing_table> ...

But then I get an error:

 EXP-00011: <schema>.<missing_table> does not exist

How can I find out what's wrong here?
How can I export all the tables?

UPDATE:

After following Gary's advice I found the following difference between the exported and missing tables. DDL of an exported table:

CREATE TABLE "MY_SCHEMA"."EXPORTED_TABLE" ( ... ) TABLESPACE "MY_TS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 64K BUFFER_POOL DEFAULT) LOGGING NOCOMPRESS

And now the DDL of a not exported table:

CREATE  TABLE "MY_SCHEMA"."MISSING_TABLE" ( ... ) TABLESPACE "MY_TS" PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( BUFFER_POOL DEFAULT) LOGGING NOCOMPRESS

The above DDLs were generated by the Enterprise Manager. The ones created with sqlplus contained no STORAGE section at all for the missing tables.

I found out that the tables get exported when I reorganize them and set the STORAGE INITIAL value to 64K.

Problem solved. (Hopefully ;))

Best Answer

Do a SELECT DBMS_METADATA.GET_DDL('TABLE','yourTableName') for one of the tables that works, and one that doesn't. Then play spot the difference. (Note that yourTableName is case-sensitive.)

Could be something like BLOBs/CLOBs or partitions in a different tablespace, a dependency on a TYPE that is owned by another schema that isn't being exported...

Related Topic