Oracle 11g - Alert Log (X$DBGALERTEXT)

Today, i would like to share one new feature of Oracle 11g which i have come across recently.

In Oracle 11g, gives us a x$dbgalertext fixed table that maps to the alert log file, and allowing us to write SQL queries against it. It is very good news for Oracle Developers; becuase they dont have to depend on DBA's to view the alert log content.

SQL> desc x$dbgalertext

Name                       Null? Type
-------------------------- ----- ---------------------------
DDR                              RAW(4)
INDX                             NUMBER
INST_ID                          NUMBER
ORIGINATING_TIMESTAMP            TIMESTAMP(3) WITH TIME ZONE
NORMALIZED_TIMESTAMP             TIMESTAMP(3) WITH TIME ZONE
ORGANIZATION_ID                  VARCHAR2(64)
COMPONENT_ID                     VARCHAR2(64)
HOST_ID                          VARCHAR2(64)
HOST_ADDRESS                     VARCHAR2(16)
MESSAGE_TYPE                     NUMBER
MESSAGE_LEVEL                    NUMBER
MESSAGE_ID                       VARCHAR2(64)
MESSAGE_GROUP                    VARCHAR2(64)
CLIENT_ID                        VARCHAR2(64)
MODULE_ID                        VARCHAR2(64)
PROCESS_ID                       VARCHAR2(32)
THREAD_ID                        VARCHAR2(64)
USER_ID                          VARCHAR2(64)
INSTANCE_ID                      VARCHAR2(64)
DETAILED_LOCATION                VARCHAR2(160)
PROBLEM_KEY                      VARCHAR2(64)
UPSTREAM_COMP_ID                 VARCHAR2(100)
DOWNSTREAM_COMP_ID               VARCHAR2(100)
EXECUTION_CONTEXT_ID             VARCHAR2(100)
EXECUTION_CONTEXT_SEQUENCE       NUMBER
ERROR_INSTANCE_ID                NUMBER
ERROR_INSTANCE_SEQUENCE          NUMBER
VERSION                          NUMBER
MESSAGE_TEXT                     VARCHAR2(2048)
MESSAGE_ARGUMENTS                VARCHAR2(128)
SUPPLEMENTAL_ATTRIBUTES          VARCHAR2(128)
SUPPLEMENTAL_DETAILS             VARCHAR2(128)
PARTITION                        NUMBER
RECORD_ID                        NUMBER

Out of all attributes from x$dbgalertext, MESSAGE_TEXT attribute is the key here. It contains the alert log conent. Have nice time.

Please post your comments (if any).

Experiment on EXECUTE IMMEDIATE

Today, We will do an interesting experiment on EXECUTE IMMEDIATE statement. This statement allows us to execute DYNAMIC SQL Statements inside the PLSQL Procedure. It is one of the good feature from Oracle. For SYNTAX and more information about this you can find out in Oracle Documentation.

Now we are going to Create one Database User and then we will write one PLSQL procedure with EXECUTE IMMEDIATE statement.
Lets see,

SQL> -– Login as SYSDBA and Create the user called ‘DEMO’
SQL> show user
USER is "SYS"

SQL> create user demo identified by demo;

User created.

SQL> grant connect,resource to demo;

Grant succeeded.

Database user ‘Demo’ has been created successfully and granted CONNECT, RESOURCE roles to demo user. Okay, It is time to understand some basics of Oracle User Management.

Privilege: A privilege is a permission to execute a SQL Statement or to access another Database User’s Objects. From Oracle Perspective, there are two types or privileges, SYSTEM Privileges like (CREATE TABLE, CREATE VIEW, CREATE PUBLIC SYNONYM etc) and OBJECT Privileges like (SELECT, UPDATE, DELETE, INSERT on Objects eg. TABLE).

Role: A role is a set or group of privileges that can be granted to Users or another Role. “Role is a set of privileges“. Then, What are the privileges that are grouped in CONNECT and RESOURCE Roles?. Let me query the Data Dictionary Tables

SQL> desc role_sys_privs;
Name                       Null?        Type
-------------              --------     ------------   
ROLE                       NOT NULL     VARCHAR2(30)
PRIVILEGE                  NOT NULL     VARCHAR2(40)
ADMIN_OPTION                            VARCHAR2(3)

SQL> select privilege 
  2  from role_sys_privs 
  3  where role = ‘CONNECT’;

PRIVILEGE
--------------
CREATE SESSION

SQL> select privilege 
  2  from role_sys_privs 
  3  where role = ‘RESOURCE’;

PRIVILEGE
-----------------
CREATE SEQUENCE
CREATE TRIGGER
CREATE CLUSTER
CREATE PROCEDURE
CREATE TYPE
CREATE OPERATOR
CREATE TABLE
CREATE INDEXTYPE

8 rows selected.

Alright. Lets continue the experiment, As, i said earlier the user called ‘DEMO’ has been created and granted some roles. Now connect as DEMO user and Create a PLSQL procedure with EXECUTE IMMEDIATE Statement.

SQL> conn demo/demo
Connected.
SQL> show user
USER is "DEMO"

SQL> /*Create a procedure to create table called t and
SQL>  attributes id,name with EXECUTE IMMEDIATE Statement */

SQL> create or replace procedure p
  2  is
  3  begin
  4   EXECUTE IMMEDIATE ‘CREATE TABLE T
  5                     (ID NUMBER, 
  6                      NAME VARCHAR2(20))’;
  7  end;
  8  /

Procedure created.

SQL> -- Created the procedure called “P”. 
SQL> -- Let me execute this procedure
SQL> execute p;
BEGIN p; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at “DEMO.P”, line 4
ORA-06512: at line 1

Oops!, got an error “insufficient privileges” - Do we have this privilege?, YES. we have. It has been grouped under RESOURCE role and the RESOURCE role has been granted to this user (FYI: We have queried the Data Dictionary table to see the privileges that are mapped under RESOURCE role. SEE ABOVE). Then, What is stopping us? Let me grant the CREATE TABLE privilege directly to the DEMO user.

SQL> conn sys@orcl as sysdba
Enter password: *******
Connected.
SQL> show user
USER is "SYS"

SQL> grant CREATE TABLE to DEMO;

Grant succeeded.

SQL> conn demo/demo
Connected.
SQL> show user
USER is "DEMO"

SQL> -- Let me execute the procedure now
SQL> execute p;

PL/SQL procedure successfully completed.

SQL> — Wow! Table ‘T’ got created.
SQL> desc t;
Name                Null?    Type
----                -----    ------------
ID                           NUMBER
NAME                         VARCHAR2(20)

The Conculsion is, In STORED PROCEDURE, roles are DISABLED. Hence any PRIVILEGES granted by a ROLE to USER, will NOT be in effect. The PRIVILEGES MUST be explicitly granted to the user.

Hope you have enjoyed the experiment :-)

Post your suggestions (if any).

Reverse Engineer - Database Schema

There are lots of tools available in the market to reverse engineer your existing database schema objects and creates the Data Model from it
(Eg. Microsoft Visio, JDeveloper etc).

To do this job, I have prepared a SQL Script; the resultset of this query will tell you the Parent/Child relationships (FK_TABLE, FK_COLUMN referring PK_TABLE, PK_COLUMN). I feel this will be very handy script for DBAs, Application Developers and Database Architect to analyze the Parent-Child relationship for their existing database objects in the schema.

I have executed the below script in SCOTT Schema, Please verify the output.

SQL>column fk_table format a30
SQL>column fk_column format a30
SQL>column pk_table format a30
SQL>column pk_column format a30
SQL>set linesize 140
SQL>
SQL> SELECT p.table_name pk_table, 
  2         p.column_name pk_column,
  3         c.table_name fk_table, 
  4      c.column_name fk_column
  5    FROM (SELECT uc.constraint_name, 
  6                 ucc.table_name, 
  7        ucc.column_name
  8            FROM user_constraints uc, 
  9           user_cons_columns ucc
 10           WHERE uc.constraint_name IN (
 11         SELECT r_constraint_name
 12                 FROM user_constraints
 13                 WHERE constraint_type = 'R')
 14           AND uc.table_name = ucc.table_name
 15           AND uc.constraint_name = ucc.constraint_name) p,
 16         (SELECT uc.r_constraint_name, 
 17           ucc.table_name, 
 18        ucc.column_name
 19            FROM user_constraints uc, 
 20          user_cons_columns ucc
 21           WHERE uc.constraint_name IN (
 22         SELECT constraint_name
 23                 FROM user_constraints
 24                 WHERE constraint_type = 'R')
 25             AND uc.table_name = ucc.table_name
 26             AND uc.constraint_name = ucc.constraint_name) c
 27   WHERE p.constraint_name = c.r_constraint_name;


PK_TABLE  PK_COLUMN  FK_TABLE  FK_COLUMN
--------  ---------  --------  ---------
DEPT      DEPTNO     EMP       DEPTNO

Hope this will be useful for you.