我正在尝试获取单个查询以返回每个表具有的行数。
在以下查询中,我列出了表格,但行数中的一些返回不正确。
SELECT
TABLE_NAME,
NUM_ROWS
FROM
all_tables;
我在另一个问题的答案中尝试了解决方案,但是我只有阅读权限,无法创建表或视图,因此我收到了错误:
pl/sql:ora-00942:表或视图不存在
该问题中的其他答案似乎也使用表或视图。
我在Excel中使用的VBA代码如下:
' 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
ODBC驱动程序是Oraclient11G_Home1(版本11.02.00.01(中的Oracle。
您不需要create view
特权;您从中获取该代码的答案不会创建表或视图。如果Oracle告诉您哪个桌子/查看找不到,但它从来没有,但遗憾的是。
问题在于,您已经从该答案中删除了所有者过滤器,即此位:
where owner = 'SCHEMA_NAME'
但是,即使这样,如果该模式不是您当前的架构,那么它仍然无法找到表。(如果您只查看自己的模式,请改用user_tables
。(
您需要在数量查询中包括所有者,因此:
" EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || RECORD.TABLE_NAME INTO NUM_ROWS;" & VBA.Constants.vbNewLine & _
变为:
" EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || RECORD.OWNER || '.' || RECORD.TABLE_NAME INTO NUM_ROWS;" & VBA.Constants.vbNewLine & _
如果您已经引用了架构/用户/所有者的标识符(所有相同的东西(或表名称,则您需要将这些标识符包装在双引号中,您必须逃脱 - 但是VBA会这样做。
。