1. Field length: Be aware of Database field length for corresponding UI fields. For text fields and text boxes, length must be smaller than Database field length (about 10 characters smaller for every 100 char in db). This is needed for dealing with multi-byte character set.
2. Blank space: No space before 'colon' and 'comma' - use space after comma.
3. Date: Proper formatting of date
4. Access Control/Privilege Checking: For important business information which depends on user privilege, make sure, list that user sees, are based on their access privileges
5. SQL injection: Use bind variables to avoid SQL injection
6. Double submission problems: What happens when user re-submit the request - handle properly.
7.27.2010
Lock
Lock: A lock is a mechanism used by Oracle that protects system resources, such as data, from being modified by more than one user/process at a time.
-ddl lock
-dml lock
-Internal locks and latches
-Breakable Parse Locks
Common database lock types are:
Shared, eXclusive, Row Share, Row eXclusive, etc.
Transaction: A transaction is what initiates the lock on a row, multiple rows, or an entire table.
Locks are released when COMMIT or ROLLBACK is issued at the end of a transaction.
Query To Find Out the Lock:
SQL> select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS "Blocking Status"
,s1.serial# "Blocking Session's Serial"
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
SQL> SELECT a.owner,a.object_name,b.sid,b.serial#,b.username,b.status
FROM dba_objects a, v$session b, v$locked_object c
WHERE a.object_id = c.object_id AND b.sid = c.session_id;
SQL> select sid,type,id1,lmode,request from v$lock;
Here, ID1 is the object_id.
Identifying the locked object:
SQL> select object_name from dba_objects where object_id=52420 ;
Identifying the locked row:
select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where sid=483;
Q: How to manage table lock?
Step-1: Kill all the locking Session:
ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
Step-2: run commit/rollback
Hanging: Sometimes the session hangs until the session that has put a lock on the row in question commits (or rollbacks). This waiting is recorded in v$session_wait:
SQL> select event, seconds_in_wait, sid from v$session_wait where sid in (483,452);
-ddl lock
-dml lock
-Internal locks and latches
-Breakable Parse Locks
Common database lock types are:
Shared, eXclusive, Row Share, Row eXclusive, etc.
Transaction: A transaction is what initiates the lock on a row, multiple rows, or an entire table.
Locks are released when COMMIT or ROLLBACK is issued at the end of a transaction.
Query To Find Out the Lock:
SQL> select s1.username || '@' || s1.machine || ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS "Blocking Status"
,s1.serial# "Blocking Session's Serial"
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;
SQL> SELECT a.owner,a.object_name,b.sid,b.serial#,b.username,b.status
FROM dba_objects a, v$session b, v$locked_object c
WHERE a.object_id = c.object_id AND b.sid = c.session_id;
SQL> select sid,type,id1,lmode,request from v$lock;
Here, ID1 is the object_id.
Identifying the locked object:
SQL> select object_name from dba_objects where object_id=52420 ;
Identifying the locked row:
select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where sid=483;
Q: How to manage table lock?
Step-1: Kill all the locking Session:
ALTER SYSTEM KILL SESSION 'SID,SERIAL#';
Step-2: run commit/rollback
Hanging: Sometimes the session hangs until the session that has put a lock on the row in question commits (or rollbacks). This waiting is recorded in v$session_wait:
SQL> select event, seconds_in_wait, sid from v$session_wait where sid in (483,452);
7.19.2010
SQL query
When DB load high, we can try to find out the current situation by these queries:
--Top queries in last 5 min
SELECT * FROM
(select parsing_schema_name as users, sql_id, sql_text
,executions, disk_reads,buffer_gets
,DECODE (executions,0,0,round(((cpu_time/1000000)/executions),2)) "CPU Time"
,DECODE (executions,0,0,round(((elapsed_time/1000000)/executions),2)) "Elapsed Time"
FROM v$sqlarea
WHERE LAST_ACTIVE_TIME BETWEEN (sysdate - 5/1440) AND sysdate
AND parsing_schema_name NOT IN ('SYS', 'SYSTEM')
ORDER BY "CPU Time" DESC) x
WHERE rownum <=5;
--Top waiting session
SELECT sess.sid, sess.serial#, sess.username, sess.machine, SUM(ash.wait_time + ash.time_waited) wait_time
FROM v$active_session_history ash, v$session sess
WHERE ash.session_id = sess.sid
AND ash.sample_time BETWEEN (sysdate - 15/1440) AND sysdate
GROUP BY sess.sid, sess.serial#, sess.username, sess.machine
ORDER BY 4 DESC;
--Kill session
alter system kill session 'sid,serial#' ;
--Top queries in last 5 min
SELECT * FROM
(select parsing_schema_name as users, sql_id, sql_text
,executions, disk_reads,buffer_gets
,DECODE (executions,0,0,round(((cpu_time/1000000)/executions),2)) "CPU Time"
,DECODE (executions,0,0,round(((elapsed_time/1000000)/executions),2)) "Elapsed Time"
FROM v$sqlarea
WHERE LAST_ACTIVE_TIME BETWEEN (sysdate - 5/1440) AND sysdate
AND parsing_schema_name NOT IN ('SYS', 'SYSTEM')
ORDER BY "CPU Time" DESC) x
WHERE rownum <=5;
--Top waiting session
SELECT sess.sid, sess.serial#, sess.username, sess.machine, SUM(ash.wait_time + ash.time_waited) wait_time
FROM v$active_session_history ash, v$session sess
WHERE ash.session_id = sess.sid
AND ash.sample_time BETWEEN (sysdate - 15/1440) AND sysdate
GROUP BY sess.sid, sess.serial#, sess.username, sess.machine
ORDER BY 4 DESC;
--Kill session
alter system kill session 'sid,serial#' ;
--Kill multiple session
SELECT 'alter system kill session '''|| a.sid||','||a.serial# ||''';' ses_serial
FROM v$session a,v$process b
WHERE a.username IS NOT NULL AND a.paddr=b.addr AND a.type='USER'
AND a.logon_time> = sysdate- 1/48 --last 30 min
AND a.username = 'PROD7'
AND a.osuser ='sqa'
;
7.15.2010
Partitioning ODD_DATA table
Steps-
1. export dump
2. rename table (and rename index if necessary)
3. create new partitioned table
4. import table data into new partitioned table - Data Only
5. Add Index and Constraints the way you want it
STEP-1: Export TABLE & Data
i. Make a directory DBDUMPS on /da02, and Give permission to PRD7
ii. Export old_data
expdp prd7/*** tables=ODD_DATA DIRECTORY=DBDUMPSSS DUMPFILE=DFF01-PRD7-20100715_%U.dmp FILESIZE=10G logfile=DFF01-PRD7-20100715_LOG.log
STEP-2: Rename Table
ALTER TABLE PROD7.ODD_DATA RENAME TO ODD_DATA_OLD;
STEP-3: Create new Partitioned Table
CREATE TABLE PRD7.ODD_DATA (
id NUMBER (10) NOT NULL
, form_type VARCHAR2 (16) NOT NULL
, class_name VARCHAR2 (64) NOT NULL
, view_technology VARCHAR2 (30) DEFAULT 'freemarker'
, archive_time TIMESTAMP(6)
, changed_by_login_id NUMBER (10)
, changed_by_login_name VARCHAR2 (64)
, changed_by_first_name VARCHAR2 (20)
, changed_by_last_name VARCHAR2 (20)
, changed_by_title VARCHAR2 (64)
, individual_id NUMBER (10)
, individual_first_name VARCHAR2 (20)
, individual_last_name VARCHAR2 (20)
, form_id VARCHAR2 (30)
, version VARCHAR2 (20)
, prov_id NUMBER (10)
, prov_code VARCHAR2 (16)
, prov_name VARCHAR2 (64)
, pgm_id NUMBER (10)
, pgm_name VARCHAR2 (64)
, tz VARCHAR2 (32)
, clob_data CLOB
, form_name VARCHAR2 (150)
, related_user_login_id NUMBER (10)
, related_user_login_name VARCHAR2 (64)
, related_user_first_name VARCHAR2 (20)
, related_user_last_name VARCHAR2 (20)
, related_user_tite VARCHAR2 (64)
, test_form NUMBER (1) DEFAULT 0
)
PARTITION BY RANGE (archive_time)
(
PARTITION OD_TILL_2006 VALUES LESS THAN (TIMESTAMP'2007-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_200701_200712 VALUES LESS THAN (TIMESTAMP'2008-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_200801_200812 VALUES LESS THAN (TIMESTAMP'2009-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_200901_200912 VALUES LESS THAN (TIMESTAMP'2010-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_201001_201003 VALUES LESS THAN (TIMESTAMP'2010-04-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_201004_201006 VALUES LESS THAN (TIMESTAMP'2010-07-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_201007_201009 VALUES LESS THAN (TIMESTAMP'2010-10-01 00:00:00') TABLESPACE DATA01_16K NOCOMPRESS,
PARTITION OD_201010_201012 VALUES LESS THAN (TIMESTAMP'2011-01-01 00:00:00') TABLESPACE DATA01_16K NOCOMPRESS,
PARTITION OD_AFTER_2010 VALUES LESS THAN (MAXVALUE) TABLESPACE DATA01_16K NOCOMPRESS
);
STEP-4: Import
impdp system/***** DIRECTORY=DBDUMPSSS CONTENT=DATA_ONLY DUMPFILE=DFF01-PRD7-20100715_%U.dmp PARALLEL=3
STEP-5: ADD Primary Key and INDEXES
SQL> ALTER TABLE PRD7.ODD_DATA ADD primary key (id);
SQL> CREATE INDEX PRD7.ODD_DATA_PGM_INDV_I1 ON PRD7.ODD_DATA ( pgm_id, individual_id );
SQL> CREATE INDEX PRD7.OD_PROV_FTYPE_ID_FID_I1 ON PRD7.ODD_DATA ( prov_id, UPPER(FORM_TYPE), id, form_id );
----------------------------------- END --------------------------------------------------------------
Checking Each Partition Data:
select count(*) from prd7.odd_data;
SQL> col HIGH_VALUE format a40
SQL> SELECT partition_name PART_NAME, tablespace_name TBSP_NAME, high_value
FROM user_tab_partitions WHERE table_name = 'ODD_DATA';
SQL> SELECT max(ARCHIVE_TIME) FROM ODD_DATA PARTITION (OD_201001_201003);
SQL> SELECT min(ARCHIVE_TIME) FROM ODD_DATA PARTITION (OD_201001_201003);
SQL> SELECT max(ARCHIVE_TIME) FROM ODD_DATA PARTITION (OD_201004_201006);
SQL> SELECT min(ARCHIVE_TIME) FROM ODD_DATA PARTITION (OD_201004_201006);
AFTER Some Days....
SQL> DROP TABLE PRD7.ODD_DATA_OLD cascade constraints;
1. export dump
2. rename table (and rename index if necessary)
3. create new partitioned table
4. import table data into new partitioned table - Data Only
5. Add Index and Constraints the way you want it
STEP-1: Export TABLE & Data
i. Make a directory DBDUMPS on /da02, and Give permission to PRD7
ii. Export old_data
expdp prd7/*** tables=ODD_DATA DIRECTORY=DBDUMPSSS DUMPFILE=DFF01-PRD7-20100715_%U.dmp FILESIZE=10G logfile=DFF01-PRD7-20100715_LOG.log
STEP-2: Rename Table
ALTER TABLE PROD7.ODD_DATA RENAME TO ODD_DATA_OLD;
STEP-3: Create new Partitioned Table
CREATE TABLE PRD7.ODD_DATA (
id NUMBER (10) NOT NULL
, form_type VARCHAR2 (16) NOT NULL
, class_name VARCHAR2 (64) NOT NULL
, view_technology VARCHAR2 (30) DEFAULT 'freemarker'
, archive_time TIMESTAMP(6)
, changed_by_login_id NUMBER (10)
, changed_by_login_name VARCHAR2 (64)
, changed_by_first_name VARCHAR2 (20)
, changed_by_last_name VARCHAR2 (20)
, changed_by_title VARCHAR2 (64)
, individual_id NUMBER (10)
, individual_first_name VARCHAR2 (20)
, individual_last_name VARCHAR2 (20)
, form_id VARCHAR2 (30)
, version VARCHAR2 (20)
, prov_id NUMBER (10)
, prov_code VARCHAR2 (16)
, prov_name VARCHAR2 (64)
, pgm_id NUMBER (10)
, pgm_name VARCHAR2 (64)
, tz VARCHAR2 (32)
, clob_data CLOB
, form_name VARCHAR2 (150)
, related_user_login_id NUMBER (10)
, related_user_login_name VARCHAR2 (64)
, related_user_first_name VARCHAR2 (20)
, related_user_last_name VARCHAR2 (20)
, related_user_tite VARCHAR2 (64)
, test_form NUMBER (1) DEFAULT 0
)
PARTITION BY RANGE (archive_time)
(
PARTITION OD_TILL_2006 VALUES LESS THAN (TIMESTAMP'2007-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_200701_200712 VALUES LESS THAN (TIMESTAMP'2008-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_200801_200812 VALUES LESS THAN (TIMESTAMP'2009-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_200901_200912 VALUES LESS THAN (TIMESTAMP'2010-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_201001_201003 VALUES LESS THAN (TIMESTAMP'2010-04-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_201004_201006 VALUES LESS THAN (TIMESTAMP'2010-07-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_201007_201009 VALUES LESS THAN (TIMESTAMP'2010-10-01 00:00:00') TABLESPACE DATA01_16K NOCOMPRESS,
PARTITION OD_201010_201012 VALUES LESS THAN (TIMESTAMP'2011-01-01 00:00:00') TABLESPACE DATA01_16K NOCOMPRESS,
PARTITION OD_AFTER_2010 VALUES LESS THAN (MAXVALUE) TABLESPACE DATA01_16K NOCOMPRESS
);
STEP-4: Import
impdp system/***** DIRECTORY=DBDUMPSSS CONTENT=DATA_ONLY DUMPFILE=DFF01-PRD7-20100715_%U.dmp PARALLEL=3
STEP-5: ADD Primary Key and INDEXES
SQL> ALTER TABLE PRD7.ODD_DATA ADD primary key (id);
SQL> CREATE INDEX PRD7.ODD_DATA_PGM_INDV_I1 ON PRD7.ODD_DATA ( pgm_id, individual_id );
SQL> CREATE INDEX PRD7.OD_PROV_FTYPE_ID_FID_I1 ON PRD7.ODD_DATA ( prov_id, UPPER(FORM_TYPE), id, form_id );
----------------------------------- END --------------------------------------------------------------
Checking Each Partition Data:
select count(*) from prd7.odd_data;
SQL> col HIGH_VALUE format a40
SQL> SELECT partition_name PART_NAME, tablespace_name TBSP_NAME, high_value
FROM user_tab_partitions WHERE table_name = 'ODD_DATA';
SQL> SELECT max(ARCHIVE_TIME) FROM ODD_DATA PARTITION (OD_201001_201003);
SQL> SELECT min(ARCHIVE_TIME) FROM ODD_DATA PARTITION (OD_201001_201003);
SQL> SELECT max(ARCHIVE_TIME) FROM ODD_DATA PARTITION (OD_201004_201006);
SQL> SELECT min(ARCHIVE_TIME) FROM ODD_DATA PARTITION (OD_201004_201006);
AFTER Some Days....
SQL> DROP TABLE PRD7.ODD_DATA_OLD cascade constraints;
Patitioned Index
Oracle 10.2g provides 3 types of partitioned indexes:
1) Local partitioned index:
Miscellaneous Information about Creating Indexes on Partitioned Tables
1) Local partitioned index:
- Index is partitioned as the same manner as table was partitioned.
- Each partition of a local index corresponds to one and only one partition of the underlying table.
- The index is partitioned with a different partitioned key than the table is partitioned.
- This one is identical to an index on a non-partitioned table. The index structure is not partitioned.
Miscellaneous Information about Creating Indexes on Partitioned Tables
You can create bitmap indexes on partitioned tables, with the restriction that the bitmap indexes must be local to the partitioned table. They cannot be global indexes.
Global indexes can be unique. Local indexes can only be unique if the partitioning key is a part of the index key.
Using Partitioned Indexes in OLTP Applications
Here are a few guidelines for OLTP applications:
-
Global indexes and unique, local indexes provide better performance than nonunique local indexes because they minimize the number of index partition probes.
-
Local indexes offer better availability when there are partition or subpartition maintenance operations on the table.
-
Hash-partitioned global indexes offer better performance by spreading out contention when the index is monotonically growing. In other words, most of the index insertions occur only on the right edge of an index.
Using Partitioned Indexes in Data Warehousing and DSS Applications
Here are a few guidelines for data warehousing and DSS applications:
-
Local indexes are preferable because they are easier to manage during data loads and during partition-maintenance operations.
-
Local indexes can improve performance because many index partitions can be scanned in parallel by range queries on the index key.
Example-2:
Here I am putting different partitions on different tablespaces and making the tablespaces Compressed.
CREATE TABLE ARCHIVE_DATA (
id NUMBER (10) NOT NULL
, form_type VARCHAR2 (16) NOT NULL
, class_name VARCHAR2 (64) NOT NULL
, view_technology VARCHAR2 (30) DEFAULT 'freemarker'
, archive_time TIMESTAMP(6)
, changed_by_login_id NUMBER (10)
, individual_id NUMBER (10)
, form_id VARCHAR2 (30)
, version VARCHAR2 (20)
, prov_id NUMBER (10)
, pgm_id NUMBER (10)
, clob_data CLOB
, form_name VARCHAR2 (150)
, related_user_login_id NUMBER (10)
, test_form NUMBER (1) DEFAULT 0
)
PARTITION BY RANGE (archive_time)
(
PARTITION OD_TILL_2006 VALUES LESS THAN (TIMESTAMP'2007-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_200701_200712 VALUES LESS THAN (TIMESTAMP'2008-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_200801_200812 VALUES LESS THAN (TIMESTAMP'2009-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_200901_200912 VALUES LESS THAN (TIMESTAMP'2010-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_201001_201003 VALUES LESS THAN (TIMESTAMP'2010-04-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_201004_201006 VALUES LESS THAN (TIMESTAMP'2010-07-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_201007_201009 VALUES LESS THAN (TIMESTAMP'2010-10-01 00:00:00') TABLESPACE DATA01_16K NOCOMPRESS,
PARTITION OD_201010_201012 VALUES LESS THAN (TIMESTAMP'2011-01-01 00:00:00') TABLESPACE DATA01_16K NOCOMPRESS,
PARTITION OD_AFTER_2010 VALUES LESS THAN (MAXVALUE) TABLESPACE DATA01_16K NOCOMPRESS
);
CREATE TABLE ARCHIVE_DATA (
id NUMBER (10) NOT NULL
, form_type VARCHAR2 (16) NOT NULL
, class_name VARCHAR2 (64) NOT NULL
, view_technology VARCHAR2 (30) DEFAULT 'freemarker'
, archive_time TIMESTAMP(6)
, changed_by_login_id NUMBER (10)
, individual_id NUMBER (10)
, form_id VARCHAR2 (30)
, version VARCHAR2 (20)
, prov_id NUMBER (10)
, pgm_id NUMBER (10)
, clob_data CLOB
, form_name VARCHAR2 (150)
, related_user_login_id NUMBER (10)
, test_form NUMBER (1) DEFAULT 0
)
PARTITION BY RANGE (archive_time)
(
PARTITION OD_TILL_2006 VALUES LESS THAN (TIMESTAMP'2007-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_200701_200712 VALUES LESS THAN (TIMESTAMP'2008-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_200801_200812 VALUES LESS THAN (TIMESTAMP'2009-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_200901_200912 VALUES LESS THAN (TIMESTAMP'2010-01-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_201001_201003 VALUES LESS THAN (TIMESTAMP'2010-04-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_201004_201006 VALUES LESS THAN (TIMESTAMP'2010-07-01 00:00:00') TABLESPACE X_DATA01_16K COMPRESS,
PARTITION OD_201007_201009 VALUES LESS THAN (TIMESTAMP'2010-10-01 00:00:00') TABLESPACE DATA01_16K NOCOMPRESS,
PARTITION OD_201010_201012 VALUES LESS THAN (TIMESTAMP'2011-01-01 00:00:00') TABLESPACE DATA01_16K NOCOMPRESS,
PARTITION OD_AFTER_2010 VALUES LESS THAN (MAXVALUE) TABLESPACE DATA01_16K NOCOMPRESS
);
Different Example-1:
---- Simple Range Partition On ARCHIVE_DATA table: ----
CREATE TABLE ARCHIVE_DATA
(
id NUMBER (10) NOT NULL
, form_type VARCHAR2 (16) NOT NULL
, class_name VARCHAR2 (64) NOT NULL
, view_technology VARCHAR2 (30) DEFAULT 'freemarker'
, archive_time TIMESTAMP(6)
, changed_by_login_id NUMBER (10)
, individual_id NUMBER (10)
, form_id VARCHAR2 (30)
, prov_id NUMBER (10)
, clob_data CLOB
, form_name VARCHAR2 (150)
)
PARTITION BY RANGE (archive_time)
(
PARTITION OD_TILL_2006 VALUES LESS THAN (TIMESTAMP'2007-01-01 00:00:00'),
PARTITION OD_200701_200712 VALUES LESS THAN (TIMESTAMP'2008-01-01 00:00:00'),
PARTITION OD_200801_200812 VALUES LESS THAN (TIMESTAMP'2009-01-01 00:00:00'),
PARTITION OD_200901_200912 VALUES LESS THAN (TIMESTAMP'2010-01-01 00:00:00') ,
PARTITION OD_201001_201003 VALUES LESS THAN (TIMESTAMP'2010-04-01 00:00:00') ,
PARTITION OD_201004_201006 VALUES LESS THAN (TIMESTAMP'2010-07-01 00:00:00') ,
PARTITION OD_201007_201009 VALUES LESS THAN (TIMESTAMP'2010-10-01 00:00:00') ,
PARTITION OD_201010_201012 VALUES LESS THAN (TIMESTAMP'2011-01-01 00:00:00') ,
PARTITION OD_AFTER_2010 VALUES LESS THAN (MAXVALUE)
);
---- Simple Range Partition On SALES_RANGE table: ----
CREATE TABLE sales_range
(
salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE
)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
);
---- Simple LIST Partition On SALES_LIST table: ----
CREATE TABLE sales_list
(
salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE
)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois'),
PARTITION sales_other VALUES(DEFAULT)
);
---- Composite Partitioning Range-List Example: ----
CREATE TABLE bimonthly_regional_sales
(
deptno NUMBER,
item_no VARCHAR2(20),
txn_date DATE,
txn_amount NUMBER,
state VARCHAR2(2)
)
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
SUBPARTITION TEMPLATE
(
SUBPARTITION east VALUES('NY', 'VA', 'FL') TABLESPACE ts1,
SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE ts2,
SUBPARTITION central VALUES('IL', 'TX', 'MO') TABLESPACE ts3
)
(
PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE('1-MAR-2000','DD-MON-YYYY')),
PARTITION marapr_2000 VALUES LESS THAN (TO_DATE('1-MAY-2000','DD-MON-YYYY')),
PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE('1-JUL-2000','DD-MON-YYYY')
)
);
CREATE TABLE ARCHIVE_DATA
(
id NUMBER (10) NOT NULL
, form_type VARCHAR2 (16) NOT NULL
, class_name VARCHAR2 (64) NOT NULL
, view_technology VARCHAR2 (30) DEFAULT 'freemarker'
, archive_time TIMESTAMP(6)
, changed_by_login_id NUMBER (10)
, individual_id NUMBER (10)
, form_id VARCHAR2 (30)
, prov_id NUMBER (10)
, clob_data CLOB
, form_name VARCHAR2 (150)
)
PARTITION BY RANGE (archive_time)
(
PARTITION OD_TILL_2006 VALUES LESS THAN (TIMESTAMP'2007-01-01 00:00:00'),
PARTITION OD_200701_200712 VALUES LESS THAN (TIMESTAMP'2008-01-01 00:00:00'),
PARTITION OD_200801_200812 VALUES LESS THAN (TIMESTAMP'2009-01-01 00:00:00'),
PARTITION OD_200901_200912 VALUES LESS THAN (TIMESTAMP'2010-01-01 00:00:00') ,
PARTITION OD_201001_201003 VALUES LESS THAN (TIMESTAMP'2010-04-01 00:00:00') ,
PARTITION OD_201004_201006 VALUES LESS THAN (TIMESTAMP'2010-07-01 00:00:00') ,
PARTITION OD_201007_201009 VALUES LESS THAN (TIMESTAMP'2010-10-01 00:00:00') ,
PARTITION OD_201010_201012 VALUES LESS THAN (TIMESTAMP'2011-01-01 00:00:00') ,
PARTITION OD_AFTER_2010 VALUES LESS THAN (MAXVALUE)
);
---- Simple Range Partition On SALES_RANGE table: ----
CREATE TABLE sales_range
(
salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_amount NUMBER(10),
sales_date DATE
)
PARTITION BY RANGE(sales_date)
(
PARTITION sales_jan2000 VALUES LESS THAN(TO_DATE('02/01/2000','DD/MM/YYYY')),
PARTITION sales_feb2000 VALUES LESS THAN(TO_DATE('03/01/2000','DD/MM/YYYY')),
PARTITION sales_mar2000 VALUES LESS THAN(TO_DATE('04/01/2000','DD/MM/YYYY')),
PARTITION sales_apr2000 VALUES LESS THAN(TO_DATE('05/01/2000','DD/MM/YYYY'))
);
---- Simple LIST Partition On SALES_LIST table: ----
CREATE TABLE sales_list
(
salesman_id NUMBER(5),
salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE
)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois'),
PARTITION sales_other VALUES(DEFAULT)
);
---- Composite Partitioning Range-List Example: ----
CREATE TABLE bimonthly_regional_sales
(
deptno NUMBER,
item_no VARCHAR2(20),
txn_date DATE,
txn_amount NUMBER,
state VARCHAR2(2)
)
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
SUBPARTITION TEMPLATE
(
SUBPARTITION east VALUES('NY', 'VA', 'FL') TABLESPACE ts1,
SUBPARTITION west VALUES('CA', 'OR', 'HI') TABLESPACE ts2,
SUBPARTITION central VALUES('IL', 'TX', 'MO') TABLESPACE ts3
)
(
PARTITION janfeb_2000 VALUES LESS THAN (TO_DATE('1-MAR-2000','DD-MON-YYYY')),
PARTITION marapr_2000 VALUES LESS THAN (TO_DATE('1-MAY-2000','DD-MON-YYYY')),
PARTITION mayjun_2000 VALUES LESS THAN (TO_DATE('1-JUL-2000','DD-MON-YYYY')
)
);
Q: What is partition?
Partition:
Partitioning enables tables and indexes to be split into smaller, more manageable components and is a key requirement for any large database with high performance and high availability requirements.
Benefits:
- The primary purpose of partition is to improve performance of queries.
- Making the administration task of big table easier.
- Fast deleting of data based on partition.
- Faster archiving.
- Faster Data movement across other table.
- Efficient backup strategy.
- Better Management of data life cycle.
There are 6 different partitioning techniques on Oracle 10g:
1) Range Partition:
Partitioning enables tables and indexes to be split into smaller, more manageable components and is a key requirement for any large database with high performance and high availability requirements.
Benefits:
- The primary purpose of partition is to improve performance of queries.
- Making the administration task of big table easier.
- Fast deleting of data based on partition.
- Faster archiving.
- Faster Data movement across other table.
- Efficient backup strategy.
- Better Management of data life cycle.
There are 6 different partitioning techniques on Oracle 10g:
1) Range Partition:
- Most common and used whenever your query/administration is based on date column.
- Suppose you want to archive data one month old or your database query lies within a month then you can choose range partition.
- If within your column data contains a list of values like, a department can be divided into several lists - CSE, EEE, MCE, CIVIL. Then you might choose to partition list wise.
- If you could not decide either to be range or list then you can choose hash partition.
- Like id of a table as you dont have idea about its range so you might think hash while choosing partitioning.
- Here, oracle internal hash algorithm is applied to the partitioned key and the row lies within a partition.
- The table is first ranged partitioned and then further each partition is sub partitioned by hash values.
- The table is first ranged partitioned and then further each partition is sub partitioned by list values.
7.06.2010
ORA-01692
ORA-01692: unable to extend lob segment PROD7.SYS_LOB0000151343C00022$$
I was trying to execute the following procedure :
SQL> EXECUTE DBMS_REDEFINITION.START_REDEF_TABLE('PROD7', 'OLD_DATA', 'OLD_DATA_RD');
& got ORA-01692: unable to extend lob segment PROD7.SYS_LOB0000151343C00022$$
It means, I need to add more space on that Tablespace which contains this object PROD7.SYS_LOB0000151343C00022$$.
select owner,segment_name,segment_type,tablespace_name
from dba_segments
where
--lower(tablespace_name) like lower('%DATA02%') and
lower(segment_name) like lower('%SYS_LOB0000151343C00022%')
order by owner, segment_name;
By this query I can get the Tablespace name.
Then I add one more datafile on this Tablespace.
I was trying to execute the following procedure :
SQL> EXECUTE DBMS_REDEFINITION.START_REDEF_TABLE('PROD7', 'OLD_DATA', 'OLD_DATA_RD');
& got ORA-01692: unable to extend lob segment PROD7.SYS_LOB0000151343C00022$$
It means, I need to add more space on that Tablespace which contains this object PROD7.SYS_LOB0000151343C00022$$.
select owner,segment_name,segment_type,tablespace_name
from dba_segments
where
--lower(tablespace_name) like lower('%DATA02%') and
lower(segment_name) like lower('%SYS_LOB0000151343C00022%')
order by owner, segment_name;
By this query I can get the Tablespace name.
Then I add one more datafile on this Tablespace.
Move Datafile
If the datafiles that need to be changed or moved do not belong to SYSTEM tablespaces, and do not contain active rollback segments or temporary segments, that does not require database instance to be shutdown.
Old Location - /oradata/datafiles/THRP/DATA02_01.dbf
New Location - /oradata3/DATAFILES/DATA02_01.dbf
Old Location - /oradata/datafiles/THRP/DATA02_01.dbf
New Location - /oradata3/DATAFILES/DATA02_01.dbf
select d.tablespace_name,d.file_name from DBA_DATA_FILES d where d.tablespace_name ='DATA02' order by d.tablespace_name;
DataFiles Moving:
------------------------
connect as sysdba..
SQL> ALTER TABLESPACE DATA02 read only;
SQL> ALTER TABLESPACE DATA02 OFFLINE;
Copy from OS level ..
cd /oradata/datafiles/THRP/
cp DATA02_01.dbf /oradata3/DATAFILES/DATA02_01.dbf
SQL>
ALTER TABLESPACE DATA02
RENAME DATAFILE '/oradata/datafiles/THRP/DATA02_01.dbf' TO '/oradata3/DATAFILES/DATA02_01.dbf';
SQL> ALTER TABLESPACE DATA02 ONLINE;
If you get err.......................
ERROR at line 1:
ORA-01113: file 26 needs media recovery
ORA-01110: data file 26: '/oradata3/DATAFILES/DATA02_01.dbf'
SQL> RECOVER DATAFILE '/oradata3/DATAFILES/DATA02_01.dbf';
..............................................
SQL> ALTER TABLESPACE DATA02 read write;
--And at last drop the datafile:
oracle@dbservername ~$ cd /oradata/datafiles/THRP/
oracle@dbservername THRP$ rm DATA02_01.dbf
More..
--http://wiki.answers.com/Q/How_do_you_rename_a_datafile_in_Oracle
--http://www.adp-gmbh.ch/ora/concepts/datafiles.html
Subscribe to:
Posts (Atom)