4

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 ;))

Tilo Prütz
  • 225
  • 1
  • 3
  • 8
  • I am getting the same issue. I have issued the command for two tables one which is working and for one which is not exported: For the one which is not workin: SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','DR$XMLDOCIX5_TX$I') from dual; DBMS_METADATA.GET_DDL('TABLE','DR$XMLDOCIX5_TX$I') -------------------------------------------------------------------------------- CREATE TABLE "SDE_RIYADH"."DR$XMLDOCIX5_TX$I" ( "TOKEN_TEXT" VARCHAR2(64) For the one which is getting exported: SQL> SELECT DBMS_METADATA.GET_DDL('TABLE','CHAINED_ROWS') from dual; DBMS_METADATA.GET_DDL('TABLE','CHAINED_ROWS') ------- – Ashu Oct 27 '11 at 17:56

8 Answers8

6

Starting from Oracle 11gR2 (11.2.0.1) there is a new feature: Deferred Segment Creation: the creation of a table sent is deferred until the first row is inserted. This results as empty tables not being listed in dba_segments and not being exported by exp utility.

The easiest solution is to use expdp utility.

If you have to use exp utility, you must run this command for all your empty tables:

ALTER TABLE tablename ALLOCATE EXTENTS
user78141
  • 61
  • 1
  • 2
1

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...

anon
  • 404
  • 1
  • 5
  • 15
Gary
  • 1,839
  • 10
  • 14
  • I am not that deep into Oracle. What has to be inserted at the "..."? – Tilo Prütz May 25 '10 at 07:28
  • So now I know more (and less ;)): The missing tables do NOT have a 'STORAGE' section in the output of SELECT DBMS_METADATA.GET_DDL. I will edit the question to contain the new information. – Tilo Prütz Jun 09 '10 at 06:49
1

Table exist in the DB but you cannot export it unless you allocate a extent to this table….

Elapsed: 00:00:00.04 14:44:54 PIMUSER @ mdmqa > alter table RECORDAPPROVALHISTORY allocate extent ;

shahzada
  • 11
  • 1
1

I'm getting similar error and think it has something to do with synonyms also.

I can describe the specific table in the database and select from it (zero rows). I can also describe select from the public synonym

But when I export that table, I get the 011 does not exist error, name or synoynm. When I insert a row (that solves one problem), I can export via table name but not via the synonym.

Of course, all this used to work in 11.1

First I've heard of expdp. Will look at that

Mike Moxcey
  • 11
  • 1
0

are the tables in the same tablespaces you export ? Check with this query: 

SELECT table_name, tablespace_name FROM user_tables
Vincent
  • 395
  • 1
  • 9
0

It is a new Oracle11 feature. only in Enterprise edition. Empty table will not be exported.

0

Yes the tables exist in the same schema. Some tables which are not exported contain data, only a few tables are there whose count is zero, rest all have data in them.

Regards

Ashu
  • 1
0

The allocate extent works. You can find the tables with this statement:

select 'alter table '||table_Name||' allocate extent;' from user_Tables where temporary='N' and table_name not in (select segment_name from user_segments)

Kenny Rasschaert
  • 8,925
  • 3
  • 41
  • 58
Korina Tolboom
  • 1