Home » SQL & PL/SQL » SQL & PL/SQL » select index count on a different schema (10.2.0.2)
select index count on a different schema [message #324093] Fri, 30 May 2008 15:14 Go to next message
evoradba
Messages: 104
Registered: April 2005
Location: Canada
Senior Member
hello

How does one select count from other users indexes?

e.g.
connect test/test
select count(*) from user_indexes;

I want to select like this

select count(*) from TEST1.user_indexes;

I get this error message


ERROR at line 1:
ORA-00903: invalid table name

when I do this
select count(*) from TEST1.user_indexes;
select count(*) from TEST1.user_tables;

thanks
Maria
Re: select index count on a different schema [message #324094 is a reply to message #324093] Fri, 30 May 2008 15:16 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
look into dba_indexes
Re: select index count on a different schema [message #324099 is a reply to message #324093] Fri, 30 May 2008 16:13 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
user_indexes or user_tables are "global" view.

There are owner or similar column in it.

Re: select index count on a different schema [message #324100 is a reply to message #324099] Fri, 30 May 2008 16:19 Go to previous messageGo to next message
Martin Eysackers
Messages: 80
Registered: October 2005
Location: Belgium
Member
select count(*)
  from all_indexes
where owner = 'TEST1'
Re: select index count on a different schema [message #324101 is a reply to message #324093] Fri, 30 May 2008 16:20 Go to previous messageGo to next message
BlackSwan
Messages: 25039
Registered: January 2009
Location: SoCal
Senior Member
>user_indexes or user_tables are "global" view.
What is this supposed to mean?
USER_* objects are LOCAL to the USER accessing them.

>There are owner or similar column in it.
WHERE?

SQL> desc user_tables
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLESPACE_NAME                                    VARCHAR2(30)
 CLUSTER_NAME                                       VARCHAR2(30)
 IOT_NAME                                           VARCHAR2(30)
 STATUS                                             VARCHAR2(8)
 PCT_FREE                                           NUMBER
 PCT_USED                                           NUMBER
 INI_TRANS                                          NUMBER
 MAX_TRANS                                          NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 LOGGING                                            VARCHAR2(3)
 BACKED_UP                                          VARCHAR2(1)
 NUM_ROWS                                           NUMBER
 BLOCKS                                             NUMBER
 EMPTY_BLOCKS                                       NUMBER
 AVG_SPACE                                          NUMBER
 CHAIN_CNT                                          NUMBER
 AVG_ROW_LEN                                        NUMBER
 AVG_SPACE_FREELIST_BLOCKS                          NUMBER
 NUM_FREELIST_BLOCKS                                NUMBER
 DEGREE                                             VARCHAR2(10)
 INSTANCES                                          VARCHAR2(10)
 CACHE                                              VARCHAR2(5)
 TABLE_LOCK                                         VARCHAR2(8)
 SAMPLE_SIZE                                        NUMBER
 LAST_ANALYZED                                      DATE
 PARTITIONED                                        VARCHAR2(3)
 IOT_TYPE                                           VARCHAR2(12)
 TEMPORARY                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NESTED                                             VARCHAR2(3)
 BUFFER_POOL                                        VARCHAR2(7)
 ROW_MOVEMENT                                       VARCHAR2(8)
 GLOBAL_STATS                                       VARCHAR2(3)
 USER_STATS                                         VARCHAR2(3)
 DURATION                                           VARCHAR2(15)
 SKIP_CORRUPT                                       VARCHAR2(8)
 MONITORING                                         VARCHAR2(3)
 CLUSTER_OWNER                                      VARCHAR2(30)
 DEPENDENCIES                                       VARCHAR2(8)
 COMPRESSION                                        VARCHAR2(8)
 DROPPED                                            VARCHAR2(3

[Updated on: Fri, 30 May 2008 16:39] by Moderator

Report message to a moderator

icon1.gif  Re: select index count on a different schema [message #324188 is a reply to message #324093] Sat, 31 May 2008 06:42 Go to previous messageGo to next message
zoltanp
Messages: 58
Registered: March 2005
Location: Hungary
Member
Embarassed Sorry, I made an error.
I gave wrong answer.


You should grant "select any dictionary" privilege to
user.
Re: select index count on a different schema [message #324205 is a reply to message #324188] Sat, 31 May 2008 09:45 Go to previous message
Michel Cadot
Messages: 64111
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No you should not, you first have to know the need to know it and then think about which privilege to grant.
Here, maybe it is just querying all_indexes.

Regards
Michel
Previous Topic: find elapsed time?
Next Topic: help required in PLSQL
Goto Forum:
  


Current Time: Mon Dec 05 21:37:28 CST 2016

Total time taken to generate the page: 0.25490 seconds