This discussion is archived
1 Reply Latest reply: Sep 24, 2012 7:22 AM by Tridus RSS

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

963832 Newbie
Currently Being Moderated
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
STD.Select

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
Next
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
RS.MoveNext
Loop
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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points