| getting count of objects after schema refresh [message #595763] |
Fri, 13 September 2013 05:58  |
8939513598$
Messages: 103
Registered: July 2013
Location: chennai
|
Senior Member |
|
|
Iam having the following query, After executing schema refresh using export & import , getting count of database objects comparison to be done,
-- SELECT 'TRUNCATE TABLE '||OWNER||'.'||TABLE_NAME||' ;' FROM DBA_TABLES WHERE OWNER='PRICING' order by TABLE_NAME;
SQL> SELECT 'select count(*) from '||OWNER||'.'||TABLE_NAME||' ;' FROM DBA_TABLES WHERE OWNER='PRICING' order by TABLE_NAME;
The output expected was to display each table name in a schema following below with corresponding number of records to be displayed, but it wasn't showing correctly,Can anybody do a correction?
|
|
|
|
| Re: getting count of objects after schema refresh [message #595765 is a reply to message #595763] |
Fri, 13 September 2013 06:17  |
cookiemonster
Messages: 13963
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
So you need to add the table name to the select list, straight forward since you know how to concatenate the name in:
SELECT 'select '''||table_name||''', count(*) from '||OWNER||'.'||TABLE_NAME||' ;' FROM DBA_TABLES WHERE OWNER='PRICING' order by TABLE_NAME;
|
|
|
|
| Re: getting count of objects after schema refresh [message #595814 is a reply to message #595765] |
Sat, 14 September 2013 00:52 |
8939513598$
Messages: 103
Registered: July 2013
Location: chennai
|
Senior Member |
|
|
the statement actually was a real-time environment, i want to test the output with my own tables and schema in my database. 'PRICING' schema not present in my database, i checked your statement in my oracle database, the SQL query and output was,
SQL> SELECT 'select '''||table_name||''', count(*) from '||OWNER||'.'||TABLE_NAME||' ;' FROM ALL_TABLES WHERE OWNER='TDUMP' order by TABLE_NAME; (enter)
'SELECT'''||TABLE_NAME||''',COUNT(*)FROM'||OWNER||'.'||TABLE_NAME||';'
--------------------------------------------------------------------------------
select 'T1', count(*) from TDUMP.T1 ;
select 'T2', count(*) from TDUMP.T2 ;
select 'T3', count(*) from TDUMP.T3 ;
select 'T4', count(*) from TDUMP.T4 ;
select 'T5', count(*) from TDUMP.T5 ;
from the above output, there are 5 tables under schema 'TDUMP'. where was the number of rows count displaying in the query, the output wasn't satisfactory....
what will be actual result?
|
|
|
|
|
|
|
|
| Re: getting count of objects after schema refresh [message #595891 is a reply to message #595825] |
Mon, 16 September 2013 00:06 |
8939513598$
Messages: 103
Registered: July 2013
Location: chennai
|
Senior Member |
|
|
Suppose, iam trying to view the database object contents of 'sys' user,i am getting set of select query list in a spool file, iam showing the spool file content, before executing the file, unnecessary statement lines & characters such as,
'SELECT'''||TABLE_NAME||''',COUNT(*)"NOOFROWS"FROM'||OWNER||'.'||TABLE_NAME||';'
--------------------------------------------------------------------------------
have to be deleted, how's that possible? any linux command idea do you have? b'coz each time the above statement occuring continuously in the file..
[Updated on: Mon, 16 September 2013 00:14] by Moderator Report message to a moderator |
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
