1 Reply Latest reply on Sep 24, 2012 2:22 PM by Tridus

    Fetch tablespace data from Oracle database(HP UNIX server) to excel Sheet

      Hi team,

      I have 5 oracle databases and they all are on different machines. i want to fetch data(tablespace data) from db to excel.
      here is my macro code

      Sub tbs_STD()
      Dim Conn As New ADODB.Connection
      Dim RS As New ADODB.Recordset
      Dim Cmd As New ADODB.Command
      Dim sqlText As String
      Dim Row As Long
      Dim Findex As Long
      Dim STD As Worksheet
      Dim X As Long
      Dim UID As String
      Dim PWD As String
      Dim Server As String
      UID = "monitordb"
      PWD = "monitor1"
      Server = "STD"
      Set STD = ActiveSheet

      Conn.Open "PROVIDER=MSDAORA.Oracle;DATA SOURCE=" & Server & ";" & _
      "USER ID=" & UID & ";PASSWORD=" & PWD

      Cmd.ActiveConnection = Conn
      Cmd.CommandType = adCmdText
      sqlText = "select sum(a.tots/1048576) Tot_Size,sum(a.sumb/1048576) Tot_Free,round(sum(a.tots-a.sumb)*100/sum(a.tots),2) Pct_used,sum(a.largest/1024) Max_Free,sum(a.chunks) Chunks_Free from(select tablespace_name,0 tots,sum(bytes) sumb,max(bytes) largest,count(*) chunks from dba_free_space a group by tablespace_name union select tablespace_name,sum(bytes) tots,0,0,0 from dba_data_files group by tablespace_name) a group by a.tablespace_name order by a.tablespace_name"
      Cmd.CommandText = sqlText
      Set RS = Cmd.Execute
      For X = 0 To 4 ' Number of columns returning minus 1
      'Data.Cells(1, X + 1) = RS.Fields(X).Name
      Do While Not RS.EOF
      Row = Row + 1
      For Findex = 0 To RS.Fields.Count - 1
      STD.Cells(Row + 2, Findex + 67) = RS.Fields(Findex).Value
      Next Findex
      End Sub

      I am able to connect through sqlplus on all machines but when i run the above code it gives me TNS error..
      ORA-12154- TNS could not resolve the connect identifier specified..

      Each database use same port 1527 and only one of them is able to fetch the data. and if i remove that database from client then the other one starts connecting. I think problem is due to same port number.

      Entries in the listener.ora file was made throough oracle client only 32bit.
      and i have installed both 10g and 11g client on my local machine.

      Please help.