SQL：表空间、用户，及其对象
显示了用户，及其对象所属的表空间与对象的大小的信息。
Shell Log：
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 |
SQL> @user_tbs.sql +------------------------------------------------------------------------+ | Report : Owner to Tablespace Report | | Instance : misrepdb1 | +------------------------------------------------------------------------+ Owner Tablespace Name Segment Type Size (in Bytes) Segment Count -------------------- ------------------------------ ------------------ ------------------ -------------- DBSNMP SYSAUX INDEX 655,360 10 SYSAUX TABLE 1,179,648 18 ******************** ------------------ -------------- 1,835,008 28 LZR USERS TABLE 196,608 2 ******************** ------------------ -------------- 196,608 2 OUTLN SYSTEM INDEX 262,144 4 SYSTEM LOBINDEX 65,536 1 SYSTEM LOBSEGMENT 65,536 1 SYSTEM TABLE 196,608 3 ******************** ------------------ -------------- 589,824 9 SYS SYSAUX INDEX 72,613,888 217 SYSAUX INDEX PARTITION 51,838,976 216 SYSAUX LOB PARTITION 65,536 1 SYSAUX LOBINDEX 2,293,760 35 SYSAUX LOBSEGMENT 4,718,592 35 SYSAUX NESTED TABLE 65,536 1 SYSAUX TABLE 53,739,520 178 SYSAUX TABLE PARTITION 83,755,008 205 SYSTEM CLUSTER 25,362,432 10 SYSTEM INDEX 51,445,760 453 SYSTEM LOBINDEX 3,801,088 58 SYSTEM LOBSEGMENT 7,471,104 58 SYSTEM NESTED TABLE 196,608 3 SYSTEM ROLLBACK 393,216 1 SYSTEM TABLE 106,168,320 364 UNDOTBS1 TYPE2 UNDO 12,845,056 10 UNDOTBS2 TYPE2 UNDO 4,718,592 10 ******************** ------------------ -------------- 481,492,992 1,855 SYSTEM SYSAUX INDEX 917,504 14 SYSAUX INDEX PARTITION 2,162,688 33 SYSAUX LOBINDEX 458,752 7 SYSAUX LOBSEGMENT 458,752 7 SYSAUX TABLE 1,441,792 22 SYSAUX TABLE PARTITION 1,769,472 27 SYSTEM INDEX 8,454,144 129 SYSTEM LOBINDEX 983,040 15 SYSTEM LOBSEGMENT 983,040 15 SYSTEM TABLE 5,570,560 85 ******************** ------------------ -------------- 23,199,744 354 TSMSYS SYSAUX INDEX 65,536 1 SYSAUX LOBINDEX 65,536 1 SYSAUX LOBSEGMENT 65,536 1 SYSAUX TABLE 65,536 1 ******************** ------------------ -------------- 262,144 4 WMSYS SYSAUX INDEX 3,866,624 59 SYSAUX LOBINDEX 524,288 8 SYSAUX LOBSEGMENT 524,288 8 SYSAUX NESTED TABLE 131,072 2 SYSAUX TABLE 2,424,832 37 ******************** ------------------ -------------- 7,471,104 114 YJJ USERS TABLE 1,900,544 29 ******************** ------------------ -------------- 1,900,544 29 ------------------ -------------- Grand Total: 516,947,968 2,395 44 rows selected. SQL> |
SQL:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
[oracle@misrep1 ~]$ cat user_tbs.sql SET TERMOUT OFF; COLUMN current_instance NEW_VALUE current_instance NOPRINT; SELECT rpad(sys_context('USERENV', 'INSTANCE_NAME'), 17) current_instance FROM dual; SET TERMOUT ON; PROMPT PROMPT +------------------------------------------------------------------------+ PROMPT | Report : Owner to Tablespace Report | PROMPT | Instance : ¤t_instance | PROMPT +------------------------------------------------------------------------+ SET ECHO OFF SET FEEDBACK 6 SET HEADING ON SET LINESIZE 180 SET PAGESIZE 50000 SET TERMOUT ON SET TIMING OFF SET TRIMOUT ON SET TRIMSPOOL ON SET VERIFY OFF CLEAR COLUMNS CLEAR BREAKS CLEAR COMPUTES COLUMN owner FORMAT a20 HEADING "Owner" COLUMN tablespace_name FORMAT a30 HEADING "Tablespace Name" COLUMN segment_type FORMAT a18 HEADING "Segment Type" COLUMN bytes FORMAT 9,999,999,999,999 HEADING "Size (in Bytes)" COLUMN seg_count FORMAT 9,999,999,999 HEADING "Segment Count" BREAK ON report ON owner SKIP 2 COMPUTE sum LABEL "" OF seg_count bytes ON owner COMPUTE sum LABEL "Grand Total: " OF seg_count bytes ON report SELECT owner , tablespace_name , segment_type , sum(bytes) bytes , count(*) seg_count FROM dba_segments GROUP BY owner , tablespace_name , segment_type ORDER BY owner , tablespace_name , segment_type / [oracle@misrep1 ~]$ |