Exporting a database does not export some tables
Platform Notice: Data Center Only - This article only applies to Atlassian products on the Data Center platform.
Note that this KB was created for the Data Center version of the product. Data Center KBs for non-Data-Center-specific features may also work for Server versions of the product, however they have not been tested. Support for Server* products ended on February 15th 2024. If you are running a Server product, you can visit the Atlassian Server end of support announcement to review your migration options.
*Except Fisheye and Crucible
Problem
When using the exp utility to export an Oracle database, you may find that not all tables are included in the export.
Cause
Empty tables may not be included in the export, as they have not been allocated space on disk. In versions of Oracle before 11g R2, Oracle allocates space for a table when the table is first created.
After 11g R2, Oracle defaults the option deferred_segment_creation to true, which allocates space for a table when the first row is inserted.
If the tables were created when this option was enabled, tables may be empty and may not exist on disk. Those tables would then subsequently fail to export.
Diagnosis
Run the following query as your database user. If it returns any rows, you are affected, and should proceed with the resolution:
1
SELECT * FROM user_tables WHERE segment_created = 'NO'; Resolution
Always back up your data before making any database modifications. If possible, test any alter, insert, update, or delete SQL commands on a staging server first.
Run the following SQL query to generate a list of alter statements:
1
SELECT 'ALTER TABLE '||table_name||' ALLOCATE EXTENT;' FROM user_tables WHERE segment_created = 'NO';Run the ALTER statements against your database, and then attempt the export again.
Acknowledgements
The contents for this article were derived from this StackOverflow post.
Was this helpful?