Make an EXP full

[skiche]

Today when I climbed a test base on my Oracle bank, I had some problems with unzipped objects, they gave the following Table Or View Not Found errors. Verifying better I saw that these tables that were not found, were tables that existed in the Bank but had no record.

I would like to know how I can make an EXP with all the tables including those that are empty in the bank.

I do as follows:

Select all

exp teste/t@teste full=y file=c:\arquivo25042012.dmp

Thank you in advance.

[stcoutinho]

Hello Skiche,

I suspect that your database is Oracle 11g.

In this case, I have a little bad news for you. In this release, Oracle 11G only exports data from tables that have been populated with at least one record since its creation. That is, if in your list of tables you have one that was created and never populated, it will never be related to your export, even if it is full.

The export product is based on the DBA_SEGMENTS information to evaluate which tables should be exported. In previous versions to 11G, when giving a CREATE TABLE command, DBA_SEGMENTS was always populated with a record at least. So

but in 11g this does not happen anymore. By giving CREATE TABLE, it does not populate more DBA_Segments. It is populated only when a first record is entered in this table. This feature was incorporated to 11G to streamline the execution of DDL scripts, since the CREATE TABLE / INDEX commands do not automatically allocate disk space. Only when a record is entered in the new table if you create records in DBA_Segments. After this, you can run a delete from <table>, which even thus becomes subsequently included in Export.

A solution to your problem is to stop using the IMP / EXP and run the IMPDP and EXPDP. These are the currently recognized programs as backup from 11G, being the use of Imp / EXP no more recommended by it.

But the annoying of this is that you can not run locally an IPDP or EXPDP. Only on the bank server this is possible.

I had a problem similar to yours with an 11g base. In my case I could solve the problem by entering and deleting (then) a record in each of the tables without records. After this Lusitana activity, I could relate all the tables in Export.

I imagine this to be complicated in a relational database, full of fks. But just temporarily disable these fks during insertion / deletion, and then reactivate fks. After this, you will notice that your export will start relating these tables.

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

Hugs and good luck,

Sergio Coutinho

[stcoutinho]

Hello Skiche,

I said now with a DBA colleague and this one passed me a much simpler solution to your problem.

In 11g it is possible to allocate extents (space) for the empty tables that still do not have segments. It would be enough to run the ALTER TABLE .... ALLOCATE EXTENT; command for the empty table.

In this way, run the script below, which should show you the alter commands that must be performed on your schema where the tables reside:

Select all

 
SELECT 'ALTER TABLE '||TABLE_NAME||' ALLOCATE EXTENT;' FROM USER_TABLES WHERE TABLE_NAME NOT IN (SELECT SEGMENT_NAME FROM USER_SEGMENTS); 

] Run the alter commands generated by the queries above.

After that, you can take a test with the EXP. You will find that the tables without records will be related to your Export.

Hugs,

Sergio Coutinho

[skiche]

Thank you stcoutinho for your help.
I will be verifying this possibility.