Home » Infrastructure » Windows » pls help to reduce the manual activity (oracle 10g,)
pls help to reduce the manual activity [message #513787] Wed, 29 June 2011 07:04 Go to next message
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 #513793 is a reply to message #513787] Wed, 29 June 2011 07:35 Go to previous messageGo to next message
Michel Cadot
Messages: 68055
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
VBA (Visual Basic for Application) and ODBC/OO4O/OLE DB, as you want.

Regards
Michel
Re: pls help to reduce the manual activity [message #513797 is a reply to message #513793] Wed, 29 June 2011 08:04 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member
Yes this kind of stuff is required. I think we can do it through macros in excel.
can I get help from anybody to do this through excel macros?
Re: pls help to reduce the manual activity [message #513804 is a reply to message #513797] Wed, 29 June 2011 08:25 Go to previous messageGo to next message
Michel Cadot
Messages: 68055
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe you then should post this in an Excel forum.

Regards
Michel
Re: pls help to reduce the manual activity [message #513915 is a reply to message #513804] Thu, 30 June 2011 01:51 Go to previous messageGo to next message
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 #513920 is a reply to message #513915] Thu, 30 June 2011 02:48 Go to previous messageGo to next message
Michel Cadot
Messages: 68055
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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?

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.

Quote:
But instead, I like to get the output format as below

Which is with words? I don't see any difference or special thing between the 2.

Regards
Michel
Re: pls help to reduce the manual activity [message #513923 is a reply to message #513920] Thu, 30 June 2011 03:15 Go to previous messageGo to next message
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 #513926 is a reply to message #513923] Thu, 30 June 2011 04:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68055
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Each line of BAT file is read at DOS level ONLY, not at SQL*Plus level.
You must put the SQL part inside a SQL script that you give on sqlplus command line.

Regards
Michel
Re: pls help to reduce the manual activity [message #513961 is a reply to message #513926] Thu, 30 June 2011 07:27 Go to previous messageGo to next message
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 #513965 is a reply to message #513961] Thu, 30 June 2011 07:36 Go to previous messageGo to next message
Michel Cadot
Messages: 68055
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use "sqlplus -s ..."

Regards
Michel
Re: pls help to reduce the manual activity [message #513970 is a reply to message #513965] Thu, 30 June 2011 08:09 Go to previous messageGo to next message
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

Re: pls help to reduce the manual activity [message #513976 is a reply to message #513970] Thu, 30 June 2011 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 68055
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I don't think so.

Regards
Michel
Re: pls help to reduce the manual activity [message #514048 is a reply to message #513976] Fri, 01 July 2011 00:57 Go to previous messageGo to next message
sathik
Messages: 196
Registered: August 2009
Location: INDIA
Senior Member

it works if we add SET NEWPAGE 0 in the sql script.

Re: pls help to reduce the manual activity [message #514051 is a reply to message #514048] Fri, 01 July 2011 01:23 Go to previous message
Michel Cadot
Messages: 68055
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Thanks for the feedback.

Regards
Michel
Previous Topic: upgrading
Next Topic: Oracle Version Mismatch ?
Goto Forum:
  


Current Time: Wed Dec 08 16:51:15 CST 2021