PLSQL Native Compilation

By default, PL/SQL code is compiled and stored in the form of byte code ready for execution. During the execution process, this byte code is interpreted, a process which requires time and resources. The process of native compilation converts PL/SQL stored procedures to Pro*C, which is then compiled to native code shared libraries, resulting in performance increases for the procedural code. The extent of the performance increase depends on the content of the PL/SQL, with the best results shown in code containing lots of loops, logic, mathematical operations and comparatively less database work.

In Oracle 11g, PL/SQL native compilation requires no C compiler, no DBA intervention and is fully supported in a RAC environment. By setting the PLSQL_CODE_TYPE to a value of NATIVE, rather than the default value of INTERPRETED, code is compiled directly to machine code and stored in the SYSTEM tablespace. When the code is called, it is loaded into shared memory, making it accessible for all sessions in that instance. The %_PLSQL_OBJECT_SETTINGS views include the current PLSQL_CODE_TYPE setting for each PL/SQL object.

Remember, native compilation will improve the speed of procedure code, but has no effect on the performance of SQL. When code performs lots of mathematical operations, like the SIMPLE_INTEGER example, native compilation can produce considerable performance improvements. If code is predominantly performing SQL, little improvement will be noticed.

CREATE OR REPLACE PROCEDURE test_speed AS
  v_number  NUMBER;
BEGIN
  FOR i IN 1 .. 10000000 LOOP
    v_number := i / 1000;
  END LOOP;
END;
/

SQL> show parameter PLSQL_CODE_TYPE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_code_type                      string      INTERPRETED

SQL> SET TIMING ON
SQL> EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.74
SQL> EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.74

SQL> alter system set PLSQL_CODE_TYPE='NATIVE' SCOPE=SPFILE;

System altered.

SQL> shutdown immediate
STARTUP
Database closed.
Database dismounted.

ORACLE instance shut down.
SQL>
ORACLE instance started.

Total System Global Area 2781020160 bytes
Fixed Size                  2251456 bytes
Variable Size            1711277376 bytes
Database Buffers         1061158912 bytes
Redo Buffers                6332416 bytes
Database mounted.

Database opened.
SQL> SQL> SQL> SQL>
SQL>
SQL>
SQL> SET TIMING ON
SQL> EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.72
SQL>
SQL> EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.73
SQL>
SQL> CREATE OR REPLACE PROCEDURE test_speed AS
  2    v_number  NUMBER;
  3  BEGIN
  FOR i IN 1 .. 10000000 LOOP
    v_number := i / 1000;
  END LOOP;
  4    5    6    7  END;
  8  /

Procedure created.

Elapsed: 00:00:00.15
SQL> SET TIMING ON
SQL> EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.51
SQL>
SQL> EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.51
SQL>
SQL> EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.51
SQL>
SQL> EXEC test_speed;

PL/SQL procedure successfully completed.

Elapsed: 00:00:01.51
SQL>
SQL>

🙂

http://www.dba-oracle.com/t_compiled_pl_sql.htm
http://www.oracle-base.com/articles/10g/plsql-enhancements-10g.php#plsql_native_compilation

SQL Reports

Test-1 compute the sum of a column in Oracle SQL?

SQL> create table testsumrpt
2 (name varchar2(25),
3 mark number);

Table created.

SQL> insert into testsumrpt
2 values(‘&1’,&2
3 );
Enter value for 1: Shiv
Enter value for 2: 100
old 2: values(‘&1’,&2
new 2: values(‘Shiv’,100

1 row created.

SQL> /
Enter value for 1: Hitesh
Enter value for 2: 50
old 2: values(‘&1’,&2
new 2: values(‘Hitesh’,50

1 row created.

SQL> /
Enter value for 1: Kalpesh
Enter value for 2: 98
old 2: values(‘&1’,&2
new 2: values(‘Kalpesh’,98

1 row created.

SQL> commit
2 /

Commit complete.

SQL> select * from testsumrpt;

NAME MARK
————————- ———-
Shiv 100
Hitesh 50
Kalpesh 98

SQL> COLUMN dummy NOPRINT
SQL> COMPUTE SUM OF "mark" ON REPORT
SQL> BREAK ON REPORT
SQL> select * from testsumrpt;

NAME                           MARK
------------------------- ----------
Shiv                        100
Hitesh                       50
Kalpesh                      98
                          ----------
sum                         248

Working with Oracle Timestamp

SQL> SELECT TIMESTAMP,USAGETIME,TIMESTAMP - numtodsinterval(USAGETIME,'second') from TBLCDR WHERE ROWNUM <= 10;

TIMESTAMP                                                                   USAGETIME                                                   TIMESTAMP-NUMTODSINTERVAL(USAGETIME,'SECOND')
--------------------------------------------------------------------------- ---------------------------------------------------------------- ----------------------------------------
18-MAY-14 10.09.52.778000 AM                                                20                                                          18-MAY-14 10.09.32.778000000 AM
20-MAY-14 10.10.12.778000 AM                                                30                                                          20-MAY-14 10.09.42.778000000 AM
20-MAY-14 10.10.42.778000 AM                                                20                                                          20-MAY-14 10.10.22.778000000 AM
20-MAY-14 10.11.02.778000 AM                                                20                                                          20-MAY-14 10.10.42.778000000 AM
20-MAY-14 10.09.52.778000 AM                                                20                                                          20-MAY-14 10.09.32.778000000 AM
20-MAY-14 10.10.32.778000 AM                                                40                                                          20-MAY-14 10.09.52.778000000 AM
20-MAY-14 10.10.47.778000 AM                                                15                                                          20-MAY-14 10.10.32.778000000 AM
20-MAY-14 10.10.55.778000 AM                                                8                                                           20-MAY-14 10.10.47.778000000 AM
20-MAY-14 10.11.33.778000 AM                                                28                                                          20-MAY-14 10.11.05.778000000 AM
20-MAY-14 10.11.53.778000 AM                                                20                                                          20-MAY-14 10.11.33.778000000 AM

10 rows selected.
SQL> SELECT TIMESTAMP,USAGETIME,to_char(timestamp - (usagetime/86400),'DD-MON-YY HH24-MI-SS') FROM TBLCDR WHERE ROWNUM <=10;

TIMESTAMP                                                                   USAGETIME                                                   TO_CHAR(TIMESTAMP-(USAGETIM
--------------------------------------------------------------------------- ---------------------------------------------------------------- ---------------------------
18-MAY-14 10.09.52.778000 AM                                                20                                                          18-MAY-14 10-09-32
20-MAY-14 10.10.12.778000 AM                                                30                                                          20-MAY-14 10-09-42
20-MAY-14 10.10.42.778000 AM                                                20                                                          20-MAY-14 10-10-22
20-MAY-14 10.11.02.778000 AM                                                20                                                          20-MAY-14 10-10-42
20-MAY-14 10.09.52.778000 AM                                                20                                                          20-MAY-14 10-09-32
20-MAY-14 10.10.32.778000 AM                                                40                                                          20-MAY-14 10-09-52
20-MAY-14 10.10.47.778000 AM                                                15                                                          20-MAY-14 10-10-32
20-MAY-14 10.10.55.778000 AM                                                8                                                           20-MAY-14 10-10-47
20-MAY-14 10.11.33.778000 AM                                                28                                                          20-MAY-14 10-11-05
20-MAY-14 10.11.53.778000 AM                                                20                                                          20-MAY-14 10-11-33

http://docs.oracle.com/cd/B12037_01/server.101/b10759/functions093.htm

RT Report

http://www.bestpractical.com/rt/

—Eva
SELECT * FROM tickets N
WHERE EXISTS
(SELECT * FROM TRANSACTIONS T
WHERE T.objectid = N.ID
and t.newvalue IN (‘closed’,’resolved’,’922′) or t.field <> ‘Queue’)
and N.queue = 81
and N.CREATED >= ’01-JAN-2014′;

—Level1
SELECT * FROM tickets N
WHERE EXISTS
(SELECT * FROM TRANSACTIONS T
WHERE T.objectid = N.ID
and t.newvalue IN (‘closed’,’resolved’)
and t.field <> ‘Queue’)
and n.queue = 81
and N.CREATED >= ’01-JAN-2014′

–Level1-Level2
SELECT * FROM tickets N
WHERE EXISTS
(SELECT * FROM TRANSACTIONS T
WHERE T.objectid = N.ID
and ( t.newvalue IN (‘closed’,’resolved’,’81’,’82’) or t.oldvalue IN (’81’,’82’))
and t.field IN (‘Status’,’Queue’))
and N.queue in (81,82)
and N.CREATED >= ’01-JAN-2014′;

FORALL & BULK COLLECT

The FORALL syntax allows us to bind the contents of a collection to a single DML statement, allowing the DML to be run for each row in the collection without requiring a context switch each time. To test bulk binds using records we first create a test table.

SET SERVEROUTPUT ON
DECLARE
TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

l_tab t_forall_test_tab := t_forall_test_tab();
l_start NUMBER;
l_size NUMBER := 2000;
BEGIN
-- Populate collection.
FOR i IN 1 .. l_size LOOP
l_tab.extend;

l_tab(l_tab.last).id := i;
l_tab(l_tab.last).code := TO_CHAR(i);
l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
END LOOP;

EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';

-- Time regular inserts.
l_start := DBMS_UTILITY.get_time;

FOR i IN l_tab.first .. l_tab.last LOOP
INSERT INTO forall_test (id, code, description)
VALUES (l_tab(i).id, l_tab(i).code, l_tab(i).description);
END LOOP;

DBMS_OUTPUT.put_line('Normal Inserts: ' ||
(DBMS_UTILITY.get_time - l_start));

EXECUTE IMMEDIATE 'TRUNCATE TABLE forall_test';

-- Time bulk inserts.
l_start := DBMS_UTILITY.get_time;

FORALL i IN l_tab.first .. l_tab.last
INSERT INTO forall_test VALUES l_tab(i);

DBMS_OUTPUT.put_line('Bulk Inserts : ' ||
(DBMS_UTILITY.get_time - l_start));

COMMIT;
END;
/
SET SERVEROUTPUT ON
DECLARE
TYPE t_id_tab IS TABLE OF forall_test.id%TYPE;
TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

l_id_tab t_id_tab := t_id_tab();
l_tab t_forall_test_tab := t_forall_test_tab ();
l_start NUMBER;
l_size NUMBER := 2000;
BEGIN
-- Populate collections.
FOR i IN 1 .. l_size LOOP
l_id_tab.extend;
l_tab.extend;

l_id_tab(l_id_tab.last) := i;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).code := TO_CHAR(i);
l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
END LOOP;

-- Time regular updates.
l_start := DBMS_UTILITY.get_time;

FOR i IN l_tab.first .. l_tab.last LOOP
UPDATE forall_test
SET ROW = l_tab(i)
WHERE id = l_tab(i).id;
END LOOP;

DBMS_OUTPUT.put_line('Normal Updates : ' ||
(DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;

-- Time bulk updates.
FORALL i IN l_tab.first .. l_tab.last
UPDATE forall_test
SET ROW = l_tab(i)
WHERE id = l_id_tab(i);

DBMS_OUTPUT.put_line('Bulk Updates : ' ||
(DBMS_UTILITY.get_time - l_start));

COMMIT;
END;
/
SET SERVEROUTPUT ON
DECLARE
TYPE t_id_tab IS TABLE OF forall_test.id%TYPE;
TYPE t_forall_test_tab IS TABLE OF forall_test%ROWTYPE;

l_id_tab t_id_tab := t_id_tab();
l_tab t_forall_test_tab := t_forall_test_tab ();
l_start NUMBER;
l_size NUMBER := 2000;
BEGIN
-- Populate collections.
FOR i IN 1 .. l_size LOOP
l_id_tab.extend;
l_tab.extend;

l_id_tab(l_id_tab.last) := i;
l_tab(l_tab.last).id := i;
l_tab(l_tab.last).code := TO_CHAR(i);
l_tab(l_tab.last).description := 'Description: ' || TO_CHAR(i);
END LOOP;

-- Time regular updates.
l_start := DBMS_UTILITY.get_time;

FOR i IN l_tab.first .. l_tab.last LOOP
DELETE forall_test
WHERE id = l_tab(i).id;
END LOOP;

DBMS_OUTPUT.put_line('Normal Delete : ' ||
(DBMS_UTILITY.get_time - l_start));

l_start := DBMS_UTILITY.get_time;

-- Time bulk updates.
FORALL i IN l_tab.first .. l_tab.last
DELETE forall_test
WHERE id = l_id_tab(i);

DBMS_OUTPUT.put_line('Bulk Deletes : ' ||
(DBMS_UTILITY.get_time - l_start));

COMMIT;
END;
/

From Oracle 10g onward, the optimizing PL/SQL compiler converts cursor FOR LOOPs into BULK COLLECTs with an array size of 100. The following example compares the speed of a regular cursor FOR LOOP with BULK COLLECTs using varying array sizes.

http://www.oracle-base.com/articles/9i/bulk-binds-and-record-processing-9i.php
http://www.oracle-base.com/articles/11g/plsql-new-features-and-enhancements-11gr1.php#forall_restriction_removed
http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html
http://www.oracle.com/technetwork/issue-archive/2008/08-mar/o28plsql-095155.html

DELETE Dublicate Records

1. General Method

  SELECT COUNT(1) FROM (
  SELECT USERID,aggregatekey,COUNT(1) FROM MYUSAGESUMMARY
  GROUP BY USERID, aggregatekey HAVING COUNT(1) > 1);
DELETE FROM MYUSAGESUMMARY
WHERE ROWID NOT IN (SELECT   MIN (ROWID)
                          FROM MYUSAGESUMMARY
                          GROUP BY USERID,AGGREGATEKEY);

2. Advanced Customized Method

SELECT COUNT(1) FROM (
SELECT A.USERID,A.aggregatekey,min(B.TOTALOCTETS) MIN_TOTALOCTETS,max(B.TOTALOCTETS) MAX_TOTALOCTETS FROM (
SELECT USERID,aggregatekey,COUNT(1) FROM MYUSAGESUMMARY GROUP BY USERID, aggregatekey HAVING COUNT(1) > 1) A, MYUSAGESUMMARY B
WHERE A.USERID = B.USERID
AND A.aggregatekey = B.aggregatekey
GROUP BY A.USERID, A.aggregatekey
having min(B.TOTALOCTETS) <> max(B.TOTALOCTETS));
  ---Max TOTOAL
  SET SERVEROUTPUT ON
  BEGIN
  FOR REC IN (
        SELECT A.USERID,A.aggregatekey,min(B.TOTALOCTETS) MIN_TOTALOCTETS,max(B.TOTALOCTETS) MAX_TOTALOCTETS
        FROM
           (SELECT USERID,aggregatekey,COUNT(1) FROM MYUSAGESUMMARY
            GROUP BY USERID, aggregatekey HAVING COUNT(1) >1 1) A,MYUSAGESUMMARY B
            WHERE A.USERID = B.USERID
            AND A.aggregatekey = B.aggregatekey
            GROUP BY A.USERID, A.aggregatekey
            having min(B.TOTALOCTETS) <> max(B.TOTALOCTETS)
            )
LOOP
DBMS_OUTPUT.PUT_LINE('USERID==>'||REC.USERID||' MIN_T ==> '||REC.MIN_TOTALOCTETS||' MAX_T ==>'||REC.MAX_TOTALOCTETS);

DELETE FROM MYUSAGESUMMARY
WHERE USERID = REC.USERID
AND aggregatekey = REC.aggregatekey
AND TOTALOCTETS = REC.MIN_TOTALOCTETS;

END LOOP;

END;
/

Note:-  check the duplicate record in same session and confirm and commit it.
http://www.oratable.com/duplicate-rows-query/
🙂

Automatic Daily – Interval Partitioned Table Management

Automatic Daily – Interval partitioned table management

spool APP_DBC_PARTITION-TBLCDR_v1.log

--create tablespaces for TBLCDR
DEFINE db_datafile="&&dbf_storage_directory"
prompt &db_datafile

CREATE TABLESPACE tbs_cdr_1   DATAFILE '&&db_datafile/tbs_cdr_1.dbf'  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_2   DATAFILE '&&db_datafile/tbs_cdr_2.dbf'  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_3   DATAFILE '&&db_datafile/tbs_cdr_3.dbf'  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_4   DATAFILE '&&db_datafile/tbs_cdr_4.dbf'  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_5   DATAFILE '&&db_datafile/tbs_cdr_5.dbf'  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_6   DATAFILE '&&db_datafile/tbs_cdr_6.dbf'  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_7   DATAFILE '&&db_datafile/tbs_cdr_7.dbf'  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_8   DATAFILE '&&db_datafile/tbs_cdr_8.dbf'  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_9   DATAFILE '&&db_datafile/tbs_cdr_9.dbf'  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_10  DATAFILE '&&db_datafile/tbs_cdr_10.dbf' size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_11  DATAFILE '&&db_datafile/tbs_cdr_11.dbf' size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_12  DATAFILE '&&db_datafile/tbs_cdr_12.dbf' size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_13  DATAFILE '&&db_datafile/tbs_cdr_13.dbf' size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_14  DATAFILE '&&db_datafile/tbs_cdr_14.dbf' size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_cdr_15  DATAFILE '&&db_datafile/tbs_cdr_15.dbf' size 250M AUTOEXTEND ON;

CREATE TABLESPACE tbs_idxcdr  DATAFILE '&&db_datafile/tbs_idxcdr.dbf' size 250M AUTOEXTEND ON;

connect &&username/&&password

set long 10000
set pagesize 0
select dbms_metadata.get_dependent_ddl('INDEX','TBLCDR') from duAL;

SELECT DBMS_METADATA.GET_DDL('TABLE','TBLCDR') FROM DUAL;

RENAME TBLCDR TO OLD_TBLCDR;

CREATE TABLE TBLCDR
  (
    CDRID            NUMBER(20,0),
    SESSIONID        VARCHAR2(100 BYTE),
    USERNAME         VARCHAR2(264 BYTE),
    FRAMMEDIPADDRESS VARCHAR2(32 BYTE),
    GATEWAYADDRESS   VARCHAR2(32 BYTE),
    REQUESTTYPE      VARCHAR2(30 BYTE),
    SERVICETYPE      VARCHAR2(30 BYTE),
    MONITORINGKEY    VARCHAR2(64 BYTE),
    INPUTOCTETS      NUMBER(32,0),
    OUTPUTOCTETS     NUMBER(32,0),
    TOTALOCTETS      NUMBER(32,0),
    USAGETIME        VARCHAR2(64 BYTE),
    PARAM1           VARCHAR2(64 BYTE),
    PARAM2           VARCHAR2(64 BYTE),
    PARAM3           VARCHAR2(64 BYTE),
    PARAM4           VARCHAR2(64 BYTE),
    PARAM5           VARCHAR2(64 BYTE),
    CREATEDATE       TIMESTAMP (6),
    LASTMODIFIEDDATE TIMESTAMP (6),
    TIMESTAMP        TIMESTAMP (6),
    AGGREGATEKEY  VARCHAR2(128 BYTE),
    IMSI          VARCHAR2(100 BYTE),
    APN           VARCHAR2(25 BYTE),
    LAC           VARCHAR2(5 BYTE),
    USEREQUIPMENT VARCHAR2(25 BYTE)
  )
PARTITION BY RANGE (CREATEDATE)
INTERVAL (NUMTODSINTERVAL(1,'day')) store in (tbs_cdr_1,tbs_cdr_2,tbs_cdr_3,tbs_cdr_4,tbs_cdr_5,tbs_cdr_6,tbs_cdr_7,tbs_cdr_8,tbs_cdr_9,tbs_cdr_10,tbs_cdr_11,tbs_cdr_12,tbs_cdr_13,tbs_cdr_14,tbs_cdr_15)
(PARTITION p_first VALUES LESS THAN (TO_DATE('19-12-2013', 'DD-MM-YYYY')))
NOLOGGING;

 CREATE UNIQUE INDEX IDX_UNQ_CDR ON TBLCDR(CDRID)
 TABLESPACE tbs_idxcdr
 NOLOGGING;

 ALTER TABLE TBLCDR INITRANS 200;

 ALTER INDEX IDX_UNQ_CDR INITRANS 200;

 ALTER SEQUENCE SEQ_CDR CACHE 2000;

 ALTER TABLE TBLCDR ENABLE ROW MOVEMENT;

 set long 10000
 set pagesize 0
 select dbms_metadata.get_dependent_ddl('INDEX','TBLCDR') from DUAL;

 SELECT DBMS_METADATA.GET_DDL('TABLE','TBLCDR') FROM DUAL;

--Create Procedure for TRUNCATE PARTITION which are older then 6 months
-- TRUNCATE PARTITION which are older then 6 months (MANUAL OAM)

-- ALTER TABLE TBLCDR TRUNCATE PARTITION 'NAME_OF_PARTITION' UPDATE INDEXES;

spool off;

Automatic Maintenance

create or replace procedure sp_netvertex_data_purge_cycle(t_name varchar2)
as
    v_date     DATE;
    v_date_cut DATE := trunc(SYSDATE) - 15;
    --v_date_cut DATE := add_months(trunc(SYSDATE, 'mm'), purge_month_interval);
BEGIN

/*

-- Automatic Daily Interval Partition Configured on below modules
    1.) TBLCDR
    2.) TBLEDR
    3.) TBLMSESSIONUSAGECDR

-- This Code is for Automatic data purging cycle configuration to drop the specified partitin
   which is older than 3 or 6 months

-- How to use this procedure

 EXEC sp_netvertex_data_purge_cycle('TBLEDR',1);
 EXEC sp_netvertex_data_purge_cycle('TBLCDR',1);
 EXEC sp_netvertex_data_purge_cycle('TBLSESSIONUSAGECDR',1);

 we can also configure the database scheduler and configured in pick off hour for automize the maintainanace

*/

    FOR r IN (SELECT table_name,high_value,interval,partition_name FROM   user_tab_partitions tp WHERE  tp.table_name = t_name)
    LOOP
        EXECUTE IMMEDIATE 'SELECT ' || r.high_value || ' from dual' INTO v_date;
        IF v_date < v_date_cut
        THEN
            IF r.interval = 'NO'
            THEN
                EXECUTE IMMEDIATE 'alter table ' ||  r.table_name || ' truncate partition ' ||r.partition_name || ' UPDATE INDEXES';

                --Debug Command
                DBMS_OUTPUT.PUT_LINE('alter table ' || r.table_name || ' truncate partition ' ||r.partition_name|| ' UPDATE INDEXES');
            ELSE
                EXECUTE IMMEDIATE 'alter table ' ||  r.table_name || ' drop partition ' ||r.partition_name || ' UPDATE INDEXES';

                --Debug Command
			          DBMS_OUTPUT.PUT_LINE('alter table ' || r.table_name || ' drop partition ' ||r.partition_name|| ' UPDATE INDEXES');
            END IF;
        END IF;
    END LOOP;

END;

ALTER TABLE TBLEDR ADD PARTITION “P2 “VALUES LESS THAN (TIMESTAMP’ 2013-12-16 00:00:00′)
Error report –
SQL Error: ORA-14760: ADD PARTITION is not permitted on Interval partitioned objects

You can not drop the oldest, root, partition in an interval partitioned table.

ORA-14758: Last partition in the range section cannot be dropped
https://forums.oracle.com/message/10220371

APP TABLE PARTITION – Tuning

–create tablespaces for TBLMSESSIONUSAGECDR
DEFINE db_datafile=”&&dbf_storage_directory”
prompt &db_datafile

CREATE TABLESPACE tbs_usagecdr_1   DATAFILE ‘/ORADATA2/tbs_usagecdr_1.dbf’  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_2   DATAFILE ‘/ORADATA2/tbs_usagecdr_2.dbf’  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_3   DATAFILE ‘/ORADATA2/tbs_usagecdr_3.dbf’  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_4   DATAFILE ‘/ORADATA2/tbs_usagecdr_4.dbf’  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_5   DATAFILE ‘/ORADATA2/tbs_usagecdr_5.dbf’  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_6   DATAFILE ‘/ORADATA2/tbs_usagecdr_6.dbf’  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_7   DATAFILE ‘/ORADATA2/tbs_usagecdr_7.dbf’  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_8   DATAFILE ‘/ORADATA2/tbs_usagecdr_8.dbf’  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_9   DATAFILE ‘/ORADATA2/tbs_usagecdr_9.dbf’  size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_10  DATAFILE ‘/ORADATA2/tbs_usagecdr_10.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_11  DATAFILE ‘/ORADATA2/tbs_usagecdr_11.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_usagecdr_12  DATAFILE ‘/ORADATA2/tbs_usagecdr_12.dbf’ size 250M AUTOEXTEND ON;

connect &&username/&&password

–Keep history of TBLMSESSIONUSAGESUMMARY without depend on year so every
–new calendar month same partition will use next year as well

–Backup Existing Table
SELECT COUNT(1) FROM TBLMSESSIONUSAGECDR;

RENAME TBLMSESSIONUSAGECDR TO TBLMSESSIONUSAGECDR_BCK031213;

CREATE TABLE TBLMSESSIONUSAGECDR
(CREATE_DATE        TIMESTAMP(6),
USERID             VARCHAR2(50),
MONITORINGKEY       VARCHAR2(100) NOT NULL ENABLE,
USAGETIME           NUMBER(20,0) DEFAULT 0 NOT NULL ENABLE,
UPLOADOCTETS        NUMBER(20,0) DEFAULT 0 NOT NULL ENABLE,
DOWNLOADOCTETS      NUMBER(20,0) DEFAULT 0 NOT NULL ENABLE,
TOTALOCTETS         NUMBER(20,0) DEFAULT 0 NOT NULL ENABLE,
AGGREGATEKEY        VARCHAR2(128),
LASTUPDATETIME      TIMESTAMP (6),
METERINGLEVEL       VARCHAR2(50),
CUI                 VARCHAR2(50),
V_CREATE_DATE NUMBER GENERATED ALWAYS AS (TO_NUMBER(TO_CHAR(CREATE_DATE,’MM’))) VIRTUAL VISIBLE
)
PARTITION BY LIST (V_CREATE_DATE)
(
PARTITION p_1 VALUES (1)  tablespace tbs_usagecdr_1,
PARTITION p_2 VALUES (2)  tablespace tbs_usagecdr_2,
PARTITION p_3 VALUES (3)  tablespace tbs_usagecdr_3,
PARTITION p_4 VALUES (4)  tablespace tbs_usagecdr_4,
PARTITION p_5 VALUES (5)  tablespace tbs_usagecdr_5,
PARTITION p_6 VALUES (6)  tablespace tbs_usagecdr_6,
PARTITION p_7 VALUES (7)  tablespace tbs_usagecdr_7,
PARTITION p_8 VALUES (8)  tablespace tbs_usagecdr_8,
PARTITION p_9 VALUES (9)  tablespace tbs_usagecdr_9,
PARTITION p_10 VALUES(10) tablespace  tbs_usagecdr_10,
PARTITION p_11 VALUES(11) tablespace  tbs_usagecdr_11,
PARTITION p_12 VALUES(12) tablespace  tbs_usagecdr_12
);

ALTER TABLE TBLMSESSIONUSAGECDR     INITRANS 100;

ALTER TABLE TBLMSESSIONUSAGECDR ENABLE ROW MOVEMENT;

–Create Procedure for TRUNCATE PARTITION which are older then 6 months
create or replace PROCEDURE PROC_NVUSAGECDR_MAINTAINANCE
AS
p_name varchar2(100);
BEGIN

/*
EliteCSM NetVertex Module
EliteCore Technologies Pvt. Ltd.
*/

FOR REC IN (SELECT distinct ‘p_’||TO_NUMBER(TO_CHAR(CREATE_DATE,’MM’))
p_name
FROM TBLMSESSIONUSAGECDR
WHERE CREATE_DATE + 185  < SYSDATE)
LOOP
EXECUTE IMMEDIATE ‘ALTER TABLE TBLMSESSIONUSAGECDR TRUNCATE PARTITION ‘ || REC.p_name || ‘ UPDATE INDEXES’;

DBMS_OUTPUT.PUT_LINE (‘ALTER TABLE TBLMSESSIONUSAGECDR TRUNCATE PARTITION ‘ || REC.p_name || ‘ UPDATE INDEXES; ——-> successfully completed’);

END LOOP;
END;
/

— Run on the next to last day of every month:
BEGIN
— Job defined entirely by the CREATE JOB procedure.
DBMS_SCHEDULER.create_job (
job_name => ‘JOB_USAGECDR_MAINTAINANCE’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN PROC_NVUSAGECDR_MAINTAINANCE(); END;’,
START_DATE => SYSTIMESTAMP,
repeat_interval => ‘freq=yearly;bymonth=6;bymonthday=30;byhour=7;byminute=5;’,
end_date => NULL,
enabled => TRUE,
comments => ‘Job defined entirely by the CREATE JOB procedure.’);
END;
/

begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => ‘”NETVERTEXP”‘,
tabname => ‘”TBLMSESSIONUSAGECDR”‘,
estimate_percent => 1
);
end;
/

–create tablespaces for TBLEDR
DEFINE db_datafile=”&&dbf_storage_directory”
prompt &db_datafile

CREATE TABLESPACE tbs_edr_1   DATAFILE ‘/ORADATA1/tbs_edr_1.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_2   DATAFILE ‘/ORADATA1/tbs_edr_2.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_3   DATAFILE ‘/ORADATA1/tbs_edr_3.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_4   DATAFILE ‘/ORADATA1/tbs_edr_4.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_5   DATAFILE ‘/ORADATA1/tbs_edr_5.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_6   DATAFILE ‘/ORADATA1/tbs_edr_6.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_7   DATAFILE ‘/ORADATA1/tbs_edr_7.dbf’ size 250M AUTOEXTEND ON;
CREATE TABLESPACE tbs_edr_8   DATAFILE ‘/ORADATA1/tbs_edr_8.dbf’ size 250M AUTOEXTEND ON;

connect &&username/&&password

–Keep history of TBLEDR without depend on year so every
–new calendar month same partition will use next year as well

–Backup Existing Table
SELECT COUNT(1) FROM TBLEDR;
RENAME TBLEDR TO TBLEDR_BCK03122013;

CREATE TABLE TBLEDR
(EDRID       NUMBER(20,0),
SESSIONID   VARCHAR2(100),
USERNAME    VARCHAR2(264),
EXPIREDRULE VARCHAR2(100),
NEWRULE     VARCHAR2(100),
OLDBW       VARCHAR2(32),
NEWBW       VARCHAR2(32),
PARAM1      VARCHAR2(64),
PARAM2      VARCHAR2(64),
CREATEDATE  TIMESTAMP (6),
LASTMODIFIEDDATE TIMESTAMP (6),
TIMESTAMP        TIMESTAMP (6),
V_CREATEDATE       NUMBER
GENERATED ALWAYS AS
(
TO_NUMBER(TO_CHAR(CREATEDATE,’DD’))
) VIRTUAL
)PARTITION BY LIST (V_CREATEDATE)
(
PARTITION p_1 VALUES  (1)   tablespace  tbs_edr_1,
PARTITION p_2 VALUES  (2)   tablespace  tbs_edr_2,
PARTITION p_3 VALUES  (3)   tablespace  tbs_edr_3,
PARTITION p_4 VALUES  (4)   tablespace  tbs_edr_4,
PARTITION p_5 VALUES  (5)   tablespace  tbs_edr_5,
PARTITION p_6 VALUES  (6)   tablespace  tbs_edr_6,
PARTITION p_7 VALUES  (7)   tablespace  tbs_edr_7,
PARTITION p_8 VALUES  (8)   tablespace  tbs_edr_8,
PARTITION p_9 VALUES  (9)   tablespace  tbs_edr_1,
PARTITION p_10 VALUES (10)   tablespace tbs_edr_2,
PARTITION p_11 VALUES (11)   tablespace tbs_edr_3,
PARTITION p_12 VALUES (12)   tablespace tbs_edr_4,
PARTITION p_13 VALUES (13)   tablespace tbs_edr_5,
PARTITION p_14 VALUES (14)   tablespace tbs_edr_6,
PARTITION p_15 VALUES (15)   tablespace tbs_edr_7,
PARTITION p_16 VALUES (16)   tablespace tbs_edr_8,
PARTITION p_17 VALUES (17)   tablespace tbs_edr_1,
PARTITION p_18 VALUES (18)   tablespace tbs_edr_2,
PARTITION p_19 VALUES (19)   tablespace tbs_edr_3,
PARTITION p_20 VALUES (20)   tablespace tbs_edr_4,
PARTITION p_21 VALUES (21)   tablespace tbs_edr_5,
PARTITION p_22 VALUES (22)   tablespace tbs_edr_6,
PARTITION p_23 VALUES (23)   tablespace tbs_edr_7,
PARTITION p_24 VALUES (24)   tablespace tbs_edr_8,
PARTITION p_25 VALUES (25)   tablespace tbs_edr_1,
PARTITION p_26 VALUES (26)   tablespace tbs_edr_2,
PARTITION p_27 VALUES (27)   tablespace tbs_edr_3,
PARTITION p_28 VALUES (28)   tablespace tbs_edr_4,
PARTITION p_29 VALUES (29)   tablespace tbs_edr_5,
PARTITION p_30 VALUES (30)   tablespace tbs_edr_6,
PARTITION p_31 VALUES (31)   tablespace tbs_edr_7
);

ALTER TABLE TBLEDR
ADD CONSTRAINT “PK_PEDR” PRIMARY KEY (“EDRID”);

ALTER TABLE TBLEDR INITRANS 100;

ALTER INDEX PK_PEDR INITRANS 100;

ALTER TABLE TBLEDR ENABLE ROW MOVEMENT;

–Create Procedure for TRUNCATE PARTITION which are older then 6 months
create or replace PROCEDURE PROC_TBLEDR_MAINTAINANCE
AS
p_name varchar2(100);
BEGIN

/*
EliteCSM NetVertex Module
EliteCore Technologies Pvt. Ltd.
*/

FOR REC IN (SELECT distinct ‘p_’||TO_NUMBER(TO_CHAR(CREATEDATE,’DD’)) p_name FROM TBLEDR WHERE CREATEDATE + 15 < SYSDATE)
LOOP

EXECUTE IMMEDIATE ‘ALTER TABLE TBLEDR TRUNCATE PARTITION ‘ || REC.p_name || ‘ UPDATE INDEXES’;

DBMS_OUTPUT.PUT_LINE (‘ALTER TABLE TBLEDR TRUNCATE PARTITION ‘ || REC.p_name || ‘ UPDATE INDEXES; ——-> successfully completed’);

END LOOP;

END;
/

— Run on the next to last day of every month:
BEGIN
— Job defined entirely by the CREATE JOB procedure.
DBMS_SCHEDULER.create_job (
job_name => ‘JOB_TBLEDR_MAINTAINANCE’,
job_type => ‘PLSQL_BLOCK’,
job_action => ‘BEGIN PROC_TBLEDR_MAINTAINANCE(); END;’,
START_DATE => SYSTIMESTAMP,
repeat_interval => ‘freq=monthly;bymonthday=14,28;byhour=6;byminute=33;’,
end_date => NULL,
enabled => TRUE,
comments => ‘Job defined entirely by the CREATE JOB procedure.’);
END;
/

begin
DBMS_STATS.GATHER_TABLE_STATS (
ownname => ‘”NETVERTEXP”‘,
tabname => ‘”TBLEDR”‘,
estimate_percent => 1
);
end;
/

🙂

😦

Insert data only single table space so we should improve insert performance via taking advantage of SAN storage

specify multiple tablespaces

Data Management of Table MAXVALUE partition

–Data Verification on PRD

SELECT MAX(CREATE_DATE) FROM TBLEMPSTATISTICS PARTITION (P2013_10);

SELECT MAX(CREATE_DATE) FROM TBLEMPSTATISTICS PARTITION (PMAXDATE);

SQL> SQL>  set colsep "|"
SQL>  set linesize 32767
SQL>  set pagesize 10000
SQL>  COLUMN table_name     FORMAT A20
SQL>  COLUMN partition_name FORMAT A20
SQL>  COLUMN high_value     FORMAT 999
SQL>  COLUMN num_rows       FORMAT 999999999
SQL>
SQL>  SELECT table_name,
  2          partition_name,
  3          high_value,
  4          num_rows
  5   FROM   user_tab_partitions
  6   WHERE table_name = 'TBLEMPSTATISTICS'
  7   ORDER BY table_name, partition_name;

TABLE_NAME          |PARTITION_NAME      |HIGH_VALUE                                                                      |  NUM_ROWS
--------------------|--------------------|--------------------------------------------------------------------------------|----------
TBLEMPSTATISTICS   |P2013_10            |TIMESTAMP' 2013-11-01 00:00:00'                                                 |   8308323
TBLEMPSTATISTICS   |P2013_11            |TIMESTAMP' 2013-12-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2013_12            |TIMESTAMP' 2014-01-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2013_5             |TIMESTAMP' 2013-06-01 00:00:00'                                                 |   6116188
TBLEMPSTATISTICS   |P2013_6             |TIMESTAMP' 2013-07-01 00:00:00'                                                 |  23133572
TBLEMPSTATISTICS   |P2013_7             |TIMESTAMP' 2013-08-01 00:00:00'                                                 |   8822588
TBLEMPSTATISTICS   |P2013_8             |TIMESTAMP' 2013-09-01 00:00:00'                                                 |   9575553
TBLEMPSTATISTICS   |P2013_9             |TIMESTAMP' 2013-10-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |PMAXDATE            |MAXVALUE 			                                                 |   4100000

–Temporary shift the data of nov to other table

CREATE TABLE TBLEMPSTATISTICS_P2013_11
AS
SELECT * FROM TBLEMPSTATISTICS PARTITION (PMAXDATE);

–drop the max partition

ALTER TABLE TBLEMPSTATISTICS DROP PARTITION PMAXDATE UPDATE INDEXES;

–add the new partitions for 12 months

ALTER TABLE TBLEMPSTATISTICS ADD PARTITION P2013_11 VALUES LESS THAN (TO_DATE('2013-12-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2013_11;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2013_12  VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2013_12;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_01  VALUES LESS THAN (TO_DATE('2014-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_01;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_02  VALUES LESS THAN (TO_DATE('2014-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_02;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_03  VALUES LESS THAN (TO_DATE('2014-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_03;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_04  VALUES LESS THAN (TO_DATE('2014-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_04;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_05  VALUES LESS THAN (TO_DATE('2014-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_05;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_06  VALUES LESS THAN (TO_DATE('2014-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_06;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_07  VALUES LESS THAN (TO_DATE('2014-08-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_07;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_08  VALUES LESS THAN (TO_DATE('2014-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_08;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_09  VALUES LESS THAN (TO_DATE('2014-10-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_09;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION P2014_10  VALUES LESS THAN (TO_DATE('2014-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS'))
TABLESPACE tbsuserstatistics2014_10;

ALTER TABLE TBLEMPSTATISTICS  ADD PARTITION PMAXDATE  VALUES LESS THAN (MAXVALUE) TABLESPACE tbsuserstatisticsmaxdate;

–OUTPUT================

SQL>  set colsep "|"
SQL>  set linesize 32767
SQL>  set pagesize 10000
SQL>  COLUMN table_name     FORMAT A20
SQL>  COLUMN partition_name FORMAT A20
SQL>  COLUMN high_value     FORMAT 999
SQL>  COLUMN num_rows       FORMAT 999999999
SQL>
SQL>  SELECT table_name,
  2          partition_name,
  3          high_value,
  4          num_rows
  5   FROM   user_tab_partitions
  6   WHERE table_name = 'TBLEMPSTATISTICS'
  7   ORDER BY table_name, partition_name;

TABLE_NAME          |PARTITION_NAME      |HIGH_VALUE                                                                      |  NUM_ROWS
--------------------|--------------------|--------------------------------------------------------------------------------|----------
TBLEMPSTATISTICS   |P2013_10            |TIMESTAMP' 2013-11-01 00:00:00'                                                 |   8308323
TBLEMPSTATISTICS   |P2013_11            |TIMESTAMP' 2013-12-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2013_12            |TIMESTAMP' 2014-01-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2013_5             |TIMESTAMP' 2013-06-01 00:00:00'                                                 |   6116188
TBLEMPSTATISTICS   |P2013_6             |TIMESTAMP' 2013-07-01 00:00:00'                                                 |  23133572
TBLEMPSTATISTICS   |P2013_7             |TIMESTAMP' 2013-08-01 00:00:00'                                                 |   8822588
TBLEMPSTATISTICS   |P2013_8             |TIMESTAMP' 2013-09-01 00:00:00'                                                 |   9575553
TBLEMPSTATISTICS   |P2013_9             |TIMESTAMP' 2013-10-01 00:00:00'                                                 |   8116638
TBLEMPSTATISTICS   |P2014_01            |TIMESTAMP' 2014-02-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2014_02            |TIMESTAMP' 2014-03-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2014_03            |TIMESTAMP' 2014-04-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2014_04            |TIMESTAMP' 2014-05-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2014_05            |TIMESTAMP' 2014-06-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2014_06            |TIMESTAMP' 2014-07-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2014_07            |TIMESTAMP' 2014-08-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2014_08            |TIMESTAMP' 2014-09-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2014_09            |TIMESTAMP' 2014-10-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |P2014_10            |TIMESTAMP' 2014-11-01 00:00:00'                                                 |
TBLEMPSTATISTICS   |PMAXDATE            |MAXVALUE

–Load the data from 1 to 14 NOV to actual table

INSERT INTO TBLEMPSTATISTICS
SELECT * FROM TBLEMPSTATISTICS_P2013_11;

–Clean temporary table

drop table TBLEMPSTATISTICS_P2013_11;

–Check INVALID objects

SELECT OBJECT_NAME FROM USER_OBJECTS
WHERE STATUS != 'VALID';

—cross check prd ddl

set long 10000
set pagesize 0
select dbms_metadata.get_ddl('TABLE','TBLEMPSTATISTICS') from dual;
select dbms_metadata.get_dependent_ddl('INDEX','TBLEMPSTATISTICS') from dual;

exposure on VLDB 🙂