Home » RDBMS Server » Server Utilities » getting count of objects after schema refresh (Oracle 8i, windows)
getting count of objects after schema refresh [message #595763] Fri, 13 September 2013 05:58 Go to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 10582
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 Go to previous messageGo to next message
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 #595818 is a reply to message #595814] Sat, 14 September 2013 01:12 Go to previous messageGo to next message
Michel Cadot
Messages: 57634
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is the actual output of your command, you have to spool it in a file and then execute this file.

Regards
Michel
Re: getting count of objects after schema refresh [message #595825 is a reply to message #595818] Sat, 14 September 2013 03:35 Go to previous messageGo to next message
8939513598$
Messages: 103
Registered: July 2013
Location: chennai
Senior Member
thanks
Re: getting count of objects after schema refresh [message #595891 is a reply to message #595825] Mon, 16 September 2013 00:06 Go to previous messageGo to next message
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

Re: getting count of objects after schema refresh [message #595894 is a reply to message #595891] Mon, 16 September 2013 00:15 Go to previous messageGo to next message
Michel Cadot
Messages: 57634
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
set head off feedback off trimspool on trimout on lines 1000 echo off verify off

Regards
Michel

[Updated on: Mon, 16 September 2013 00:16]

Report message to a moderator

Re: getting count of objects after schema refresh [message #595902 is a reply to message #595894] Mon, 16 September 2013 01:25 Go to previous message
8939513598$
Messages: 103
Registered: July 2013
Location: chennai
Senior Member
Hi Michael, It's Working, thanks for the support
Previous Topic: Exporting/Importing partitioned table
Next Topic: Sql Loader Performance
Goto Forum:
  


Current Time: Wed Apr 23 04:04:14 CDT 2014

Total time taken to generate the page: 0.11682 seconds