Tips for DBA Part 6

Print The Ourput in HTML


sqlplus -S -M "HTML ON TABLE 'BORDER="2"'" username/passwd@server_name  @c:\a.txt > c:\a.html
set longchunksize 13000
set long 13000
set linesize 15000
select long_desc from ag_product_content where rownum <10 and long_desc is not null and rownum < 10;
exit



Table Count Script:

set serveroutput on
declare
v_str varchar2(4000):=null;
v_cnt number;
begin
for i in (select log_table, master from user_mview_logs)
loop
v_str:='Select count(1) from '||i.log_table;
execute immediate v_str into v_cnt;
if v_cnt>0 then
dbms_output.put_line('Master Table name: '||i.master||chr(9)||' Log Table_Name: '||i.log_table||chr(9)||' Count: '||v_cnt);
end if;
End loop;
End;



Table Space Size Details

SELECT  t. tablespace_name,t.total_space_in_GB , f.free_space_in_GB--, TO_CHAR((f.free_space_in_MB*100/t.total_space_in_MB),'99990.000') "Free%"
FROM (SELECT tablespace_name, trunc(SUM(bytes)/1024/1024/1024,2) Total_space_in_GB FROM DBA_DATA_FILES GROUP BY tablespace_name) t, 
(SELECT tablespace_name, trunc(SUM(bytes)/1024/1024/1024,2) Free_space_in_GB FROM DBA_FREE_SPACE GROUP BY tablespace_name) f
WHERE  t.tablespace_name= f.tablespace_name
order by 1;
SELECT aus.tablespace_name, df.file_name, df.TOTAL_SPACE_MB, NVL(dfs.FREE_SPACE_MB,0) Free_space_MB,
NVL(trunc( (free_space_mb*100/total_space_mb),2) ,0)"FREE%", TRUNC(df.TOTAL_SPACE_MB-USED_SPACE_MB ) Reclaim_space_MB, AUTOEXTENSIBLE, MAXBYTES_MB,INCREMENT_BY_MB
FROM (SELECT  tablespace_name, file_id,SUM(bytes)/1024/1024 AS "FREE_SPACE_MB" FROM DBA_FREE_SPACE GROUP BY tablespace_name, file_id) dfs, (SELECT file_id, file_name, SUM(bytes)/1024/1024 AS "TOTAL_SPACE_MB" FROM DBA_DATA_FILES GROUP BY file_id, file_name) df, (SELECT DISTINCT f.TABLESPACE_NAME, file_name,((ROUND(f.bytes / 1024 / 1024) - NVL(ROUND(s.bytes / 1024 / 1024),0))) "USED_SPACE_MB", f.AUTOEXTENSIBLE, TRUNC(f.MAXBYTES/1024/1024,2) MAXBYTES_MB, TRUNC(f.INCREMENT_BY/128,2) INCREMENT_BY_MB FROM DBA_DATA_FILES f, DBA_FREE_SPACE s WHERE f.file_id = s.file_id (+)
AND NVL(s.block_id,0) IN (NVL((SELECT MAX(block_id) FROM DBA_FREE_SPACE WHERE file_id = s.file_id),0))) aus
WHERE dfs.FILE_ID (+)= df.FILE_ID
AND aus.file_name (+)= df.file_name
ORDER BY 1,2;

select tablespace_name, count(1) No_of_DataFiles, sum(TOTAL_SPACE_MB) TOTAL_SPACE_MB, sum(FREE_SPACE_MB) FREE_SPACE_MB, sum(Reclaim_space_MB) Reclaim_space_MB from
(SELECT aus.tablespace_name, df.file_name, df.TOTAL_SPACE_MB, NVL(dfs.FREE_SPACE_MB,0) Free_space_MB,
NVL(trunc( (free_space_mb*100/total_space_mb),2) ,0)"FREE%", TRUNC(df.TOTAL_SPACE_MB-USED_SPACE_MB ) Reclaim_space_MB, AUTOEXTENSIBLE, MAXBYTES_MB,INCREMENT_BY_MB
FROM (SELECT  tablespace_name, file_id,SUM(bytes)/1024/1024 AS "FREE_SPACE_MB" FROM DBA_FREE_SPACE GROUP BY tablespace_name, file_id) dfs, (SELECT file_id, file_name, SUM(bytes)/1024/1024 AS "TOTAL_SPACE_MB" FROM DBA_DATA_FILES GROUP BY file_id, file_name) df, (SELECT DISTINCT f.TABLESPACE_NAME, file_name,((ROUND(f.bytes / 1024 / 1024) - NVL(ROUND(s.bytes / 1024 / 1024),0))) "USED_SPACE_MB", f.AUTOEXTENSIBLE, TRUNC(f.MAXBYTES/1024/1024,2) MAXBYTES_MB, TRUNC(f.INCREMENT_BY/128,2) INCREMENT_BY_MB FROM DBA_DATA_FILES f, DBA_FREE_SPACE s WHERE f.file_id = s.file_id (+)
AND NVL(s.block_id,0) IN (NVL((SELECT MAX(block_id) FROM DBA_FREE_SPACE WHERE file_id = s.file_id),0))) aus
WHERE dfs.FILE_ID (+)= df.FILE_ID
AND aus.file_name (+)= df.file_name)
group by tablespace_name
ORDER BY 1;
select owner, sum(bytes)/1024 from dba_segments
where owner in ('USER','READ','DBA')
group by owner
order by 1
select owner, trunc(sum(bytes)/1024/1024/1024,2) size_in_gb from dba_segments group by owner order by 2 desc;
select * from dba_objects where owner in ('TOAD');
select * from dba_recyclebin;
select trunc(sum(bytes)/1024/1024,2) size_in_mb from dba_segments where (owner, segment_name)  in (select owner, object_name from dba_recyclebin);