Creating Unique Index By Using Table Name and Column Serial Number.

1. Create procedure for generating scripts and creating Unique Constraints.

CREATE OR REPLACE PROCEDURE SP_TABLE_CONSTRAINT
(P_TABLE_NAME IN VARCHAR2,
 P_COLUMN_SL IN VARCHAR2
 )
 IS
 V_SQL_SCRIPTS VARCHAR2(4000);
 V_COLUMN_LIST VARCHAR2(1000);
 BEGIN
    SELECT TO_CHAR(WM_CONCAT(COLUMN_NAME)) COLUMN_LIST
    INTO V_COLUMN_LIST
    FROM USER_TAB_COLUMNS C, (SELECT REGEXP_SUBSTR(P_COLUMN_SL,'[^,]+', 1, LEVEL) COLUMN_SL FROM DUAL
     CONNECT BY REGEXP_SUBSTR(P_COLUMN_SL, '[^,]+', 1, LEVEL) IS NOT NULL) S
    WHERE TABLE_NAME=P_TABLE_NAME
    AND C.COLUMN_ID=S.COLUMN_SL;
   
    V_SQL_SCRIPTS:='CREATE UNIQUE INDEX IDX_'||P_TABLE_NAME||'_UNQ_'||REPLACE(P_COLUMN_SL,',')||' ON '||P_TABLE_NAME||'('||V_COLUMN_LIST||')';
   
    EXECUTE IMMEDIATE V_SQL_SCRIPTS;
   
    DBMS_OUTPUT.PUT_LINE(V_SQL_SCRIPTS);
 
  EXCEPTION
 
     WHEN OTHERS THEN
     RAISE_APPLICATION_ERROR(-20100,SQLERRM);
   
 END;

2. Call Procedure for Creating Unique Constraints.

DECLARE
  P_TABLE_NAME VARCHAR2(32767);
  P_COLUMN_SL VARCHAR2(32767);

BEGIN
  P_TABLE_NAME := 'CONNECTIONS';
  P_COLUMN_SL := '1,2';

  SP_TABLE_CONSTRAINT ( P_TABLE_NAME, P_COLUMN_SL );

END;

Size of Redo Generation

Daily 
=========================================
SELECT TRUNC (COMPLETION_TIME) "REPORT DATE ",
     COUNT (*) "LOG SWITCH" ,
     ROUND ( (SUM (BLOCKS * BLOCK_SIZE) / 1024 / 1024)) "REDO PER DAY IN MB"
FROM V$ARCHIVED_LOG
GROUP BY TRUNC (COMPLETION_TIME)

ORDER BY 1;

Hourly 
=========================================
SELECT TO_CHAR(FIRST_TIME, 'DD-MON-YYYY:HH12:AM') "REPORT DATE AND HOUR ",
     COUNT (*) "LOG SWITCH" ,
     ROUND ( (SUM (BLOCKS * BLOCK_SIZE) / 1024 / 1024)) "REDO PER DAY IN MB"
FROM GV$ARCHIVED_LOG
GROUP BY TO_CHAR(FIRST_TIME, 'DD-MON-YYYY:HH12:AM')

ORDER BY 1;

How to Swap All Table & Index of Tablespace into different Tablespace

1. Find out distinct schema name of Tablespace using the following Query.

SELECT DISTINCT OWNER
  FROM ALL_TABLES
 WHERE TABLESPACE_NAME = 'TBS_01';

2. Now Create the following Procedure. [ if your table size is big you can add the parallel and nologging clause (||'  PARALLEL 8 NOLOGGING' ).

CREATE OR REPLACE PROCEDURE SP_TABLE_MOVE (
   P_USER_NAME            IN VARCHAR2,
   P_CURRENT_TABLESPACE   IN VARCHAR2,
   P_NEW_TABLESPACE       IN VARCHAR2)
IS
   V_ALTER_SCRIPTS_TABLE   VARCHAR2 (3000);
   V_INDEX_NAME            VARCHAR2 (100);
   V_IDX_SCRIPTS           VARCHAR2 (3000);
BEGIN
   FOR IDX_TABLE
      IN (SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
            FROM ALL_TABLES
           WHERE     OWNER = P_USER_NAME
                 AND TABLESPACE_NAME = P_CURRENT_TABLESPACE
                 AND TABLE_NAME = P_TABLE_NAME)
   LOOP
      V_ALTER_SCRIPTS_TABLE :=
            'ALTER TABLE '
         || IDX_TABLE.OWNER
         || '.'
         || IDX_TABLE.TABLE_NAME
         || ' MOVE TABLESPACE '
         || P_NEW_TABLESPACE;

      BEGIN
         EXECUTE IMMEDIATE V_ALTER_SCRIPTS_TABLE;
      EXCEPTION
         WHEN OTHERS
         THEN
            DBMS_OUTPUT.PUT_LINE (
                  'EXCEPTION IN MOVING TABLESPACE'
               || IDX_TABLE.TABLE_NAME
               || ' : '
               || SQLERRM);
      END;

      BEGIN
         FOR IDX_INDEX
            IN (SELECT OWNER, INDEX_NAME, TABLESPACE_NAME
                  FROM ALL_INDEXES
                 WHERE     OWNER = P_USER_NAME
                       AND TABLE_NAME = IDX_TABLE.TABLE_NAME)
         LOOP
            V_IDX_SCRIPTS :=
                  'ALTER INDEX '
               || IDX_INDEX.OWNER
               || '.'
               || IDX_INDEX.INDEX_NAME
               || ' REBUILD TABLESPACE '
               || P_NEW_TABLESPACE;

            BEGIN
               EXECUTE IMMEDIATE V_IDX_SCRIPTS;
            EXCEPTION
               WHEN OTHERS
               THEN
                  DBMS_OUTPUT.PUT_LINE (
                        'EXCEPTION IN REBUILD INDEX'
                     || IDX_INDEX.INDEX_NAME
                     || ' : '
                     || SQLERRM);
            END;
         END LOOP;
      END;
   END LOOP;
END;
/

3. Now execute the procedure to move table into different Tablespace.

EXEC SP_TABLE_MOVE('CBSUSR', 'TBS_01','TBS_02');

Sample Log:

SQL> conn /as sysdba
Connected.
SQL> CREATE OR REPLACE PROCEDURE SP_TABLE_MOVE (
   P_USER_NAME            IN VARCHAR2,
  2    3     P_CURRENT_TABLESPACE   IN VARCHAR2,
  4     P_NEW_TABLESPACE       IN VARCHAR2,
  5     P_TABLE_NAME           IN VARCHAR2)
  6  IS
  7     V_ALTER_SCRIPTS_TABLE   VARCHAR2 (3000);
  8     V_INDEX_NAME            VARCHAR2 (100);
  9     V_IDX_SCRIPTS           VARCHAR2 (3000);
 10  BEGIN
 11     FOR IDX_TABLE
 12        IN (SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
 13              FROM ALL_TABLES
 14             WHERE     OWNER = P_USER_NAME
 15                   AND TABLESPACE_NAME = P_CURRENT_TABLESPACE
 16                   AND TABLE_NAME = P_TABLE_NAME)
 17     LOOP
 18        V_ALTER_SCRIPTS_TABLE :=
 19              'ALTER TABLE '
 20           || IDX_TABLE.OWNER
         || '.'
 21   22           || IDX_TABLE.TABLE_NAME
         || ' MOVE TABLESPACE '
 23   24           || P_NEW_TABLESPACE;
 25
 26        BEGIN
 27           EXECUTE IMMEDIATE V_ALTER_SCRIPTS_TABLE;
 28        EXCEPTION
 29           WHEN OTHERS
 30           THEN
 31              DBMS_OUTPUT.PUT_LINE (
 32                    'EXCEPTION IN MOVING TABLESPACE'
 33                 || IDX_TABLE.TABLE_NAME
 34                 || ' : '
 35                 || SQLERRM);
 36        END;
 37
 38        BEGIN
 39           FOR IDX_INDEX
 40              IN (SELECT OWNER, INDEX_NAME, TABLESPACE_NAME
 41                    FROM ALL_INDEXES
 42                   WHERE     OWNER = P_USER_NAME
 43                         AND TABLE_NAME = IDX_TABLE.TABLE_NAME)
 44           LOOP
 45              V_IDX_SCRIPTS :=
 46                    'ALTER INDEX '
 47                 || IDX_INDEX.OWNER
 48                 || '.'
 49                 || IDX_INDEX.INDEX_NAME
 50                 || ' REBUILD TABLESPACE '
 51                 || P_NEW_TABLESPACE;
 52
 53              BEGIN
 54                 EXECUTE IMMEDIATE V_IDX_SCRIPTS;
 55              EXCEPTION
 56                 WHEN OTHERS
 57                 THEN
 58                    DBMS_OUTPUT.PUT_LINE (
 59                          'EXCEPTION IN REBUILD INDEX'
 60                       || IDX_INDEX.INDEX_NAME
 61                       || ' : '
 62                       || SQLERRM);
 63              END;
 64           END LOOP;
 65        END;
 66     END LOOP;
 67  END;
 68  /

Procedure created.

SQL>
SQL> SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
  FROM ALL_TABLES
 WHERE OWNER = 'CBSUSR' AND TABLE_NAME = 'ACCOUNTS'  2    3  ;

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
CBSUSR                   ACCOUNTS                      TBS_01
SQL> EXEC SP_TABLE_MOVE('CBSUSR', 'TBS_01','TBS_02');

PL/SQL procedure successfully completed.

SQL> SELECT OWNER, TABLE_NAME, TABLESPACE_NAME
  FROM ALL_TABLES
 WHERE OWNER = 'CBSUSR' AND TABLE_NAME = 'ACCOUNTS'  2    3  ;

OWNER                          TABLE_NAME                     TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
CBSUSR                  ACCOUNTS                          TBS_02

SQL>

How to find Session ID/ SQL ID/ SQL Statement Using OS Process ID

1. It will show you the basic Session Information.

SELECT P.SPID OS_PROCESS_ID,
      S.SID SESSION_ID,
      S.SERIAL#,
      S.USERNAME,
      S.STATUS,
      S.LAST_CALL_ET,
      P.PROGRAM,
      P.TERMINAL,
      LOGON_TIME,
      MODULE,
      S.OSUSER
 FROM GV$PROCESS P, GV$SESSION S
WHERE S.PADDR = P.ADDR
AND S.INST_ID=P.INST_ID
AND P.SPID=&Process_ID
ORDER BY S.INST_ID;

2. It Will Show You the SQL ID which SQL Statements currently Executing.

 SELECT S.SQL_ID
  FROM GV$PROCESS P, GV$SESSION S
 WHERE S.PADDR = P.ADDR
 AND S.INST_ID=P.INST_ID
 AND  P.SPID=&Process_ID
 ORDER BY S.INST_ID;

3. It Will Show You the SQL Statements Details which SQL Statements are currently Running.

SELECT *
FROM GV$SQLAREA
WHERE SQL_ID =
(SELECT S.SQL_ID
FROM GV$PROCESS P, GV$SESSION S
WHERE     S.PADDR = P.ADDR
AND S.INST_ID = P.INST_ID
AND P.SPID = &Process_ID)
ORDER BY INST_ID;

Find and recompile invalid object for particular table/objects.

SELECT DISTINCT NAME, TYPE, STATUS, 'ALTER '||REPLACE (TYPE, 'PACKAGE BODY', 'PACKAGE')||' '||NAME||' COMPILE;' RECOMPILE_SCRIPTS
  FROM USER_SOURCE U, USER_OBJECTS O
 WHERE     O.OBJECT_NAME = U.NAME
       AND UPPER (TEXT) LIKE '%TABLE_NAME%'
       AND UPPER(STATUS)<>'VALID'