indexfile is not a good solution

indexfile的方式不是一个好的解决办法。接着上一篇文章,试想如果scott用户下面的对象有几万个,还一个个去更改TABLESPACE “USERS”吗?工作量还是大了一些,当然你会说不是有vi,editplus之类的工具嘛。。。个人觉得还是unlimited tablespace权限回收的方式,比较妥当。

************************************************************************
REM  CREATE TABLE “SCOTT”.”DEPT” (“DEPTNO” NUMBER(2, 0), “DNAME”
REM  VARCHAR2(14), “LOC” VARCHAR2(13)) PCTFREE 10 PCTUSED 40 INITRANS 1
REM  MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1
REM  BUFFER_POOL DEFAULT) TABLESPACE “USERS” LOGGING NOCOMPRESS ;
CONNECT SCOTT;
CREATE UNIQUE INDEX “SCOTT”.”PK_DEPT” ON “DEPT” (“DEPTNO” ) PCTFREE 10
INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS
1 BUFFER_POOL DEFAULT) TABLESPACE “USERS” LOGGING ;
REM  ALTER TABLE “SCOTT”.”DEPT” ADD CONSTRAINT “PK_DEPT” PRIMARY KEY
REM  (“DEPTNO”) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
REM  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
REM  DEFAULT) TABLESPACE “USERS” LOGGING ENABLE ;
REM  CREATE TABLE “SCOTT”.”EMP” (“EMPNO” NUMBER(4, 0), “ENAME”
REM  VARCHAR2(10), “JOB” VARCHAR2(9), “MGR” NUMBER(4, 0), “HIREDATE” DATE,
REM  “SAL” NUMBER(7, 2), “COMM” NUMBER(7, 2), “DEPTNO” NUMBER(2, 0))
REM  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536
REM  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE “USERS”
REM  LOGGING NOCOMPRESS ;
CREATE UNIQUE INDEX “SCOTT”.”PK_EMP” ON “EMP” (“EMPNO” ) PCTFREE 10
INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS
1 BUFFER_POOL DEFAULT) TABLESPACE “USERS” LOGGING ;
REM  ALTER TABLE “SCOTT”.”EMP” ADD CONSTRAINT “PK_EMP” PRIMARY KEY
REM  (“EMPNO”) USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
REM  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
REM  DEFAULT) TABLESPACE “USERS” LOGGING ENABLE ;
REM  CREATE TABLE “SCOTT”.”SALGRADE” (“GRADE” NUMBER, “LOSAL” NUMBER,
REM  “HISAL” NUMBER) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
REM  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
REM  DEFAULT) TABLESPACE “USERS” LOGGING NOCOMPRESS ;
REM  CREATE TABLE “SCOTT”.”STATTAB” (“STATID” VARCHAR2(30), “TYPE”
REM  CHAR(1), “VERSION” NUMBER, “FLAGS” NUMBER, “C1” VARCHAR2(30), “C2”
REM  VARCHAR2(30), “C3” VARCHAR2(30), “C4” VARCHAR2(30), “C5”
REM  VARCHAR2(30), “N1” NUMBER, “N2” NUMBER, “N3” NUMBER, “N4” NUMBER,
REM  “N5” NUMBER, “N6” NUMBER, “N7” NUMBER, “N8” NUMBER, “N9” NUMBER,
REM  “N10” NUMBER, “N11” NUMBER, “N12” NUMBER, “D1” DATE, “R1” RAW(32),
REM  “R2” RAW(32), “CH1” VARCHAR2(1000), “CL1” CLOB) PCTFREE 10 PCTUSED 40
REM  INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST
REM  GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE “USERS_REORG0” LOGGING
REM  NOCOMPRESS LOB (“CL1”) STORE AS (TABLESPACE “USERS_REORG0” ENABLE
REM  STORAGE IN ROW CHUNK 8192 PCTVERSION 10 NOCACHE LOGGING
REM  STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
REM  DEFAULT)) ;
CREATE INDEX “SCOTT”.”STATTAB” ON “STATTAB” (“STATID” , “TYPE” , “C5” ,
“C1” , “C2” , “C3” , “C4” , “VERSION” ) PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE “USERS_REORG0” LOGGING ;
REM  CREATE TABLE “SCOTT”.”TEST” (“OWNER” VARCHAR2(30), “OBJECT_NAME”
REM  VARCHAR2(128), “SUBOBJECT_NAME” VARCHAR2(30), “OBJECT_ID” NUMBER,
REM  “DATA_OBJECT_ID” NUMBER, “OBJECT_TYPE” VARCHAR2(19), “CREATED” DATE,
REM  “LAST_DDL_TIME” DATE, “TIMESTAMP” VARCHAR2(19), “STATUS” VARCHAR2(7),
REM  “TEMPORARY” VARCHAR2(1), “GENERATED” VARCHAR2(1), “SECONDARY”
REM  VARCHAR2(1), “NAMESPACE” NUMBER, “EDITION_NAME” VARCHAR2(30)) PCTFREE
REM  10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 9437184
REM  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
REM  “USERS_REORG0” LOGGING NOCOMPRESS ;
REM  CREATE TABLE “SCOTT”.”TEST2″ (“OWNER” VARCHAR2(30), “OBJECT_NAME”
REM  VARCHAR2(128), “SUBOBJECT_NAME” VARCHAR2(30), “OBJECT_ID” NUMBER,
REM  “DATA_OBJECT_ID” NUMBER, “OBJECT_TYPE” VARCHAR2(19), “CREATED” DATE,
REM  “LAST_DDL_TIME” DATE, “TIMESTAMP” VARCHAR2(19), “STATUS” VARCHAR2(7),
REM  “TEMPORARY” VARCHAR2(1), “GENERATED” VARCHAR2(1), “SECONDARY”
REM  VARCHAR2(1), “NAMESPACE” NUMBER, “EDITION_NAME” VARCHAR2(30)) PCTFREE
REM  10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 9437184
REM  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
REM  “USERS_REORG0” LOGGING NOCOMPRESS ;
REM  CREATE TABLE “SCOTT”.”TEST3″ (“OWNER” VARCHAR2(30), “OBJECT_NAME”
REM  VARCHAR2(128), “SUBOBJECT_NAME” VARCHAR2(30), “OBJECT_ID” NUMBER,
REM  “DATA_OBJECT_ID” NUMBER, “OBJECT_TYPE” VARCHAR2(19), “CREATED” DATE,
REM  “LAST_DDL_TIME” DATE, “TIMESTAMP” VARCHAR2(19), “STATUS” VARCHAR2(7),
REM  “TEMPORARY” VARCHAR2(1), “GENERATED” VARCHAR2(1), “SECONDARY”
REM  VARCHAR2(1), “NAMESPACE” NUMBER, “EDITION_NAME” VARCHAR2(30)) PCTFREE
REM  10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 9437184
REM  FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE
REM  “USERS_REORG0” LOGGING NOCOMPRESS ;
REM  CREATE TABLE “SCOTT”.”TEST4″ (“DEPTNO” NUMBER(2, 0), “DNAME”
REM  VARCHAR2(14), “LOC” VARCHAR2(13)) PCTFREE 10 PCTUSED 40 INITRANS 1
REM  MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1
REM  BUFFER_POOL DEFAULT) TABLESPACE “USERS_REORG0” LOGGING NOCOMPRESS ;
REM  CREATE TABLE “SCOTT”.”TEST5″ (“DEPTNO” NUMBER(2, 0), “DNAME”
REM  VARCHAR2(14), “LOC” VARCHAR2(13)) PCTFREE 10 PCTUSED 40 INITRANS 1
REM  MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1
REM  BUFFER_POOL DEFAULT) TABLESPACE “USERS_REORG0” LOGGING NOCOMPRESS ;
REM  ALTER TABLE “SCOTT”.”EMP” ADD CONSTRAINT “FK_DEPTNO” FOREIGN KEY
REM  (“DEPTNO”) REFERENCES “DEPT” (“DEPTNO”) ENABLE NOVALIDATE ;
REM  ALTER TABLE “SCOTT”.”EMP” ENABLE CONSTRAINT “FK_DEPTNO” ;

困惑好久的事情。。。

很多朋友希望在进行数据迁移的时候,能够将数据导入不同于原来系统的表空间,但是在导入后却发现,数据还是被导入了原表空间,也就是只跟着user变化的想法落空了,fromuser to user。。这是因为如果缺省的原用户具备unlimited tablespace权限的话,那么导入的时候会按照原来的位置导入,也就是带着storage选项,选择缺省的表空间。所以,在规划之初,建议unlimited tablespace选项还是慎重授予。