Find Unused Columns in Oracle Database

Sometimes during development of new systems, you need to/may add new columns to tables and then you don’t use it and forget dropping it.

So you want to know which these columns to drop. Usually unused columns have NULL value, So I created a function to return array of column names in given/main schema have NULL value.

GET_NULL_COLUMNS function returns VARRAY of varchar2. It has only one parameter (IN_TABLE_NAME). If user passes a value for IN_TABLE_NAME then it will return NULL columns in this table only, otherwise it will return NULL columns in entire schema.

CREATE OR REPLACE FUNCTION GET_NULL_COLUMNS (  
   IN_TABLE_NAME VARCHAR2 DEFAULT NULL)  
   RETURN SYS.ODCIVARCHAR2LIST  
 IS  
   L_NULL_COLS   SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST ();  
   LN$ROW_COUNT  NUMBER;  
   LN$NULL_COUNT  NUMBER;  
   LN$INDEX    NUMBER := 1;  
   CURSOR LCUR$COLS (P_TABLE_NAME VARCHAR2)  
   IS  
    SELECT TABLE_NAME, COLUMN_NAME  
     FROM USER_TAB_COLS  
     WHERE DECODE (P_TABLE_NAME, TABLE_NAME, 1, NULL, 1, 0) = 1;  
 BEGIN  
   FOR LREC$COLS IN LCUR$COLS (IN_TABLE_NAME)  
   LOOP  
    EXECUTE IMMEDIATE 'SELECT COUNT(1) FROM ' || LREC$COLS.TABLE_NAME  
      INTO LN$ROW_COUNT;  
    EXECUTE IMMEDIATE  'SELECT COUNT(1) FROM '  
             || LREC$COLS.TABLE_NAME  
             || ' WHERE '  
             || LREC$COLS.COLUMN_NAME  
             || ' IS NULL'  
      INTO LN$NULL_COUNT;  
    IF LN$ROW_COUNT = LN$NULL_COUNT  
    THEN  
      L_NULL_COLS.EXTEND;  
      L_NULL_COLS (LN$INDEX) :=  
       LREC$COLS.TABLE_NAME || '.' || LREC$COLS.COLUMN_NAME;  
      LN$INDEX := LN$INDEX + 1;  
    END IF;  
   END LOOP;  
   RETURN L_NULL_COLS;  
 END;  

Let’s now test this function in HR schema:

 SELECT COLUMN_VALUE FROM TABLE (GET_NULL_COLUMNS);  

Image

Advertisements

Create Insert Statement for Table Data

In every site we have more than one environment  as Development, System Test, Integration Test, UAT, Pre-Production, Production, …..etc). Sometimes we need to insert some data in one environment and want to migrate it to another  environment. And what if we don’t have DBA rights. Under this condition, we can’t migrate any data from one environment to another. And,Usually we use database editors to do this task like (Toad, Plsql Developer, SQL developer, …. etc), but in this post , you can find a custom function which is called GEN_INSERT_STATEMENT and which is used for returning “SQL select  statement against input table parameter which we can use it to generate insert statement”.

CREATE OR REPLACE FUNCTION GEN_INSERT_STATEMENT (IN_TABLE_NAME VARCHAR2)  
   RETURN VARCHAR2  
 IS  
   LC$COLS_SELECT     VARCHAR2 (4000);  
   LC$COLS_VALUES     VARCHAR2 (4000);  
   LC$COLOUMN      VARCHAR2 (200);  
   CURSOR LCUR$TAB_COLUMNS (IN_TABLE_NAME VARCHAR2)  
   IS  
     SELECT COLUMN_NAME, DATA_TYPE, COLUMN_ID  
      FROM USER_TAB_COLS  
      WHERE TABLE_NAME = IN_TABLE_NAME  
    ORDER BY COLUMN_ID;  
 BEGIN  
   FOR LREC$TAB_COLUMNS IN LCUR$TAB_COLUMNS (UPPER (IN_TABLE_NAME))  
   LOOP  
    LC$COLS_SELECT :=  
       LC$COLS_SELECT  
      || CASE LREC$TAB_COLUMNS.COLUMN_ID WHEN 1 THEN '' ELSE ',' END  
      || LREC$TAB_COLUMNS.COLUMN_NAME;  
    IF INSTR (LREC$TAB_COLUMNS.DATA_TYPE, 'CHAR') > 0  
    THEN  
      LC$COLOUMN :=  
       '''''''''||' || LREC$TAB_COLUMNS.COLUMN_NAME || '||''''''''';  
    ELSIF INSTR (LREC$TAB_COLUMNS.DATA_TYPE, 'DATE') > 0  
    THEN  
      LC$COLOUMN :=  
       '''TO_DATE(''''''||TO_CHAR(' || LREC$TAB_COLUMNS.COLUMN_NAME  
       || ',''mm/dd/yyyy hh24:mi'')||'''''',''''mm/dd/yyyy hh24:mi'''')''';  
    ELSE  
      LC$COLOUMN := LREC$TAB_COLUMNS.COLUMN_NAME;  
    END IF;  
    LC$COLS_VALUES :=  
       LC$COLS_VALUES  
      || CASE LREC$TAB_COLUMNS.COLUMN_ID WHEN 1 THEN '' ELSE ',' END  
      || '''||DECODE('  
      || LREC$TAB_COLUMNS.COLUMN_NAME  
      || ',NULL,''NULL'','  
      || LC$COLOUMN  
      || ')||''';  
   END LOOP;  
   RETURN  'SELECT ''INSERT INTO '  
      || IN_TABLE_NAME  
      || ' ('  
      || LC$COLS_SELECT  
      || ') VALUES ('  
      || LC$COLS_VALUES  
      || ');'' FROM '  
      || IN_TABLE_NAME  
      || ';';  
 END;

Let’s Now run function for table EMPLOYEES.

SELECT GEN_INSERT_STATEMENT('EMPLOYEES') FROM DUAL;

The output is select statement that we should run it to get insert statement of data. Which is :

SELECT ‘INSERT INTO EMPLOYEES (EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE,JOB_ID,SALARY,COMMISSION_PCT,MANAGER_ID,DEPARTMENT_ID)VALUES (‘||DECODE(EMPLOYEE_ID,NULL,’NULL’,EMPLOYEE_ID)||’,’||DECODE(FIRST_NAME,NULL,’NULL’,””||FIRST_NAME||””)||’,’||DECODE(LAST_NAME,NULL,’NULL’,””||LAST_NAME||””)||’,’||DECODE(EMAIL,NULL,’NULL’,””||EMAIL||””)||’,’||DECODE(PHONE_NUMBER,NULL,’NULL’,””||PHONE_NUMBER||””)||’,’||DECODE(HIRE_DATE,NULL,’NULL’,’TO_DATE(”’||TO_CHAR(HIRE_DATE,’mm/dd/yyyy hh24:mi’)||”’,”mm/dd/yyyy hh24:mi”)’)||’,’||DECODE(JOB_ID,NULL,’NULL’,””||JOB_ID||””)||’,’||DECODE(SALARY,NULL,’NULL’,SALARY)||’,’||DECODE(COMMISSION_PCT,NULL,’NULL’,COMMISSION_PCT)||’,’||DECODE(MANAGER_ID,NULL,’NULL’,MANAGER_ID)||’,’||DECODE(DEPARTMENT_ID,NULL,’NULL’,DEPARTMENT_ID)||’);’ FROM EMPLOYEES;

And after running this statement. you will get your insert script as below:

Image

Get All PLSQL Errors via custom Development

Here is a way  to get all PLSQL errors in schema. This function should be used after calling program units so that can get PLSQL errors and you can use for logging and tracing.

CREATE OR REPLACE FUNCTION GET_PLSQL_ERROS
   RETURN VARCHAR2
IS
   LC$RETVALUE   VARCHAR2 (4000);

   CURSOR LCUR$ERRORS
   IS
        SELECT DISTINCT NAME, TYPE
          FROM USER_ERRORS
      ORDER BY 1, 2;

   PROCEDURE ADD_LINE (IN_LINE IN VARCHAR2)
   IS
   BEGIN
      LC$RETVALUE := SUBSTR (LC$RETVALUE || IN_LINE || CHR (10), 1, 4000);
   END ADD_LINE;
BEGIN
   FOR LREC$ERRORS IN LCUR$ERRORS
   LOOP
      ADD_LINE (LREC$ERRORS.NAME || ' ' || LREC$ERRORS.TYPE);

      ADD_LINE (
         RPAD ('-', LENGTH (LREC$ERRORS.NAME || LREC$ERRORS.TYPE) + 1, '-'));

      FOR LREC$ERROR_DET
         IN (  SELECT LINE, POSITION, SUBSTR (TEXT, 1, 128) TEXT
                 FROM USER_ERRORS
                WHERE NAME = LREC$ERRORS.NAME AND TYPE = LREC$ERRORS.TYPE
             ORDER BY SEQUENCE)
      LOOP
         ADD_LINE (
               LPAD (LREC$ERROR_DET.LINE, 4)
            || ' '
            || LPAD (LREC$ERROR_DET.POSITION, 3)
            || ' '
            || LREC$ERROR_DET.TEXT);
      END LOOP;

      ADD_LINE ('*******************' || CHR (10));
   END LOOP;

   IF LENGTH (LC$RETVALUE) = 4000
   THEN
      LC$RETVALUE := SUBSTR (LC$RETVALUE, 1, 3996) || CHR (10) || '...';
   END IF;

   RETURN NVL (LC$RETVALUE, 'No Errors');
END;
/

Create AWR Reports and Send Them via Email

DECLARE

   bid            NUMBER;
   eid            NUMBER;
   db_unique_name VARCHAR2(30);
   host_name      VARCHAR2(64);
   starttime      CHAR (5);
   endtime        CHAR (5);
   v_from         VARCHAR2 (80);
   v_recipient    VARCHAR2 (80) := ‘trial@trial.com’;
   v_mail_host    VARCHAR2 (30) := ‘oursmtpserver’;
   v_mail_conn    UTL_SMTP.connection;
BEGIN
   starttime := ’06:00′;
   endtime := ’10:00′;
   SELECT MIN (snap_id), MAX (snap_id)
     INTO bid, eid
     FROM dba_hist_snapshot
    WHERE TO_CHAR (begin_interval_time, ‘hh24:mi’) >= starttime
      AND TO_CHAR (end_interval_time, ‘hh24:mi’) <= endtime
      AND TRUNC (begin_interval_time) = TRUNC (SYSDATE)
      AND TRUNC (end_interval_time) = TRUNC (SYSDATE);
  SELECT host_name
    INTO host_name
    FROM v$instance;
  SELECT db_unique_name
    INTO db_unique_name
    FROM v$database;
   v_from := db_unique_name ||  ‘@’ || host_name;
   v_mail_conn := UTL_SMTP.OPEN_CONNECTION (v_mail_host, 25);
   UTL_SMTP.HELO (v_mail_conn, v_mail_host);
   UTL_SMTP.MAIL (v_mail_conn, v_from);
   UTL_SMTP.RCPT (v_mail_conn, v_recipient);
   UTL_SMTP.OPEN_DATA( v_mail_conn );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, ‘From:’ || v_from || UTL_TCP.CRLF );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, ‘To:’ || v_recipient || UTL_TCP.CRLF );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, ‘Subject: ‘
        || ‘AWR Report of ‘ || v_from || ‘ ‘
        || SYSDATE || ‘ ‘ || starttime || ‘-‘ || endtime || UTL_TCP.CRLF  );
   UTL_SMTP.WRITE_DATA ( v_mail_conn, ‘MIME-Version: 1.0’ || UTL_TCP.CRLF  );
   UTL_SMTP.WRITE_DATA ( v_mail_conn,
        ‘Content-Type: multipart/mixed; boundary=NEXTSLIDEPLEASE’
        || UTL_TCP.CRLF || UTL_TCP.CRLF );
   UTL_SMTP.WRITE_DATA ( v_mail_conn,
        ‘–NEXTSLIDEPLEASE’ || UTL_TCP.CRLF
        || ‘Content-Type: text/plain;’
        || UTL_TCP.CRLF || UTL_TCP.CRLF );
   UTL_SMTP.WRITE_DATA ( v_mail_conn,
        ‘AWR Reports are attached.’
        || UTL_TCP.CRLF || UTL_TCP.CRLF );
   FOR rac IN (SELECT dbid, inst_id FROM gv$database)
   LOOP
       UTL_SMTP.WRITE_DATA ( v_mail_conn,
        ‘–NEXTSLIDEPLEASE’ || UTL_TCP.CRLF
        || ‘Content-Disposition: attachment; ‘
        || ‘filename=”awr_’ || db_unique_name || rac.inst_id || ‘.html”‘
        || UTL_TCP.CRLF
        || ‘Content-Type: text/html; charset=utf8; ‘
        || UTL_TCP.CRLF || UTL_TCP.CRLF );
       FOR c1_rec IN
          (SELECT output
             FROM TABLE (DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(rac.dbid,
               rac.inst_id, bid, eid, 8 )))
       LOOP
          UTL_SMTP.WRITE_DATA (v_mail_conn, c1_rec.output || UTL_TCP.CRLF );
       END LOOP;
   END LOOP;
   UTL_SMTP.CLOSE_DATA (v_mail_conn);
   UTL_SMTP.QUIT (v_mail_conn);
EXCEPTION
   WHEN UTL_SMTP.TRANSIENT_ERROR OR UTL_SMTP.PERMANENT_ERROR
   THEN
      RAISE_APPLICATION_ERROR (-20000, ‘Unable to send mail: ‘ || SQLERRM);
END;
/
I’ve seen a question on OTN forum about how to create a job in Grid Control for generating AWR/ADDM reports and send these reports via email. As I know OEM Grid Control doesn’t have such a job template but as above we can write a PL/SQL script for this task and define it as a job, so we can automate it for all databases.

Using the MERGE Statement on Oracle

Oracle9i introduced the SQL MERGE command to combine a sequence of conditional INSERT and UPDATE commands in a single atomic statement, depending on the existence of a record. This operation is commonly known as Upsert functionality. Functionality like this is most often seen in Data Warehousing applications during the “Extraction, Transformation, and Loading” (ETL) process.Typical examples for apply the new MERGE function is in a data-warehousing environment where tables (typically fact tables) need to be refreshed periodically with new data arriving from on-line systems. The arriving data may contain changes to existing rows in a fact table and/or new rows that may need to be inserted. If, for example, a row in the new data corresponds to an item that already exists in the table, an UPDATE should be performed; if the row’s primary key does not exist in the table, an INSERT should be performed. Many times, the source system will not be able to distinguish between newly inserted or changed information during the extraction phase. During the transformation phase, especially with complex transformations, it is sometimes impossible in knowing when data needs to be inserted or updated. All of these scenarios would therefore require the determination of INSERT versus UPDATE to be done during the data-loading phase.

Prior to Oracle9i, the typical way to implement this type of functionality was to write a sequence of DMLs (INSERT/UPDATE) or as PL/SQL loops and then deciding, for each row, what action to take, insert or update. Commonly seen with this approach is a serious performance degradation. The first method would require multiple data scans while the second method operates on a “pre-record” bases. With the new SQL MERGE command can overcome these deficiencies by processing the conditional INSERT -or- UPDATE within a single atomic statement. The data will only be scanned once, and the appropriate DML command will be issued. This can be done in serial or in parallel.

Examples

The new SQL MERGE statement INSERTs some rows and UPDATEs others in a single operation. The decision about whether to update or insert into the target table is based upon a join condition: rows already in the target table that match the join condition are updated; otherwise a row is inserted using values from a separate subquery.

 

Example 1

The following example will MERGE values from the table DEPT_ONLINE into a table called DEPT:

SQL> SELECT * FROM dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        60 HELP DESK      PITTSBURGH
        40 OPERATIONS     BOSTON

SQL> SELECT * FROM dept_online;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON
        20 RESEARCH DEV   DALLAS
        50 ENGINEERING    WEXFORD

SQL> MERGE INTO dept d
     USING (SELECT deptno, dname, loc
            FROM dept_online) o
     ON (d.deptno = o.deptno)
     WHEN MATCHED THEN
         UPDATE SET d.dname = o.dname, d.loc = o.loc
     WHEN NOT MATCHED THEN
         INSERT (d.deptno, d.dname, d.loc)
         VALUES (o.deptno, o.dname, o.loc);

3 rows merged.

SQL>  SELECT * FROM dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH DEV   DALLAS
        30 SALES          CHICAGO
        50 ENGINEERING    WEXFORD
        60 HELP DESK      PITTSBURGH
        40 OPERATIONS     BOSTON

6 rows selected.


Example 2

The following example will MERGE values from the table DEPT_ONLINE into a table called DEPT, similar to Example 1 but using a slightly different syntax:

SQL> SELECT * FROM dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        60 HELP DESK      PITTSBURGH
        40 OPERATIONS     BOSTON

SQL> SELECT * FROM dept_online;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        40 OPERATIONS     BOSTON
        20 RESEARCH DEV   DALLAS
        50 ENGINEERING    WEXFORD

SQL> MERGE INTO dept d
     USING dept_online o
     ON (d.deptno = o.deptno)
     WHEN MATCHED THEN
         UPDATE SET d.dname = o.dname, d.loc = o.loc
     WHEN NOT MATCHED THEN
         INSERT (d.deptno, d.dname, d.loc)
         VALUES (o.deptno, o.dname, o.loc);

3 rows merged.

SQL> SELECT * FROM dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        50 ENGINEERING    WEXFORD
        10 ACCOUNTING     NEW YORK
        20 RESEARCH DEV   DALLAS
        30 SALES          CHICAGO
        60 HELP DESK      PITTSBURGH
        40 OPERATIONS     BOSTON

6 rows selected.


Example 3

The following example will MERGE literal values (not values from another table) using one SQL statement into a table called DEPT. Prior to Oracle9i, this would need to be accomplished using PL/SQL, but with Oracle9i‘s MERGE command, this can all be written within one SQL statement. Keep in mind that this example will start with a pretty clean DEPT table (DEPTNO 10 – 40). I will then be merging in a DEPTNO of 50. The first time the MERGE runs, it will insert the new DEPTNO of 50. The second MERGE example will update the record for DEPTNO 50 since it already exists.

SQL> SELECT * FROM dept;

    DEPTNO DNAME           LOC
---------- --------------- -----------
        10 ACCOUNTING      NEW YORK
        20 RESEARCH DEV    DALLAS
        30 SALES           CHICAGO
        40 OPERATIONS      BOSTON

SQL> MERGE INTO dept a
     USING (
         SELECT 50            deptno
              , 'ENGINEERING' dname
              , 'WEXFORD'     loc
         FROM dual
     ) b
     ON (a.deptno = b.deptno)
     WHEN NOT MATCHED THEN
         INSERT VALUES (b.deptno, b.dname, b.loc)
     WHEN MATCHED THEN
         UPDATE SET a.loc = 'WEXFORD, PA';

1 row merged.

SQL> SELECT * FROM dept;

    DEPTNO DNAME           LOC
---------- --------------- -----------
        10 ACCOUNTING      NEW YORK
        20 RESEARCH DEV    DALLAS
        30 SALES           CHICAGO
        40 OPERATIONS      BOSTON
        50 ENGINEERING     WEXFORD

SQL> MERGE INTO dept a
     USING (
         SELECT 50            deptno
              , 'ENGINEERING' dname
              , 'WEXFORD'     loc
         FROM dual
     ) b
     ON (a.deptno = b.deptno)
     WHEN NOT MATCHED THEN
         INSERT VALUES (b.deptno, b.dname, b.loc)
     WHEN MATCHED THEN
         UPDATE SET a.loc = 'WEXFORD, PA';

1 row merged.

SQL> SELECT * FROM dept;

    DEPTNO DNAME           LOC
---------- --------------- -----------
        10 ACCOUNTING      NEW YORK
        20 RESEARCH DEV    DALLAS
        30 SALES           CHICAGO
        40 OPERATIONS      BOSTON
        50 ENGINEERING     WEXFORD, PA


Example 4

In this example, I provide the same demo scenario but using a slightly different syntax for the MERGE statement. Both provide the same performance benefits and explain plan.

SQL> SELECT * FROM dept;

    DEPTNO DNAME           LOC
---------- --------------- -----------
        10 ACCOUNTING      NEW YORK
        20 RESEARCH DEV    DALLAS
        30 SALES           CHICAGO
        40 OPERATIONS      BOSTON

SQL> MERGE INTO dept
     USING dual ON (dept.deptno = 50)
     WHEN NOT MATCHED THEN
         INSERT VALUES (50, 'ENGINEERING', 'WEXFORD')
     WHEN MATCHED THEN
         UPDATE SET dept.loc = 'WEXFORD, PA';

1 row merged.

SQL> SELECT * FROM dept;

    DEPTNO DNAME           LOC
---------- --------------- -----------
        10 ACCOUNTING      NEW YORK
        20 RESEARCH DEV    DALLAS
        30 SALES           CHICAGO
        40 OPERATIONS      BOSTON
        50 ENGINEERING     WEXFORD

SQL> MERGE INTO dept
     USING dual ON (dept.deptno = 50)
     WHEN NOT MATCHED THEN
         INSERT VALUES (50, 'ENGINEERING', 'WEXFORD')
     WHEN MATCHED THEN
         UPDATE SET dept.loc = 'WEXFORD, PA';

1 row merged.

SQL> SELECT * FROM dept;

    DEPTNO DNAME           LOC
---------- --------------- -----------
        10 ACCOUNTING      NEW YORK
        20 RESEARCH DEV    DALLAS
        30 SALES           CHICAGO
        40 OPERATIONS      BOSTON
        50 ENGINEERING     WEXFORD, PA