Pavan DBA's Blog

The DBA Knowledge Store

Archive for the ‘Administration’ Category

script to check tablespace free space for all db’s in server

Posted by Pavan DBA on May 24, 2012


***************************************************
script to check tablespace free space for all db’s in server
***************************************************

#!/bin/csh

setenv ORATAB /var/opt/oracle/oratab
foreach x (`cat ${ORATAB} | grep -v “^#”| grep “^[a-z]” | grep -v “demo” |grep -v _sp | grep -v “test”| awk -F:

‘{print $1}’`)
        setenv ORAENV_ASK 1
        setenv ORACLE_SID “$x”
        source /usr/local/default/oracle_sid.csh
            sqlplus -s <<EOF
        / as sysdba
col total_space format 999,999,999,999
col free_space format 999,999,999,999
col pct_used format 999.99
col value new_value sid
set termout off
set head off
select value from v\$parameter where name=’db_name’;
set termout on
ttitle sid ” – Tablespaces Free space information” skip 2

set head on
set lines 234
set pages 100

SELECT /* + RULE */  df.tablespace_name “Tablespace”,
       df.bytes / (1024 * 1024) “Size (MB)”,
       round(SUM(fs.bytes) / (1024 * 1024)) “Free (MB)”,
       Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) “% Free”,
       Round((df.bytes – SUM(fs.bytes)) / 1024/1024) “Used space”,
       Round((df.bytes – SUM(fs.bytes)) * 100 / df.bytes) “% Used”
  FROM dba_free_space fs,
       (SELECT tablespace_name,SUM(bytes) bytes
          FROM dba_data_files
         GROUP BY tablespace_name) df
 WHERE fs.tablespace_name (+)  = df.tablespace_name
 GROUP BY df.tablespace_name,df.bytes
 order by  “% Used” desc
/
        exit
EOF
end

Note: This script is written in C shell. Do make changes if you are using other shell. Also, test it before…

Posted in Administration, Scripts | Tagged: | 2 Comments »

script to get row count of all tables in a schema

Posted by Pavan DBA on May 24, 2012


***************************************************
script to get row count of all tables in a schema
***************************************************
#!/bin/csh

            sqlplus -s <<EOF
username/password  
spool tablecount.log
select
      table_name,
      to_number(
        extractvalue(
          xmltype(dbms_xmlgen.getxml(‘select count(*) c from ‘||table_name))
          ,’/ROWSET/ROW/C’)
          )
          count
    from user_tables order by table_name;
spool off
        exit
EOF

Note: This script is written in C shell. Do necessary changes if you are using any other shell. Do test it before executing in prod env’s

Posted in Administration, Scripts | Tagged: | Leave a Comment »

script to check db status in a server

Posted by Pavan DBA on May 24, 2012


***************************************************
script to check db status in a server
***************************************************

#!/bin/csh

setenv ORATAB /var/opt/oracle/oratab
date
foreach x (`cat ${ORATAB} | grep -v “^#”| grep “^[a-z]” | grep -v “demo” | grep -v “test”| grep -v “*” | awk -F:

‘{print $1}’`)
        setenv ORAENV_ASK 1
        setenv ORACLE_SID “$x”
        source /usr/local/default/oracle_sid.csh
            sqlplus -s <<EOF
        / as sysdba
        set lines 132 pages 200 hea off feedback off trims on
        col host_name for a20
                spool chk_dbstatus.log
        select instance_name, host_name, logins, version, status from v\$instance;
                spool off
        exit
EOF

Note: The above script is written using “C” shell, so if you are using any other shell, make necessary changes. Also as always first test it in lower environments

Posted in Administration, Scripts | Tagged: , , | Leave a Comment »

script to check possible resize value for a datafile

Posted by Pavan DBA on March 30, 2012


Sometimes, we will be getting a requirement to resize the datafile to a lower value in order to create some space in the mount point (file system). In that case, how we would know which datafile and to what size we can lower it? The below script will helps us in that….

###################################################

SCRIPT TO CALCUTE POSSIBLE RESIZE VALUE

###################################################

 

 

set verify off

column file_name format a50 word_wrapped

column smallest format 999,990 heading “Smallest|Size|Poss.”

column currsize format 999,990 heading “Current|Size”

column savings format 999,990 heading “Poss.|Savings”

break on report

compute sum of savings on report

 

column value new_val blksize

select value from v$parameter where name = ‘db_block_size’

/

 

select file_name,

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

ceil( blocks*&&blksize/1024/1024) currsize,

ceil( blocks*&&blksize/1024/1024) –

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

/

 

select file_name,

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) smallest,

ceil( blocks*&&blksize/1024/1024) currsize,

ceil( blocks*&&blksize/1024/1024) –

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) savings

from dba_temp_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

/

 

column cmd format a75 word_wrapped

 

select ‘alter database datafile ”’ || file_name || ”’ resize ‘ ||

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || ‘m;’ cmd

from dba_data_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

and ceil( blocks*&&blksize/1024/1024) –

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0

/

 

 

select ‘alter database tempfile ”’ || file_name || ”’ resize ‘ ||

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) || ‘m;’ cmd

from dba_temp_files a,

( select file_id, max(block_id+blocks-1) hwm

from dba_extents

group by file_id ) b

where a.file_id = b.file_id(+)

and ceil( blocks*&&blksize/1024/1024) –

ceil( (nvl(hwm,1)*&&blksize)/1024/1024 ) > 0

/

 

Posted in Administration, Scripts | Tagged: , | 2 Comments »

script to check free space in a datafile

Posted by Pavan DBA on March 30, 2012


Friends, Many a times we will be requiring to resize a datafile. For suppose, we have a tablespace with 3 datafiles with equal size. Then how we would be knowing which datafile to increase?? It is based on free space in a datafile. So, the below script will provide the info of how much free space is left in each datafile for a tablespace

#################################################

DATAFILE FREE SIZE

#################################################

 

SELECT SUBSTR (df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024 allocated_mb,

((df.bytes / 1024 / 1024) – NVL (SUM (dfs.bytes) / 1024 / 1024, 0))

used_mb,

NVL (SUM (dfs.bytes) / 1024 / 1024, 0) free_space_mb

FROM v$datafile df, dba_free_space dfs

WHERE df.file# = dfs.file_id(+)

GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes,dfs.tablespace_name

ORDER BY file_name;

Posted in Administration, Scripts | Tagged: , | 1 Comment »

 
%d bloggers like this: