Do a full exp

Backup, Recover, Import, Export, Datapump, etc
  

Poston Fri, 27 Apr 2012 10:25 am

Today when I got a test on my Oracle database, I had some problems with uncompiled objects, they gave the following errors table or view not found. Checking best saw that these tables that were not found, were tables that existed at the Bank but not had any record.

I wonder how I can make exp with all tables including the ones that are empty in the Bank.

Do as follows: exp/t test @ test full = y file = c:arquivo25042012.dmp thank you in advance.
skiche

Poston Wed, 02 May 2012 6:51 pm

Hello skiche, I suspect that your database is ORACLE 11 g.

In this case, I have a bad news for you. In this version, ORACLE 11 g only exports data from tables that have been populated with at least one record since its inception. IE, if in your relationship, you have a table that was created and populated, it will never be linked to your export, even if it is FULL.

The EXPORT product relies on information from DBA_SEGMENTS to assess which tables should be exported. In versions earlier than 11 g, when you give a CREATE TABLE command, DBA_SEGMENTS was ALWAYS populated with a record at least. Then, But in 11 g that's not happening anymore. In CREATE TABLE, it does not populate the DBA_SEGMENTS. It is populated only when a first record is inserted in this table. This feature was incorporated into the 11 g to expedite the execution of DDL scripts, since the commands CREATE TABLE/INDEX does not allocate disk space automatically. Only when a record is inserted into the new table you create records in DBA_SEGMENTS. Once this is done, you can perform a DELETE FROM <tabela>, which still she happens to be included later in the EXPORT.

A solution to your problem is to stop using the IMP/EXP and run the EXPDP and IMPDP. These are the currently recognized as backup programs from 11 g, being the use of the IMP/EXP no longer recommended for her.

But the annoying thing about this is that you can't run locally a IMPDP or EXPDP. Only in the database server it is possible.

I had a problem similar to yours with a base 11 g. In my case get solve the problem by inserting and deleting (soon) a record in each of the tables without records. After this activity lusitana, I get to relate all tables in EXPORT.

I guess that's complicated in a relational database, full of FKs. But just temporarily disable these FKs during insertion/deletion, and then reactivate the FKs. Once this is done, you will notice that your EXPORT will begin to relate these tables.

This is the only possible solution, if you want to use the IMP and EXP in 11 g.

Hugs and good luck, Sergio Coutinho
stcoutinho
Location: Sao Paulo - SP

Poston Mon, 07 May 2012 5:13 pm

Hello skiche, I just got off the phone with a colleague forwarded me DBA and a much simpler solution to your problem.

In 11 g it is possible to allocate extents (space) for empty tables that still don't have segments. Just run the command " ALTER TABLE .... ALLOCATE EXTENT; " to the empty table.

In this way, run the script below, which should show you the ALTER commands that should be executed in your schema where the tables reside:
Code: Select all
SELECT 'ALTER TABLE '||TABLE_NAME||' ALLOCATE EXTENT;' FROM USER_TABLES WHERE TABLE_NAME NOT IN (SELECT SEGMENT_NAME FROM USER_SEGMENTS);
Execute ALTER commands generated by the above querie.

After that, you can do a test with the EXP. you'll note that the tables no records will be listed in your export.

Hugs, Sergio Coutinho
stcoutinho
Location: Sao Paulo - SP

Poston Wed, 09 May 2012 6:23 pm

Thanks STCOUTINHO for your help.
I will be checking this possibility.
skiche



Return to DBA Backup / Recover

Who is online

Users browsing this forum: No registered users and 2 guests