Skip to content
Advertisement

How to analyze metadata of given schema name and table name dynamically in Oracle?

I am trying to write a dynamic oracle plsql script that takes schema_name and table_name for parameter and returns Primaty Key Column names,Index flag(if table contains index returns 1 else 0),data volume for size of table in db,row count and column count of selected schema and table.Here is my code

It gives me these errors :

How can i fix these errors ? What is worng with my syntax ? Thanks for your help.

Advertisement

Answer

Working version:

The only part that needs to be dynamic is counting the rows.

In many systems there will be tables so large that counting the rows could take many hours, so you might want to consider the requirement for this. Would an approximate count be acceptable? Will statistics (all_tables.num_rows) be close enough? If you really need an exact count, you might consider parallel query (requires Enterprise Edition).

User contributions licensed under: CC BY-SA
10 People found this is helpful
Advertisement