表的在线移动表空间

ALTER SESSION SET CONTAINER=pdb2;

CREATE TABLESPACE users DATAFILE '/u02/oradata/CDB1/pdb2/users01.dbf'

SIZE 100M AUTOEXTEND ON NEXT 10M;

SELECT tablespace_name FROM dba_tablespaces;

 SELECT property_value

FROM database_properties

WHERE property_name = 'DEFAULT_PERMANENT_TABLESPACE';

创建测试用户

CREATE USER lihao IDENTIFIED BY aabb123;

GRANT CREATE SESSION, DBA TO lihao;

 

创建表

CREATE TABLE t1 AS

SELECT level AS id,

       'Description for ' || level AS description,

       SYSDATE AS created_date

FROM   dual

CONNECT BY level <= 1000;

COMMIT;

ALTER TABLE t1 ADD CONSTRAINT t1_pk PRIMARY KEY (id);

CREATE INDEX t1_created_date_idx ON t1(created_date);

SELECT index_name, status FROM user_indexes ORDER BY 1;

 

select   tablespace_name,table_name  from user_tables where table_name='T1';

 

ALTER TABLE t1 MOVE TABLESPACE users;

SELECT index_name, status FROM user_indexes ORDER BY 1;

 

SELECT index_name, status FROM user_indexes ORDER BY 1;

ALTER INDEX t1_pk REBUILD ONLINE;

ALTER INDEX t1_created_date_idx REBUILD ONLINE;

 SELECT index_name, status FROM user_indexes ORDER BY 1;

ALTER TABLE t1 MOVE TABLESPACE lh UPDATE INDEXES;

 

 

Oracle 12.2 开始，我们可以使用ONLINE关键字将表作为在线操作移动。除了移动表外，在线移动还会自动维护索引

ALTER TABLE t1 MOVE ONLINE TABLESPACE users;

SELECT index_name, status FROM user_indexes ORDER BY 1;