Archiv für den Monat: Dezember 2014

Some not so often used SQL statements

There are some SQL statements that I only need from time to time. And in the rare moments I need them I have to look them up again. To have only one place to lookup the statements this page exists.

Database import with Oracle datapump

Before an import can be performed the target schema need to exist. A simple script to create a new schema could look like this:

create user <schema name> identified by <password> default tablespace <tablespace> temporary tablespace <temporary tablespace> quota unlimited on <tablespace>;
grant connect to <schema name>;
grant resource to <schema name>;
grant dba to <schema name>;
grant create any directory to <schema name>;
grant read, write on directory ORABACK to <schema name>;

Secondly there must be a database directory existing with the name ORABACK. To check if the directory is defined use:

select owner, directory_name, directory_patch from all_directories;

To create the directory ORABACK use:

create or replace directory ORABACK as ‘<directory for import>’;

Now everything is prepared for the import. Put the export file into the defined directory <directory for import>. Open a console and type:

impdb <schema name>/<password>@<database> schemas=<old schema name> directory=ORABACK dumpfile=<file name>.dmp logfile=<logfile name>.txt remap_schema=<old schema name>:<new schema name> TRANSFORM=OID:n:TYPE

Database export with Oracle datapump

For an export with the Oracle datapump a directory with the name ORABACK need to be defined in the database. How it can be defined is described in the above paragraph.

In the console type:

expdb <username>/<password>@<database> schemas=<schema name> directory=ORABACK dumpfile=<filename>.dmp logfile=<logfile name>.txt

Check for invalid objects

To check if invalid objects exist in the database use the statement:

select * from user_objects where status != ‘VALID’;

If invalid objects exist the following statements create statements to recompile the invalid objects.


select ‘alter ‘ || object_type || ‘ ‘ || object_name || ‘ compile;’ from user_objects where status != ‘VALID’ and object_type not like ‘PACKAGE%’:


select ‘alter package ‘ || object_name || ‘ compile package;’ from user_objects where status != ‘VALID’ and object_type = ‘PACKAGE’:


select ‘alter package ‘ || object_name || ‘ compile body;’ from user_objects where status != ‘VALID’ and object_type = ‘PACKAGE’ BODY;

Missing indices

Some times it is useful to check whether indices are missing. They could be responsible for performance issues. The following statements creates statements for the missing indices. Be careful, the tablespace for the indices has to be correct!

SELECT ‚CREATE INDEX ‚ || a.constraint_name || ‚I ON ‚ || a.table_name || ‚(‚ || a.columns || ‚) TABLESPACE <tablespace> COMPUTE STATISTICS;‘ stmt
  FROM ( SELECT a.table_name, a.constraint_name,
                LISTAGG(a.column_name, ‚,‘) within GROUP(ORDER BY a.position) columns
           FROM user_cons_columns a, user_constraints b
          WHERE a.constraint_name = b.constraint_name
            AND b.constraint_type = ‚R‘
            AND a.owner = b.owner
            GROUP BY a.table_name, a.constraint_name
       ) a,
       ( SELECT table_name, index_name,
        LISTAGG(c.column_name, ‚,‘) within GROUP(ORDER BY c.column_position) columns
           FROM user_ind_columns c
           GROUP BY table_name, index_name
       ) b
WHERE a.table_name = b.table_name(+)
   AND b.table_name is null
   AND b.columns(+) LIKE a.columns || ‚%‘
ORDER BY stmt;