| pls help to reduce the manual activity [message #513787] |
Wed, 29 June 2011 07:04  |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
Hi,
I need tablespace name,tablespace total size and used size of every tablespace for my 106 databases.
bcz I am planning to prepare tablespace growth report for my 106 dbs. If I have the 2 months data from now in xl sheet, then I can predict the growth rate of every tablespace accurate almost.
it is not a easy task to fill the space detail manually in xl sheet everyday.
Hence I need some mechanism to do this activity automatically.
I know we can collect this detail in a csv file every day while running the script. but is there any mechanism to store these detail in a particular xl sheet one by one column automatically?
pls help me.
|
|
|
|
|
|
|
|
|
|
| Re: pls help to reduce the manual activity [message #513915 is a reply to message #513804] |
Thu, 30 June 2011 01:51  |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
k then I will post it excel forum.
and one more thing is, I have a BAT file with the below contents.
sqlplus /nolog
conn user1/pwd1@tns1;
spool output.csv
@tbs.sql;
disconnect;
conn user2/pwd2@tns2;
@tbs.sql;
disconnect;
spool off;
1. When I run this BAT file,it opens a command prompt and logged into the database in nolog mode. but after that 2nd step is not working.just the cursor is blinking for a long time. why?
2. If I run the above steps manually, I am getting the below output in a csv file.
C:\Documents and Settings\603453302\script>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 30 07:45:11 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn edie/heml0ck@EDIE;
Connected.
SQL>
SQL> spool output.csv
SQL> @tbs.sql;
DBA_UTILITIES 25 .625
EDIE_DATA02 32 .0625
EDIE_DATA1 13600 11498.8125
EDIE_INDX1 1000 467.625
STATSPACK_DATA 1450 1225.875
SYSTEM 250 180.0625
UNDOTBS1 2001 1753.625
SQL> disconnect;
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production
SQL> conn carisman/carisman@CARISMAN;
Connected.
SQL> @tbs.sql;
DATA_L_NEW 87496 77334.0938
DATA_T_NEW 32472 19779.1172
IDX 152508 36716.2578
IDX_L 35116 19364.9688
IDX_T_NEW 20644 12450.0859
STATSPACK 1000 413.523438
SUM 177816 91452.7734
SYSTEM 1000 329.453125
TS_DATA01 100912 52415.4609
TS_IDX01 50000 4273.85156
UNDO 30000 2563.375
USERS 1384 832.867188
USERS2 2000 .0078125
SQL> disconnect;
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production
SQL> spool off;
But instead, I like to get the output format as below. For this what are the things I need to add in the BAT file or SQL script?
DBA_UTILITIES 25 .625
EDIE_DATA02 32 .0625
EDIE_DATA1 13600 11498.8125
EDIE_INDX1 1000 467.625
STATSPACK_DATA 1450 1225.875
SYSTEM 250 180.0625
UNDOTBS1 2001 1753.625
DATA_L_NEW 87496 77334.0938
DATA_T_NEW 32472 19779.1172
IDX 152508 36716.2578
IDX_L 35116 19364.9688
IDX_T_NEW 20644 12450.0859
STATSPACK 1000 413.523438
SUM 177816 91452.7734
SYSTEM 1000 329.453125
TS_DATA01 100912 52415.4609
TS_IDX01 50000 4273.85156
UNDO 30000 2563.375
USERS 1384 832.867188
USERS2 2000 .0078125
SQL script:
set heading off;
set feedback off;
break on table_space on free
column free format 999,999,999,999,990
column bytes format 999,999,999,999,990
column Used format 999,999,999,999,990
set pagesize 1000
set lines 1000
SELECT Total.name "Tablespace Name",
total_space Size_MB,
nvl(total_space-Free_space, 0) Used_space_MB
FROM
(select tablespace_name, sum(bytes/1024/1024) Free_Space
from sys.dba_free_space
group by tablespace_name
) Free,
(select b.name, sum(bytes/1024/1024) TOTAL_SPACE
from sys.v_$datafile a, sys.v_$tablespace B
where a.ts# = b.ts#
group by b.name
) Total
WHERE Free.Tablespace_name(+) = Total.name
ORDER BY Total.name;
|
|
|
|
|
|
| Re: pls help to reduce the manual activity [message #513923 is a reply to message #513920] |
Thu, 30 June 2011 03:15 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
Quote:I can't debug something I don't see, copy and paste what you did and got, startint with a "type" of you BAT file.
The BAT file content is below.
sqlplus /nolog
conn user1/pwd1@tns1;
spool output.csv
@tbs.sql;
disconnect;
conn user2/pwd2@tns2;
@tbs.sql;
disconnect;
spool off;
The output I got below in a command prompt. it is not going to next step.
C:\Documents and Settings\603453302\script>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 30 09:10:51 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL>
Quote:Which is with words? I don't see any difference or special thing between the 2.
I want to suppress the output as I dont want the below content in output file and I don't want the words whatever I am typing or mentioning in the script.
C:\Documents and Settings\603453302\script>sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jun 30 07:45:11 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
SQL> conn edie/heml0ck@EDIE;
Connected.
SQL>
SQL> spool output.csv
SQL> @tbs.sql;
SQL> disconnect;
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production
SQL> conn carisman/carisman@CARISMAN;
Connected.
SQL> @tbs.sql;
SQL> disconnect;
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.7.0 - Production
SQL> spool off;
[Updated on: Thu, 30 June 2011 03:16] Report message to a moderator |
|
|
|
|
|
| Re: pls help to reduce the manual activity [message #513961 is a reply to message #513926] |
Thu, 30 June 2011 07:27 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
Thanks for your response.
Now BAT file has been executed and got the below output.
25 .625
32 .0625
13600 11498.8125
1000 467.625
1450 1225.875
250 180.0625
2001 1784.375
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production
Connected.
87496 77334.0938
32472 19779.1172
152508 36716.2578
35116 19364.9688
20644 12450.0859
1000 413.523438
177816 91452.7734
1000 329.453125
100912 52415.4609
50000 4273.85156
30000 1963.375
1384 832.867188
2000 .0078125
But I dont want text part below in the output. Can you plesae help me to suppress this from the output?
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production
Connected. [Updated on: Thu, 30 June 2011 07:46] Report message to a moderator |
|
|
|
|
|
| Re: pls help to reduce the manual activity [message #513970 is a reply to message #513965] |
Thu, 30 June 2011 08:09 |
sathik
Messages: 196
Registered: August 2009
Location: INDIA
|
Senior Member |
|
|
Thanks. It works.
But the output contains one line space between each database output as below. is there anyway to suppress that line also? I dont want to print that line.
25 .625
32 .0625
13600 11498.8125
1000 467.625
1450 1225.875
250 180.0625
2001 1784.375
87496 77334.0938
32472 19779.1172
152508 36716.2578
35116 19364.9688
20644 12450.0859
1000 413.523438
177816 91452.7734
1000 329.453125
100912 52415.4609
50000 4273.85156
30000 1963.375
1384 832.867188
2000 .0078125 [Updated on: Thu, 30 June 2011 08:09] Report message to a moderator |
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
