Oracle Records Do not Match

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.
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DavidSenior Oracle Database AdministratorCommented:
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';
jjmekkattilAuthor Commented:
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?
DavidSenior Oracle Database AdministratorCommented:
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.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

jjmekkattilAuthor Commented:
Well again in the above attachments "locks01" shows rows but "process_information" shows no rows.
DavidSenior Oracle Database AdministratorCommented:
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.
slightwv (䄆 Netminder) Commented:
>>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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.