Re: Database migration question.

From: Karsten Farell <kfarrell_at_medimpact.com>
Date: Fri, 27 Sep 2002 17:44:58 GMT
Message-ID: <uO0l9.291$0V5.17560816@newssvr13.news.prodigy.com>

I hope I'm interpreting your query correctly.

If so, I wrote a series of scripts to compare the layout (structure) of two databases. I've included 3 of them (below your question) in the hopes that they might help you get started. You might need to modify some of them because I only included things that we use in our particular database. We aren't cutting (bleeding?) edge here! ;)

Praveen wrote:

> Hi..All,
> 
>     We are migrating on of our Oracle 8i database from one machine to 
> another. So, we are trying to find out, the lay out of the database i.e 
> tablespaces,datafiles,tables associated with particular tablespace, 
> partitioning  etc. and ofcourse there sizes.
> 
> Are there any SQL's which will help me in doing so.
> 
> TIA
> 
> Praveen

List Table Layout

---

set echo off feedback off pause off pagesize 0 set serveroutput on size 100000

-- 

DECLARE


   --

   cursor c_user_tables is
     select table_name
     from   user_tables
     order by table_name;


   --

   cursor c_user_tab_columns ( vin_table_name in varchar2 ) is
     select table_name,
            column_name,
            data_type,
            decode ( data_type,
              'NUMBER',    '(' || data_precision || ',' || data_scale || 
')',
              'VARCHAR2',  '(' || data_length    || ')',
              'NVARCHAR2', '(' || data_length    || ')',
              'CHAR',      '(' || data_length    || ')',
              'NCHAR',     '(' || data_length    || ')',
              ''
            )                      decode_data_type,
            decode ( nullable,
              'N',  ' NOT NULL',
              ''
            )                      decode_nullable
     from   user_tab_columns
     where  table_name = vin_table_name
     order by column_id;


   --

BEGIN


   --

   dbms_output.enable ( 100000 );


   --

   -- Display header.

   dbms_output.put_line (
     chr(10) || 'Table Layout Listing'
   );


   --

   -- For each table...

   for r_user_tables
     in c_user_tables
   loop
     --
     -- Display table name.
     dbms_output.put_line (
       chr(10) ||
       'Table: ' || r_user_tables.table_name
     );
     --
     -- For each column...
     for r_user_tab_columns
       in c_user_tab_columns ( r_user_tables.table_name )
     loop
       --
       -- Display column attributes.
       dbms_output.put_line (
         '..Column: ' || r_user_tab_columns.column_name ||
         ' '  || r_user_tab_columns.data_type ||
         r_user_tab_columns.decode_data_type ||
         r_user_tab_columns.decode_nullable
       );
       --
     end loop;  -- End for each column...
     --
   end loop;  -- End for each table...


   --

END;
/

-- 
set serveroutput off
set feedback on pagesize 24

List Constraint Layout
----------------------
set echo off feedback off pause off pagesize 0
set serveroutput on size 100000
-- 

DECLARE


   --

   cursor c_user_tables is
     select table_name
     from   user_tables
     order by table_name;


   --

   cursor c_user_constraints ( vin_table_name in varchar2 ) is
     select table_name,
            constraint_type,
            constraint_name,
            r_constraint_name,
            decode ( delete_rule,
              'CASCADE', '-YES-',
              'No'
            )                      decode_delete_rule,
            status
     from   user_constraints
     where  constraint_type in ('P', 'R')
     and    table_name = vin_table_name
     order by table_name, constraint_type, constraint_name;


   --

   cursor c_user_cons_columns ( vin_constraint_name in varchar2 ) is
     select constraint_name,
            column_name
     from   user_cons_columns
     where  constraint_name = vin_constraint_name
     order by position;


   --

BEGIN


   --

   dbms_output.enable ( 100000 );


   --

   -- Display header.

   dbms_output.put_line (
       chr(10) || 'Primary/Foreign Key Constraint Layout Listing'
   );


   --

   -- For each table...

   for r_user_tables
     in c_user_tables
   loop
     --
     -- Display table name.
     dbms_output.put_line (
       chr(10) || 'Table: ' || r_user_tables.table_name
     );
     --
     -- For each constraint...
     for r_user_constraints
       in c_user_constraints ( r_user_tables.table_name )
     loop
       --
       -- Indicate whether Primary or Foreign key.
       if r_user_constraints.constraint_type = 'P' then
         dbms_output.put ( '..Primary Key: ' );
       else
         dbms_output.put ( '..Foreign Key: ' );
       end if;
       --
       -- Display constraint attributes.
       dbms_output.put_line (
         r_user_constraints.constraint_name ||
         ' (' || r_user_constraints.status || ')'
       );
       --
       -- If FK, display PK of parent.
       if r_user_constraints.constraint_type = 'R' then
         dbms_output.put_line (
           '....Cascade Del: ' || r_user_constraints.decode_delete_rule
         );
         --
         dbms_output.put_line (
           '....Parent (PK): ' || r_user_constraints.r_constraint_name
         );
       end if;
       --
       -- For each constraint column...
       for r_user_cons_columns
         in c_user_cons_columns ( r_user_constraints.constraint_name )
       loop
         --
         dbms_output.put_line (
           '....Column: ' || r_user_cons_columns.column_name
         );
         --
       end loop;  -- End for each constraint column...
       --
     end loop;  -- End for each constraint...
     --
   end loop;  -- End for each table...


   --

END;
/

-- 
set serveroutput off
set feedback on pagesize 24

List Java Source
----------------
set echo off feedback off pause off pagesize 0
set serveroutput on size 100000
-- 

DECLARE


   --

   cursor c_user_source is
     select name,
            type,
            line,
            text
     from   user_source
     where  type = 'JAVA SOURCE'
     order by name, type, line;


   --

BEGIN


   --

   dbms_output.enable ( 100000 );


   --

   -- Display header.

   dbms_output.put_line (
     chr(10) || 'Java Source Layout Listing'
   );


   --

   -- For each Java source code name...

   for r_user_source
     in c_user_source
   loop
     --
     -- When first line, display Java source code name.
     if r_user_source.line = 1 then
       dbms_output.put_line (
         chr(10) ||
         'Java Source: ' || r_user_source.name
       );
     end if;
     --
     -- Display code line.
     dbms_output.put_line (
       '..' || r_user_source.text
     );
     --
   end loop;  -- End for each Java source code name...


   --

END;
/

-- 
set serveroutput off
set feedback on pagesize 24

Received on Fri Sep 27 2002 - 12:44:58 CDT