--重建索引
ALTER INDEX IX_COMMONINFO_ACCEPTERNO REBUILD ONLINE;

查找索引表空间信息
select * from all_indexes WHERE tablespace_name = 'SERVICE_LOG_IDX';

--数据文件
SELECT * from dba_data_files;

--日志文件
select * from v$controlfile;

--控制文件
select * from v$controlfile;

--临时表空间文件
select * from v$tempfile;

--查看数据库字典
select * from dict

row_number() over(partition by b.serialno,b.serviceclassid order by b.replytime;

select table_name from user_tables where partitioned = 'YES';

select  * from dba_segments
where length(partition_name)<4 and wner='ICD3MAIN';

---表的创建时间可以查询user_objects得到

SELECT *
  FROM DBA_OBJECTS T
 WHERE WNER = 'ICD'
   AND OBJECT_NAME = 'T_DAYLOG_CALLBYCALLEENO';

---表的大小可以查询user_segments得到

SELECT SUM(BYTES) / 1024 / 1024 || 'M'
  FROM DBA_SEGMENTS
 WHERE WNER = 'ICD'
   AND SEGMENT_NAME = 'T_CUSTLEVEL';

SELECT a.table_name, SUM(T.BYTES) / 1024 / 1024 || 'M' AS "大小"
  FROM DBA_TABLES A, DBA_SEGMENTS T
 WHERE a.TABLESPACE_NAME = UPPER('icd_service')
   AND a.tablespace_name = t.tablespace_name
   AND a.table_name = t.segment_name
 GROUP BY a.table_name

--临时表空间
select file_name,bytes/1024/1024 "MB",autoextensible,tablespace_name from dba_temp_files;

--查找哪个session用了多少临时段
select * from (
select sess.SID,  segtype, blocks*8/1000 "MB" ,sql_text
from v$sort_usage sort, v$session sess ,v$sql sql
where sort.SESSION_ADDR = sess.SADDR
and sql.ADDRESS = sort.SQLADDR (+)
order by blocks desc
) a
where rownum<10

--表空间使用率
1.
SELECT A.TABLESPACE_NAME,
       A.BYTES TOTAL,
       B.BYTES USED,
       C.BYTES FREE,
       (B.BYTES * 100) / A.BYTES "%   USED",
       (C.BYTES * 100) / A.BYTES "%   FREE"
  FROM SYS.SM$TS_AVAIL A, SYS.SM$TS_USED B, SYS.SM$TS_FREE C
 WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME
   AND A.TABLESPACE_NAME = C.TABLESPACE_NAME;

--查看默认表空间
SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS;

--增加数据文件

ALTER TABLESPACE "SERVICE_HIST_IDX"
    ADD
    DATAFILE '/dev/rma_idx08' SIZE 4000M;