SELECT a.tablespace_name, a.used File_size,a.maxallocated,
(a.used -b.freespace) Actualused,a.maxallocated -(a.used-b.freespace) actuallFree,
ROUND((((a.used-b.freespace)/a.maxallocated)*100),2) "percent_used",
100 - ROUND((((a.used-b.freespace)/a.maxallocated)*100),2) "percent_free"
FROM
(SELECT tablespace_name,
SUM(bytes) used,
SUM(maxallocated) maxallocated
FROM
(SELECT tablespace_name,
ROUND((a.bytes/1048576),2) bytes,
CASE
WHEN round(maxbytes,2)<A.BYTES
THEN ROUND((A.bytes /1048576),2)
ELSE ROUND((A.maxbytes/1048576),2)
END maxallocated
FROM sys.DBA_DATA_FILES a
)
GROUP BY tablespace_name
)a,
(SELECT tablespace_name ,
ROUND(SUM(bytes/1048576),2) freespace
FROM dba_free_space
GROUP BY TABLESPACE_NAME
)B
where a.TABLESPACE_NAME=B.TABLESPACE_NAME (+)
ORDER BY 6 DESC
---------------------------------------------------------------
Enable/Disabled Constraint
---------------------------------------------------------------
select ' alter table AFDBA.'|| table_name || ' enable constraint ' || constraint_name || ' ; ' from dba_constraints where owner = 'abcd'
and constraint_type in ('R') and status = 'DISABLED'
select table_name , constraint_name , status
from dba_constraints where owner = 'abcd' and constraint_type in ('R')
and status = 'ENABLED' ;
---------------------------------------------------------------
Enable/Disabled Trigger
---------------------------------------------------------------
select ' alter table AFDBA.'|| table_name || ' enable ALL TRIGGERS ; '
from dba_triggers where owner = 'abcd' ;
---------------------------------------------------------------
Check Trigger Status
---------------------------------------------------------------
select * from dba_triggers where status = 'DISABLED' and owner = 'abcd' ;
---------------------------------------------------------------
Check Index Status
---------------------------------------------------------------
select table_name , index_name , status
from dba_indexes
where owner = 'AFDBA'
and status = 'UNUSABLE'
---------------------------------------------------------------
Check Constaint Status
---------------------------------------------------------------
SELECT
constraint_name,
constraint_type,
table_name,
search_condition,
r_constraint_name,
status,
deferrable,
deferred,
validated,
index_name
FROM
all_constraints
WHERE
-- table_name = 'SPRING_SESSION_ATTRIBUTES' and owner='ADMIN';
table_name = 'CUSTOMER-sale' and owner='test';