Exp 的TABLESPACE 参数

最新推荐文章于 2021-04-04 14:30:20 发布

ctest011501

最新推荐文章于 2021-04-04 14:30:20 发布

阅读量139

点赞数

文章标签：数据库

如果exp时指定TABLESPACES,会把指定TABLESPACES的对象导出，而且table所属的INDEX,，就算不在指定的TABLESPACE上；Partition分区表的其他partiton也会同时导出；
例子：
SQL> alter user kong quota unlimited on t1;

User altered.

SQL> c/t1/t2/
1* alter user kong quota unlimited on t2
SQL> /

User altered.

SQL> conn kong/kong
Connected.
SQL>
SQL> create table test (id number, a char(10), b char(10), c char(10), d number)
2 tablespace t1;
insert into test values(1, 'a', 'b', 'c', 100);
insert into test values(2, 'a', 'b', 'c', 100);
insert into test values(3, 'a', 'b', 'c', 100);
insert into test values(4, 'a', 'b', 'c', 100);

alter table test
add constraint test_k primary key (id);

Table created.

SQL>
1 row created.

SQL> SQL> 2
alter index test_k rebuild tablespace t2;
Table altered.

SQL> SQL>

Index altered.

SQL>
SQL>
SQL> CREATE TABLE sales
2 ( invoice_no NUMBER,
3 sale_year INT NOT NULL)
4 PARTITION BY RANGE ( sale_year)
5 ( PARTITION sales_q1 VALUES LESS THAN (100)
6 TABLESPACE t1,
7 PARTITION sales_q2 VALUES LESS THAN ( 2005)
8 TABLESPACE t2);

Table created.

SQL> SQL> insert into sales values(1000, 99);

1 row created.

SQL> insert into sales values(1001, 199);

1 row created.

SQL> insert into sales values(1002, 99);

1 row created.

SQL> insert into sales values(1003, 399);

1 row created.

SQL>
SQL>
SQL> commit;

Commit complete.

exp \'/ as sysdba\' tablespaces=T1 wner=kong

imp \'/ as sysdba\' indexfile=index.txt file=expdat.dmp full=y

index.txt内容如下：
发现test_k 索引，sales 的sales_q2也在其中。

"index.txt" 24 lines, 1303 characters

REM CREATE TABLE "KONG"."SALES" ("INVOICE_NO" NUMBER, "SALE_YEAR"
REM NUMBER(*,0) NOT NULL ENABLE) PCTFREE 10 PCTUSED 40 INITRANS 1
REM MAXTRANS 255 TABLESPACE "KONG" LOGGING PARTITION BY RANGE
REM ("SALE_YEAR" ) (PARTITION "SALES_Q1" VALUES LESS THAN (100) PCTFREE
REM 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS
REM 1 FREELIST GROUPS 1) TABLESPACE "T1" NOCOMPRESS, PARTITION "SALES_Q2"
REM VALUES LESS THAN (2005) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
REM STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "T2"
REM NOCOMPRESS ) ;
REM ... 2 rows
REM ... 2 rows
REM CREATE TABLE "KONG"."TEST" ("ID" NUMBER, "A" CHAR(10), "B" CHAR(10),
REM "C" CHAR(10), "D" NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS
REM 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE
REM "T1" LOGGING NOCOMPRESS ;
REM ... 4 rows
CONNECT KONG;
CREATE UNIQUE INDEX "KONG"."TEST_K" ON "TEST" ("ID" ) PCTFREE 10 INITRANS
2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1)
TABLESPACE "T2" LOGGING ;
REM ALTER TABLE "KONG"."TEST" ADD CONSTRAINT "TEST_K" PRIMARY KEY ("ID")
REM USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536
REM FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "T2" LOGGING ENABLE ;