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