普通表转分区表.docxVIP

. .. 普通表转分区表 方法一、 1.查看数据类型，行数 如果存在long、raw long类型无法进行分区表 SELECT COUNT(*) FROM EMP 2.备份创建table脚本 F4键 CREATE TABLE SCOTT.EMP ( EMPNO NUMBER(4) NOT NULL, ENAME VARCHAR2(10 BYTE), JOB VARCHAR2(9 BYTE), MGR NUMBER(4), HIREDATE DATE, SAL NUMBER(7,2), COMM NUMBER(7,2), DEPTNO NUMBER(2) ) TABLESPACE SYSTEM PCTUSED 40 PCTFREE 10 INITRANS 1 MAXTRANS 255 STORAGE ( INITIAL 104K NEXT 104K MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 100 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING; -- There is no statement for index SCOTT.SYS_C0093796. -- The object is created automatically by Oracle when the parent object is created. CREATE OR REPLACE TRIGGER SCOTT.after_ins_upd_on_emp before insert or update ON SCOTT.EMP for each row begin :new.ename := upper(:new.ename); end; / ALTER TABLE SCOTT.EMP ADD ( CONSTRAINT EMP_VALID_JOB CHECK (job in (CLERK,SALESMAN,MANAGER,ANALYST,PRESIDENT)), PRIMARY KEY (EMPNO) USING INDEX TABLESPACE SYSTEM PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE ( INITIAL 104K NEXT 104K MINEXTENTS 1 MAXEXTENTS 505 PCTINCREASE 100 FREELISTS 1 FREELIST GROUPS 1 )); GRANT DELETE, INSERT, SELECT, UPDATE ON SCOTT.EMP TO PUBLIC; 3.exp or expdp数据 1.expdp 1.创建directory并赋权 SQL CREATE DIRECTORY DUMP AS /u01/oracle/; Directory created. SQL GRANT READ,WRITE ON DIRECTORY DUMP TO APPS; Grant succeeded. 2.查看directory目录权限 SELECT PRIVILEGE, DIRECTORY_NAME, DIRECTORY_PATH FROM USER_TAB_PRIVS T, ALL_DIRECTORIES D WHERE T.TABLE_NAME(+) = D.DIRECTORY_NAME ORDER BY 2, 1; 3.导出数据 [ebstst@f