ؼʣORACLE 


һ  

Ϊ˼ݿĹORACLE8Ƴ˷ѡɲͬıռϣ÷ֶ֮ķ֧͵ĴһĿɹԡָɽСķԸƱάݡָ񼰲ѯܡԵǰ籣ҵĴճҵݣƼʹORACLE8ĸѡ 


ŵ㣺 

1 ǿԣһϵͳ϶ʹãõķȻʹã 

2 ٹرʱ䣺ϵͳֻӰһַôֻⲿַҪ޸ܱ޸ʱ٣ 

3 άɣҪؽÿȹҪɵöࣻ 

4 I/O:԰ѱĲͬ䵽ͬĴƽI/Oܣ 

5 ܣԴĲѯӡ޸ĵȲԷֽ⵽ĲִͬУʹٶȸ죻 

6 û͸ûоĴڡ 


Ĺ 

1 Ľ 

ĳ˾ÿ޴ۼ¼DBA˾ÿȵݷһڣʾǸù˾1999(ÿ²30M)£ 


STEP1ĸıռ䣺 

CREATE TABLESPACE ts_sale1999q1 

DATAFILE /u1/oradata/sales/sales1999_q1.dat 

SIZE 100M 

DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0) 

CREATE TABLESPACE ts_sale1999q2 

DATAFILE /u1/oradata/sales/sales1999_q2.dat 

SIZE 100M 

DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0) 

CREATE TABLESPACE ts_sale1999q3 

DATAFILE /u1/oradata/sales/sales1999_q3.dat 

SIZE 100M 

DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0) 

CREATE TABLESPACE ts_sale1999q4 

DATAFILE /u1/oradata/sales/sales1999_q4.dat 

SIZE 100M 

DEFAULT STORAGE (INITIAL 30m NEXT 30m MINEXTENTS 3 PCTINCREASE 0) 


STEP2ڷı 

CREATE TABLE sales 

(invoice_no NUMBER, 

... 

sale_date DATE NOT NULL ) 

PARTITION BY RANGE (sale_date) 

(PARTITION sales1999_q1 

VALUES LESS THAN (TO_DATE(1999-04-01,YYYY-MM-DD) 

TABLESPACE ts_sale1999q1, 

PARTITION sales1999_q2 

VALUES LESS THAN (TO_DATE(1999-07-01,YYYY-MM-DD) 

TABLESPACE ts_sale1999q2, 

PARTITION sales1999_q3 

VALUES LESS THAN (TO_DATE(1999-10-01,YYYY-MM-DD) 

TABLESPACE ts_sale1999q3, 

PARTITION sales1999_q4 

VALUES LESS THAN (TO_DATE(2000-01-01,YYYY-MM-DD) 

TABLESPACE ts_sale1999q4 ); 


2 ݣ 

1999ףDBAӦм2000ıռ䣬ͬÿһռ䣬ڹ˾ҵ٣ԤÿΪ40M¡ 

STEP1ռ䣺 

CREATE TABLESPACE ts_sale2000q1 

DATAFILE /u1/oradata/sales/sales2000_q1.dat 

SIZE 130M 

DEFAULT STORAGE (INITIAL 40m NEXT 40m MINEXTENTS 3 PCTINCREASE 0) 

ռts_sale2000q2,ts_sale2000q3,ts_sales2000q4編ơ 

STEP2Ϊӱռ䣺 

ALTER TABLE sales 

ADD PARTITION sales2000_q1 

VALUES LESS THAN (TO_DATE(2000-04-01,YYYY-MM-DD) 

TABLESPACE ts_sale2000q1; 

sales2000_q1sales2000_q1sales2000_q1編ơ 


3 ɾҪķ 

˾涨۵ϸڱ뱣ߡ2001꣬DBA뽫1999ݱݣݷ5EXPORT1999ķɾռ乩ʹáѭԶߡ 

STEP1DROP  

ALTER TABLE sales 

DROP PARTION sales1999_q1; 

ALTER TABLE sales 

DROP PARTION sales1999_q2; 

ALTER TABLE sales 

DROP PARTION sales1999_q3; 

ALTER TABLE sales 

DROP PARTION sales1999_q4; 

STEP2òϵͳĹɾϱռռõļռ豸β,UNIXϵͳΪ 

oracle$ rm /u1/oradata/sales/sales1999_q1.dat 

oracle$ rm /u1/oradata/sales/sales1999_q2.dat 

oracle$ rm /u1/oradata/sales/sales1999_q3.dat 

oracle$ rm /u1/oradata/sales/sales1999_q4.dat 


4  

ض̷truncateڵķΪ(split)(exchange)rename,ΪȡDBAԸʵʹá 

½˵ѷsplit,ù˾1999ļϸݼӣΪ졢ӭǧػع飩DBA˾齫ļȵķΪÿ·ݵݣ£ 

STEP11ķıռts_sales1999q4p1, 

ts_sales1999q4p2 

STEP2sales1999_q4_p1,sales1999_q4_p2; 

STEP3ѷ 

ALTER TABLE sales 

SPLIT PARTITON sales1999_q4 

AT TO_DATE (1999-11-01,YYYY-MM-DD) 

INTO (partition sales1999_q4_p1, partition sales1999_q4_p2) 


5 鿴Ϣ 

DBAҪ鿴ķϢɲ鿴ֵUSER_EXTENTS,£ 

SVRMGRL>SELECT * FROM user_extents WHERE SEGMENT_NAME=SALES; 

SEGMENT_NA PARTITION_ SEGMENT_TYPE TABLESPACE 

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

SALES SALES1999_Q1 TABLE PARTITION TS_SALES1999Q1 

SALES SALES1999_Q2 TABLE PARTITION TS_SALES1999Q2 

SALES SALES1999_Q3 TABLE PARTITION TS_SALES1999Q3 

SALES SALES1999_Q4 TABLE PARTITION TS_SALES1999Q4 

SALES SALES2000_Q1 TABLE PARTITION TS_SALES1999Q1 

SALES SALES2000_Q2 TABLE PARTITION TS_SALES1999Q2 

SALES SALES2000_Q3 TABLE PARTITION TS_SALES1999Q3 

SALES SALES2000_Q4 TABLE PARTITION TS_SALES1999Q4 


5 EXPORT 

ORACLE8EXPORT ߿ڱķԼݣ絽2001꣬DBA뽫1999ݰ£ 

oracle$ exp sales/sales_password tables=sales:sales1999_q1 rows=Y 

file=sales1999_q1.dmp 

oracle$ exp sales/sales_password tables=sales:sales1999_q2 rows=Y 

file=sales1999_q2.dmp 

oracle$ exp sales/sales_password tables=sales:sales1999_q3 rows=Y 

file=sales1999_q3.dmp 

oracle$ exp sales/sales_password tables=sales:sales1999_q4 rows=Y 

file=sales1999_q4.dmp 


6 IMPORT 

ORACLE8IMPORT ߿ڱķԼݣ2001꣬ûҪ鿴1999ݣDBA뵼1999ݣʹ֮ߣ£ 

STEP11999ĸռӦķգ2 

STEP2ݣ 

oracle$ imp sales/sales_password FILE =sales1999_q1.dmp 

TABLES = (sales:sales1999_q1) IGNORE=y 

oracle$ imp sales/sales_password FILE =sales1999_q2.dmp 

TABLES = (sales:sales1999_q2) IGNORE=y 

oracle$ imp sales/sales_password FILE =sales1999_q3.dmp 

TABLES = (sales:sales1999_q3) IGNORE=y 

oracle$ imp sales/sales_password FILE =sales1999_q4.dmp 

TABLES = (sales:sales1999_q4) IGNORE=y
