Re: querying the data dictionary

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 30 Mar 2002 11:40:27 -0800
Message-ID: <a854bb0vv0@drn.newsguy.com>

In article <be3be537.0203301104.283dcd29_at_posting.google.com>, dkniveton71_at_msn.com says...
>
>I have two questions on this topic.
>First if I query my user_tab_columns I have about 1062 column names.
>How do I query to figure out which one is used the most?
>

you can use:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * 2 from ( select column_name, count(*)

  3                     from user_tab_columns
  4                    group by column_name
  5                    order by 2 DESC )

6 where rownum = 1
7 /

COLUMN_NAME                      COUNT(*)
------------------------------ ----------
DEPTNO                                  4

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select * from 2 (
3 select column_name, dense_rank() over ( order by cnt DESC ) dr 4 from(select column_name, count(*) cnt

  5                                           from user_tab_columns
  6                                          group by column_name )

  7 )
  8 where dr = 1
  9 /

COLUMN_NAME                            DR
------------------------------ ----------
DEPTNO                                  1
X                                       1

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>
ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select column_name, count(*) 2 from user_tab_columns

  3     group by column_name
  4     having count(*) =
5            ( select max(count(*)) from user_tab_columns group by column_name);

COLUMN_NAME                      COUNT(*)
------------------------------ ----------
DEPTNO                                  4
X                                       4

ops$tkyte_at_ORA817DEV.US.ORACLE.COM>

but as you see -- you might get different answers using different techniques. In this schema - there were 2 columns that occured "most frequently"

>Second, if I query my all_users table I get a list of about 200 users
>created on different dates. How do I find out which one was created
>most recent (by date only)? I know I can do an order by created but I
>lose the the top of the list because it is too long to keep in the
>buffer. Isn't there an easier way?
>

select * from all_users
where trunc(created) = ( select max(trunc(created)) from all_users );

>Thanks for any help on this.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp

Received on Sat Mar 30 2002 - 13:40:27 CST