Exporting a database does not export empty tables

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

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.

Run the following query as your database user. If it returns any rows, you are affected, and should proceed with the resolution:

SELECT * FROM user_tables WHERE segment_created = 'NO';

Resolution

Run the following SQL query to generate a list of alter statements:

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.

ALTER TABLE <table_name> ALLOCATE EXTENT;

Run below script to create procedure and then execute the procedure in Begin..End to allocate extent to empty tables

Create Or Replace Procedure Allocate_Extent Is
  Cursor c Is
    Select 'ALTER TABLE ' || Table_Name || ' ALLOCATE EXTENT' Script
      From User_Tables
     Where Segment_Created = 'NO';
  m_Sql Varchar2(1000);
Begin
  For i In c Loop
    m_Sql := i.Script;
    Dbms_Output.Put_Line(m_Sql);
    Execute Immediate (m_Sql);
  End Loop;
End;

I hope it's helpful for you. If you have any queries, don't hesitate to contact me.

Name : Muhammad Sahal Qasim

E-mail : s.m.sahal789@gmail.com

Thank you.

Read More

Place the stacked canvas in TAB page canvas

The stacked canvas cannot be placed in the TAB page canvas. However, the stacked canvas may be shown on all/required tab pages.

Example: (Requirement such as this...) we must display the ITEM DETAILS (Line sequence number, item number, and description) on all TAB pages (Item details, OSP WIP Job, PO Details, Container and notes).

As specified, you designed the content and TAB page canvas; when you attempt to construct the stacked canvas, the developer suite will generate an error...

I hope it's helpful for you. If you have any queries, don't hesitate to contact me.

Name : Muhammad Sahal Qasim

E-mail : s.m.sahal789@gmail.com

Thank you.

Read More