Oracle Records Do not Match

Posted on 2013-09-15
Medium Priority
Last Modified: 2013-11-18
So I am kind of confused on why this is happening and trying to find a way to resolve this.  There were multiple users connected to Oracle I ensured all users disconnected however there is a couple of tables that should show no data but it does and doesn't, this is what I mean.  When I run a query against the database:



But when I into the oracle GUI looked at the same table it initially shows 77 rows but when I want to view the data it shows 0.  So I am guessing these are orphaned records so how would I delete them?  

The attached document should clarify it better.
Question by:jjmekkattil
  • 3
  • 2
LVL 23

Expert Comment

ID: 39494990
The only reason for "orphaned rows" in Oracle, is if poorly written code allows for child records to survive the deletion of their parent rows.  I would question the GUI and the precise source of the SQL statement.  In such a scenerio, please stick with the terminal level SQL*Plus session instead.

The select count result is an estimate, affected by the DML (inserts, deletes) on a table and the accuracy of the table's statistics.  

The solution is to query the underlying SYS views if you have DBA or SYSOPER privilege:

select num_rows from sys.dba_tables where table_name = 'PROCESS_INFORMATION' and owner = 'SDE';

Author Comment

ID: 39495040
Ok so with the above mentioned query I did get the same matching number but again it does not resolve my underlying issue because when I ran another query specifically:

select sde_id from sde.process_information;

no rows selected

So there I need to remove those rows and I guess how would I go about doing that?
LVL 23

Expert Comment

ID: 39495045
I suggest you truncate the table. If you still see rows in the GUI, they have to have a different source.  Possibly, a materialized view.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.


Author Comment

ID: 39495052
Well again in the above attachments "locks01" shows rows but "process_information" shows no rows.
LVL 23

Expert Comment

ID: 39495128
Please execute
select table_name , num_rows from sys.user_tables order by 1;
I do not yet grasp why you wish to zero out the other tables -- if that's even the case.
LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 2000 total points
ID: 39496059
>>initially shows 77 rows

Looks like that first image is from all_tables (or one of the _tables views).

The num_rows column is not the number of rows currently in the table.  If is the number of rows when statistics were last generated.

Check out the following test case to confirm this.

You will need to change ownname below to the user that you are using.
drop table tab1 purge;
create table tab1(col1 char(1));
insert into tab1 values('a');
insert into tab1 values('b');

exec dbms_stats.gather_table_Stats(ownname=>'SCOTT',tabname=>'TAB1');

delete from tab1;

select num_rows from user_tables where table_name='TAB1';

Open in new window


Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
An introductory discussion about Oracle Analytic Functions which are used to calculate or compute Aggregate values, based on a group of rows.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

607 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