Data dictionary views for Oracle Types

User defined Oracle types can be a very powerful tool for experienced developers. As with any other object type, it is critical to know if an object (i.e. TYPE here) exists before you create it during execution of the deployment script. The following Oracle data dictionaries have the required information whether you need type or type attribute details:

user_types          - lists all user owned custom types
user_type_attrs     - lists all attributes for the owned custom types
user_type_methods   - lists all methods found in the owned custom types

Below you can find a few functions that check if a required entity exists or not and return 1 (exists) or 0 (does not exist). Note that these functions can check types in other schemas given that respective privileges have been granted.

CREATE FUNCTION type_exists(p_schema_name VARCHAR2,
                            p_type_name   VARCHAR2) 
RETURN INTEGER
AS
    v_cnt INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_cnt
    FROM all_types
    WHERE owner=UPPER(p_schema_name)
      AND type_name=UPPER(p_type_name);

    RETURN SIGN(v_cnt);
END type_exists;

CREATE FUNCTION type_attribute_exists(p_schema_name VARCHAR2,
                                      p_type_name   VARCHAR2,
                                      p_attr_name   VARCHAR2) 
RETURN INTEGER
AS
    v_cnt INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_cnt
    FROM all_type_attrs
    WHERE owner=UPPER(p_schema_name)
      AND type_name=UPPER(p_type_name)
      AND attr_name=UPPER(p_attr_name);

    RETURN SIGN(v_cnt);
END type_attribute_exists;

CREATE FUNCTION type_method_exists(p_schema_name VARCHAR2,
                                   p_type_name   VARCHAR2,
                                   p_method_name VARCHAR2) 
RETURN INTEGER
AS
    v_cnt INTEGER;
BEGIN
    SELECT COUNT(*) INTO v_cnt
    FROM all_type_methods
    WHERE owner=UPPER(p_schema_name)
      AND type_name=UPPER(p_type_name)
      AND method_name=UPPER(p_method_name);

    RETURN SIGN(v_cnt);
END type_method_exists;

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.

Create Database Link using EZConnect Syntax

EZConnect syntax eliminates the need for service name lookup in tnsnames.ora files when connecting to an Oracle Database across a TCP/IP network. In a previous post we have already demonstrated how to use this syntax to establish connection in SQL*Plus.

Now, we are going even further. You can use the same syntax for creating Database Links (assuming that you have a privilege to create database links in general).

CREATE DATABASE LINK dbl_test
CONNECT TO scott
IDENTIFIED BY tiger
USING ‘scott/tiger@192.168.1.180:1521/ORCL’;

Database link created.

See http://www.orafaq.com/wiki/EZCONNECT for more information on the syntax and prerequisites.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to check and change Oracle ArchiveLog Mode

Level: Beginner (DBA)

Question: How to check database ArchiveLog mode?

Answer:

To check the database archive log mode you need to
Step 1: Connect as SYSDBA

ex: connect sys/sysdbapass@server as sysdba

Step 2: Execute one of the following commands:
a) ARCHIVE LOG LIST

--In SQL*PLus
SQL> ARCHIVE LOG LIST
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36298
Current log sequence           36300

b) Select from v$database

SQL> SELECT log_mode
  2  FROM sys.v$database
  3  /

LOG_MODE
------------
NOARCHIVELOG

Question: How to enable ArchiveLog mode?

You can specify the initial archiving mode in the CREATE DATABASE statement. Normally, there is no need to use this option as you can always change the mode later.

Answer:

Step 1: Connect as sysdba user

Step 2: Assuming that you are using spfile for parameter initialization, we need to configure the archive log destination. We will use a simple single destination option. All available options are described in the Oracle documentation.

SQL> ALTER SYSTEM SET log_archive_dest='F:\OraArchivedLogs' SCOPE=spfile;

System altered.

REM Note that this change will only work if db_recovery_file_dest is set to a blank, so you can execute another command just in case:

SQL> ALTER SYSTEM SET db_recovery_file_dest='' SCOPE=spfile;

System altered.

Step 3: Shutdown the database

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Step 4: Backup the database (Cold backup)
Step 5: Start a new instance and mount the database.

SQL> startup mount
ORACLE instance started.

Total System Global Area 5227814912 bytes
Fixed Size                  2264328 bytes
Variable Size            3321889528 bytes
Database Buffers         1895825408 bytes
Redo Buffers                7835648 bytes
Database mounted.

Step 6: Change the database archiving mode

SQL> ALTER DATABASE ARCHIVELOG;

Database altered.

Step 7: Open the database.

SQL> ALTER DATABASE OPEN;

Database altered.

Step 8: Shut down the database (see Step 3 above)

Step 9: Backup the database (see Step 4 above)

Step 10: Open the database:

SQL> STARTUP
ORACLE instance started.

Total System Global Area 5227814912 bytes
Fixed Size                  2264328 bytes
Variable Size            3321889528 bytes
Database Buffers         1895825408 bytes
Redo Buffers                7835648 bytes
Database mounted.
Database opened.

SQL> ARCHIVE LOG LIST
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36298
Next log sequence to archive   36300
Current log sequence           36300

Question: How to disable ArchiveLog mode?

Answer:

Step 1: Connect as sysdba user
Step 2: Shutdown the database
Step 3: Backup the database (Cold backup)
Step 4: Start a new instance and mount the database.
Step 5: Change the archivelog mode:

SQL> ALTER DATABASE NOARCHIVELOG;

Database altered.

Step 6: Open the database

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ARCHIVE LOG LIST
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     36298
Current log sequence           36300

ORA-39087: directory name DATA_PUMP_DIR is invalid

Oracle 12c introduced a new concept of a pluggable database. It comes with many interesting features as well as some nuances that we need to know to perform such tasks as data pump export and import.

The main surprise comes with an undocumented fact that Oracle’s default Data Pump directory object, DATA_PUMP_DIR, cannot be used for data pump export and import.

When you try to attempt running expdp or impdp utilities using DIRECTORY=DATA_PUMP_DIR, you should get the following error message:

ORA-39002: invalid operation
ORA-39070: Unable to open the log file.
ORA-39087: directory name DATA_PUMP_DIR is invalid

The solution is quite straightforward: do not use DATA_PUMP_DIR directory object for pluggable db.

You can create a new directory object (as a SYS user or as a user with CREATE ANY DIRECTORY privilege granted), grant READ and WRITE privileges on that DIRECTORY, and perform data pump export/import tasks.

Replace clause for changing one’s password.

Starting with Oracle 11g, if you get the following error when changing your own password, it means that you have to include REPLACE clause in your ALTER USER command:

ALTER USER testuser IDENTIFIED BY testpassword;

Oracle: “ORA-28221: REPLACE not specified” 

Syntax with REPLACE clause:

ALTER USER testuser IDENTIFIED BY testpassword REPLACE oldpassword;

User altered.

If your password contains special characters, you may need to put it in quotations:

ALTER USER testuser IDENTIFIED BY "testp@ssw@rd" REPLACE oldpassword;

User altered.

As per Oracle documentation,

You can omit the REPLACE clause if you are setting your own password or you have the ALTER USER system privilege and you are changing another user’s password. However, unless you have the ALTER USER system privilege, you must always specify the REPLACE clause if a password complexity verification function has been enabled, either by running the UTLPWDMG.SQL script or by specifying such a function in the PASSWORD_VERIFY_FUNCTION parameter of a profile that has been assigned to the user.

Source: http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_4003.htm#SQLRF01103

How to DELETE all records from all tables in a schema?

This is rather an exercise than a practical task. Anyway, the approach presented below might be helpful in some practical situations.

Problem Level: Beginner/Intermediate

Step 1: Disable all foreign key constraints.

DECLARE
  CURSOR c IS
  SELECT table_name, constraint_name
  FROM user_constraints
  WHERE constraint_type='R'
   AND status='ENABLED';
BEGIN
  FOR v IN c LOOP
     EXECUTE IMMEDIATE 'ALTER TABLE ' || v.table_name || ' DISABLE CONSTRAINT ' || v.constraint_name;
  END LOOP;
END;
/

Step 2: DELETE from all tables

DECLARE
  CURSOR c IS
  SELECT table_name
  FROM user_tables;
BEGIN
  FOR v IN c LOOP
     EXECUTE IMMEDIATE 'DELETE FROM ' || v.table_name;
  END LOOP;
END;
/

Step 3: COMMIT or ROLLBACK

ROLLBACK; --you can commit here instead if needed

STEP 4: Enable all FOREIGN KEY Constraints

DECLARE
  CURSOR c IS
  SELECT table_name, constraint_name
  FROM user_constraints
  WHERE constraint_type='R'
   AND status='DISABLED';
BEGIN
  FOR v IN c LOOP
     EXECUTE IMMEDIATE 'ALTER TABLE ' || v.table_name || ' ENABLE CONSTRAINT ' || v.constraint_name;
  END LOOP;
END;
/

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.

How to find all packaged procedures that have argument(s) of a specific data type?

This task is quite trivial if you know the right data dictionary to use.
Let’s say we need to see all procedures that has DATE arguments:

SELECT package_name, object_name AS procedure_name, argument_name, data_type
FROM user_arguments a
WHERE data_type='DATE'
  AND package_name IS NOT NULL
ORDER BY 1,2,3

How to create a report showing the number of records in each table of a given schema?

How to create a report showing the number of records in each table of a given schema?
Well, if you have just updated statistics for the entire schema, you can simply query a data dictionary table. We will not consider such case. Let’s assume that statistics is inaccurate, so we need a SQL or a PL/SQL solution.

1) PL/SQL solution
There are many approaches to procedural solution, for ex:
1. Loop over the cursor based on “SELECT table_name FROM user_tables”
2. Count number of rows using “EXECUTE IMMEDIATE” statement.
3. Use dbms_output package to produce the output or populate a collection and select from it to return a cursor to the caller.

2) SQL*Plus script generation
You can write a SQL script that will generate another script that counts number of rows and combines them all together with UNION ALL statement.

Options 1 and 2 have been around for years and are not of any significant interest anymore as Oracle now supports XML with many interesting applications – see the next option for details.

3) Update schema statistics and query user_tables view

SELECT table_name, num_rows 
FROM user_tables

This could be a time consuming process depending on the size of existing tables and number of indexes.

4) Use dbms_xmlgen.getxmltype
dbms_xmlgen package allows to dynamically create and execute numerous SELECT statements and parse the result XML to extract necessary information. Here is the most elegant solution to the problem:

col "Records" for a10

SELECT table_name,                  
       dbms_xmlgen.getxmltype('SELECT COUNT(1) cnt from '|| table_name).extract('/ROWSET/ROW/CNT/text()').getstringval() "Records"
FROM user_tables;

TABLE_NAME                     Records
------------------------------ ---------
DEPT                           4
EMP                            14
BONUS                          0
SALGRADE                       5

Special Note: Sometimes, if your schema has huge tables, this approach may fail if the database does not have enough memory resources available or allocated to process the query. In this case, we may suggest using one of the approaches mentioned above (1-3).

Suggested further reading:

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions. The book is also available on Amazon and in all major book stores.

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

A tricky Oracle DDL question from a real job interview

Recently I was asked the following question during the phone interview with one of the large companies:

Consider the following table:
[CREATE] TABLE table1
(
column1 NUMBER PRIMARY KEY,
column2 NUMBER,
column3 VARCHAR2(50),
column4 NUMBER
)

Change the data type of column3 from VARCHAR2(50) to CLOB while keeping the order of columns intact.

The anticipated answer was:

-- Step 1: Create a backup table with PK and last 2 columns
CREATE TABLE table1_bak AS
SELECT column1, column3, column4
FROM table1;

--Step 2: Drop last 2 columns
ALTER TABLE table1 DROP (column3, column4);

--Step 3: Add last 2 columns back:
ALTER TABLE table1 ADD(column3 CLOB, column4 NUMBER);

--Step 4: Restore the data
MERGE INTO table1 a
USING table1_bak b
ON (a.column1=b.column1)
WHEN MATCHED THEN
    UPDATE SET a.column3=b.column3,
               a.column4=b.column4;

--Step 5: Drop backup table
DROP TABLE table1_bak;

This answer was provided. In the spirit of finding workarounds I added another solution:

--Step 1: Add temp column
ALTER TABLE table1 ADD (column5 VARCHAR2(50));

--Step 2: Update column3 with NULLs and column5 with values of column3
UPDATE table1
SET column5=column3,
    column3=NULL;

--Step 3: Change column3 data type to LONG first and CLOB after that:
ALTER TABLE table1 MODIFY (column3 LONG);
ALTER TABLE table1 MODIFY (column3 CLOB);

--Note: trying to change empty column VARCHAR2 to CLOB straight throws the following error:

ALTER TABLE table1 MODIFY (column3 CLOB)
                           *
ERROR at line 1:
ORA-22858: invalid alteration of datatype

--Step 4: Restore the content of column3 from column5:
UPDATE table1
SET column3=column5;

--Step 5: Drop the temp column:
ALTER TABLE table1 DROP COLUMN column5;

My Oracle Group on Facebook:

If you like this post, you may want to join my new Oracle group on Facebook: https://www.facebook.com/groups/sqlpatterns/

Would you like to read about many more tricks and puzzles?

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds”.

How to find and recompile all invalid Oracle objects?

Finding invalid Oracle object is a fairly simple DBA task (run as system or a user with select privilege on dba_objects view):

col object_type for a20
col object_name for a50

SELECT owner, object_type, object_name
FROM dba_objects
WHERE status='INVALID'
ORDER BY 1,2
/

Recompiling the invalid objects involves a few tricks. Normally, an Oracle object can be recompiled with the following SQL:

ALTER  [object_type] [owner].[object_name] COMPILE

However, PACKAGE BODY and SYNONYM objects require some special treatment. For the PACKAGE BODY objects the syntax changes just slightly:

ALTER  PACKAGE [owner].[object_name] COMPILE BODY

For the SYNONYMS (which don’t necessarily need to be recompiled) the most reliable way to obtain the VALID status is to recreate them using dbms_metadata.get_ddl function.

Below is a complete script that puts everything together:

--******************Recompiling Invalid Objects (if any exist)***************
--Author: Zahar Hilkevich (https://sqlpatterns.wordpress.com)
--***************************************************************************

DECLARE
CURSOR c IS
SELECT owner, object_name, object_type
FROM dba_objects
WHERE status='INVALID';
v_sql VARCHAR2(500);
BEGIN
  FOR v IN c LOOP
    IF v.object_type='PACKAGE BODY' THEN
        v_sql:='ALTER PACKAGE ' || v.owner || '.' || v.object_name || ' COMPILE BODY'; 
    ELSIF v.object_type='SYNONYM' THEN
	v_sql:=dbms_metadata.get_ddl(object_type=>'SYNONYM',
                                     name=>v.object_name,
                                     schema=>v.owner);
    ELSE
        v_sql:='ALTER ' || v.object_type || ' ' || v.owner || '.' || v.object_name || ' COMPILE'; 
    END IF;
    --DBMS_OUTPUT.PUT_LINE(v_sql);
    BEGIN
        EXECUTE IMMEDIATE v_sql;
    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(v_sql);
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
    END;
  END LOOP;
END;
/

--****************** End of Recompiling Invalid Objects *********************

--****************** Invalid Objects Report *********************************

col object_type for a20
col object_name for a50

SELECT owner, object_type, object_name
FROM dba_objects
WHERE status='INVALID'
ORDER BY 1,2
/

--******************End of Invalid Objects Report ***************************

For more tricks and cool techniques check my book “Oracle SQL Tricks and Workarounds” for instructions.