|
2）表空间 SQL> set linesize 300 SQL> col tablespace_name for a16 SQL> SELECT upper(f.tablespace_name) "tablespace_name", d.Tot_grootte_Mb "tablespace(M)", d.Tot_grootte_Mb - f.total_bytes "used(M)", round((d.Tot_grootte_Mb - f.total_bytes) / d.Tot_grootte_Mb * 100,2) "use%", f.total_bytes "free_space(M)", round(f.total_bytes / d.Tot_grootte_Mb * 100,2) "free%", f.max_bytes "max_block(M)" FROM (SELECT tablespace_name, round(SUM(bytes)/(1024*1024),2) total_bytes, round(MAX(bytes)/(1024*1024),2) max_bytes FROM sys.dba_free_space GROUP BY tablespace_name) f, (SELECT dd.tablespace_name, round(SUM(dd.bytes)/(1024*1024),2) Tot_grootte_Mb FROM sys.dba_data_files dd GROUP BY dd.tablespace_name) d WHERE d.tablespace_name = f.tablespace_name ORDER BY 4 DESC;
tablespace_name tablespace(M) used(M) use% free_space(M) free% max_block(M) ---------------- ------------- ---------- ---------- ------------- ---------- ------------ SYSTEM 680 673.31 99.02 6.69 .98 6 SYSAUX 570 534.94 93.85 35.06 6.15 35.06 UNDOTBS2 25 11.31 45.24 13.69 54.76 4.5 USERS 5 1.31 26.2 3.69 73.8 3.69 UNDOTBS1 95 18.12 19.07 76.88 80.93 53
表空间的空间使用情况 SQL> SELECT df.tablespace_name, COUNT (*) datafile_count, ROUND (SUM (df.BYTES) / 1048576) size_mb, ROUND (SUM (free.BYTES) / 1048576, 2) free_mb, ROUND (SUM (df.BYTES) / 1048576 - SUM (free.BYTES) / 1048576, 2 ) used_mb, ROUND (MAX (free.maxbytes) / 1048576, 2) maxfree, 100 - ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_used, ROUND (100.0 * SUM (free.BYTES) / SUM (df.BYTES), 2) pct_free FROM dba_data_files df, (SELECT tablespace_name, file_id, SUM (BYTES) BYTES, MAX (BYTES) maxbytes FROM dba_free_space GROUP BY tablespace_name, file_id) free WHERE df.tablespace_name = free.tablespace_name(+) AND df.file_id = free.file_id(+) GROUP BY df.tablespace_name ;
TABLESPACE_NAME DATAFILE_COUNT SIZE_MB FREE_MB USED_MB MAXFREE PCT_USED PCT_FREE ---------------- -------------- ---------- ---------- ---------- ---------- ---------- ---------- UNDOTBS1 1 95 76.88 18.13 53 19.08 80.92 SYSAUX 1 570 35.06 534.94 35.06 93.85 6.15 USERS 1 5 3.69 1.31 3.69 26.25 73.75 SYSTEM 1 680 6.69 673.31 6 99.02 .98 UNDOTBS2 1 25 13.69 11.31 4.5 45.25 54.75
表空间可用性检查 SQL> select tablespace_name,status from dba_tablespaces; TABLESPACE_NAME STATUS ---------------- --------- SYSTEM ONLINE SYSAUX ONLINE UNDOTBS1 ONLINE TEMP ONLINE USERS ONLINE UNDOTBS2 ONLINE
临时表空间使用情况和性能检查 SQL> SELECT tablespace_name, extent_management, block_size, initial_extent, next_extent, min_extents, max_extents, pct_increase FROM dba_tablespaces WHERE C;
TABLESPACE_NAME EXTENT_MAN BLOCK_SIZE INITIAL_EXTENT NEXT_EXTENT MIN_EXTENTS MAX_EXTENTS PCT_INCREASE ---------------- ---------- ---------- -------------- ----------- ----------- ----------- ------------ TEMP LOCAL 8192 1048576 1048576 1 0
SQL> SELECT username, default_tablespace, temporary_tablespace FROM dba_users;
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------- ----------------------------- ------------------------------ MGMT_VIEW SYSTEM TEMP SYS SYSTEM TEMP SYSTEM SYSTEM TEMP DBSNMP SYSAUX TEMP SYSMAN SYSAUX TEMP OUTLN SYSTEM TEMP FLOWS_FILES SYSAUX TEMP MDSYS SYSAUX TEMP ORDSYS SYSAUX TEMP EXFSYS SYSAUX TEMP WMSYS SYSAUX TEMP
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE -------------------------- ---------------------------- ------------------------------ APPQOSSYS SYSAUX TEMP APEX_030200 SYSAUX TEMP OWBSYS_AUDIT SYSAUX TEMP ORDDATA SYSAUX TEMP CTXSYS SYSAUX TEMP ANONYMOUS SYSAUX TEMP XDB SYSAUX TEMP ORDPLUGINS SYSAUX TEMP OWBSYS SYSAUX TEMP SI_INFORMTN_SCHEMA SYSAUX TEMP OLAPSYS SYSAUX TEMP
USERNAME DEFAULT_TABLESPACE TEMPORARY_TABLESPACE ------------------------- ---------------------------- ------------------------------ SCOTT USERS TEMP ORACLE_OCM USERS TEMP XS$NULL USERS TEMP MDDATA USERS TEMP DIP USERS TEMP APEX_PUBLIC_USER USERS TEMP SPATIAL_CSW_ADMIN_USR USERS TEMP SPATIAL_WFS_ADMIN_USR USERS TEMP
SQL>select tablespace_name,initial_extent,next_extent,max_extents,pct_increase,extent_management,status from dba_tablespaces order by extent_management;
TABLESPACE_NAME INITIAL_EXTENT NEXT_EXTENT MAX_EXTENTS PCT_INCREASE EXTENT_MAN STATUS ---------------- -------------- ----------- ----------- ------------ ---------- --------- SYSTEM 65536 2147483645 LOCAL ONLINE SYSAUX 65536 2147483645 LOCAL ONLINE UNDOTBS2 65536 2147483645 LOCAL ONLINE TEMP 1048576 1048576 0 LOCAL ONLINE USERS 65536 2147483645 LOCAL ONLINE UNDOTBS1 65536 2147483645 LOCAL ONLINE
SQL> select tablespace_name,EXTENT_MANAGEMENT,SEGMENT_SPACE_MANAGEMENT from dba_tablespaces; TABLESPACE_NAME EXTENT_MAN SEGMEN ---------------- ---------- ------ SYSTEM LOCAL MANUAL SYSAUX LOCAL AUTO UNDOTBS1 LOCAL MANUAL TEMP LOCAL MANUAL USERS LOCAL AUTO UNDOTBS2 LOCAL MANUAL
| 
|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛
( 渝ICP备12004239号-4 )
楼主