Home » SQL & PL/SQL » SQL & PL/SQL » Table select show blank (oracle 12c Windows 2012)
Table select show blank [message #669430] Sun, 22 April 2018 23:47 Go to next message
muthukrish104
Messages: 71
Registered: November 2017
Member
Hi,

We are facing a different issue.

In a single DB server.

1) We have 2 Different Users and 2 Different tablespaces as their default tablespaces.
2) Both of them are having a object called TABLE1, but in their own different default tablespaces.

When they do select * from <tablename>;
They are not able to get any records.

But the below method gives the output.
Select table_name, num_rows from user_tables
where table_name='<name>';


Kindly help us.

Regards
M.krish


Re: Table select show blank [message #669434 is a reply to message #669430] Mon, 23 April 2018 00:43 Go to previous messageGo to next message
John Watson
Messages: 8138
Registered: January 2010
Location: Global Village
Senior Member
You need to show the result of the commands you have given, and the queries. Please be sure to use [code] tags.

And, by the way, I wish you would not say "record" when you mean "row".
Re: Table select show blank [message #669435 is a reply to message #669430] Mon, 23 April 2018 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Answer: there are no rows in the tables.

Re: Table select show blank [message #669436 is a reply to message #669435] Mon, 23 April 2018 00:57 Go to previous messageGo to next message
muthukrish104
Messages: 71
Registered: November 2017
Member
Hi Michel and John,

Here is the query we used.

SELECT TABLE_NAME , NUM_ROWS FROM USER_TABLES WHERE NUM_ROWS > 0
AND TABLE_NAME = '<name>'

SELECT MIN( TRANS_DATE )  , MAX( TRANS_DATE ) , count(*) FROM <user_name>.<Table name> A 

SELECT * FROM <user_name>.<Table name> A 

Here the first query only returning the output.

Rest both are gives blank result.

Regards
M.krish
Re: Table select show blank [message #669437 is a reply to message #669436] Mon, 23 April 2018 01:08 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The probability I open a doc file from the web is: 0%.

Re: Table select show blank [message #669438 is a reply to message #669436] Mon, 23 April 2018 01:22 Go to previous messageGo to next message
John Watson
Messages: 8138
Registered: January 2010
Location: Global Village
Senior Member
Come on, man: use copy/paste. What did you run, what was the result?
Re: Table select show blank [message #669439 is a reply to message #669435] Mon, 23 April 2018 02:46 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Michel Cadot wrote on Mon, 23 April 2018 06:46

Answer: there are no rows in the tables.


Plot twist: VPD
Re: Table select show blank [message #669440 is a reply to message #669439] Mon, 23 April 2018 02:54 Go to previous messageGo to next message
cookiemonster
Messages: 13789
Registered: September 2008
Location: Rainy Manchester
Senior Member
num_rows is an estimate that's set when statistics are gathered.
Just because it says there are rows doesn't necessarily mean it's true.
Re: Table select show blank [message #669459 is a reply to message #669440] Tue, 24 April 2018 00:30 Go to previous messageGo to next message
muthukrish104
Messages: 71
Registered: November 2017
Member
Hi All,

Here is the output.

SQL> SELECT TABLE_NAME , NUM_ROWS FROM USER_TABLES WHERE NUM_ROWS > 0
  2  AND TABLE_NAME = '<name>';

TABLE_NAME                  NUM_ROWS
------------------------- ----------
<name>                    193850



1 row selected.

SQL> select count(1) from <name>;

  COUNT(1)
----------
         0

1 row selected.


I am explaining the issue, we were using IMPDP to import, where there was error of tablespace not able to extent..
So the import was automatically paused, then we extended the space.

Then we checked the STATISTICS shows the NO of Rows, but not exactly the rows got imported.

Kindly help.

regards
M.Krish
Re: Table select show blank [message #669460 is a reply to message #669459] Tue, 24 April 2018 00:44 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You still did not prove there is any row in the table (or there are no VPD policies as Roachcoach mentioned).

Re: Table select show blank [message #669462 is a reply to message #669460] Tue, 24 April 2018 01:28 Go to previous messageGo to next message
muthukrish104
Messages: 71
Registered: November 2017
Member
Hi Michel,

The above one was, we experienced.

There is no VPD policies.

We just imported the tables, where inbetween TBS has low spaces, later we extended.

Finally there is no rows in that table, but the above query returning the num_rows.

What went wrong, what could be the reason.

Regards
Binoj
Re: Table select show blank [message #669463 is a reply to message #669462] Tue, 24 April 2018 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 66808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Surely in what you did not post.

Re: Table select show blank [message #669464 is a reply to message #669462] Tue, 24 April 2018 03:03 Go to previous messageGo to next message
John Watson
Messages: 8138
Registered: January 2010
Location: Global Village
Senior Member
Look, it is pretty obvious that your import failed. So just drop the table and do it again. What's the problem?
Re: Table select show blank [message #669467 is a reply to message #669464] Tue, 24 April 2018 03:18 Go to previous messageGo to next message
Roachcoach
Messages: 1576
Registered: May 2010
Location: UK
Senior Member
Or the source table had no rows either. Literally all I'm seeing here is an object has statistical metadata which doesn't agree with the reality of the table. I see that daily, quite literally.

OP needs to show some investigation/evidence/logs if they want any meaningful help
Re: Table select show blank [message #669468 is a reply to message #669467] Tue, 24 April 2018 04:17 Go to previous messageGo to next message
muthukrish104
Messages: 71
Registered: November 2017
Member
Hi All,

Thanks for the reply.


There is no problem in dropping and import them again.

But need to understand:
When the import happening, if there is no room, then it pause. And when we add the space, it resumes, am I right?

Tables after this problematic tables got imported successfully.

Regards
M.krish
Re: Table select show blank [message #669469 is a reply to message #669468] Tue, 24 April 2018 04:22 Go to previous messageGo to next message
John Watson
Messages: 8138
Registered: January 2010
Location: Global Village
Senior Member
Since you have not shown the import command you are using, I have no idea if you used the RESUMABLE parameter or have set it for the instance.
Overall, you seem very reluctant to give out information. It makes one inclined to reciprocate accordingly.
Re: Table select show blank [message #669490 is a reply to message #669469] Wed, 25 April 2018 04:42 Go to previous message
muthukrish104
Messages: 71
Registered: November 2017
Member
Hi John

Yes, the information is confidential, so I am not able to disclose.

Anyway thanks for the support.

Regards
M.Krish
Previous Topic: Pagination
Next Topic: Trap Constraint Errors in PL/SQL?
Goto Forum:
  


Current Time: Wed Jan 29 02:42:53 CST 2020