#FULL DB TABLESPACE CHECK#
------------------------------------------------------------
SELECT FreeSpace.Tablespace_Name "Tablespace" ,
DataFiles.TotalSpace "Total MB" ,
( DataFiles.TotalSpace - FreeSpace.FreeSpace ) "Used MB",
FreeSpace.FreeSpace "Free MB" ,
ROUND ( 100 * ( FreeSpace.FreeSpace / DataFiles.TotalSpace ) ) "Pct.Free"
FROM
( SELECT Tablespace_Name,
ROUND ( SUM ( Bytes ) / ( 1024 * 1024 ) ) TotalSpace
FROM DBA_DATA_FILES
GROUP BY Tablespace_Name
) DataFiles ,
( SELECT Tablespace_Name,
ROUND ( SUM ( Bytes ) / ( 1024 * 1024 ) ) FreeSpace
FROM DBA_FREE_SPACE
GROUP BY Tablespace_Name
) FreeSpace
WHERE DataFiles.Tablespace_Name = FreeSpace.Tablespace_Name
ORDER BY 5 DESC;
TEMP_TABLESPACE :
---------------------------
SET PAGESIZE 60
SET LINESIZE 300
SELECT
A.tablespace_name tablespace,
D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM
v$sort_segment A,
(
SELECT
B.name,
C.block_size,
SUM (C.bytes) / 1024 / 1024 mb_total
FROM
v$tablespace B,
v$tempfile C
WHERE
B.ts#= C.ts#
GROUP BY
B.name,
C.block_size
) D
WHERE
A.tablespace_name = D.name
GROUP by
A.tablespace_name,
D.mb_total
------------------------------------------------------------
SELECT FreeSpace.Tablespace_Name "Tablespace" ,
DataFiles.TotalSpace "Total MB" ,
( DataFiles.TotalSpace - FreeSpace.FreeSpace ) "Used MB",
FreeSpace.FreeSpace "Free MB" ,
ROUND ( 100 * ( FreeSpace.FreeSpace / DataFiles.TotalSpace ) ) "Pct.Free"
FROM
( SELECT Tablespace_Name,
ROUND ( SUM ( Bytes ) / ( 1024 * 1024 ) ) TotalSpace
FROM DBA_DATA_FILES
GROUP BY Tablespace_Name
) DataFiles ,
( SELECT Tablespace_Name,
ROUND ( SUM ( Bytes ) / ( 1024 * 1024 ) ) FreeSpace
FROM DBA_FREE_SPACE
GROUP BY Tablespace_Name
) FreeSpace
WHERE DataFiles.Tablespace_Name = FreeSpace.Tablespace_Name
ORDER BY 5 DESC;
TEMP_TABLESPACE :
---------------------------
SET PAGESIZE 60
SET LINESIZE 300
SELECT
A.tablespace_name tablespace,
D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM
v$sort_segment A,
(
SELECT
B.name,
C.block_size,
SUM (C.bytes) / 1024 / 1024 mb_total
FROM
v$tablespace B,
v$tempfile C
WHERE
B.ts#= C.ts#
GROUP BY
B.name,
C.block_size
) D
WHERE
A.tablespace_name = D.name
GROUP by
A.tablespace_name,
D.mb_total
##ASM_USAGE##
================
SELECT name,group_number, total_mb,free_mb,(total_mb - free_mb)used_mb,ROUND((free_mb/total_mb)*100,2) "%FREE" , ROUND(((total_mb - free_mb)/total_mb)*100,2) "%USED" FROM v$asm_diskgroup;
ASM_DISK LEVEL DETAIL##
========================
set lines 300 pages 300
col NAME for a20
col PATH for a30
col LABEL for a20
col HEADER_STATUS for a10
select name,label,GROUP_NUMBER,DISK_NUMBER,HEADER_STATUS,STATE,TOTAL_MB/1024,FREE_MB/1024,OS_MB/1024,path from v$asm_disk order by 1,2;
//
select name
,round(TOTAL_MB/1024/2,0) "TOTAL GB"
,round((TOTAL_MB/1024/2)-(FREE_MB/1024/2),0) "USED GB"
,round(FREE_MB/1024/2,0) "FREE GB"
,round(REQUIRED_MIRROR_FREE_MB/1024/2,0) "REQUIRED FREE GB"
,round(USABLE_FILE_MB/1024,0) "USABLE FREE (GB)"
,round((USABLE_FILE_MB/1024)/(TOTAL_MB/1024/2)*100,0) "USABLE PERCENT"
from v$asm_diskgroup
where name like '%DATA%'
DATA_FILE_CHECKS :
===================
select file_name,FILE_ID,bytes/1024/1024/1024,maxbytes/1024/1024/1024,autoextensible from dba_data_files where tablespace_name='&1';
select tablespace_name,used_percent from dba_tablespace_usage_metrics where used_percent > 80;
select sum(bytes)/1024/1024/1024 from dba_data_files where tablespace_name='&1';
select sum(bytes)/1024/1024/1024 from dba_free_space where tablespace_name='&1';
SELECT s.username, s.sid, u.TABLESPACE, u.CONTENTS, u.extents, u.blocks*8/1024 "MB util" FROM v$session s, v$sort_usage u WHERE s.saddr = u.session_addr order by 6 desc;
Database_size:
============
-- get database size from v$datafile:
============================
select round((sum(bytes)/1048576/1024),2)"DB_SIZE_GB" from v$datafile;
-- get Oracle database size from dba_data_files:
=======================================
select
"Reserved_Space(MB)", "Reserved_Space(MB)" - "Free_Space(MB)" "Used_Space(MB)","Free_Space(MB)"
from(
select
(select sum(bytes/(1014*1024)) from dba_data_files) "Reserved_Space(MB)",
(select sum(bytes/(1024*1024)) from dba_free_space) "Free_Space(MB)"
from dual
);
db_recovery_file_dest_size check:
============================
col name for a32
col size_m for 999,999,999
col used_m for 999,999,999
col pct_used for 999
SELECT name
, ceil( space_limit / 1024 / 1024) SIZE_M
, ceil( space_used / 1024 / 1024) USED_M
, decode( nvl( space_used, 0),
0, 0
, ceil ( ( space_used / space_limit) * 100) ) PCT_USED
FROM v$recovery_file_dest
ORDER BY name
/
## To see how much UNDO information has been written in the past hour ##
//
select begin_time, end_time, undoblks, txncount, maxconcurrency as maxcon,nospaceerrcnt from V$UNDOSTAT where begin_time > sysdate-(4/24);
//
I guess I am the only one who comes here to share my very own experience guess what? I am using my laptop for almost the post 2 years.
ReplyDeleteTotalSpaces Crack
Ample Guitar VST Crack
SmartDraw Crack
ApowerManager Crack