My first step in Writing: 2012

Thursday, April 19, 2012

Oracle Nested Tables Example

http://www.joegarrepy.com/oracle_nestedtables.htm

/*
-- ****************************************************************** --
9/30/2003 NESTED TABLES Create Script (Joe Garrepy) VERSION 1.0
-- ****************************************************************** --
The following script creates several TYPE objects and one TABLE object
to illustrate the creation of NESTED tables. The script also shows
how to insert rows into the Regular table and also work with percision
on the nested tables (all DML statements). It also contains SQL code
to bring back values from the table.
-- ****************************************************************** --
*/
--> Create Name Column with "ROW" Type and "TABLE" Type
CREATE OR REPLACE TYPE "ROW_NAME"
AS OBJECT
(FIRST VARCHAR2(60),
LAST VARCHAR2(60),
MIDDLE VARCHAR2(30),
PREFIX VARCHAR2(10),
SUFFIX VARCHAR2(10),
CREATE_DATE DATE,
CHANGE_DATE DATE,
CREATE_USER VARCHAR2(30),
CHANGE_USER VARCHAR2(30))
/
CREATE OR REPLACE TYPE "TAB_NAME"
AS TABLE OF ROW_NAME
/
--> Create Person Column with "ROW" Type and "TABLE" Type
CREATE OR REPLACE TYPE "ROW_PERSON"
AS OBJECT
(SSN VARCHAR2(9),
GENDER VARCHAR2(1),
BDATE DATE,
ETHNICITY VARCHAR2(2),
MARITAL_STATUS VARCHAR2(2),
HAIR VARCHAR2(10),
EYES VARCHAR2(10),
HEIGHT VARCHAR2(10),
CREATE_DATE DATE,
CHANGE_DATE DATE,
CREATE_USER VARCHAR2(30),
CHANGE_USER VARCHAR2(30))
/
CREATE OR REPLACE TYPE "TAB_PERSON"
AS TABLE OF ROW_PERSON
/
--> Create Employment Column with "ROW" Type and "TABLE" Type
CREATE OR REPLACE TYPE "ROW_EMPLOYMENT"
AS OBJECT
(JOB_TITLE VARCHAR2(60),
START_DATE DATE,
TERMINATED VARCHAR2(1),
END_DATE DATE,
SALARY NUMBER(8,4),
CREATE_DATE DATE,
CHANGE_DATE DATE,
CREATE_USER VARCHAR2(30),
CHANGE_USER VARCHAR2(30)
)
/
CREATE OR REPLACE TYPE "TAB_EMPLOYMENT"
AS TABLE OF ROW_EMPLOYMENT
/
--> Create table with UID and TAB_NAME & TAB_PERSON tables as columns
CREATE TABLE "ENTITY"
( "UIDN" NUMBER(10) NOT NULL,
"NAME" "TAB_NAME" DEFAULT "TAB_NAME"(),
"PERSON" "TAB_PERSON" DEFAULT "TAB_PERSON"(),
"EMPLOYMENT" "TAB_EMPLOYMENT" DEFAULT "TAB_EMPLOYMENT"(),
"DEAD" VARCHAR2(1) DEFAULT 'N',
"DEAD_DATE" DATE,
"CREATE_DATE" DATE DEFAULT SYSDATE NOT NULL ENABLE,
"CHANGE_DATE" DATE,
"CREATE_USER" VARCHAR2(30) DEFAULT USER,
"CHANGE_USER" VARCHAR2(30),
PRIMARY KEY ("UIDN"))
NESTED TABLE "NAME" STORE AS "ENTITY_NAME"
NESTED TABLE "PERSON" STORE AS "ENTITY_PERSON"
NESTED TABLE "EMPLOYMENT" STORE AS "ENTITY_EMPLOYMENT"
/
COMMENT ON TABLE "ENTITY" IS 'Entity Base Table with repeating data as Nested Tables';
COMMENT ON COLUMN "ENTITY"."UIDN" IS 'Unique Identifaction Number/KEY';
COMMENT ON COLUMN "ENTITY"."NAME" IS 'Nested Table Storing Name Data for an entity';
COMMENT ON COLUMN "ENTITY"."PERSON" IS 'Nested Table Storing Person Data for an entity';
COMMENT ON COLUMN "ENTITY"."EMPLOYMENT" IS 'Nested Table Storing Employment related data for an entity';
COMMENT ON COLUMN "ENTITY"."DEAD" IS 'Y if entity is dead else N';
COMMENT ON COLUMN "ENTITY"."DEAD_DATE" IS 'Date of Death';
--> Now lets insert some records
INSERT INTO entity VALUES
(1,TAB_NAME(ROW_NAME('Orion','Pax',NULL,NULL,NULL,SYSDATE,NULL,USER,NULL),
ROW_NAME('Optimus','Prime',NULL,NULL,NULL,SYSDATE,NULL,USER,NULL)),
TAB_PERSON(ROW_PERSON('000000000','M','01-JAN-1985','AB',NULL,NULL,NULL,'10 meters',SYSDATE,NULL,USER,NULL)),
TAB_EMPLOYMENT(ROW_EMPLOYMENT('Autobot Leader','01-JAN-1985',NULL,NULL,1000,SYSDATE,NULL,USER,NULL)),
'N',NULL,SYSDATE,NULL,USER,NULL);
INSERT INTO entity VALUES
(2,TAB_NAME(ROW_NAME('Megatron',NULL,NULL,NULL,NULL,SYSDATE,NULL,USER,NULL)),
TAB_PERSON(ROW_PERSON('000000001','M','01-JAN-1985','DC',NULL,NULL,NULL,'10 meters',SYSDATE,NULL,USER,NULL)),
TAB_EMPLOYMENT(ROW_EMPLOYMENT('Decepticon Leader','01-JAN-1985',NULL,NULL,995,SYSDATE,NULL,USER,NULL)),
'N',NULL,SYSDATE,NULL,USER,NULL);
COMMIT;
--> View what whe have inserted by flatening out the column collections
SELECT src.uidn,
n.*,
p.*,
e.*,
dead,
dead_date,
src.create_date,
src.change_date,
src.create_user,
src.change_user
FROM ENTITY src,
TABLE(src.name) n,
TABLE(src.person) p,
TABLE(src.employment) e;
--> Insert specific records into column of collections
/* Megatron's name changed during Transformers the Movie so lets insert
a new record into the collection for Names.
The first thing that needs to be done is to get the base record from
the entity and the column with the collection that we want to update
table we do so with this statement
INSERT INTO
TABLE(select entity.name
from entity
where entity.uidn = 2)
The next part of the insert involves adding a record to the collection
column
('Galvatron',NULL,NULL,NULL,
NULL,SYSDATE,NULL,USER,NULL)
*/
INSERT INTO
TABLE(select entity.name
from entity
where entity.uidn = 2)
VALUES
('Galvatron',NULL,NULL,NULL,
NULL,SYSDATE,NULL,USER,NULL);
COMMIT;
--> Update specific records inside of column collections
/* We'll use a similiar method to update Megatrons Galvatron's
NAME record to have a last name
*/
UPDATE TABLE(select entity.name
from entity
where entity.uidn = 2)
SET last = 'Decepticon',
change_date = SYSDATE,
change_user = USER
WHERE first = 'Galvatron';
COMMIT;
--> Delete specific records inside of column collections
/* Eventually Galvatron's name returns to being Megatron,
so lets delete out the Galvatron record from the NAME
collection
*/
DELETE TABLE(select entity.name
from entity
where entity.uidn = 2)
WHERE first = 'Galvatron';
COMMIT;

Tuesday, April 17, 2012

Oracle Pipelined table functions

http://www.oracle-base.com/articles/misc/pipelined-table-functions.php

http://www.joegarrepy.com/pipelined_function.htm
http://www.akadia.com/services/ora_pipe_functions.html

Overview

Basically, when you would like a PLSQL (or java or c) routine to be the «source»
of data -- instead of a table -- you would use a pipelined function.
PIPELINED functions will operate like a table.
A PL/SQL function may be used in a data warehouse database to transform large amounts of data. This might also involve massaging the data in a series of transformations, each performed by different functions. Prior to Oracle Database 9, large transformations required either significant memory overhead, or storing the data in intermediate tables between each stage of the transformation. The loading process caused immense performance degradations in both cases.

Using PL/SQL table functions can significantly lower the over-head of doing such transformations. PL/SQL table functions accept and return multiple rows, delivering them as they are ready rather than all at once, and can be made to execute as parallel operations.

Simple Example - Generating Some Random Data

How could you create six unique random numbers between 1 and 49 with one SQL statement?

We would generate the set of numbers to pick from (see the innermost query that follows); any table with 49 or more records would do it. First the quick-and-dirty solution without a pipelined function.

select r
from (select r
           from (select rownum r
                   from all_objects
                  where rownum < 50)
          order by dbms_random.value)
where rownum <= 6;

         R
----------
        10
         2
        19
        34
        12
        21

That query works by generating the numbers 1 .. 49, using the inline view. We wrap that innermost query as an inline view and sort it by a random value, using DBMS_RANDOM.VALUE. We wrap that result set in yet another inline view and just take the first six rows. If we run that query over and over, we'll get a different set of six rows each time.

This sort of question comes up frequently—maybe not about how to generate a set of six random numbers but rather, "how can we get N rows?" For example, we'd like the inclusive set of all dates between 25-FEB-2004 and 10-MAR-2004. The question becomes how to do this without a "real" table, and the answer lies in Oracle9i/10g with its PIPELINED function capability. We can write a PL/SQL function that will operate like a table. We need to start with a SQL collection type; this describes what the PIPELINED function will return. In this case, we are choosing a table of numbers; the virtual table we are creating will simply return the numbers 1, 2, 3, ... N:

create type array
    as table of number
/

Type created.

Next, we create the actual PIPELINED function. This function will accept an input to limit the number of rows returned. If no input is provided, this function will just keep generating rows for a very long time (so be careful and make sure to use ROWNUM or some other limit in the query itself!). The PIPELINED keyword on line 4 allows this function to work as if it were a table:

create function
gen_numbers(n in number default null)
return array
PIPELINED
as
begin
     for i in 1 .. nvl(n,999999999)
     loop
         pipe row(i);
     end loop;
     return;
end;
/

Function created.

Suppose we needed three rows for something. We can now do that in one of two ways:

select * from TABLE(gen_numbers(3));

COLUMN_VALUE
------------
           1
           2
           3

or

select * from TABLE(gen_numbers)
where rownum <= 3;

COLUMN_VALUE
------------
           1
           2
           3

Now we are ready to re-answer the original question, using the following functionality:

select *
from (
select *
    from (select * from table(gen_numbers(49)))
order by dbms_random.random
)
where rownum <= 6
/

COLUMN_VALUE
------------
          47
          42
          40
          15
          48
          23

We can use this virtual table functionality for many things, such as generating that range of dates:

select to_date('25-feb-2004')+
        column_value-1
from TABLE(gen_numbers(15))
/

TO_DATE('
---------
25-FEB-04
26-FEB-04
27-FEB-04
28-FEB-04
29-FEB-04
01-MAR-04
02-MAR-04
03-MAR-04
04-MAR-04
05-MAR-04
06-MAR-04
07-MAR-04
08-MAR-04
09-MAR-04
10-MAR-04
Note the name of the column we used: COLUMN_VALUE. That is the default name for the column coming back from the PIPELINED function.

Typical Pipelined Example

This are the typical steps to perform when using PL/SQL Table Functions:

The producer function must use the PIPELINED keyword in its declaration.

The producer function must use an OUT parameter that is a record, corresponding to a row in the result set.

Once each output record is completed, it is sent to the consumer function through the use of the PIPE ROW keyword.

The producer function must end with a RETURN statement that does not specify any return value.

The consumer function or SQL statement then must use the TABLE keyword to treat the resulting rows from the PIPELINE function like a regular table.

The first step is to define the format of the rows that are going to be returned. In this case here, we're going to return a INT, DATE followed by a VARCHAR2(25).
CREATE OR REPLACE TYPE myObjectFormat 
AS OBJECT
(
  A   INT,
  B   DATE,
  C   VARCHAR2(25)
)
/
Next a collection type for the type previously defined must be created.

CREATE OR REPLACE TYPE myTableType
AS TABLE OF myObjectFormat
/

Finally, the producer function is packaged in a package. It is a pipelined function as indicated by the keyword pipelined.

CREATE OR REPLACE PACKAGE myDemoPack
AS
      FUNCTION prodFunc RETURN myTableType PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE BODY myDemoPack AS
FUNCTION prodFunc RETURN myTableType PIPELINED IS
BEGIN
FOR i in 1 .. 5
    LOOP
      PIPE ROW (myObjectFormat(i,SYSDATE+i,'Row '||i));
    END LOOP;
    RETURN;
END;
END;
/
Test It:

ALTER SESSION SET NLS_DATE_FORMAT='dd.mm.yyyy';
SELECT * FROM TABLE(myDemoPack.prodFunc());

         A B          C
---------- ---------- ---------
         1 31.05.2004 Row 1
         2 01.06.2004 Row 2
         3 02.06.2004 Row 3
         4 03.06.2004 Row 4
         5 04.06.2004 Row 5

Conclusion

Pipelined functions are useful if there is a need for a data source other than a table in a select statement.

Wednesday, April 11, 2012

SEMI-Joins & Anti-Joins in oracle

http://www.dbspecialists.com/files/presentations/semijoins.html

Wednesday, April 4, 2012

Search condition Case Statement

SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
n_salary EMPLOYEES.SALARY%TYPE;
n_emp_id EMPLOYEES.EMPLOYEE_ID%TYPE := 200;
v_msg VARCHAR(20);
BEGIN
SELECT salary INTO n_salary FROM employees WHERE employee_id = n_emp_id; CASE
WHEN n_salary < 2000 THEN
v_msg := 'Low';
WHEN n_salary >= 2000 AND n_salary <=3000
THEN
v_msg := 'Fair';
WHEN n_salary >= 3000 THEN
v_msg := 'High';
END CASE;

DBMS_OUTPUT.PUT_LINE(v_msg);

END;
/

Tuesday, April 3, 2012

Update PK - Deferrable constraints

http://www.idevelopment.info/data/Oracle/DBA_tips/Database_Administration/DBA_12.shtml

Oracle Constraints - All in one page details
http://psoug.org/reference/constraints.html

Deferred Integrity Constraints in Oracle8

by Jeff Hunter, Sr. Database Administrator

Introduction

Up until the release of Oracle8, all integrity constraints in the database where considered immediate constraints. With an immediate constraint, if a user enters data that violates a constraint, the statement is rolled back and Oracle signals an error. With the introduction of Oracle8, users now have the option of selecting between immediate and a new option called deferred itegrity constraints.
Deferred Integrity Constraints
Deferred integrity constraints are those that are not enforced until the user attempts to commit the transaction. At the time of the commit, if any data entered during the transaction violates an integrity constraint, Oracle will then rollback the entire transaction and signal an error to the user.
Why use deferrable integrity constraints?
One use is pure laziness. In Oracle7, users had no way of entering data into a child table before entering the matching data into the parent table. With Oracle8, users now have the option of inserting data into a child table before insert its matching data into the parent table by simply deferring the foreign-key constraint.

Another feature is speed. Anyone that has ever dealt with inserting large amounts of data into a table, like found in a data warehousing environment, know of the cost loading (inserting) data into tables. Deferring the integrity checking until after the data has loaded can save considerable time when dealing with large volumes of data being inserted into tables.

Solving design issues. Take for example the two tables ORDER and ORDER LINE. In this model, we cannot have an ORDER LINE unless we have an ORDER, but we also assert that all ORDERS must have ORDER LINES. This catch-22 issue can easily be resolved by deferring all integrity constraints until the time of the commit.
Warnings
Deferred integrity constraints should only be used when absolutely necessary. If at all possible, avoid hard-coding constraint names with your application code. Deferring integrity constraints should only be used when solving a design issue. Be aware that very few applications support commit time errors.
Statements
To defer ALL constraints that are deferrable during the entire session, you can use the alter session set constraints=deferred statement.

A user can defer a named or all constraints that are deferrable for a specific transaction using the set constraint name deferred or set constraint all deferred.
The user process may also set constraints for immediate enforcement for a specific transaction using the set constraint name immediate or set constraint all immediate.
Are all constraints deferrable?
Not all constraints in the database can be set to deferrable. Only when the constraint is created with the deferrable option can user processes set the constraint as deferrable. If the constraint is created with the not deferrable option (the default), then user processes will always be bound by immediate integrity constraint.
Default behavior of constraints
Along with defining whether a constraint is deferrable or not, you can also set its default behavior with the initially deferred and initially immediate statement. The initially deferred option will enable the constraint as deferrable by default whereas the initially immediate option requires the user process to explicitly set the transaction or session to deferrable.
NOTE: The initially deferred and not deferrable keyword are mutually exclusive.

New columns in the CONSTRAINTS view
There are new columns in the "USER_CONSTRAINTS/DBA_CONSTRAINTS/ALL_CONSTRAINTS" views that will give you information on whether the constraint is deferrable. For example:

SELECT
constraint_name
, deferrable
, deferred
FROM user_constraints
WHERE constraint_name like 'EMP%';

CONSTRAINT_NAME DEFERRABLE DEFERRED
------------------------------ -------------- ---------
EMP_PRIMARY_KEY NOT DEFERRABLE IMMEDIATE
EMP_SELF_KEY NOT DEFERRABLE IMMEDIATE
EMP_FOREIGN_KEY DEFERRABLE DEFERRED

Example

REM -- ---------------------
REM -- Create example tables
REM -- ---------------------

DROP TABLE emp;
DROP TABLE dept;

CREATE TABLE dept (
deptno NUMBER(2) NOT NULL
, dname CHAR(14)
, loc CHAR(13)
, CONSTRAINT dept_pk PRIMARY KEY (deptno)
);

INSERT INTO dept VALUES (10,'FINANCE','PITTSBURGH');
INSERT INTO dept VALUES (20,'SALES','NEW YORK');
INSERT INTO dept VALUES (30,'OPERATIONS','BOSTON');

COMMIT;

CREATE TABLE emp (
empno NUMBER(4) NOT NULL
, ename CHAR(10)
, job CHAR(9)
, deptno NUMBER(2) NOT NULL
, CONSTRAINT emp_fk1 FOREIGN KEY (deptno)
REFERENCES dept (deptno)
DEFERRABLE
INITIALLY IMMEDIATE
, CONSTRAINT emp_pk PRIMARY KEY (empno)
);

INSERT INTO emp VALUES (1001, 'JEFF', 'PRESIDENT', 10);
INSERT INTO emp VALUES (1002, 'MELODY', 'MANAGER', 30);
INSERT INTO emp VALUES (1003, 'MARK', 'MANAGER', 10);
INSERT INTO emp VALUES (1004, 'MARTIN', 'MANAGER', 20);

COMMIT;

REM -- -------------------------------
REM -- Try to delete from parent table
REM -- -------------------------------

DELETE FROM dept WHERE deptno = 10;

ERROR at line 1:
ORA-02292: integrity constraint (OPS$ORACLE.EMP_FK1) violated - child record found

REM -- --------------------------------------------------------
REM -- Set this transaction to defer all deferrable constraints
REM -- --------------------------------------------------------

SET CONSTRAINTS ALL DEFERRED;

DELETE FROM dept WHERE deptno = 10;

1 row deleted.

COMMIT;

ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02292: integrity constraint (OPS$ORACLE.EMP_FK1) violated - child record found

Toms Discussion,
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:914629004506

Sunday, April 1, 2012

Create XML Schema on the FLY

OTN Thread to create XML on the FLY

https://forums.oracle.com/forums/thread.jspa?threadID=426163

CREATE OR REPLACE
PROCEDURE GEN_XML_SCHEMA_OUTPUT
(p_schema_name VARCHAR2, p_table_name VARCHAR2) AS
CURSOR c_table_columns(c_table_name varchar2, c_schema_name varchar2) IS
SELECT column_name, data_type, data_default
FROM all_tab_columns
WHERE table_name = c_table_name
AND owner = c_schema_name;
l_col_str VARCHAR2(2000);
BEGIN

DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('<'>');
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('');
FOR l_cur_table_columns IN c_table_columns(p_table_name, p_schema_name) LOOP
l_col_str := 'ELSIF l_cur_table_columns.data_type = 'NUMBER' THEN
l_col_str := l_col_str || 'xs:integer' || '"';
ELSIF l_cur_table_columns.data_type = 'DATE' THEN
l_col_str := l_col_str || 'xs:date' || '"';
END IF;

IF l_cur_table_columns.data_default IS NOT NULL THEN
l_col_str := l_col_str || ' default="' || TRIM(l_cur_table_columns.data_default) || '"';
END IF;
l_col_str := l_col_str || '/>';
DBMS_OUTPUT.PUT_LINE( l_col_str);
END LOOP;
DBMS_OUTPUT.PUT_LINE( '');
DBMS_OUTPUT.PUT_LINE( '');
DBMS_OUTPUT.PUT_LINE( '');
END;

Saturday, March 31, 2012

MERGE UPDATE

MERGE INTO Movie_Ratings M
USING (SELECT 1 AS mid, 3 AS aid, 8 AS rating FROM dual) N
ON (M.mid = N.mid AND M.aid = N.aid)
WHEN MATCHED THEN UPDATE
SET M.rating = N.rating
WHEN NOT MATCHED THEN INSERT( mid, aid, rating)
VALUES(N.mid, N.aid, N.rating); (Syntax not verified.)

Update cascade Tom kyte Example (Programatic)

When you update the primary key of a parent table, you might want to
cascade the update to the children. This is hard to do for many
reasons and can be problematic. This package works around the lack of
an update cascade option.

This package uses three triggers to perform it magic.

A before update trigger; used to reset some package variables

A before update, for each row trigger; used to capture the before
and after images of the primary keys in pl/sql tables.
It also 'undoes' the update to the primary key.

An After update trigger that does the following steps:

'Clones' the parent records with their new primary key, eg:
insert into parent select NEW_KEY, other_cols
from parent where CURRENT_KEY = ( SELECT OLD_KEY
FROM DUAL)

for example, given "update dept set deptno=deptno+1", this would
insert the values of 11, 21, 31, 41 into the dept table. 11 would
have the values in the rest of the columns that 10 had. 21 would
look like 20 and so on.

If p_preserve_rowids = TRUE, then the primary keys of the row that
was cloned and the clone would be flip flopped. For example, if you
issue: update dept set deptno = 11 where deptno = 10 we would make 10
become the new value 11 and 11 become the old value 10.

Re-Parents the child records in all subordinate tables.
Performs the equivalent of:
update child set fkey = ( select new_key
from DUAL )
where fkey = ( select old_key from DUAL )

It then removes the 'cloned' parent records or the record with the
old primary key value.

A look at the code

The follow annotated code is the generated packages and triggers you
would create by generating support for the dept table.

The following package spec is what would be generated for the
typical 'DEPT' table
found in the scott schema (when declaritive RI is used). See the annotations in
the spec for a description of what each entry means and how it is used. The
annotations are not found in the generated code, the generated code is not
commented.

This generated code preserves rowids. The code that preserves rowids will
be in bold. This code would not be present in the generated package if
rowid preservation was disabled.

--------------------------------------------------------------------------------

SQL> exec update_cascade.on_table('dept',true,true);

The following is a typical package specification generated for a table.
The package spec name is always u || TABLE_NAME || p. The package name is
in mixed case (to prevent collisions with other user objects).

create or replace package "uDEPTp" as --
Rowcnt is used to collect the number of rows processed by a given update
statement. It is reset in the uDEPTp.reset routine in a before update
trigger. The 'inTrigger' variable is used to prevent recursive firing of
triggers when p_preserve_rowid = TRUE;

rowCnt number default 0; inTrigger boolean default FALSE; --
For each element in the primary key, a table type will be declared and then
an array of that type will be declared to 1.) hold the before image, 2.) the
after image, and 3.) an empty array used to zero out the previous two
arrays.

type C1_type is table of "DEPT"."DEPTNO"%type index by binary_integer; -- empty_C1 C1_type; old_C1 C1_type; new_C1 C1_type; -- --
Reset is the routine fired by the BEFORE UPDATE trigger that resets the
rowcnt variable and empties out the arrays from the previous invocation.

procedure reset; --
Do cascade is the work horse routine. It performs the actual cascade when
fired from an AFTER UPDATE trigger.

procedure do_cascade; --
Add Entry simply increments the rowcnt and collects the before/after images
of the primary keys. It also 'undoes' the update to the primary key by
accessing the :new and :old variables.

procedure add_entry ( p_old_C1 in "DEPT"."DEPTNO"%type ,p_new_C1 in out "DEPT"."DEPTNO"%type ); -- end "uDEPTp";/
--------------------------------------------------------------------------------

This is the package body generated. It implements the above specification

create or replace package body "uDEPTp" as -- procedure reset is begin --
This line is present in all routines when p_preserve_rowids = TRUE. It
prevents recursive firing of the triggers.

if ( inTrigger ) then return; end if; -- rowCnt := 0; old_C1 := empty_C1; new_C1 := empty_C1; end reset; -- procedure add_entry ( p_old_C1 in "DEPT"."DEPTNO"%type ,p_new_C1 in out "DEPT"."DEPTNO"%type ) is begin -- if ( inTrigger ) then return; end if; --
This code saves the before and after images in pl/sql tables and 'undoes'
the primary key update by setting the new columns back to the old columns.

if ( p_old_C1 <> p_new_C1 ) then rowCnt := rowCnt + 1; old_C1( rowCnt ) := p_old_C1; new_C1( rowCnt ) := p_new_C1; p_new_C1 := p_old_C1; end if; end add_entry; -- procedure do_cascade is begin -- if ( inTrigger ) then return; end if; inTrigger := TRUE; --
For every row that was updated we will perform the clone, cascade and
delete....

for i in 1 .. rowCnt loop
This insert clones the parent row, duping the old values with the new
primary key.

insert into DEPT ( "DEPTNO" ,"DNAME","LOC") select new_C1(i) ,"DNAME","LOC" from "DEPT" a where ( "DEPTNO" ) = ( select old_C1(i) from dual ); --
This code is generated only when p_preserve_rowids=true and will flip-flop
the old and new primary keys, hence preserving the rowid of the original
parent.

update "DEPT" set ( "DEPTNO" ) = ( select decode( "DEPTNO", old_c1(i), new_c1(i), old_c1(i) ) from dual ) where ( "DEPTNO" ) = ( select new_C1(i) from dual ) OR ( "DEPTNO" ) = ( select old_C1(i) from dual ); --
Do a cascade update to all children tables.

update "EMP" set ( "DEPTNO" ) = ( select new_C1(i) from dual ) where ( "DEPTNO" ) = ( select old_C1(i) from dual ); --
Removing the old primary key value.

delete from "DEPT" where ( "DEPTNO" ) = ( select old_C1(i) from dual); end loop; -- inTrigger := FALSE; reset; exception when others then inTrigger := FALSE; reset; raise; end do_cascade; -- end "uDEPTp";/

--------------------------------------------------------------------------------

Lastly, we have the three triggers placed on
the parent table to effect the update
cascade. The first trigger simply 'resets' the package variables above.

create or replace trigger "uc$DEPT_bu"before update of"DEPTNO"on "DEPT"begin "uc$DEPT_pkg".reset; end;

The next trigger, the for each row trigger,
simply calls add_entry for each changed
row.

create or replace trigger "uc$DEPT_bufer"before update of"DEPTNO"on "DEPT"for each rowbegin"uc$DEPT_pkg".add_entry(:old."DEPTNO",:new."DEPTNO");end;

The last trigger, calls do_cascade to effect the change

create or replace trigger "uc$DEPT_au"after update of"DEPTNO"on "DEPT"begin "uc$DEPT_pkg".do_cascade; end;

Monday, March 26, 2012

Oracle DBMS_SQL

http://psoug.org/reference/dbms_sql.html

identify the columns and data types in a Weakly Typed REF CURSOR

Reference : http://www.morganslibrary.com/hci/hci003.html

I can't even count the number of times I have found myself staring at the output of a pipelined table function, or a weakly typed ref cursor, and wondered about the column names, data types, and other attributes.

If you have faced this then you too can stop cursing the darkness. Heres a few demonstrations of the pain.
SELECT COUNT(*)
FROM dba_arguments
WHERE data_type = 'REF CURSOR';

SELECT DISTINCT package_name
FROM dba_arguments
WHERE data_type = 'REF CURSOR'
AND package_name LIKE 'DBMS%'
ORDER BY 1;
And that isn't counting all of the code written by internal developers and developers from software vendors. Lets start by building a demo table and loading it with a few rows.
CREATE TABLE employees (
empid NUMBER(5),
empname VARCHAR2(30));

INSERT INTO employees (empid, empname) VALUES (1, 'Dan Morgan');
INSERT INTO employees (empid, empname) VALUES (2, 'Hans Forbrich');
INSERT INTO employees (empid, empname) VALUES (3, 'Caleb Small');
COMMIT;

SELECT * FROM employees;
Then we will create a small stored procedure that uses a weakly typed ref cursor to receive input from an anonymous block.
CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor IN SYS_REFCURSOR) IS
TYPE array_t IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;

rec_array array_t;
BEGIN
FETCH p_cursor BULK COLLECT INTO rec_array;

FOR i IN rec_array.FIRST .. rec_array.LAST
LOOP
dbms_output.put_line(rec_array(i));
END LOOP;
END pass_ref_cur;
/
Lets test the anonymous block and see if it all works.
set serveroutput on

DECLARE
rec_array SYS_REFCURSOR;
BEGIN
OPEN rec_array FOR
'SELECT empname FROM employees';

pass_ref_cur(rec_array);
CLOSE rec_array;
END;
/
The block calls the pass_ref_cursor procedure which uses dbms_output to show us that it read the employee table.

Here we can see the source code and can identify the source and its data type. But what if we couldn't?

The following code demonstrates the full capability of the DBMS_SQL.DESC_TAB and takes advantage of the fact that with Database 11g we can convert ref cursors into DBMS_SQL API handles. The private procedure "p" is used to print the results using DBMS_OUTPUT.
CREATE OR REPLACE PROCEDURE print_desc_tab(desctab IN sys.dbms_sql.desc_tab)
AUTHID CURRENT_USER AS
PROCEDURE p (b in varchar2) IS
BEGIN
dbms_output.put_line(SUBSTR(b,1,255));
END p;
BEGIN
FOR i IN 1 .. desctab.count LOOP
p(rpad('-', 30, '-'));
p(rpad('COL_TYPE:',30)||to_char(desctab(i).col_type));
p(rpad('COL_MAX_LEN:',30)||to_char(desctab(i).col_max_len));
p(rpad('COL_NAME:',30)||desctab(i).col_name);
p(rpad('COL_NAME_LEN:',30)||to_char(desctab(i).col_name_len));
p(rpad('COL_SCHEMA_NAME:',30)||desctab(i).col_schema_name);
p(rpad('COL_SCHEMA_NAME_LEN:',30)||to_char(desctab(i).col_schema_name_len));
p(rpad('COL_PRECISION:',30)||to_char(desctab(i).col_precision));
p(rpad('COL_SCALE:',30)||to_char(desctab(i).col_scale));
p(rpad('COL_CHARSETID:',30)||to_char(desctab(i).col_charsetid));
p(rpad('COL_CHARSETFORM:',30)||to_char(desctab(i).col_charsetform));

IF desctab(i).col_null_ok THEN
p(rpad('COL_NULL_OK:',30)||'TRUE');
ELSIF NOT desctab(i).col_null_ok THEN
p(rpad('COL_NULL_OK:',30)||'FALSE');
ELSE
p(rpad('COL_NULL_OK:',30)||'NULL');
END IF;
END LOOP;
END print_desc_tab;
/
Lets run an anonymous block that calls the print_desc_tab and test it.
DECLARE
refcur SYS_REFCURSOR;
h NUMBER;
cnt NUMBER;
ret dbms_sql.desc_tab;
BEGIN
OPEN refcur FOR
SELECT * FROM employees;

h := dbms_sql.to_cursor_number(refcur);

dbms_sql.describe_columns(h, cnt, ret);
print_desc_tab(ret);
dbms_sql.close_cursor(h);
END demo;
/
It works well. the DBMS_SQL's DESCRIBE_COLUMNS API gives us access to the column data type in integer form, its max length in bytes, the column's name, the length of the column's name in bytes, the schema name that owns the column's data type if the data type is user defined, the length, in bytes, of the schema name, and the columns precision, scale, character set identifier, character set form, and whether it is nullable.

Lets try again but this time with a simplified version of print_desc_tab that only exposes two attributes: data type and column name.
CREATE OR REPLACE PROCEDURE print_desc_tab(desctab IN sys.dbms_sql.desc_tab)
AUTHID CURRENT_USER AS
dtype VARCHAR2(30);

PROCEDURE p (b in varchar2) IS
BEGIN
dbms_output.put_line(SUBSTR(b,1,255));
END p;
BEGIN
FOR i IN 1 .. desctab.count LOOP
SELECT DECODE(to_char(desctab(i).col_type), 1, 'VARCHAR2', 2, 'NUMBER', 12, 'DATE')
INTO dtype
FROM dual;

p(rpad('-', 30, '-'));
p(rpad('COL_TYPE:',30) || dtype);
p(rpad('COL_NAME:',30) || desctab(i).col_name);
END LOOP;
END print_desc_tab;
/
We will again run the same anonymous block to call print_desc_tab
DECLARE
refcur SYS_REFCURSOR;
h NUMBER;
cnt NUMBER;
ret dbms_sql.desc_tab;
BEGIN
OPEN refcur FOR
SELECT * FROM employees;

h := dbms_sql.to_cursor_number(refcur);

dbms_sql.describe_columns(h, cnt, ret);
print_desc_tab(ret);
dbms_sql.close_cursor(h);
END demo;
/
Now you know how to do it.The only thing left is to find a project where you can put it to good use.

Translate & Replace Oracle

TRANSLATE(
str1 VARCHAR2 CHARACTER SET ANY_CS,
src VARCHAR2 CHARACTER SET STR1%CHARSET,
dest VARCHAR2 CHARACTER SET STR1%CHARSET)
RETURN VARCHAR2 CHARACTER SET STR1%CHARSET;

Example : In this demo a string is first encrypted then decrypted

SELECT TRANSLATE('this is a secret',
'abcdefghijklmnopqrstuvxyz', '0123456789qwertyuiop[kjhbv')
FROM DUAL;

SELECT TRANSLATE('p78o 8o 0 o42i4p',
'0123456789qwertyuiop[kjhbv', 'abcdefghijklmnopqrstuvxyz')
FROM DUAL;

Example 2 :The a is replaced with an e, the h has no complement and is dropped.
SELECT TRANSLATE('So What', 'ah', 'e')
FROM DUAL;

Overload 1
REPLACE(
srcstr VARCHAR2 CHARACTER SET ANY_CS,
oldsub VARCHAR2 CHARACTER SET SRCSTR%CHARSET,
newsub VARCHAR2 CHARACTER SET SRCSTR%CHARSET := NULL)
RETURN VARCHAR2 CHARACTER SET SRCSTR%CHARSET;

Overload 2
REPLACE(
srcstr CLOB CHARACTER SET ANY_CS,
oldsub CLOB CHARACTER SET SRCSTR%CHARSET,
newsub CLOB CHARACTER SET SRCSTR%CHARSET := NULL)
RETURN CLOB CHARACTER SET SRCSTR%CHARSET;

Example 1: Single Character Replacement
REPLACE(, <'string_to_match'>,<'replacements_string'>)
SELECT REPLACE('So What', 'o', 'ay')
FROM DUAL;

Example 2: Multiple Character Replacement
Replacement of a single character with a phrase
SELECT REPLACE('An idea that is not dangerous is unworthy of being called an idea at all.', 'n idea', ' software program') TRUTH
FROM DUAL;

Count the occurence of a string in Oracle

WITH data AS (SELECT 'Whose line is it anyway' line FROM DUAL)
SELECT LENGTH(line)-LENGTH(TRANSLATE(line,'xaeiou','x')) nbVowels
FROM data;

Sunday, March 25, 2012

Reference Cursor

Using Ref Cursors To Return Recordsets
Since Oracle 7.3 the REF CURSOR type has been available to allow recordsets to be returned from stored procedures and functions. Oracle 9i introduced the predefined SYS_REFCURSOR type, meaning we no longer have to define our own REF CURSOR types. The example below uses a ref cursor to return a subset of the records in the EMP table.

The following procedure opens a query using a SYS_REFCURSOR output parameter. Notice the cursor is not closed in the procedure. It is up to the calling code to manage the cursor once it has been opened.

CREATE OR REPLACE
PROCEDURE get_emp_rs (p_deptno IN emp.deptno%TYPE,
p_recordset OUT SYS_REFCURSOR) AS
BEGIN
OPEN p_recordset FOR
SELECT ename,
empno,
deptno
FROM emp
WHERE deptno = p_deptno
ORDER BY ename;
END GetEmpRS;
/

The resulting cursor can be referenced from PL/SQL as follows.

SET SERVEROUTPUT ON SIZE 1000000
DECLARE
l_cursor SYS_REFCURSOR;
l_ename emp.ename%TYPE;
l_empno emp.empno%TYPE;
l_deptno emp.deptno%TYPE;
BEGIN
get_emp_rs (p_deptno => 30,
p_recordset => l_cursor);

LOOP
FETCH l_cursor
INTO l_ename, l_empno, l_deptno;
EXIT WHEN l_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(l_ename || ' | ' || l_empno || ' | ' || l_deptno);
END LOOP;
CLOSE l_cursor;
END;
/
If you are using a version of Oracle before 9i, then create the following package and replace any references to SYS_REFCURSOR with TYPES.cursor_type.

CREATE OR REPLACE PACKAGE types AS
TYPE cursor_type IS REF CURSOR;

Other examples for ADO or Java
http://www.oracle-base.com/articles/misc/UsingRefCursorsToReturnRecordsets.php
END Types;
/

Monday, February 13, 2012

http://www.java2s.com/Tutorial/Oracle/0601__System-Packages/DBMSCRYPTOencryptaes128DBMSCRYPTOchaincbcDBMSCRYPTOpadpkcs5.htm - DBMS Crypto

http://www.dba-oracle.com/t_dbms_crypto.htm - crypto
http://www.orafaq.com/usenet/comp.databases.oracle.misc/2007/03/01/0001.htm - crypto

http://www.oracle.com/technetwork/database/focus-areas/security/tde-faq-093689.html#A12001

TDE - Encryption using Wallet Manager

http://www.oracle-base.com/articles/11g/TablespaceEncryption_11gR1.php - Table space encryption

http://www.oracle-base.com/articles/8i/DataEncryption.php - Data Obfuscation

http://www.oracle-base.com/articles/8i/DataEncryption.php - Data Encryption - DBMS_OBFUSCATION_TOOLKIT

Thursday, February 9, 2012

Syntax to add partitions dynamically

Dear Oracle experts,
In our system we have a requirement like...
we need to create a partitioned table with 3 partitions and each
partition contains data of only one day.
Moreover I need to store only the last 3 days data in those
partitions(need not bother about old data).
I mean, for eg. today is 30-apr-2011, i need to have 28/29/30-apr-2011
days data in the partitions of table.
Hope this is clear.

Please guide me the way to implement this.

Thanks.

Regards,
Madhu.

Best Answerby Larson E Apr 29, 2011
We do this by creating a table and partition on the date, so the partition
would be defined as:

alter table abc add partition abc_20110428 values less than ('29-APR-11');
alter table abc add partition abc_20110429 values less than ('30-APR-11');
alter table abc add partition abc_20110430 values less than ('01-MAY-11');

A job is created that runs each day. On this example, let's assume the job
runs at 1AM each nigh. So on May 2, data for 4/29 would be processed, then
remove the oldest partition, such as:

declare

my_date date;
my_datenum number;
my_partition varchar2(16);

my_date := to_char(sysdate-3);

do_processing (my_date);

-- remove old partition

my_partition := 'abc_' to_char(my_date, 'YYYYMMDD');

execute immediate 'alter table abc drop partition ' || my_partition || '
update global indexes';

-- now add my new partition for today

my_partition := 'abc_' || to_number(to_char(sysdate));

execute immediate 'alter table abc add partition ' || my_partition || '
values less than (''' || to_char(sysdate) || ''')';

-- schedule tomorrow's job

Dynamic partition in Oracle 10G

http://www.ora600.be/news/tool-add-range-partitions-automatically-table

Link to create dynamic partition

Tuesday, February 7, 2012

http://docs.oracle.com/cd/A87860_01/doc/server.817/a76965/c11ordb.htm - Nested Data types

http://database.itags.org/oracle/68426/ - Encryption sample
http://www.oracle-base.com/articles/11g/SecureFiles_11gR1.php#lob_encryption - Secure File
http://psoug.org/reference/dbms_crypto.html - Bunch of encryption functions

Thursday, February 2, 2012

Oracle 11G partitioning & timestamp related importan sites

http://www.dba-oracle.com/t_interval_partitioning.htm
http://shonythomas.blogspot.com/2011/05/oracle-partitioning.html
http://www.orafaq.com/node/1912
http://docs.oracle.com/cd/B28359_01/server.111/b32024/partition.htm
http://rampant-books.com/art_hernandez_partitioning_in_oracle.htm
http://www.orafaq.com/node/1912
http://psoug.org/reference/timestamp.html - timestamp
http://forum.springsource.org/showthread.php?65749-Oracle-TIMESTAMP(6)-WITH-LOCAL-TIME-ZONE-Problems - timestamp
http://oracle-cookies.blogspot.com/2008/03/whats-wrong-with-oracle-timestamp-with.html - timestamp

http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_crypto.htm encryption on CLOB

Wednesday, February 1, 2012

Partition table syntaxes

create table part(a date)
PARTITION BY RANGE (a)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(partition p1 values less than (TO_DATE('01-NOV-2007','DD-MON-YYYY')));

CREATE TABLE "KAJAL"."PRODUCTS"
( "PARTNO" NUMBER, "DESCRIPTION" VARCHAR2(60 BYTE) )
PARTITION BY HASH ("PARTNO")
(PARTITION "SYS_P21" TABLESPACE "USERS" ,
PARTITION "SYS_P22" TABLESPACE "USERS" ,
PARTITION "SYS_P23" TABLESPACE "USERS" ,
PARTITION "SYS_P24" TABLESPACE "USERS" ) ;

CREATE TABLE "KAJAL"."SALES"
( "YEAR" NUMBER(4,0),
"PRODUCT" VARCHAR2(10 BYTE),
"AMT" NUMBER(10,2))
PARTITION BY RANGE ("YEAR")
(PARTITION "P1" VALUES LESS THAN (1992) ,
PARTITION "P2" VALUES LESS THAN (1993) ,
PARTITION "P3" VALUES LESS THAN (1994) ,
PARTITION "P4" VALUES LESS THAN (1995) ,
PARTITION "P5" VALUES LESS THAN (MAXVALUE) ;

Automatic Partition Management for Oracle 10g

If you have a moving window partitioning scheme then you should know that in Oracle 11g you now have a brand new way of creating new partitions. Oracle 11g creates the partitions in an automatic fashion, so that when the row arrives at the table, if the partition does not exist, Oracle will create it. This is awsome, but imagine two things:
* You can’t migrate to Oracle 11g on the fly. You’re stuck in 10g* You also need to drop the oldest partition, because you have lack of storage issues
Then if you are in this situation we have the solution for you. It’s bullet-proof tested and proven at production sites.
Every night you need to add another partition and remove the oldest. Pure moving window stuff. The format of the partition name is: PXXXX_YYYY_MM_DD, where XXXX is a sequential number that increments every day. This is daily partitioning and should only have a job running this code everynight:declare
x varchar2(90);
s varchar2(900);
begin
-- Fetchs the name of the oldest partition
select partition_name
into x
from sys.dba_tab_partitions
where partition_position = 1
and table_name = 'MYTABLE'
and table_owner = 'MYOWNER';
-- Builds the name-string
s := 'ALTER TABLE MYOWNER.MYTABLE DROP PARTITION 'x' UPDATE INDEXES';
-- Uses a customized report and sends it by email
-- so you can see the partitions state before and after
MYOWNER.my_pkg.sends_email('REPORT6');
-- Drops the Partition
execute immediate s;
-- And now adds another
MYOWNER.add_partition;
MYOWNER.my_pkg.sends_email('REPORT6');
--dbms_output.put_line(s);
end;
The procedure “add_partition” is another piece of automatic code that you must create prior to the previous PL/SQL block:CREATE OR REPLACE procedure MYOWNER.add_partition
is
next_part varchar2(40);
less_than_char varchar2(20);
comando_add varchar2(1000);
BEGIN-- Generates the name of the partition
select 'P'to_char(to_number(substr(partition_name,2,
instr(partition_name,'_',1)-2))+1)'_'
to_char(to_date(substr(partition_name,
instr(partition_name,'_',1)+1),'yyyy_mm_dd')+1,'yyyy_mm_dd'),
replace(to_char(to_date(substr(partition_name,
instr(partition_name,'_',1)+1),'yyyy_mm_dd')+2,'yyyy_mm_dd'),'_','-')
into next_part,less_than_char
from dba_tab_partitions
where table_owner = 'MYOWNER'
and table_name = 'MYTABLE'
and partition_position = (select max(partition_position)
from dba_tab_partitions where table_owner = 'MYOWNER'
and table_name = 'MYTABLE');
-- Builds the statement string
comando_add := 'ALTER TABLE MYOWNER.MYTABLE ADD PARTITION 'next_part;
comando_add := comando_add' VALUES LESS THAN (to_date('chr(39)less_than_char;
comando_add := comando_addchr(39)','chr(39)
'yyyy-mm-dd'chr(39)')) TABLESPACE DATA_PARTITIONED';
-- Executes the statement
execute immediate(comando_add);
--dbms_output.put_line(comando_add);
end;
/

Partitioning Enhancements in Oracle Database 11g Release 1

Ref: http://www.oracle-base.com/articles/11g/PartitioningEnhancements_11gR1.php
This article uses simple examples to describe the partitioning enhancements in Oracle 11g Release 1.
Extended Composite Partitioning
Interval Partitioning
System Partitioning
Reference Partitioning
Virtual Column-Based Partitioning
Single Partition Transportable for Oracle Data Pump
Partition Advisor
Enhanced Statistics Collection for Partitioned Objects
Related articles.
Partitioned Tables And Indexes in Oracle 8i
Partitioning Enhancements In Oracle9i
Hash Partitioned Global Indexes in Oracle 10g
Partitioning an Existing Table using DBMS_REDEFINITION
Partitioning an Existing Table using EXCHANGE PARTITION
Extended Composite Partitioning
In previous releases of Oracle, composite partitioning was limited to Range-Hash and Range-List partitioning. Oracle 11g Release 1 extends this to allow the following composite partitioning schemes:
Range-Hash (available since 8i)
Range-List (available since 9i)
Range-Range
List-Range
List-Hash
List-List
Interval partitioning, described below, is a form of range partitioning, so the previous list also implies the following combinations:
Interval-Hash
Interval-List
Interval-Range
The follow code provides an example of one of the new composite partitioning schemes. First we create a table with List-Hash composite partitioning.
CREATE TABLE list_hash_tab (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE
)
PARTITION BY LIST (code)
SUBPARTITION BY HASH (id)
(
PARTITION part_aa values ('AA')
(
SUBPARTITION part_aa_01,
SUBPARTITION part_aa_02
),
partition part_bb values ('BB')
(
SUBPARTITION part_bb_01,
SUBPARTITION part_bb_02
)
);
Next we populate it with some data, which we expect to be spread throughout the subpartitions.
DECLARE
l_code VARCHAR2(10);
BEGIN
FOR i IN 1 .. 40 LOOP
IF MOD(i, 2) = 0 THEN
l_code := 'BB';
ELSE
l_code := 'AA';
END IF;
INSERT INTO list_hash_tab (id, code, description, created_date)
VALUES (i, l_code, 'Description for ' i ' ' l_code, SYSDATE);
END LOOP;
COMMIT;
END;
/
EXEC DBMS_STATS.gather_table_stats(USER, 'LIST_HASH_TAB', granularity=>'ALL');
Finally, we query the USER_TAB_SUBPARTITIONS view to see if the data has been distributed across the subpartitions.
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20
SELECT table_name, partition_name, subpartition_name, num_rows
FROM user_tab_subpartitions
ORDER by table_name, partition_name, subpartition_name;
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
LIST_HASH_TAB PART_AA PART_AA_01 7
LIST_HASH_TAB PART_AA PART_AA_02 13
LIST_HASH_TAB PART_BB PART_BB_01 10
LIST_HASH_TAB PART_BB PART_BB_02 10
4 rows selected.
SQL>
The query shows the data has been split into the two partitions based on the vale of the CODE column, then hashed between the subpartitions.
Interval Partitioning
Interval partitioning is an extension of range partitioning, where the system is able to create new partitions as they are required. The PARTITION BY RANGE clause is used in the normal way to identify the transition point for the partition, then the new INTERVAL clause used to calculate the range for new partitions when the values go beyond the existing transition point.
The following code shows an example of a table using interval partitioning.
CREATE TABLE interval_tab (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(
PARTITION part_01 values LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
);
Querying the USER_TAB_PARTITIONS view shows there is only a single partition.
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');11g
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A40
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 0
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
1 row selected.
SQL>
Provided we insert data with a created_date value less than '01-NOV-2007' the data will be placed in the existing partition and no new partitions will be created.
INSERT INTO interval_tab VALUES (1, 'ONE', 'One', TO_DATE('16-OCT-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (2, 'TWO', 'Two', TO_DATE('31-OCT-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
1 row selected.
SQL>
If we add data beyond the range of the existing partition, a new partition is created.
INSERT INTO interval_tab VALUES (3, 'THREE', 'Three', TO_DATE('01-NOV-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (4, 'FOUR', 'Four', TO_DATE('30-NOV-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TAB SYS_P44 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
11g
2 rows selected.
SQL>
If we insert data for two months after the current largest transition point, only the required partition is created, not the intervening partitions.
INSERT INTO interval_tab VALUES (5, 'FIVE', 'Five', TO_DATE('01-JAN-2008', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (4, 'FOUR', 'Four', TO_DATE('31-JAN-2008', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TAB SYS_P44 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TAB SYS_P45 TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
3 rows selected.
SQL>
Notice that a partition to hold January 2008 data has not been created. If we insert data for this time period, the missing partition is created.
INSERT INTO interval_tab VALUES (7, 'SEVEN', 'Seven', TO_DATE('01-DEC-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (8, 'EIGHT', 'Eight', TO_DATE('31-DEC-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------- -------------------- ---------------------------------------- ----------
INTERVAL_TAB PART_01 TO_DATE(' 2007-11-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TAB SYS_P44 TO_DATE(' 2007-12-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TAB SYS_P45 TO_DATE(' 2008-02-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
INTERVAL_TAB SYS_P46 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
4 rows selected.
SQL>
The following restrictions apply to interval partitioned tables:
Interval partitioning is restricted to a single partition key that must be a numerical or date range.
At least one partition must be defined when the table is created.
Interval partitioning is not supported for index-organized tables.
You cannot create a domain index on an interval partitioned table.
Interval partitioning can be used as the primary partitioning mechanism in composite partitioning, but it can't be used at the subpartition level.
A MAXVALUE partition cannot be defined for an interval partitioned table.
NULL values are not allowed in the partition column.
System Partitioning
As you would expect, system partitioning allows large tables to be broken down into smaller partitions, but unlike other partitioning schemes, the database has no control over the placement of rows during insert operations. The following example shows the creation of a system partitioned table.
CREATE TABLE system_partitioned_tab (
id NUMBER,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE
)
PARTITION BY SYSTEM
(
PARTITION part_1,
PARTITION part_2
);
The partition must be explicitly defined in all insert statements or an error is produced.
INSERT INTO system_partitioned_tab VALUES (1, 'ONE', 'One', SYSDATE);
*
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by
the System method
SQL>
The PARTITION clause is used to define which partition the row should be placed in.
INSERT INTO system_partitioned_tab PARTITION (part_1) VALUES (1, 'ONE', 'One', SYSDATE);
INSERT INTO system_partitioned_tab PARTITION (part_2) VALUES (2, 'TWO', 'Two', SYSDATE);
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'SYSTEM_PARTITIONED_TAB');
COLUMN table_name FORMAT A25
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A10
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------- -------------------- ---------- ----------
SYSTEM_PARTITIONED_TAB PART_1 1
SYSTEM_PARTITIONED_TAB PART_2 1
2 rows selected.
SQL>
Notice that the HIGH_VALUE for the partitions is blank.
The PARTITION clause is optional for update and delete statements, but omitting this clause will force all partitions to be scanned, since there is no way perform automatic partition pruning when the database has no control over row placement. When the PARTITION clause is used, you must be sure to perform the operation against the correct partition.
SQL> DELETE FROM system_partitioned_tab PARTITION (part_2) WHERE id = 1;
0 rows deleted.
SQL> UPDATE system_partitioned_tab PARTITION (part_1) SET code = 'TWO' WHERE id = 2;
0 rows deleted.
SQL>
The PARTITION clause can also be used in queries to target specific partitions.
SELECT COUNT(*)
FROM system_partitioned_tab PARTITION (part_1);
COUNT(*)
----------
1
1 row selected.
SQL>
System partitioning gives you many of the advantages of partitioning, but leaves the decision of how the data is partitioned to the application layer.
Conditions and restrictions on system partitioning include:
If you specify the PARTITION BY SYSTEM clause, but don't define partitions, a single partition is created with the name in the format of "SYS_Pn".
If you specify PARTITION BY SYSTEM PARTITIONS n clause, the database creates "n" partitions with the name in the format of "SYS_Pn". The range of allowable values for "n" is from 1 to 1024K-1.
System partitioning is not available for index-organized tables or a table that is part of a cluster.
System partitioning can play no part in composite partitioning.
You cannot split a system partition.
System partitioning cannot be specified in a CREATE TABLE ... AS SELECT statement.
To insert data into a system-partitioned table using an INSERT INTO ... AS subquery statement, you must use partition-extended syntax to specify the partition into which the values returned by the subquery will be inserted.
Reference Partitioning
Reference partitioning allows tables related by foreign keys to be logically equi-partitioned. The child table is partitioned using the same partitioning key as the parent table without having to duplicate the key columns. Partition maintenance operations performed on the parent table are reflected on the child table, but no partition maintenance operations are allowed on the child table.
The following code contains a partitioned parent table and a dependent reference partitioned child table.
CREATE TABLE parent_tab (
id NUMBER NOT NULL,
code VARCHAR2(10) NOT NULL,
description VARCHAR2(50),
created_date DATE,
CONSTRAINT parent_tab_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_date)
(
PARTITION part_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY')),
PARTITION part_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))
);
CREATE TABLE child_tab (
id NUMBER NOT NULL,
parent_tab_id NUMBER NOT NULL,
code VARCHAR2(10),
description VARCHAR2(50),
created_date DATE,
CONSTRAINT child_tab_pk PRIMARY KEY (id),
CONSTRAINT child_parent_tab_fk FOREIGN KEY (parent_tab_id)
REFERENCES parent_tab (id)
)
PARTITION BY REFERENCE (child_parent_tab_fk);
Child records that foreign key to rows in the first partition of the parent table should be placed in the first partition of the child table. So we insert two rows into the first partition and one row into the second of the parent table. We then insert three rows into the child table, with one foreign keyed to a row in the first partition and two foreign keyed to a row in the second partition of the master table.
INSERT INTO parent_tab VALUES (1, 'ONE', '1 ONE', SYSDATE);
INSERT INTO parent_tab VALUES (2, 'TWO', '2 TWO', SYSDATE);
INSERT INTO parent_tab VALUES (3, 'THREE', '3 THREE', ADD_MONTHS(SYSDATE,12));
INSERT INTO child_tab VALUES (1, 1, 'ONE', '1 1 ONE', SYSDATE);
INSERT INTO child_tab VALUES (2, 3, 'TWO', '2 3 TWO', SYSDATE);
INSERT INTO child_tab VALUES (3, 3, 'THREE', '3 3 THREE', SYSDATE);
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'PARENT_TAB');
EXEC DBMS_STATS.gather_table_stats(USER, 'CHILD_TAB');
We now expect the parent table to have 2 records in the 2007 partition and 1 in the 2008 partition, while the child table should have 1 row in the 2007 partition and 2 rows in the 2008 partition. The following query confirms out expectation.
COLUMN table_name FORMAT A25
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A40
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
CHILD_TAB PART_2007 1
CHILD_TAB PART_2008 2
PARENT_TAB PART_2007 TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-M 2
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
PARENT_TAB PART_2008 TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-M 1
M-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
4 rows selected.
SQL>
The following conditions and restrictions apply to reference partitioning:
The child table must specify a referential integrity constraint defined on the table being created. This constraint must be in ENABLE VALIDATE NOT DEFERRABLE state (the default) and refer to a primary or unique key on the parent table.
The foreign key columns referenced in constraint must be NOT NULL.
The constraint cannot use the ON DELETE SET NULL clause.
The parent table referenced must be an existing partitioned table. All partitioning methods except interval partitioning are supported.
The foreign key cannot contain any virtual columns.
The referenced primary key or unique constraint on the parent table cannot contain any virtual columns.
Reference partitioning cannot be used for index-organized tables, external tables, or domain index storage tables.
A chain of reference partitioned tables can be created, but constraint used can't be self-referencing.
The ROW MOVEMENT setting for both tables must match.
Reference partitioning cannot be specified in a CREATE TABLE ... AS SELECT statement.
Virtual Column-Based Partitioning
Oracle 11g supports the concept of virtual columns on tables. These virtual columns are not physically stored in the table, but derived from data in the table. These virtual columns can be used in the partition key in all basic partitioning schemes. The example below creates a table that is list partitioned on a virtual column that represents the first letter in the username column of the table.
CREATE TABLE users (
id NUMBER,
username VARCHAR2(20),
first_letter VARCHAR2(1)
GENERATED ALWAYS AS
(
UPPER(SUBSTR(TRIM(username), 1, 1))
) VIRTUAL
)
PARTITION BY LIST (first_letter)
(
PARTITION part_a_g VALUES ('A','B','C','D','E','F','G'),
PARTITION part_h_n VALUES ('H','I','J','K','L','M','N'),
PARTITION part_o_u VALUES ('O','P','Q','R','S','T','U'),
PARTITION part_v_z VALUES ('V','W','X','Y','Z')
);
The following code inserts two rows into each partition defined in the table.
INSERT INTO users (id, username) VALUES (1, 'Andy Pandy');
INSERT INTO users (id, username) VALUES (1, 'Burty Basset');
INSERT INTO users (id, username) VALUES (1, 'Harry Hill');
INSERT INTO users (id, username) VALUES (1, 'Iggy Pop');
INSERT INTO users (id, username) VALUES (1, 'Oliver Hardy');
INSERT INTO users (id, username) VALUES (1, 'Peter Pervis');
INSERT INTO users (id, username) VALUES (1, 'Veruca Salt');
INSERT INTO users (id, username) VALUES (1, 'Wiley Cyote');
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'USERS');
The following query shows the data was distributed as expected.
COLUMN table_name FORMAT A25
COLUMN partition_name FORMAT A20
COLUMN high_value FORMAT A40
SELECT table_name, partition_name, high_value, num_rows
FROM user_tab_partitions
ORDER BY table_name, partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE NUM_ROWS
------------------------- -------------------- ---------------------------------------- ----------
USERS PART_A_G 'A', 'B', 'C', 'D', 'E', 'F', 'G' 2
USERS PART_H_N 'H', 'I', 'J', 'K', 'L', 'M', 'N' 2
USERS PART_O_U 'O', 'P', 'Q', 'R', 'S', 'T', 'U' 2
USERS PART_V_Z 'V', 'W', 'X', 'Y', 'Z' 2
4 rows selected.
SQL>
Single Partition Transportable for Oracle Data Pump
Oracle 11g allows single table partitions to be transported between databases, rather than requiring the whole table to be transported. To show this in action we need to create two tablespaces to hold the table partitions and give the test use a quota on these tablespaces.
CONN sys/password@db11g AS SYSDBA
CREATE TABLESPACE transport_test_ts_1
DATAFILE '/u01/app/oracle/oradata/DB11G/tt_ts_1'
SIZE 128K AUTOEXTEND ON NEXT 128K;
CREATE TABLESPACE transport_test_ts_2
DATAFILE '/u01/app/oracle/oradata/DB11G/tt_ts_2'
SIZE 128K AUTOEXTEND ON NEXT 128K;
ALTER USER test
QUOTA UNLIMITED ON transport_test_ts_1
QUOTA UNLIMITED ON transport_test_ts_2;
CONN test/test@db11g
Next, we create and populate a test partitioned table.
CREATE TABLE transport_test_tab (
id NUMBER NOT NULL,
code VARCHAR2(10) NOT NULL,
description VARCHAR2(50),
created_date DATE,
CONSTRAINT transport_test_pk PRIMARY KEY (id)
)
PARTITION BY RANGE (created_date)
(
PARTITION part_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY'))
TABLESPACE transport_test_ts_1,
PARTITION part_2008 VALUES LESS THAN (TO_DATE('01-JAN-2009','DD-MON-YYYY'))
TABLESPACE transport_test_ts_2
);
INSERT INTO transport_test_tab VALUES (1, 'ONE', '1 ONE', SYSDATE);
INSERT INTO transport_test_tab VALUES (2, 'TWO', '2 TWO', SYSDATE);
INSERT INTO transport_test_tab VALUES (3, 'THREE', '3 THREE', ADD_MONTHS(SYSDATE,12));
INSERT INTO transport_test_tab VALUES (4, 'FOUR', '4 FOUR', ADD_MONTHS(SYSDATE,12));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'TRANSPORT_TEST_TAB');
The following query shows that each partition is on a separate tablespace and contains some data.
COLUMN table_name FORMAT A20
COLUMN partition_name FORMAT A20
COLUMN tablespace_name FORMAT A20
SELECT table_name, partition_name, tablespace_name, num_rows
FROM user_tab_partitions;
TABLE_NAME PARTITION_NAME TABLESPACE_NAME NUM_ROWS
-------------------- -------------------- -------------------- ----------
TRANSPORT_TEST_TAB PART_2007 TRANSPORT_TEST_TS_1 2
TRANSPORT_TEST_TAB PART_2008 TRANSPORT_TEST_TS_2 2
2 rows selected.
SQL>
Any tablespaces we wish to transport must be made read only.
CONN sys/password@db11g AS SYSDBA
ALTER TABLESPACE transport_test_ts_1 READ ONLY;
When we perform the data pump export, we can specify the individual partition we wish to export using the following syntax.
tables=schema.table:partition transportable=always
Notice the ":partition" section of the TABLES parameter. The TRANSPORTABLE parameter indicates that we wish to capture just the metadata for the partiton.
We can now run the Data Pump export using the following command.
expdp system/password tables=test.transport_test_tab:part_2007 transportable=always
directory=data_pump_dir dumpfile=part_2007.dmp
The output is displayed below.
$ expdp system/password tables=test.transport_test_tab:part_2007 transportable=always
directory=data_pump_dir dumpfile=part_2007.dmp
Export: Release 11.1.0.6.0 - Production on Friday, 19 October, 2007 16:40:45
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** tables=test.transport_test_tab:part_2007
transportable=always directory=data_pump_dir dumpfile=part_2007.dmp
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/DB11G/dpdump/part_2007.dmp
******************************************************************************
Datafiles required for transportable tablespace TRANSPORT_TEST_TS_1:
/u01/app/oracle/oradata/DB11G/tt_ts_1
Datafiles required for transportable tablespace USERS:
/u01/app/oracle/oradata/DB11G/users01.dbf
Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 16:40:55
$
To simulate transporting the tablespace, we will drop the existing table and tablespaces. Notice, that we don't actually remove the datafile associated with the first tablespace, as this is the datafile containing our transportable partition.
CONN sys/password@db11g AS SYSDBA
DROP TABLE test.transport_test_tab;
DROP TABLESPACE transport_test_ts_1 INCLUDING CONTENTS;
DROP TABLESPACE transport_test_ts_2 INCLUDING CONTENTS AND DATAFILES;
We can now import the dump file to snap in our transportable partition using the PARTITION_OPTIONS=DEPARTITION parameter setting, which converts all partitions into standalone table segments.
impdp system/password partition_options=departition dumpfile=part_2007.dmp
transport_datafiles='/u01/app/oracle/oradata/DB11G/tt_ts_1'
The expected output is displayed below.
$ impdp system/password partition_options=departition dumpfile=part_2007.dmp
transport_datafiles='/u01/app/oracle/oradata/DB11G/tt_ts_1'
Import: Release 11.1.0.6.0 - Production on Friday, 19 October, 2007 16:47:04
Copyright (c) 2003, 2007, Oracle. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** partition_options=departition
dumpfile=part_2007.dmp transport_datafiles=/u01/app/oracle/oradata/DB11G/tt_ts_1
Processing object type TABLE_EXPORT/TABLE/PLUGTS_BLK
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
ORA-39083: Object type INDEX failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
CREATE UNIQUE INDEX "TEST"."TRANSPORT_TEST_PK" ON "TEST"."TRANSPORT_TEST_TAB" ("ID") PCTFREE 10
INITRANS 2 MAXTRANS 255 STORAGE(SEG_FILE 4 SEG_BLOCK 59 OBJNO_REUSE 70550 INITIAL 65536
NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT) TABLESPACE "USERS" PARALLEL 1
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
ORA-39083: Object type CONSTRAINT failed to create with error:
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "TEST"."TRANSPORT_TEST_TAB" ADD CONSTRAINT "TRANSPORT_TEST_PK" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(SEG_FILE 4 SEG_BLOCK 59 OBJNO_REUSE 70550
INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "USERS" ENABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
ORA-39112: Dependent object type INDEX_STATISTICS skipped, base object type
INDEX:"TEST"."TRANSPORT_TEST_PK" creation failed
Processing object type TABLE_EXPORT/TABLE/END_PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" completed with 3 error(s) at 16:47:07
$
The table segment is named using a combination of the table and partition name, so dependent objects fail because they are referencing the wrong table name. We can see the new segment using the following query.
CONN test/test@db11g
EXEC DBMS_STATS.gather_schema_stats(USER);
COLUMN table_name FORMAT A30
COLUMN tablespace_name FORMAT A20
SELECT table_name, tablespace_name, partitioned, num_rows
FROM user_tables;
TABLE_NAME TABLESPACE_NAME PAR NUM_ROWS
------------------------------ -------------------- --- ----------
TRANSPORT_TES_PART_2007 TRANSPORT_TEST_TS_1 NO 2
1 row selected.
SQL>
Partition Advisor
The SQL Access Advisor, originally introduced in Oracle 10g, has been updated to include advice on partitioning existing tables, materialized views and indexes. Partitioning exiting tables can be quite a lengthy process, so don't expect this advice to provide a quick fix.
Enhanced Statistics Collection for Partitioned Objects
Oracle 11g includes improvements to statistics collection for partitioned objects so untouched partitions are not rescanned. This significantly increases the speed of statistics collection on large tables where some of the partitions contain static data. Where partition exchange load (PEL) is used to add data to the a table, only the newly added partition must be scanned to update the global statistics. See here.
For more information see:
Partitioning Concepts
CREATE TABLE
Oracle Database New Features Guide 11g Release 1 (11.1)
Partitioned Tables And Indexes in Oracle 8i
Partitioning Enhancements In Oracle9i
Hash Partitioned Global Indexes in Oracle 10g
Partitioning an Existing Table using DBMS_REDEFINITION
Partitioning an Existing Table using EXCHANGE PARTITION
Hope this helps. Regards Tim...
Back to the Top.

How to Create and Manage Partition Tables in Oracle

Partitioning
Now a days enterprises run databases of hundred of Gigabytes in size. These databases are known as
Very Large Databases (VLDB). From Oracle Ver. 8.0 Oracle has provided the feature of
table partitioning i.e. you can partition a table according to some criteria . For example you
have a SALES table with the following structure

Suppose this table contains millions of records, but all the records belong to four years only i.e. 1991, 1992, 1993 and 1994. And most of the time you are concerned about only one year i.e. you give queries like the following

select sum(amt) from sales where year=1991;
select product,sum(amt) from sales where year=1992
Group by product;
Now whenever you give queries like this Oracle will search the whole table. If you partition this table according to year, then the performance is improve since oracle will scan only a single partition instead of whole table.

CREATING PARTITION TABLES

To create a partition table give the following statement

create table sales (year number(4),
product varchar2(10),
amt number(10,2))
partition by range (year)
partition p1 values less than (1992) tablespace u1,
partition p2 values less than (1993) tablespace u2,
partition p3 values less than (1994) tablespace u3,
partition p4 values less than (1995) tablespace u4,
partition p5 values less than (MAXVALUE) tablespace u5;
In the above example sales table is created with 5 partitions. Partition p1 will contain rows of year 1991 and it will be stored in tablespace u1. Partition p2 will contain rows of year 1992 and it will be stored in tablespace u2. Similarly p3 and p4.

In the above example if you don’t specify the partition p4 with values less than MAVALUE, then you will not be able to insert any row with year above 1994.

Although not required, you can place partitions in different tablespaces. If you place partitions in different tablespaces then you can isolate problems due to failures as only a particular partition will not be available and rest of the partitions will still be available.

The above example the table is partition by range.

In Oracle you can partition a table by

Range Partitioning
Hash Partitioning
List Partitioning
Composite Partitioning

Range Partitioning

This type of partitioning is useful when dealing with data that has logical ranges into which it can be distributed; for example, value of year. Performance is best when the data evenly distributes across the range

Hash partitioning

Use hash partitioning if your data does not easily lend itself to range partitioning, but you would like to partition for performance and manageability reasons. Hash partitioning provides a method of evenly distributing data across a specified number of partitions. Rows are mapped into partitions based on a hash value of the partitioning key

The following example shows how to create a hash partition table.
The following example creates a hash-partitioned table. The partitioning column is partno, four partitions are created and assigned system generated names, and they are placed in four named tablespaces (tab1,tab2, ...).

CREATE TABLE products
(partno NUMBER,
description VARCHAR2 (60))
PARTITION BY HASH (partno)
PARTITIONS 4
STORE IN (tab1, tab2, tab3, tab4);
List Partitioning Use list partitioning when you require explicit control over how rows map to partitions. You can specify a list of discrete
values for the partitioning column in the description for each partition. This is different from range partitioning, where a
range of values is associated with a partition, and from hash partitioning, where the user has no control of the row to
partition mapping. List partitioning allows unordered and unrelated sets of data to be grouped and organized together very naturally The following example creates a table with list partitioning Create table customers (custcode number(5), Name varchar2(20), Addr varchar2(10,2), City varchar2(20), Bal number(10,2)) Partition by list (city),Partition north_India values (‘DELHI’,’CHANDIGARH’),Partition east_India values (‘KOLKOTA’,’PATNA’),Partition south_India values (‘HYDERABAD’,’BANGALORE’, ’CHENNAI’),Partition west India values (‘BOMBAY’,’GOA’); If a row is inserted in the above table then oracle maps the value of city column and whichever partition list matches the
city column the row is stored in that partition.
COMPOSITE PARTITONING
Composite partitioning partitions data using the range method, and within each partition, subpartitions it using the hash method. Composite partitions are ideal for both historical data and striping, and provide improved manageability of range partitioning and data placement, as well as the parallelism advantages of hash partitioning.
When creating composite partitions, you specify the following:
Partitioning method: range
Partitioning column(s)
Partition descriptions identifying partition bounds
Subpartitioning method: hash
Subpartitioning column(s)
Number of subpartitions for each partition or descriptions of subpartitions
The following statement creates a composite-partitioned table. In this example, three range partitions are created, each containing eight subpartitions. Because the subpartitions are not named, system generated names are assigned, but the STORE IN clause distributes them across the 4 specified tablespaces (tab1, ...,tab4). CREATE TABLE PRODUCTS (partno NUMBER, description VARCHAR(32), costprice NUMBER) PARTITION BY RANGE (partno) SUBPARTITION BY HASH(description) SUBPARTITIONS 8 STORE IN (tab1, tab2, tab3, tab4) (PARTITION p1 VALUES LESS THAN (100), PARTITION p2 VALUES LESS THAN (200), PARTITION p3 VALUES LESS THAN (MAXVALUE));
ALTERING PARTITION TABLES To add a partition You can add add a new partition to the "high" end (the point after the last existing partition). To add a partition
at the beginning or in the middle of a table, use the SPLIT PARTITION clause. For example to add a partition to sales table give the following command. alter table sales add partition p6 values less than (1996); To add a partition to a Hash Partition table give the following command. Alter table products add partition; Then Oracle adds a new partition whose name is system generated and it is created in the default tablespace.
To add a partition by user define name and in your specified tablespace give the following command. Alter table products add partition p5 tablespace u5; To add a partition to a List partition table give the following command. alter table customers add partition central_India values (‘BHOPAL’,’NAGPUR’);
Any value in the set of literal values that describe the partition(s) being added must not exist in any of the other partitions of the table.

Coalescing Partitions
Coalescing partitions is a way of reducing the number of partitions in a hash-partitioned table, or the number of subpartitions in a composite-partitioned table. When a hash partition is coalesced, its contents are redistributed into one or more remaining partitions determined by the hash function. The specific partition that is coalesced is selected by Oracle, and is dropped after its contents have been redistributed.
To coalesce a hash partition give the following statement.
Alter table products coalesce partition;
This reduces by one the number of partitions in the table products.
DROPPING PARTITIONS
To drop a partition from Range Partition table, List Partition or Composite Partition table give the following command.
Alter table sales drop partition p5;
Once you have drop the partition and if you have created a global index on the table. Then you have to rebuild the global index after dropping the partition by giving the following statement.
Alter index sales_ind rebuild;
To avoid rebuilding of indexes after dropping of the partitions you can also first delete all the records and then dropthe partition like this
Delete from sales where year=1994;
Alter table sales drop partition p4;
This method is most appropriate for small tables, or for large tables when the partition being dropped contains a small percentage of the total data in the table.
Another method of dropping partitions is give the following statement.ALTER TABLE sales DROP PARTITION p5 UPDATE GLOBAL INDEXES; This causes the global index to be updated at the time the partition is dropped.

Exchanging a Range, Hash, or List Partition
To exchange a partition of a range, hash, or list-partitioned table with a nonpartitioned table, or the reverse, use the ALTER TABLE ... EXCHANGE PARTITION statement. An example of converting a partition into a nonpartitioned table follows. In this example, table stocks can be range, hash, or list partitioned. ALTER TABLE stocks EXCHANGE PARTITION p3 WITH stock_table_3;
Merging Partitions
Use the ALTER TABLE ... MERGE PARTITIONS statement to merge the contents of two partitions into one partition. Te two original partitions are dropped, as are any corresponding local indexes.
You cannot use this statement for a hash-partitioned table or for hash subpartitions of a composite-partitioned table.
You can only merged two adjacent partitions, you cannot merge non adjacent partitions.
For example the merge the partition p2 and p3 into one partition p23 give the following statement.
Alter table sales merge partition p2 and p3 into
partition p23;

Modifying Partitions: Adding Values
Use the MODIFY PARTITION ... ADD VALUES clause of the ALTER TABLE statement to extend the value list of an existing partition. Literal values being added must not have been included in any other partition's value list. The partition value list for any corresponding local index partition is correspondingly extended, and any global index, or global or local index partitions, remain usable.
The following statement adds a new set of cities ('KOCHI', 'MANGALORE') to an existing partition list. ALTER TABLE customers MODIFY PARTITION south_india ADD VALUES ('KOCHI', 'MANGALORE');
Modifying Partitions: Dropping Values
Use the MODIFY PARTITION ... DROP VALUES clause of the ALTER TABLE statement to remove literal values from the value list of an existing partition. The statement is always executed with validation, meaning that it checks to see if any rows exist in the partition that correspond to the set of values being dropped. If any such rows are found then Oracle returns an error message and the operation fails. When necessary, use a DELETE statement to delete corresponding rows from the table before attempting to drop values.
You cannot drop all literal values from the value list describing the partition. You must use the ALTER TABLE ... DROP PARTITION statement instead.
The partition value list for any corresponding local index partition reflects the new value list, and any global index, or global or local index partitions, remain usable.
The statement below drops a set of cities (‘KOCHI' and 'MANGALORE') from an existing partition value list. ALTER TABLE customers MODIFY PARTITION south_india DROP VALUES (‘KOCHI’,’MANGALORE’);
SPLITTING PARTITIONS You can split a single partition into two partitions. For example to split the partition p5 of sales table into two partitions give the following command. Alter table sales split partition p5 into (Partition p6 values less than (1996), Partition p7 values less then (MAXVALUE));
TRUNCATING PARTITON Truncating a partition will delete all rows from the partition. To truncate a partition give the following statement Alter table sales truncate partition p5;
LISTING INFORMATION ABOUT PARTITION TABLES To see how many partitioned tables are there in your schema give the following statement Select * from user_part_tables; To see on partition level partitioning information Select * from user_tab_partitions;

My first step in Writing