About
Oracle schema extraction
Oracle professionals must frequently
"punch," or extract, table and index
definitions from Oracle and move them to
different systems. Extracting Data
Definition Language (DDL) for Oracle schema
objects (e.g., tables, indexes, stored
procedures, triggers, sequences, etc.) from
the dictionary is very useful when you're
migrating a system to a new platform and you
want to pre-create the objects in a new
tablespace.
Generally, in an Oracle migration, the
schema and DDL are created in the target
database, and then the rows are imported
into the new database using the Oracle imp
utility with the IGNORE=Y parameter. The
IGNORE=Y parameters tells Oracle to use the
new DDL definitions instead of the DDL
inside the import data file.
But in Oracle9i and beyond, you can use the new utility
package called dbms_metadata that will
easily display DDL and stored procedures
directly from the data dictionary. Using
this powerful utility, you can punch
individual objects or an entire Oracle
schema. Best of all, it is easy to use. You
simply execute dbms_metadata.get_ddl,
specify the object names, and Oracle will
extract ready-to-use DDL.
For large migrations, you can modify the
dbms_metadata syntax to punch a whole
schema. As you can see in Listing C, it is
easily done by selecting dbms_metadata.
get_ddl and specifying USER_TABLES and
USER_INDEXES. This syntax will punch all
table and index definitions for the entire
schema.This
exercise will allow you to view the
important statistics that are gathered
within the Oracle database. Your first task
is to analyze your pubs schema. To do this,
you must "analyze" all of the tables and
indexes in the database. For example, and
analyze command for the authors table might
look like this:
analyze
table authors compute statistics;
Now, after having analyzed your schema,
rerun the query from the previous step, and
note the changes in the statistics. Contrast
your personal findings with the topics in
the reading "What is your optimizer
philosophy?", and discuss how often it is
"necessary" to re-analyze a schema for the
cost-based optimizer. Post your thoughts and
notes in the discussion forum. The
query below will extract all of the schema
names from the Oracle data dictoinary.
select distinct
(owner) from
dba_segments
where
owner in (select username
from dba_users
where default_tablespace not in ('SYSTEM','SYSAUX')
) ;
or
select distinct(owner) from dba_segments
where owner not in (
'SYSTEM',
'XDB',
'SYS',
'TSMSYS',
'MDSYS',
'EXFSYS',
'WMSYS',
'ORDSYS',
'OUTLN',
'DBSNMP'
);
Related Schema Articles:
Oracle Schema Documentation
Get Oracle schema DDL syntax with dbms_metadata
Oracle Schema Components
Tracking Oracle schema changes
Oracle schema change control
Oracle Schema Statistics Management |