Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Help with Database size script

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Help with Database size script
# 1  
Old 10-19-2011
Help with Database size script
Hello,

I'm not very good at scripting as my job is an DBA for a small firm, but now I'd like to implement a few cron jobs which access and report on the databases on an automatic basis.

This is my SQL TEXT

Code:
# -- Total size of Database Size in GB set echo off feedback off verify off pause off SELECT 'Database Size is --> ' || TO_CHAR(ROUND((select sum(bytes)/1024/1024/1024 from dba_data_files) + (select sum(bytes)/1024/1024/1024 from v$log),0)) ||  'GB' FROM dual; 

PROMPT +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ 

exit

The SQL part works fine. However I would like to schedule this via cron, so it runs on a weekly basis at say 4am every Monday.

The mail part I believe is as follows - which I can end at the end of the script
Code:
mail -s 'Database Size for Database DB1' name@company.com < /tmp/dbsize.log

This will execute on a linux box under the Oracle user

Could someone please help and many thanks in advance
Last edited by radoulov; 10-19-2011 at 07:53 AM.. Reason: Code tags!
# 2  
Old 10-19-2011
And where is the problem? Why not try the mail part out if you are not sure? What should happen?
# 3  
Old 10-19-2011
Hello,

the problem is if I execute the script from the shell prompt - it enters the database and nothing more. It will just sit at the SQL> prompt and do nothing. If I enter the database and copy the SQL statement in it will work fine.

Hope this helps
# 4  
Old 10-20-2011
Can you post the commands you are running?
# 5  
Old 10-21-2011
Here is my results of running the script

Code:
$ ./db_size

SQL> 

SQL> exit

Here is the script
Code:
# -- Total size of Database Size in GB
set echo off feedback off verify off pause off
SELECT 'Database Size is --> ' || TO_CHAR(ROUND((select sum(bytes)/1024/1024/1024 from dba_data_files) + (select sum(bytes)/1024/1024/1024 from v$log),0)) ||
 'GB'
FROM dual;
PROMPT +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
exit

And this is what happens when I execute the same script after I access the database

Code:
# -- Total size of Database Size in GB
SQL> SELECT 'Database Size is --> ' || TO_CHAR(ROUND((select sum(bytes)/1024/1024/1024 from dba_data_files) + (select sum(bytes)/1024/1024/1024 from v$log),0)) ||
  2   'GB'
  3  FROM dual;
PROMPT +++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

'DATABASESIZEIS-->'||TO_CHAR(ROUND((SELECTSUM(BYTES)/1024/1024/
---------------------------------------------------------------
Database Size is --> 83GB
SQL> exit

As you can it works well if I enter the database, but I am trying to schedule this job via cron so that it runs overnight

Many thanks
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Help with Database size script

Hello, I'm not very good at scripting as my job is an DBA for a small firm, but now I'd like to implement a few cron jobs which access and report on the databases on an automatic basis. This is my SQL TEXT # -- Total size of Database Size in GB set echo off feedback off verify off pause... (2 Replies)
Discussion started by: jnrpeardba
2 Replies

2. Shell Programming and Scripting

Script to read file size and send email only if size > 0.

Hi Experts, I have a script like $ORACLE_HOME/bin/sqlplus username/password # << ENDSQL set pagesize 0 trim on feedback off verify off echo off newp none timing off set serveroutput on set heading off spool Schemaerrtmp.txt select ' TIMESTAMP COMPUTER NAME ... (5 Replies)
Discussion started by: welldone
5 Replies

3. Solaris

Solaris Volume Manger - Database Replicas Question - Benefits of Increasing Default Size?

Hey all! I was hoping someone knew anything about this one... I know with Solaris Volume Manager the default Database Replica size is 8192 blocks (4MB approximately) Now I know you can increase this amount but is there any point? The reason I am asking this is that I've setup mirroring on... (2 Replies)
Discussion started by: Keepcase
2 Replies

4. UNIX and Linux Applications

Database Size

Can anybody tell me how to look for the size of the database on the solariis servers. I am using Solaris 5.6 and Solaris 1.1.1 Any help will be appreciated.................. (1 Reply)
Discussion started by: asalman.qazi
1 Replies

5. UNIX for Dummies Questions & Answers

find the size of a database by counting all the used pages

Hi all, I am looking to find the size of the database by counting all the used pages. 1. I have a file which reads like below 16384 4750850 32768 165 The first column is the pagesize and the second column is the number of pages... (6 Replies)
Discussion started by: family_guy
6 Replies

6. HP-UX

know the size of database

Hi all , How to know the size of a oracle database running in hp-ux server. (1 Reply)
Discussion started by: megh
1 Replies

7. Shell Programming and Scripting

bash script working for small size files but not for big size files.

Hi, I have one file stat. Stat file contents are as follows: for example. H50768020040913,00260100,507680,13,0000000643,0000000643,00000,0000 H50769520040808,00260100,507695,13,0000000000,0000000000,00000,0000 H50770620040611,00260100,507706,13,0000000000,0000000000,00000,0000 Now i... (1 Reply)
Discussion started by: davidpreml
1 Replies

8. UNIX for Dummies Questions & Answers

command(s) to find size of mysql database?

hello, i'm trying to figure out the size of a mysql database shelling into my server with putty / command line. is there / are there any commands that will return the size of a database, in MB? thanks. (4 Replies)
Discussion started by: sbourgeois
4 Replies

9. Solaris

database image size

hi , we are using Veritas netbackup 5 to backup all databases at our site. i noticed lately that a specific filesystem on the netbackup servers in running critically out of space , after some investigations i found that there are images that are increasing in size everytime a backup is taken .... (6 Replies)
Discussion started by: ppass
6 Replies

10. UNIX for Advanced & Expert Users

how to figure out the size in MB for an informix database

This is the info i have so far. please if you are dead positive can u let me know if i my math/process is correct. Chunks address chk/dbs offset size free bpages flags pathname 5003e1f8 1 1 0 250000 177177 PO- /database/link_cw_p01_rootdbs 5003e494 2 ... (5 Replies)
Discussion started by: Optimus_P
5 Replies
Login or Register to Ask a Question