标题: 处理system表空间中的业务数据 [打印本页] 作者: denglj 时间: 2022-2-9 16:14 标题: 处理system表空间中的业务数据 有时部分业务表或索引在建立时错放于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.