Query the number of rows of each table in Oracle 11g without create view permissions

Question

I'm trying to get a single query to return the number of rows that each table has.

With the following query I get the tables listed but some of the number of rows returned are incorrect.

SELECT
  TABLE_NAME,
  NUM_ROWS
FROM
  all_tables;

I tried the solution in an answer from another question but I only have reading permissions and can't create tables or views so I got the error:

PL/SQL: ORA-00942: table or view does not exist

The other answers in that question seem to also make use of either tables or views.

The VBA code that I'm using in Excel is the following:

' Reference:
' Microsoft AtiveX Data Objects 6.1 Library

Private Sub NumberOfRowsOfEachTable()

  Dim Connection As ADODB.Connection
  Const ConnectionTimeout As Long = 0
  Const ConnectionString As String = "Driver={Oracle in OraClient11g_home1}; Dbq=...;"
  Const UserId As String = "..."
  Const Password As String = "..."
  Const CommandText As String = _
    "DECLARE" & VBA.Constants.vbNewLine & _
    "  NUM_ROWS integer;" & VBA.Constants.vbNewLine & _
    "BEGIN" & VBA.Constants.vbNewLine & _
    "  FOR RECORD IN (SELECT TABLE_NAME, OWNER FROM all_tables)" & VBA.Constants.vbNewLine & _
    "  LOOP" & VBA.Constants.vbNewLine & _
    "  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || RECORD.TABLE_NAME INTO NUM_ROWS;" & VBA.Constants.vbNewLine & _
    "  INSERT INTO STATS_TABLE(TABLE_NAME, SCHEMA_NAME, RECORD_COUNT, CREATED)" & VBA.Constants.vbNewLine & _
    "    VALUES (RECORD.TABLE_NAME, RECORD.OWNER, NUM_ROWS, SYSDATE);" & VBA.Constants.vbNewLine & _
    "  END LOOP;" & VBA.Constants.vbNewLine & _
    "END;"
  Dim Recordset As ADODB.Recordset
  Dim Fields As ADODB.Fields
  Dim Field As ADODB.Field
  Dim Value As String

  Set Connection = New ADODB.Connection
  Connection.ConnectionTimeout = ConnectionTimeout
  Connection.ConnectionString = ConnectionString
  Connection.Open UserId:=UserId, Password:=Password
  Set Recordset = Connection.Execute(CommandText:=CommandText)
  Set Fields = Recordset.Fields
  Set Field = Fields.Item(Index:=0)
  Value = VBA.CStr(Expression:=Field.Value)
  Set Field = Nothing
  Set Fields = Nothing
  Recordset.Close
  Set Recordset = Nothing
  Connection.Close
  Set Connection = Nothing
  MsgBox Prompt:=Value

End Sub

The ODBC driver is the Oracle in OraClient11g_home1 (version 11.02.00.01).

[This answer](https://stackoverflow.com/a/10705088/330315) in the linked question neither uses PL/SQL nor a view — , Aug 02 '19 at 20:34

score 2 · Answer 1 · answered Aug 02 '19 at 16:13

You don't need create view privileges; the answer you took that code from doesn't create tables or views. It would be helpful if Oracle told you which table/view it can't find, but it never has, sadly.

The problem is that you've removed the owner filter from that answer, i.e. this bit:

where owner = 'SCHEMA_NAME'

But even with that, if that schema isn't your current schema then it can still fail to find tables. (And if you're only looking at your own schema, use user_tables instead.)

You need to include the owner in count query, so this:

"  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || RECORD.TABLE_NAME INTO NUM_ROWS;" & VBA.Constants.vbNewLine & _

becomes:

"  EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || RECORD.OWNER || '.' || RECORD.TABLE_NAME INTO NUM_ROWS;" & VBA.Constants.vbNewLine & _

If you have quoted identifiers for schemas/user/owners (all the same thing) or table names then you will need to enclose those in double quotes, which you will have to escape - however VBA does that.

Query the number of rows of each table in Oracle 11g without create view permissions

1 Answers1