Home » SQL & PL/SQL » SQL & PL/SQL » need to find out db name for the indexes (Oracle 11g)
need to find out db name for the indexes [message #574739] Tue, 15 January 2013 09:40 Go to next message
ajdba
Messages: 5
Registered: January 2013
Junior Member
I am trying to find out database name for the unusable indexes thru the query; I am using
dba_indexes for the index name but not sure which view I need to join with to find the db name

Thanks

Report message to a moderator

Re: need to find out db name for the indexes [message #574741 is a reply to message #574739] Tue, 15 January 2013 09:46 Go to previous messageGo to next message
BlackSwan
Messages: 20245
Registered: January 2009
Senior Member
ajdba wrote on Tue, 15 January 2013 07:40
I am trying to find out database name for the unusable indexes thru the query; I am using
dba_indexes for the index name but not sure which view I need to join with to find the db name

Thanks

DB Name or Schema Name?

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/

Report message to a moderator

Re: need to find out db name for the indexes [message #574771 is a reply to message #574741] Tue, 15 January 2013 13:46 Go to previous messageGo to next message
ajdba
Messages: 5
Registered: January 2013
Junior Member
what about the db name.

Report message to a moderator

Re: need to find out db name for the indexes [message #574772 is a reply to message #574771] Tue, 15 January 2013 13:48 Go to previous messageGo to next message
BlackSwan
Messages: 20245
Registered: January 2009
Senior Member
 
SQL> select name from v$database;

NAME
---------
ORCL

SQL>

Report message to a moderator

Re: need to find out db name for the indexes [message #574773 is a reply to message #574771] Tue, 15 January 2013 13:49 Go to previous messageGo to next message
ajdba
Messages: 5
Registered: January 2013
Junior Member
How about db name

Report message to a moderator

Re: need to find out db name for the indexes [message #574774 is a reply to message #574773] Tue, 15 January 2013 13:52 Go to previous messageGo to next message
BlackSwan
Messages: 20245
Registered: January 2009
Senior Member
>How about db name

asked & answered!

SQL> desc dba_indexes
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 OWNER                                     NOT NULL VARCHAR2(30)
 INDEX_NAME                                NOT NULL VARCHAR2(30)
 INDEX_TYPE                                         VARCHAR2(27)
 TABLE_OWNER                               NOT NULL VARCHAR2(30)
 TABLE_NAME                                NOT NULL VARCHAR2(30)
 TABLE_TYPE                                         VARCHAR2(11)
 UNIQUENESS                                         VARCHAR2(9)
 COMPRESSION                                        VARCHAR2(8)
 PREFIX_LENGTH                                      NUMBER
 TABLESPACE_NAME                                    VARCHAR2(30)
 INI_TRANS                                          NUMBER
 MAX_TRANS                                          NUMBER
 INITIAL_EXTENT                                     NUMBER
 NEXT_EXTENT                                        NUMBER
 MIN_EXTENTS                                        NUMBER
 MAX_EXTENTS                                        NUMBER
 PCT_INCREASE                                       NUMBER
 PCT_THRESHOLD                                      NUMBER
 INCLUDE_COLUMN                                     NUMBER
 FREELISTS                                          NUMBER
 FREELIST_GROUPS                                    NUMBER
 PCT_FREE                                           NUMBER
 LOGGING                                            VARCHAR2(3)
 BLEVEL                                             NUMBER
 LEAF_BLOCKS                                        NUMBER
 DISTINCT_KEYS                                      NUMBER
 AVG_LEAF_BLOCKS_PER_KEY                            NUMBER
 AVG_DATA_BLOCKS_PER_KEY                            NUMBER
 CLUSTERING_FACTOR                                  NUMBER
 STATUS                                             VARCHAR2(8)
 NUM_ROWS                                           NUMBER
 SAMPLE_SIZE                                        NUMBER
 LAST_ANALYZED                                      DATE
 DEGREE                                             VARCHAR2(40)
 INSTANCES                                          VARCHAR2(40)
 PARTITIONED                                        VARCHAR2(3)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 BUFFER_POOL                                        VARCHAR2(7)
 FLASH_CACHE                                        VARCHAR2(7)
 CELL_FLASH_CACHE                                   VARCHAR2(7)
 USER_STATS                                         VARCHAR2(3)
 DURATION                                           VARCHAR2(15)
 PCT_DIRECT_ACCESS                                  NUMBER
 ITYP_OWNER                                         VARCHAR2(30)
 ITYP_NAME                                          VARCHAR2(30)
 PARAMETERS                                         VARCHAR2(1000)
 GLOBAL_STATS                                       VARCHAR2(3)
 DOMIDX_STATUS                                      VARCHAR2(12)
 DOMIDX_OPSTATUS                                    VARCHAR2(6)
 FUNCIDX_STATUS                                     VARCHAR2(8)
 JOIN_INDEX                                         VARCHAR2(3)
 IOT_REDUNDANT_PKEY_ELIM                            VARCHAR2(3)
 DROPPED                                            VARCHAR2(3)
 VISIBILITY                                         VARCHAR2(9)
 DOMIDX_MANAGEMENT                                  VARCHAR2(14)
 SEGMENT_CREATED                                    VARCHAR2(3)

SQL>

[Updated on: Tue, 15 January 2013 13:53]

Report message to a moderator

Re: need to find out db name for the indexes [message #574775 is a reply to message #574773] Tue, 15 January 2013 13:56 Go to previous messageGo to next message
ajdba
Messages: 5
Registered: January 2013
Junior Member

select owner,
index_name
from dba_indexes
where status='UNUSABLE';

I need to figure out how to add db name for the indexes that I find above

Report message to a moderator

Re: need to find out db name for the indexes [message #574776 is a reply to message #574775] Tue, 15 January 2013 14:00 Go to previous messageGo to next message
BlackSwan
Messages: 20245
Registered: January 2009
Senior Member
>I need to figure out how to add db name for the indexes that I find above
You can only be logged into one DB at a time.
Are you claiming that you don't know the name of DB that you are logged into when you run the SQL?

[Updated on: Tue, 15 January 2013 14:00]

Report message to a moderator

Re: need to find out db name for the indexes [message #574899 is a reply to message #574776] Wed, 16 January 2013 11:10 Go to previous messageGo to next message
ajdba
Messages: 5
Registered: January 2013
Junior Member
I was not looking for the db name that I am connected to; you completely misunderstood. Anyway, I got what I was looking for.
Thanks for your help.

Report message to a moderator

Re: need to find out db name for the indexes [message #574901 is a reply to message #574899] Wed, 16 January 2013 11:26 Go to previous messageGo to next message
Michel Cadot
Messages: 54717
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I got what I was looking for.


Which was?
Post the final query you use.

Regards
Michel

Report message to a moderator

Re: need to find out db name for the indexes [message #574905 is a reply to message #574899] Wed, 16 January 2013 13:06 Go to previous messageGo to next message
joy_division
Messages: 4288
Registered: February 2005
Location: East Coast USA
Senior Member
ajdba wrote on Wed, 16 January 2013 12:10
I was not looking for the db name that I am connected to; you completely misunderstood.


Eh?

ajdba wrote

I am trying to find out database name for the unusable indexes...


ajdba wrote

How about db name

Report message to a moderator

Re: need to find out db name for the indexes [message #574920 is a reply to message #574905] Wed, 16 January 2013 13:59 Go to previous message
Michel Cadot
Messages: 54717
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I just think he is the kind of guy we can ignore.

Regards
Michel

[Updated on: Wed, 16 January 2013 14:01]

Report message to a moderator

Previous Topic: Query to find relevant Table name
Next Topic: Total Recors Count Calculation
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Jun 20 00:11:25 CDT 2013

Total time taken to generate the page: 0.18710 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.