select d.tablespace_name,round((d.max – u.bytes)/1024/1024,2) as max_free_mbytes,round(u.bytes*100/d.max,2) as used_pctfrom sys.SM$TS_USED u,(select tablespace_name,sum(decode(MAXBYTES,0,bytes,maxbytes)) maxfrom sys.dba_data_filesgroup by tablespace_name) dwhere u.tablespace_name = d.tablespace_name andround(u.bytes*100/d.max,2)>70;
Read MoreTo Find % of free space left in Tablespace select (BYTES/1024)/1024 “Used Space(MB)”, total “allocated size(MB)”, maxi “maximum allowable (MB)”, maxi-(BYTES/1024)/1024 “effectivefree(MB)”, –maxi-total “free(MB)”, round(((maxi-(BYTES/1024)/1024)/maxi)*100,2) “% FREE” from SM$TS_USED,(select sum((BYTES/1024)/1024)…
Read Moreset linesize 150set pagesize 1000col name for a50col parameter for a30col value for a30col member for a50col DATAFILE_NAME for a70col TEMPFILE_NAME for a70col CONTROLFILE_NAME for a70col LOGFILE_MEMBER for a70 select…
Read MoreMethod 1) sqlplus “/as sysdba”-> Verify the bit in the banner message of SQLPLUS OR “select banner from v$version;” Method 2) $ cd $ORACLE_HOME/bin …
Read Moreselect instance_name||’, up since ‘|| to_char(startup_time,’DD-MON-YYYY HH24:MI:SS’) start_time from gv$instance;
Read Moreset numf 999999.99 select sum(bytes)/(1024*1024*1024) “Total Allocated GB”,Total_Consumed_GB from dba_data_files, (select sum(bytes)/(1024*1024*1024) Total_Consumed_GB from dba_segments) group by Total_Consumed_GB;
Read Moreset linesize 150 set pagesize 5000 col owner for a15 col segment_name for a30 col segment_type for a20 col TABLESPACE_NAME for a30 clear breaks clear computes compute sum of SIZE_IN_GB…
Read Morecol member for a65 select lf.MEMBER,l.GROUP#,THREAD#,SEQUENCE#,MEMBERS,bytes/1024/1024 “BYTES IN MB”,ARCHIVED,l.status from v$log l,v$logfile lf where l.GROUP#=lf.GROUP#
Read Morecol name for a90 select * from v$controlfile
Read Moreselect tablespace_name,file_name,bytes/1024/1024 “SIZE_IN_MB” from dba_temp_files order by file_name asc;
Read More