Skip to Main Content
  • Questions

  • Regarding dba_tables and dba_segments

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Anthony.

Asked: July 26, 2005 - 10:17 pm UTC

Last updated: July 27, 2005 - 2:48 pm UTC

Version: 9.2.0.6

Viewed 1000+ times

You Asked

Hi Tom,

I read the "Predictive Reorganization" topic and I can to this statement :

(do you really use this? i mean, you join dba_tables to dba_segments by
table_name to segment_name? ).

I was just curious, can you give me the reason why this 2 views cannot be joined with each other?

Also, can you give me some advice what is the best approach to verify the username and password for a user in my database, without disconnecting the current session? Currently we have 2 approach:
1. create loopback database link and use that username and password to connect.
2. create a Java Store procedure to try to connect to database using the username and password submitted.

Base on your experience, can you give me the best approach?

Thanks and more power to your site

Cheers,
AKV



and Tom said...

You can join dba_tables to dba_segments

Only you CANNOT do it by table_name to segment_name, you must use the OWNER as well as the segment type!

I can have a table named T, with an index named T.
So can you.
So can they.
So can lots of people.

The point was -- not only was the use of dbms_space flawed (would not show unbalanced freelists, heck it would not even show all of the freelists), the query itself was not "correct" in a real database - I'm sure in many databases the same segment name would appear twice.

1 select owner, segment_name, segment_type
2 from dba_segments
3* where segment_name = 'T'
ops$tkyte@ORA10G> /

OWNER SEGMENT_NAME SEGMENT_TYPE
------------------------------ ------------------------------ ------------------
BIG_TABLE T TABLE
OPS$TKYTE T TABLE
SCOTT T TABLE
SCOTT T CLUSTER
OPS$TKYTE T INDEX
SCOTT T INDEX

6 rows selected.

which T is which....


So, those two views CAN be joined to each other.
But you do have to use the proper join conditions.



Based on my experience - why do that is my typical answer. What is the point? But if you really really find the desire to do so

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:258815248980 <code>

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Now I get it...

AKV, July 27, 2005 - 1:32 pm UTC

Hi Tom,

Thanks for the enlightenment regarding the the views. And regarding the user validation, we need this function for the supervisor authentication. We are using Forms as the front end and this supervisort authentication is called from a database package. Can you advice what is the best approach we can implement for this ?

1. LOOPBACK DB LINK
2. Java SP.
3. your package with 'alter user' command?

Cheers,
AKV

Tom Kyte
July 27, 2005 - 2:48 pm UTC

"supervisisor authentication" means nothing to me.

but any of them work, none of them are "good". Why not just open a new form and connect using those credentials?

more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library