Wednesday, February 27, 2013

Generating DDL of a schema using datapump


Here is the method to get entire DDL of a schema in SQL using datapump. In this example, we are trying to get DDL of a schema called MYSCHEMA.

Export entire schema metadata into a dump file. It will be created in directory MYDIR. To this work, first there should be a directory object MYDIR in database.
expdp murty@mydb schemas=MYSCHEMA directory=MYDIR dumpfile=schema_metadata.dmp content=METADATA_ONLY

Now we can extract SQL from the dump file like below.
impdp murty@mydb directory=MYDIR dumpfile=schema_metadata.dmp sqlfile=schema_metadata.sql exclude=STATISTICS

Estimate savings of disk space by shrinking a table


This post shows how to estimate disk space savings of shrinking a table. Here we are using Segment space advisor task.


-- Create a table and make it fragmented

SQL> create table tab1 (a number, b varchar2(10));

Table created.

-- Filling table with 1 million records
begin

  for i in 1 .. 1000000
  loop
     insert into tab1 values(i, 'AAAAAAAAAA');
  end loop;

  commit;
end;
/

SQL> select count(*) from tab1;

  COUNT(*)
----------
   1000000


-- Check the space usage in MB
SQL> select bytes/(1024*1024) from user_segments where segment_name='TAB1';

BYTES/(1024*1024)
-----------------
               23

-- Let us fragment it by deleting 90% of records evenly across table
SQL> delete from tab1 where mod(a, 10) != 1;

900000 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from tab1;

  COUNT(*)
----------
    100000

-- We see that it still using same space
SQL> select bytes/(1024*1024) from user_segments where segment_name='TAB1';

BYTES/(1024*1024)
-----------------
               23

Here is the script to estimate space savings if we do shrink space.

declare
 tname varchar2(100);
 oid number;
 tid number;
begin

tname:='CHECK_SHRINKABLE_1';

dbms_advisor.create_task (
  advisor_name     => 'Segment Advisor',
  task_id          => tid,
  task_name        => tname);

dbms_advisor.create_object (
  task_name        => tname,
  object_type      => 'TABLE',
  attr1            => 'MURTY',
  attr2            => 'TAB1',
  attr3            => NULL,
  attr4            => NULL,
  attr5            => NULL,
  object_id        => oid);

  dbms_advisor.set_task_parameter(
  task_name        => tname,
  parameter        => 'recommend_all',
  value            => 'TRUE');

dbms_advisor.execute_task(tname);

end;
/

We can use the view USER_ADVISOR_RECOMMENDATIONS view to check result.

SQL> set linesize 110

SQL> select BENEFIT_TYPE from USER_ADVISOR_RECOMMENDATIONS where TASK_NAME='CHECK_SHRINKABLE_1';

BENEFIT_TYPE
--------------------------------------------------------------------------------------------------------------------------------------------
Enable row movement of the table MURTY.TAB1 and perform shrink, estimated savings is 17512778 bytes.

Let us shrink the table and check the space usage.

SQL> alter table tab1 enable row movement;

Table altered.

SQL> alter table tab1 shrink space;

Table altered.

SQL> select bytes/(1024*1024) from user_segments where segment_name='TAB1';

BYTES/(1024*1024)
-----------------
           2.4375