how to show diff betw two column amts in recursive select

[raygg]

A user schema was created using the system user for the staging tables of a 10gR2 data warehouse on windows. I expdp'd out the system version, built new dbf's and impdp'd and remapped schema and tablspace to a new user name STAGEW. I ran a script to list the rows for comparison of the tables. How do I change this script to include a difference column? If possible I would like to keep it as a cmd line script rather than change it to a declared procedure. Here is my script


SQL> SELECT S.TABLE_NAME, S.NUM_ROWS AS SYSTEMROWS, T.NUM_ROWS AS STAGEWROWS
2 FROM DBA_TABLES s, DBA_TABLES T
3 WHERE s.TABLE_NAME = T.TABLE_NAME
4 AND s.OWNER = 'SYSTEM'
5 AND T.OWNER = 'STAGEW'
6 ORDER BY S.TABLE_NAME;

TABLE_NAME SYSTEMROWS STAGEWROWS
------------------------------ ---------- ----------
STAGE_1901 6571 6571
STAGE_1902 6571 6571
STAGE_1903 6560 6560

Just because these amts are identical do not think I made a mistake there are other rows where differences occur due to i/o errors

 

[LKBrwnDBA]

If you use the NUM_ROWS column from DBA_TABLES there might be a difference because that column is valid only for the date the table was analyzed.
And...datapump will compute new statistics.



----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[raygg]

I am loading each table only once so it is not a problem. Do you know how to code the diff column?

 

[Dagon]

What do you mean by a diff column? Do you mean subtract systemrows from stagerows?

abs(S.NUM_ROWS AS SYSTEMROWS - T.NUM_ROWS AS STAGEWROWS) as diff


For Oracle-related work, contact me through Linked-In.