SQL> SELECT s.sid, s.serial#, s.username, s.program, i.block_changes
FROM v$session s, v$sess_io i
WHERE s.sid = i.sid
ORDER BY 5 desc;
//查看数据库大小
select sum(SumMB)/1024,sum(usedMB)/1024,sum(freeMB)/1024 ,(sum(usedMB)/1024+sum(freeMB)/1024)from
(
select a.tablespace_name,
a.bytes / 1024 / 1024 SumMB,
(a.bytes - b.bytes) / 1024 / 1024 usedMB,
b.bytes / 1024 / 1024 freeMB,
round(((a.bytes - b.bytes) / a.bytes) * 100, 2) "percent_used"
from (select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) a,
(select tablespace_name, sum(bytes) bytes, max(bytes) largest
from dba_free_space
group by tablespace_name) b
where a.tablespace_name = b.tablespace_name );
//表空间使用情况
col TABLESPACE_NAME for a20
select
a.tablespace_name,
a.bytes/1024/1024 "Sum MB",
(a.bytes-b.bytes)/1024/1024 "used MB",
b.bytes/1024/1024 "free MB",
round(((a.bytes-b.bytes)/a.bytes)*100,2) "percent_used"
from
(select tablespace_name,sum(bytes) bytes from dba_data_files group by tablespace_name) a,
(select tablespace_name,sum(bytes) bytes,max(bytes) largest from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
order by ((a.bytes-b.bytes)/a.bytes) desc ;
//查询表所占用的数据块blocks的数量
SQL> select PCT_FREE,LOGGING,NUM_ROWS,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='EMP';
PCT_FREE LOG NUM_ROWS BLOCKS EMPTY_BLOCKS
---------- --- ---------- ---------- ------------
10 YES 14 5 0
//释放表未用空间
SQL> alter table EMP deallocate unused;
Table altered.
//分析表
SQL> analyze table EMP compute statistics;
Table analyzed.
//将表改变所在表空间
SQL> select TABLE_NAME,TABLESPACE_NAME,OWNER from dba_tables where TABLESPACE_NAME ='USERDATA';
TABLE_NAME TABLESPACE_NAME OWNER
------------------------------ ------------------------------ ------------------------------
T USERDATA TJ
LL_T1 USERDATA TJ
SQL> conn tj/tj
Connected.
SQL> alter table T move tablespace USERDATA;
Table altered.
//重建表索引，同时改变所在空间
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME from user_indexes where TABLE_NAME='T';
INDEX_NAME INDEX_TYPE TABLE_OWNE TABLE_NAME TABLESPACE_NAME
------------------------------ --------------------------- ---------- ---------- ------------------------------
IND_T_OBJECT_ID NORMAL TJ T USERDATA
SQL> alter index IND_T_OBJECT_ID rebuild tablespace users;
Index altered.
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME from user_indexes where TABLE_NAME='T';
INDEX_NAME INDEX_TYPE TABLE_OWNE TABLE_NAME TABLESPACE_NAME
------------------------------ --------------------------- ---------- ---------- ------------------------------
IND_T_OBJECT_ID NORMAL TJ T USERS
//整理表空间碎片
SQL> alter tablespace users coalesce ;
Tablespace altered.
//shrink降低表HWM（10g开始）
首先要确认段自动管理
SQL> select tablespace_name, block_size,extent_management,allocation_type,segment_space_management from dba_tablespaces where tablespace_name = 'USERS';
TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ ---------- ---------- --------- ------
USERS 8192 LOCAL SYSTEM AUTO
SQL> alter table emp enable row movement;
Table altered.
SQL> alter table emp shrink space;
Table altered.
//整理蜂窝式碎片的方式
SQL> alter tablespace tablespacename coalesce
//将缺省表空间与临时表空间改为非system
SQL> alter user rsingh default users temporary temp
SQL> alter table EMP deallocate unused;
Table altered.
//分析表
SQL> analyze table EMP compute statistics;
Table analyzed.
//将表改变所在表空间
SQL> select TABLE_NAME,TABLESPACE_NAME,OWNER from dba_tables where TABLESPACE_NAME ='USERDATA';
TABLE_NAME TABLESPACE_NAME OWNER
------------------------------ ------------------------------ ------------------------------
T USERDATA TJ
LL_T1 USERDATA TJ
SQL> conn tj/tj
Connected.
SQL> alter table T move tablespace USERDATA;
Table altered.
//重建表索引，同时改变所在空间
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME from user_indexes where TABLE_NAME='T';
INDEX_NAME INDEX_TYPE TABLE_OWNE TABLE_NAME TABLESPACE_NAME
------------------------------ --------------------------- ---------- ---------- ------------------------------
IND_T_OBJECT_ID NORMAL TJ T USERDATA
SQL> alter index IND_T_OBJECT_ID rebuild tablespace users;
Index altered.
SQL> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLESPACE_NAME from user_indexes where TABLE_NAME='T';
INDEX_NAME INDEX_TYPE TABLE_OWNE TABLE_NAME TABLESPACE_NAME
------------------------------ --------------------------- ---------- ---------- ------------------------------
IND_T_OBJECT_ID NORMAL TJ T USERS
//整理表空间碎片
SQL> alter tablespace users coalesce ;
Tablespace altered.
//shrink降低表HWM（10g开始）
首先要确认段自动管理
SQL> select tablespace_name, block_size,extent_management,allocation_type,segment_space_management from dba_tablespaces where tablespace_name = 'USERS';
TABLESPACE_NAME BLOCK_SIZE EXTENT_MAN ALLOCATIO SEGMEN
------------------------------ ---------- ---------- --------- ------
USERS 8192 LOCAL SYSTEM AUTO
SQL> alter table emp enable row movement;
Table altered.
SQL> alter table emp shrink space;
Table altered.
//整理蜂窝式碎片的方式
SQL> alter tablespace tablespacename coalesce
//将缺省表空间与临时表空间改为非system
SQL> alter user rsingh default users temporary temp