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
);

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')
)
);

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:

• 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.

2) List 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.

3) Hash partition:

• 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.

4) Range-Hash Partition:

• The table is first ranged partitioned and then further each partition is sub partitioned by hash values.

5) Range-List Partition:

• The table is first ranged partitioned and then further each partition is sub partitioned by list values.

6) There is another partitioning choice of index organized table which can be partitioned by range, hash or list.

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.

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

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

IN versus EXIST

When you want to make a query faster you can look -

EXISTS is more faster than IN.
Because EXISTS returns a Boolean value whereas IN returns a value.

Example:

Wrong:
SELECT COUNT(distinct emp.id) AS emp_id FROM emp
LEFT JOIN program_client ON (emp.client_id = program_client.client_id
AND emp.prov_id = program_client.prov_id)
WHERE program_client.id IN
(
SELECT pc_id FROM pgm_role ar
WHERE ar.login_id = 100 AND ar.role = 1
)
AND program_client.status = 1
AND emp.status IN (1,6) AND emp.prov_id = 580;


Right:
SELECT COUNT(emp.id) AS emp_id FROM emp
WHERE EXISTS
(
SELECT psr.client_id FROM TABLE2 psr
(WHERE emp.client_id = psr.client_id AND psr.login_id = 100
AND psr.role = 1 AND psr.status = 1
)
AND emp.status IN (1,6) AND emp.prov_id = 580;

So -

• Syntax: SELECT columns FROM tables WHERE EXISTS ( subquery );
• Used 'EXISTS' instead of 'Distinct'. Which works Faster.
• The subquery returns distinct result. It's like 'IN' operation.
• But 'IN' takes more time as 'EXISTS' just check for true/false but 'IN' returns the result then compare.
• The EXISTS condition is considered "to be met" if the subquery returns at least one row.
• The EXISTS condition can be used in any valid SQL statement - select, insert, update, or delete.
• You can also use 'NOT EXISTS'.

Expdp & Impdp Related

Export/Import Status:

To see the status of Import: press ctrl+c
import>status
To see the status of Export: press ctrl+c
export>status

Unlike the original exp and imp utilities all data pump ".dmp" and ".log" files are created on the Oracle server, not the client machine.

Links:
http://www.oracle-base.com/articles/10g/OracleDataPump10g.php

Different Options for Exports/Imports:

expdp help=y

To estimate size without actually taking the dump:
expdp prod7/thword7 tables=OLD_DATA directory=DUMPS_NAHAR estimate_only=y
=>estimated "PROD7"."OLD_DATA" 156.8 GB

Directory creation
SQL> col DIRECTORY_PATH format a60
SQL> select DIRECTORY_NAME,DIRECTORY_PATH from dba_directories;

SQL> Create or replace directory DUMPS_NAHAR3 As '/oracle';
SQL> grant read,write on directory DUMPS_NAHAR3 to prod7;

EXPORT
expdp single table
expdp prod/pass tables=LOGIN directory=DBDUMPS dumpfile=LOGIN_20100620.dmp logfile=LOGIN_20100620_LOG.log

expdp Multiple tables, starting with different name
expdp prod/pass tables=LOGIN_CLIENTS,TAC_FORM_SUMMARY directory=DUMPS_TAHSEEN dumpfile=DUMPS_TAHSEEN_20100301.dmp logfile=DUMPS_TAHSEEN_EXPORT_LOG_20100301.log

expdp Multiple tables, starting with same name - TMS
expdp prod/pass DIRECTORY=DUMPS_NAHAR_NEPTUNE SCHEMAS=prod INCLUDE=TABLE:"like'TR_%'" DUMPFILE=DUMPS_ZAHIDUL_20100308.dmp logfile=DUMPS_ZAHIDUL_EXPORT_LOG_20100308.log

Example - Exclude tables
expdp prod/pass DIRECTORY=DUMPS_TERMINUS_PROD7_20100708 SCHEMAS=PROD EXCLUDE=TABLE:\"IN \(\'OLD_DATA\',\'USER_ACTIVITY\'\)\" DUMPFILE=DUMPS_TERMINUS_PROD_20100708.dmp logfile=DUMPS_TERMINUS_PROD_20100708_LOG_20100308.log

exclude=TABLE:\"='TABLE_NAME'\"
EXCLUDE=TABLE:\"IN \(\'TEMP\',\'TEMP1\'\)\"

Full schema
expdp atiq2/pass schemas=atiq2 directory=DUMPS_NAHAR3 dumpfile=atiq2_nd_20100907.dmp logfile=exp_atiq2_nd_20100907.log

In multiple file
expdp prod7/pass SCHEMAS=prod7 DIRECTORY=data_pump_dir DUMPFILE=saturn_prod7_%U.dmp FILESIZE=5G LOGFILE=saturn_prod7_exp.log


IMPORT
1: TABLE_EXISTS_ACTION=replace
impdp prod/pass DIRECTORY=DUMPS_NAHAR_NEPTUNE SCHEMAS=prod DUMPFILE=dbserver_scema_date.dmp TABLE_EXISTS_ACTION=replace PARALLEL=3

Full schema
impdp prod/pass DIRECTORY=DUMPS_NAHAR_NEPTUNE DUMPFILE=df01_prod_flexdoc_smaruf_nahar_20101012.dmp TABLE_EXISTS_ACTION=replace PARALLEL=3

2: REMAP_SCHEMA
impdp system/pass DIRECTORY=DUMPS_NAHAR4 DUMPFILE=atiq2_nd_20100907.dmp REMAP_SCHEMA=atiq2:states PARALLEL=3

-- REMAP_TABLE: We can't do it in 10g. This is only available in 11g.

3: CONTENT=DATA_ONLY/ METADATA_ONLY
impdp system/pass DIRECTORY=DUMPS_NAHAR CONTENT=DATA_ONLY DUMPFILE=OLD_DATA_20100705.dmp REMAP_SCHEMA=PROD7:NAHAR PARALLEL=3

4: Import on a fresh a schema
impdp states/pass DIRECTORY=DUMPS_NAHAR_NEPTUNE SCHEMAS=states DUMPFILE=states_dump_20100919.dmp PARALLEL=3

--wrong
expdp prod/pass DIRECTORY=DUMPS_TERMINUS_PROD_20100708 SCHEMAS=PROD DUMPFILE=DUMPS_TERMINUS_PROD_20100708.dmp logfile=DUMPS_TERMINUS_PROD_20100708_LOG_20100308.log

--If I wanted to take dump from different directory then I should use like:
DUMPFILE=datadir1:schema1%U.dmp,datadir2:schema2%U.dmp

How to zip & unzip
zip: nahar@mf01:~$ gzip df01_prod7_flexdoc_smaruf_nahar_201001012.dmp
zip: nahar@mf01:~$ gunzip df01_prod7_flexdoc_smaruf_nahar_20101012.zip

More:
www.oracle-dba-online.com
http://www.oracle-base.com/articles/10g/OracleDataPump10g.php

Working Example:

I will export dump from Estern's (db server) prod (db user) and will import this dump in juptr's (db server) nahar (db user).

I have to pass following steps-
Directory Creation -> Export the dump file to that directory of Estern -> Copy the dump file to another machine -> Make a directory to that location where I create the dump -> Impdp .dmp file to that directory of juptr

Step-1: I am searching where the space available to export?

oracle@Estern:~$ df -h

I need to make a physical directory-
oracle@Estern:~$ cd /dump
oracle@Estern:/dump$ mkdir dumps_nahar_20091015

Step-2: Create Directory

oracle@Estern:~$ sqlplus
Enter user-name: s as sysdba

SQL> Create or replace directory dumps_nahar As '/dump/dumps_nahar_20091015';

Testing the view is really created or not-
SQL> select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from DBA_DIRECTORIES where DIRECTORY_NAME like '%NAHAR%';

SQL> grant read,write on directory dumps_nahar to prod;

Step-3: Taking the Dump or Export the Dump

expdp prod/password DIRECTORY=dumps_nahar SCHEMAS=prd7 DUMPFILE=nahar_from_Estern_20091015_%U.dmp

Step-4: copy dump from Estern to Jupiter

Now I am coping the dump file from Estern to Juptr's /backup1/dumps_nahar_20091015 location.

bash-3.00$ scp nahar_from_Estern_20091015_01.dmp oracle@juptr:/backup1/dumps_nahar_20091015

--Export from Estern and Copy the dump in juptr done.
--Now I will Import the dump in Juptr's nahar user.

oracle@juptr:~$ sqlplus s as sysdba
Step-5: Want to import the dump at nahar user

If nahar does not exists-
SQL> create user nahar identified by nahar
default tablespace users quota unlimited on users;

SQL> grant dba to nahar;

Step-6: Create Directory where the dump I copied

SQL> Create or replace directory dumps_nahar As '/backup1/dumps_nahar_20091015';

Testing the view is really created or not-
SQL> select OWNER,DIRECTORY_NAME,DIRECTORY_PATH from DBA_DIRECTORIES where DIRECTORY_NAME like '%NAHAR%';

SQL> grant read,write on directory dumps_nahar to nahar;

Step-7: Import the dump

impdp system/password DIRECTORY=dumps_nahar SCHEMAS=prd7 DUMPFILE=nahar_from_Estern_20091015_01.dmp REMAP_SCHEMA=prd7:nahar PARALLEL=3

Database Status

How large is the database?

col "Database Size" format a20
col "Free space" format a20
col "Used space" format a20

select round(sum(used.bytes) / 1024 / 1024 / 1024 ) || ' GB' "Database Size"
, round(sum(used.bytes) / 1024 / 1024 / 1024 ) -
round(free.p / 1024 / 1024 / 1024) || ' GB' "Used space"
, round(free.p / 1024 / 1024 / 1024) || ' GB' "Free space"
from (select bytes
from v$datafile
union all
select bytes
from v$tempfile
union all
select bytes
from v$log) used
, (select sum(bytes) as p
from dba_free_space) free
group by free.p;

Database Size: 436 GB
Used space: 355 GB
Free space: 81 GB

How many Table's under "Nahar" user?

select count(*) from dba_objects do where do.owner = 'NAHAR'
and lower(object_type)=lower('Table');

Total tables: 490