Script to check free space in a datafile

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;

Script to check possible resize value for a datafile

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

/

Script to check db status in a server

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

#!/bin/ksh

setenv ORATAB /etc/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.ksh
            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

Script to get row count of all tables in a schema

***************************************************
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

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

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

#!/bin/ksh

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.ksh
            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

What is the difference between database upgradation and database migration

Today, let us see about the difference between upgrade and migration.

First of all let me give you a brief definition of both

UPGRADE:

Process of changing version of database from lower release to major release is called UPGRADE. For example, moving from 10.2.0.1 to 11.2.0.1. 10.2.0.4 to 11.2.0.3, 9.2.0.8 to 10.2.0.5 etc

Usually, we will upgrade the database without changing the physical server. Means, in the existing server itself we will upgrade the DB to new release.

Reasons for upgrade:

1. To continue support with Oracle (because Oracle will not support if you have a license of unspported version. for example, Oracle stopped support for 10g now, so if you have 10g database you cannot get any help from Oracle support to raise SR’s, to fix any bugs etc)

2. There is a serious bug which got fixed only in new release.

3. Client has license for new release

4. Application will work better with new version of database etc

MIGRATION:

Process of changing OS platform for a database server. For example, moving a database from Solaris server to Linux server, windows to Solaris etc.

Ideally in most of the migration projects, there will be a change in physical server i.e If we have a server with Solaris 5.10 and now client decided to move to latest server with RHEL 6, we call it as migration.

Some of the reasons why client need this…

1. the existing server became old and doesn’t have expanding capability (like we cannot extend memory, disk space or CPU etc)

2. existing OS license got expired and client want now to use cheaper OS (like Linux)

3. moving from one data center to another to reduce cost of maintenance etc

Many a times, migration also involves upgrade. usually, out of 100% migration projects, 80-90% will involve the task for upgrade database along with migration.

For example, there is a 10.2.0.4 database running on X server with Solaris 5.10 and client decided to move to 11.2.0.3 database on Y server with RHEL 6. In this case, it involves both migration (because you are changing the platform) and upgrade (because database release is changing).

With this, I hope I have explained little bit about upgrade Vs migrate.

How to resolve ORA-20000 Error during concurrent statistics

Recently I have faced an issue when application team is executing gather stats in their job.

An application job is loading the data into tables and after that it is performing gather stats. They received below error during gather stats

ERROR at line 1:
ORA-20000: Unable to gather statistics concurrently: insufficient privileges
ORA-06512: at “SYS.DBMS_STATS”, line 34634
ORA-06512: at line 1

There are two steps to resolve this issue.

Step 1: Disabling Global preferences

check for the stats global preference value using below query

SELECT DBMS_STATS.get_prefs(‘CONCURRENT’) FROM dual;

DBMS_STATS.GET_PREFS(‘CONCURRENT’)
—————————————————————————————————-
OFF

By output, we can understand that it is OFF and we can turn it on

BEGIN
DBMS_STATS.set_global_prefs (
pname => ‘CONCURRENT’,
pvalue => ‘ALL’);
END;
/

Instead of ALL (which works for both manual and automatic stats collection), we can also set below values
MANUAL – only for manual stats collection
AUTOMATIC – only for automatic stats collection

Step 2: Granting roles to the user

If you see that Global preferences value is already set to ALL, you need to grant below mentioned roles to the user which is performing gather stats.
These grants are not default, so users will face issues if they use concurrent statistics.

SQL> GRANT CREATE JOB, MANAGE SCHEDULER, MANAGE ANY QUEUE TO testuser;

Once both of above steps are done, issue is resolved.