Solved

PL/SQL for 2 looping structure

Posted on 2011-09-21
6
220 Views
Last Modified: 2012-05-12
I want to get the name of the tables belonging to a user and then find the record count count(*) for that table in a 2nd loop and insert table_name, count(*) into a static table
Thanks
0
Comment
Question by:Greens8301
  • 3
  • 2
6 Comments
 

Author Comment

by:Greens8301
ID: 36577112

For ueach user in (usr1,usr2,usr3 ...)
loop 1
Get the names of tables belonging to the user
loo 2
get the total number of records for each table that user owns

INSERT into a static table (table_name, recCNT)

I need a PL/SQL code to do this

Thanks
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
ID: 36577538
You can do in in a single sql statement.

Take the following solution, add the table_name, an insert into table (select ...), change the view from user_tables to dba_users and add a: where username in ('A','B');
And you should be good to go.

http://www.experts-exchange.com/Database/Oracle/Q_25111045.html?#a26476979
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36580723
I remembered you need to remove the SUM call in that code.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Expert Comment

by:iaakov
ID: 36713243
try


insert into tab_resume (tab_name ,tab_count)
select table_name ,num_rows
from dba_tables
where owner = 'NAME_USER'
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 36713473
Num_rows is an estimate based off statistics.  You need to be aware the number might be off.
0
 

Author Closing Comment

by:Greens8301
ID: 37033417
Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
select query - oracle 16 82
Oracle Database Upgrade 13 41
Clone Oracle 12c Database 5 27
automatic email alert 1 21
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to recover a database from a user managed backup

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now