--查看表空间使用情况1select a.tablespace_name, total, free, total - free used from (select tablespace_name, sum(bytes) / 1024 / 1024 total from dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) / 1024 / 1024 free from dba_free_space group by tablespace_name) b where a.tablespace_name = b.tablespace_name;--查看表空间使用情况2 select df.tablespace_name "Tablespace", df.bytes / (1024 * 1024) "Total Size(MB)", sum(fs.bytes) / (1024 * 1024) "Free Size(MB)", round(sum(fs.bytes) * 100 / df.bytes) "% Free", round((df.bytes - sum(fs.bytes)) * 100 / df.bytes) "% Used" from dba_free_space fs, (select tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) df where fs.tablespace_name = df.tablespace_name group by df.tablespace_name, df.bytes;--索引占用空间大小select SEGMENT_NAME, bytes / 1024 / 1024 || 'MB' from dba_segments where SEGMENT_TYPE = 'INDEX' AND TABLESPACE_NAME = 'IDXLASDB' order by bytes desc;select sum(bytes / 1024 / 1024) from dba_segments where SEGMENT_TYPE = 'INDEX' AND TABLESPACE_NAME = 'IDXLASDB';--表占用空间大小select SEGMENT_NAME, bytes / 1024 / 1024 || 'MB' from dba_segments where SEGMENT_TYPE = 'TABLE' AND TABLESPACE_NAME = 'LASDB' order by bytes desc;select sum(bytes / 1024 / 1024) || 'MB' from dba_segmentsNO NO NO where SEGMENT_TYPE = 'TABLE' AND TABLESPACE_NAME = 'LASDB';/*索引维护 http://blog.csdn.net/tianlesoftware/article/details/5680706*/--查看索引段中extent的数量：SELECT segment_name, COUNT(*) FROM dba_extents WHERE segment_type = 'INDEX' AND owner = 'LASDB' GROUP BY segment_name;--查看表空间内的索引的扩展情况：SELECT SUBSTR(segment_name, 1, 20), bytes, COUNT(bytes) FROM user_extents WHERE segment_name IN (SELECT index_name FROM user_indexes WHERE tablespace_name = 'IDXLASDB') GROUP BY segment_name, bytes ORDER BY segment_name;SELECT COUNT(*), owner, segment_name, tablespace_name FROM dba_extents WHERE segment_type = 'INDEX' AND owner NOT IN ('SYS', 'SYSTEM') GROUP BY owner, segment_name, tablespace_nameHAVING COUNT(*) > 10 ORDER BY COUNT(*) DESC;--创建索引重建语句SELECT 'alter index ' || owner || '.' || segment_name || ' rebuild;' FROM (SELECT COUNT(*), owner, segment_name, t.tablespace_name FROM dba_extents t WHERE t.segment_type = 'INDEX' AND t.owner NOT IN ('SYS', 'SYSTEM') GROUP BY owner, segment_name, t.tablespace_name HAVING COUNT(*) > 10 ORDER BY COUNT(*) DESC);--查看所有表空间的碎片程度（值在30以下表示碎片很多）select tablespace_name, sum(bytes), sum(free), sum(free) * 100 / sum(bytes) from (select b.file_id file_ID, b.tablespace_name tablespace_name, b.bytes Bytes, (b.bytes - sum(nvl(a.bytes, 0))) used, sum(nvl(a.bytes, 0)) free, sum(nvl(a.bytes, 0)) / (b.bytes) * 100 Percent from dba_free_space a, dba_data_files b where a.file_id = b.file_id group by b.tablespace_name, b.file_id, b.bytes order by b.file_id) group by tablespace_name order by sum(free) * 100 / sum(bytes);--查看主表所有的子表select 'alter table ' || c.table_name || ' disable constraint ' || c.constraint_name "fk", c.table_name child_table, p.constraint_name " RK", p.constraint_type, p.owner, p.table_name from user_constraints c, user_constraints p where c.constraint_type = 'R' and c.r_owner = p.owner and c.r_constraint_name = p.constraint_name and p.table_name = upper('tbl_device'); --查询用户下没有建主键的表SELECT table_name FROM all_tables WHERE owner = USERMINUSSELECT table_name FROM all_constraints WHERE owner = USER AND constraint_type = 'P';----查询用户下有建主键的表 SELECT table_name FROM all_tables WHERE owner = USERintersectSELECT table_name FROM all_constraints WHERE owner = USER AND constraint_type = 'P'/*扩展表空间3种方式:*/--首先查看表空间的名字和所属文件 select tablespace_name, file_id, file_name, round(bytes / (1024 * 1024), 0) total_space from dba_data_files;--1.增加数据文件:alter tablespace IDXLASDB add datafile '/opt/oracle/data/idxlasdb2.dbf' size 10000M--2.手动增加数据文件大小:alter database datafile '/opt/oracle/data/idxlasdb.dbf' resize 20000M--3.设定数据文件自动扩展 (重新设置datafile maxsize)alter database datafile '/opt/oracle/data/idxlasdb.dbf' autoextend on next 100M maxsize 20000M;--根据表空间修改数据库文件(确保表空间下只有一个数据库文件)example:declare p_file_name varchar2(100);begin select file_name into p_file_name from dba_data_files where TABLESPACE_NAME='IDXLASDB'; EXECUTE IMMEDIATE 'alter database datafile '''||p_file_name||''' autoextend on next 100M maxsize 20000M';end;--设定后查看表空间信息 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--查看表的高水位declareu1 number;u2 number;u3 number;u4 number;u5 number;u6 number;u7 number;beginDBMS_SPACE.UNUSED_SPACE('LASDB','TBL_OLS_REPORT_ONU_RUN_STATE','TABLE',u1,u2,u3,u4,u5,u6,u7);dbms_output.put_line('TOTAL_BLOCKS: '||u1);dbms_output.put_line('TOTAL_BYTES: '||u2);dbms_output.put_line('UNUSED_BLOCKS: '||u3);dbms_output.put_line('UNUSED_BYTES: '||u4);dbms_output.put_line('LAST_USED_EXTENT_FILE_ID: '||u5);dbms_output.put_line('LAST_USED_EXTENT_BLOCK_ID: '||u6);dbms_output.put_line('LAST_USED_BLOCK: '||u7);end;--查找耗用资源高的SQL语句select SQL_TEXT, EXECUTIONS, DISK_READS, BUFFER_GETS,DISK_READS / DECODE(EXECUTIONS, 0, 1, EXECUTIONS) as AVER_DISK,BUFFER_GETS / DECODE(EXECUTIONS, 0, 1, EXECUTIONS) as AVER_BUFFfrom v$sqlarea order by AVER_BUFF desc--server端字符集检查select userenv('language') from dual;--Data buffer 命中率检查select 1 - (phy.value / (cur.value + con.value)) "HIT RATIO" from v$sysstat cur, v$sysstat con, v$sysstat phy where cur.name = 'db block gets' and con.name = 'consistent gets' and phy.name = 'physical reads';--Dictionary cache命中率检查SELECT 1 - (SUM(getmisses)/SUM(gets)) "Data Dictionary Hit Ratio" FROM v$rowcache;--Library cache命中率检查SELECT 1-(SUM(reloads)/SUM(pins)) "Library cache Hit Ratio" FROM v$librarycache;
