prompt Space usage (as of &p_currdate.):
prompt clear breaks
clear computes
clear columns set heading on
column tablespace_name heading 'Tablespace' justify left format a20 truncated
column tbsize heading 'Size|in MB.' justify left format 999,990
column tbused heading 'Used|in MB.' justify right format 999,990
column tbfree heading 'Free|in MB.' justify right format 999,990
column tbusedpct heading '' justify left format a6
column tbfreepct heading '' justify left format a6 break on report
compute SUM label 'Totals:' of tbsize tbused tbfree on report
-- - - - - - - - - - - - - - - - - - - - - - - - - -
SELECT t.tablespace_name,
ROUND(a.bytes) tbsize,
NVL(ROUND(b.bytes),0) tbused,
'(' || TO_CHAR(ROUND(100*(NVL(b.bytes,0)/NVL(a.bytes,0))))
|| '%)' tbusedpct,
NVL(ROUND(c.bytes),0) tbfree,
'(' || TO_CHAR(ROUND(100*(NVL(c.bytes,0)/NVL(a.bytes,0))))
|| '%)' tbfreepct
FROM dba_tablespaces t,
(
SELECT tablespace_name,
SUM(bytes)/1024/1024 bytes
FROM dba_data_files
GROUP BY tablespace_name
a,
(
SELECT e.tablespace_name,
SUM(e.bytes)/1024/1024 bytes
FROM dba_extents e
GROUP BY e.tablespace_name b,
(
SELECT f.tablespace_name,
SUM(f.bytes)/1024/1024 bytes
FROM dba_free_space f
GROUP BY f.tablespace_name
c
WHERE t.tablespace_name = a.tablespace_name(+)
AND t.tablespace_name = b.tablespace_name(+)
AND t.tablespace_name = c.tablespace_name(+)
;
prompt clear breaks
clear computes
clear columns set heading on
column tablespace_name heading 'Tablespace' justify left format a20 truncated
column tbsize heading 'Size|in MB.' justify left format 999,990
column tbused heading 'Used|in MB.' justify right format 999,990
column tbfree heading 'Free|in MB.' justify right format 999,990
column tbusedpct heading '' justify left format a6
column tbfreepct heading '' justify left format a6 break on report
compute SUM label 'Totals:' of tbsize tbused tbfree on report
-- - - - - - - - - - - - - - - - - - - - - - - - - -
SELECT t.tablespace_name,
ROUND(a.bytes) tbsize,
NVL(ROUND(b.bytes),0) tbused,
'(' || TO_CHAR(ROUND(100*(NVL(b.bytes,0)/NVL(a.bytes,0))))
|| '%)' tbusedpct,
NVL(ROUND(c.bytes),0) tbfree,
'(' || TO_CHAR(ROUND(100*(NVL(c.bytes,0)/NVL(a.bytes,0))))
|| '%)' tbfreepct
FROM dba_tablespaces t,
(
SELECT tablespace_name,
SUM(bytes)/1024/1024 bytes
FROM dba_data_files
GROUP BY tablespace_name
a,
(
SELECT e.tablespace_name,
SUM(e.bytes)/1024/1024 bytes
FROM dba_extents e
GROUP BY e.tablespace_name b,
(
SELECT f.tablespace_name,
SUM(f.bytes)/1024/1024 bytes
FROM dba_free_space f
GROUP BY f.tablespace_name
c
WHERE t.tablespace_name = a.tablespace_name(+)
AND t.tablespace_name = b.tablespace_name(+)
AND t.tablespace_name = c.tablespace_name(+)
;
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;2、查看表空间物理文件的名称及大小select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;3、查看回滚段名称及大小select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;4、查看控制文件select name from v$controlfile;5、查看日志文件select member from v$logfile;6、查看表空间的使用情况select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space
group by tablespace_name;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; 7、查看数据库库对象select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;8、查看数据库的版本 Select version FROM Product_component_version
Where SUBSTR(PRODUCT,1,6)='Oracle';9、查看数据库的创建日期和归档方式Select Created, Log_Mode, Log_Mode From V$Database;
(p_segname in varchar2,
p_type in varchar2 default 'TABLE' ,
p_owner in varchar2 default user) AS
v_segname varchar2(100);
v_type varchar2(10);
l_free_blks number;
l_total_blocks number;
l_total_bytes number;
l_unused_blocks number;
l_unused_bytes number;
l_LastUsedExtFileId number;
l_LastUsedExtBlockId number;
l_LAST_USED_BLOCK number;
PROCEDURE p( p_label in varchar2, p_num in number )
IS
BEGIN
dbms_output.put_line( rpad(p_label,40,'.')|| p_num );
END;
BEGIN
v_segname := upper(p_segname);
v_type := p_type;
if (p_type = 'i' or p_type = 'I') then
v_type := 'INDEX';
end if;
if (p_type = 't' or p_type = 'T') then
v_type := 'TABLE'; end if;
if (p_type = 'c' or p_type = 'C') then
v_type := 'CLUSTER';
end if;
--以下部分不能用于ASSM
dbms_space.free_blocks ( segment_owner => p_owner, segment_name => v_segname, segment_type => v_type, freelist_group_id => 0, free_blks => l_free_blks );
--以上部分不能用于ASSM dbms_space.unused_space ( segment_owner => p_owner, segment_name => v_segname, segment_type => v_type, total_blocks => l_total_blocks, total_bytes => l_total_bytes, unused_blocks => l_unused_blocks, unused_bytes => l_unused_bytes, LAST_USED_EXTENT_FILE_ID => l_LastUsedExtFileId, LAST_USED_EXTENT_BLOCK_ID => l_LastUsedExtBlockId, LAST_USED_BLOCK => l_LAST_USED_BLOCK );
--显示结果
p( 'Free Blocks', l_free_blks );
p( 'Total Blocks', l_total_blocks );
p( 'Total Bytes', l_total_bytes );
p( 'Unused Blocks', l_unused_blocks );
p( 'Unused Bytes', l_unused_bytes );
p( 'Last Used Ext FileId', l_LastUsedExtFileId );
p( 'Last Used Ext BlockId', l_LastUsedExtBlockId );
p( 'Last Used Block', l_LAST_USED_BLOCK );
END; 执行结果将如下所示 SQL> set serveroutput on;
SQL> exec show_space('test');
Free Blocks.............................1
Total Blocks............................8
Total Bytes.............................65536
Unused Blocks...........................6
Unused Bytes............................49152
Last Used Ext FileId....................1
Last Used Ext BlockId...................48521
Last Used Block.........................2
PL/SQL procedure successfully completed