有时部分业务表或索引在建立时错放于system表空间, system表空间一旦被业务数据撑满,将引起整个数据库挂起, 存在严重的安全隐患,此时需数据库管理员提前将业务数据移动到业务表空间. 查业务用户在system表空间是否存在数据: SQL> Select Owner, Segment_Name, Segment_Type, Tablespace_Name, (Bytes /1024 /1024) As "size(m)" From dba_segments Where Owner Not In ('SYS', 'SYSTEM', 'OUTLN', 'WMSYS') And Tablespace_Name = 'SYSTEM'; 以下为执行过程: SQL> conn jack/jack Connected. 查该用户有哪些对象: SQL> select table_name,table_type from user_tab_comments; TABLE_NAME TABLE_TYPE ------------------------------ ---------------------- EMPLOYEES TABLE TS VIEW SYS用户查询EMPLOYEES对应表空间: SQL> conn / as sysdba Connected. SQL> select b.file_name,a.owner,a.segment_name,a.tablespace_name from dba_extents a,dba_data_files b where a.file_id=b.file_id and a.segment_name='EMPLOYEES' and a.owner='JACK'; FILE_NAME OWNER SEGMENT_NAME TABLESPACE_NAME -------------------------------------------------- --------------- ------------------------------ ------------------------------ /u01/app/oracle/oradata/orcl150/users01.dbf JACK EMPLOYEES USERS 普通用户建表且表空间指定为system: SQL> show user; USER is "JACK" SQL> create table employees1 tablespace system as select * from employees; Table created. SQL> conn / as sysdba Connected. SQL> select b.file_name,a.owner,a.segment_name,a.tablespace_name from dba_extents a,dba_data_files b where a.file_id=b.file_id and a.segment_name='EMPLOYEES1' and a.owner='JACK'; FILE_NAME OWNER SEGMENT_NAME TABLESPACE_NAME -------------------------------------------------- --------------- ------------------------------ ------------------------------ /u01/app/oracle/oradata/orcl150/system01.dbf JACK EMPLOYEES1 SYSTEM 普通用户建索引且表空间指定为system: SQL> show user; USER is "JACK" SQL> create index employees1 on employees1(id) tablespace system; Index created. SQL> select index_name,table_name from user_indexes where table_name='EMPLOYEES1'; INDEX_NAME TABLE_NAME ------------------------------ ------------------------------ EMPLOYEES1 EMPLOYEES1 SQL> conn / as sysdba Connected. SQL> select index_name,tablespace_name from dba_indexes where index_name='EMPLOYEES1'; INDEX_NAME TABLESPACE_NAME ------------------------------ ------------------------------ EMPLOYEES1 SYSTEM 查业务用户存在于system表空间的对象: SQL> conn / as sysdba Connected. SQL> Select Owner, Segment_Name, Segment_Type, Tablespace_Name, (Bytes / 1024 / 1024) As "size(m)" 2 From Dba_Segments 3 Where Owner Not In ('SYS', 'SYSTEM', 'OUTLN', 'WMSYS') And Tablespace_Name = 'SYSTEM'; OWNER SEGMENT_NAME SEGMENT_TYPE TABLESPACE_NAME size(m) --------------- ------------------------------ ------------------------------------ ------------------------------ ---------- JACK EMPLOYEES1 TABLE SYSTEM .0625 JACK EMPLOYEES1 INDEX SYSTEM .0625 问题:如何将以上EMPLOYEES1表及索引移动到users表空间? SQL> conn JACK/JACK Connected. SQL> alter table EMPLOYEES1 move tablespace users; Table altered. SQL> conn / as sysdba Connected. SQL> select owner,segment_name,segment_type,tablespace_name,(bytes/1024/1024) as "size(m)" from dba_segments where owner not in ('SYS','SYSTEM','OUTLN','WMSYS') and tablespace_name='SYSTEM'; no rows selected #查表对应表空间 SQL> select t.table_name,t.tablespace_name from dba_tables t where t.table_name='EMPLOYEES1'; TABLE_NAME TABLESPACE_NAME ------------------------------------------------------------ ------------------------------ EMPLOYEES1 USERS SQL> select index_name,tablespace_name from dba_indexes where index_name='EMPLOYEES1'; INDEX_NAME TABLESPACE_NAME ------------------------------------------------------------ ------------------------------ EMPLOYEES1 SYSTEM #此处确认表已在users表空间,索引还在system表 SQL> conn JACK/JACK Connected. SQL> alter index employees1 rebuild tablespace users; Index altered. SQL> conn / as sysdba Connected. SQL> select index_name,tablespace_name from dba_indexes where index_name='EMPLOYEES1'; INDEX_NAME TABLESPACE_NAME ------------------------------ ------------------------------ EMPLOYEES1 USERS #此处确认索引employees1表空间也已更改为users. |
|