CODEWITHSUNDEEP
databaseoracle-databaseoracle11gdatabase-backups
AuthenticReplica
AuthenticReplica

Reputation: 870

Oracle database export missing tables

I've exported an instance of an Oracle 11g database (full) without shutting it down only to find that there are 400+ tables missing (were not exported). This database is used by an application and possibly had users on it.

The command I used was 

exp SYSTEM@db1 FULL=y FILE="C:\backup.dmp" GRANTS=y ROWS=y log="C:\backup.log"

Would not shutting it down before exporting make it skip all these tables?

Upvotes: 3

Views: 2584

Answers (2)

Pablishe
Pablishe

Reputation: 81

I had the same issue. In my case the issue was that the tablespace had not more free space (and it was not auto expanded).

The solution was to increase the tablespace size using the following command

ALTER DATABASE DATAFILE '../stuff01.dbf' RESIZE 100M;

check this link for more details.

RECOMENDATIONS

  • Use AUTOEXTEND ON for the tablespace.
  • In the future use expdp and impdp commands in order to have more details in the logs.

Upvotes: 0

Jon Heller
Jon Heller

Reputation: 36807

exp does not understand deferred segment creation and may not include these tables:

select owner, table_name from dba_tables where segment_created = 'NO' order by 1, 2;

Have you tried expdp instead? exp was deprecated in 10g, although there are bugs with the new tool and the workaround is often to use exp. If you really need to use exp then you may need to run this command on the tables:

alter table unexported_table allocate extent;

Upvotes: 2

Related Questions