When Do Global And Local Indexes Becoming UNUSABLE?
Nov 29, 2012
RDMS Version : 11.2.0.2
Platfomrm : AIX 6.1
For partitioned tables (RANGE, LIST types ) what are circumstances when a Global or a Local Index become UNUSABLE ? I was told that in some circumstances where the Indexes become UNUSABLE , not even a SELECT query against the table will work. Is this true ? For partitioned tables with Global Index, the global index will be listed in DBA_INDEXES . Right ?
If I have owner, table_name is there a query I can issue that will tell me if I have to add the "update global indexes" clause when dropping a partition from a table?
ALTER TABLE SNYT.PART_ESTD SPLIT PARTITION ESTD_M13_S22 AT ('ESTD', 13, '22') INTO (PARTITION ESTD_M13_S21, PARTITION ESTD_M13_S22) update indexes;
(per http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1401247200346349807) ================================================================= If Global index used
ALTER TABLE SNYT.PART_ESTD SPLIT PARTITION ESTD_M13_S22 AT ('ESTD', 13, '22') INTO (PARTITION ESTD_M13_S21, PARTITION ESTD_M13_S22) UPDATE GLOBAL INDEXES;
I have a partitioned table like below. I want to create a B-Tree index on SALES_RGN column which is neither the part of Primary key or the Partitioned key. Should I create this index as local or Global ?
CREATE TABLE sales_dtl ( txn_id number (9), salesman_id number(5), salesman_name varchar2(30), sales_rgn varchar2(10), -----------------------------> This column needs to be indexed sales_amount number(10), sales_date date, constraint pk_sales_dtl primary key (txn_id) [code]....
writing a trigger to drop partitions with zero rows which are older than 6months and drop the local indexes and rebuild the global indexes for any schema in a databaase ?
I have tried the below code :
declare v_statement varchar2(600); v_rows number; begin for x in (select * from dba_tab_partitions [code]........
I want to avoid using row number and also want to dynamically select a schema when executing the script.
I'm trying to find a way to ADD new partitions to local indexes and at the same time specify their tablespaces without having to DROP and RECREATE.
Here´s an example table based on yearly partitioning:
CREATE TABLE "TABL_ANOM" ( ANOM_TS TIMESTAMP(6) NOT NULL , ANOM_TIPO NUMBER(2, 0) NOT NULL , ANOM_NIVEL NUMBER(2, 0) NOT NULL , ANOM_ID NUMBER(10, 0) NOT NULL
[code]...
Here´s an index def for the table:
CREATE INDEX "TABL_ANOM_INDEX1" ON "TABL_ANOM" ("ANOM_NIVEL") LOCAL (PARTITION DGSCOPSX_2011 TABLESPACE DGSCOPSX_2011 ,PARTITION DGSCOPSX_2012 TABLESPACE DGSCOPSX_2012 )
OK. Now I want to add partitions for 2013 so for the table I use:
ALTER TABLE TABL_ANOM ADD PARTITION DGSCOPS_2013 VALUES LESS THAN (TO_DATE('2014-01-01 00:00:00','YYYY-MM-DD HH24:MI:SS')) TABLESPACE DGSCOPS_2013;
and this works fine for the table but I can't find a similar command to simply add additional partitions to the indexes. I know that I can drop and recreate the indexes with the additional partition defs but on some of my tables, I'm dealing with hundreds of millions of rows and I think it would take way too long to drop and recreate all indexes on all partitions.
Also related is the PRIMARY KEY index partitions. Is there a way to add partitions (specifying the tablespaces) without having to DROP and re-ADD the CONSTRAINT with the additional partition for 2013?
I have a huge table (about 60 gb) partition over range. The index on this table is global index created on 4 columns together. I have a query which is running very slowly. The explain plan is showing the use of this global index.Explain plan is not showing pstart and pend because the index is global.
SNRCTL> stat LISTENER Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))) STATUS of the LISTENER ------------------------ Alias LISTENER Version TNSLSNR for IBM/AIX RISC System/6000: Version 11.2.0.1.0 - Production Start Date 19-JAN-2013 00:50:10 Uptime 0 days 0 hr. 29 min. 51 sec Trace Level off Security ON: Local OS Authentication [code]....
In every oracle documentation for e.g:11.2 Scan and Node TNS Listener Setup Examples [ID 1070607.1] we found the local listener status showing both local-ip and vip. Why is not showing in our case?
2437312:ORA-12801: error signaled in parallel query server P009 2437313:ORA-01502: index 'POS.XIETBK_POS_FACT_TRAN_DATE' or partition of such index is in unusable state
and tried to rebuild the index and i got following error.
ORA-14086: a partitioned index may not be rebuilt as a whole.
The table size for the index is large. we need to rebuild the hole index.
after merging two partitions into single partition (partition is by list) of a table ,when i analyzed the table it is giving this error : ORA-01502 INDEX TEST.PK_ID or partition of such index is in unusable state.
when I check one database, I noticed that the primary key constraint is in UNUSABLE status. But when I check the table, the table DDL itself,the below query mentioned.
ALTER INDEX "COMMON_DATA"."PK_DISE_MOBILE" UNUSABLE ENABLE
suggest whether I need rebuild this index? if I rebuild this index, will it useful for me for the performance improvement? But the table has only 578234 records.
I am facing the error "ORA-01502: index or partition of such index is in unusable state " while loading the text data using sql loader with direct path (direct = Y ,rows = 10000) option. Table consists an composite non unique index. If I query the dba indexes for the effected index it shows the index status as VALID. There was no maintaince done on the effected table or index. I have tried loading the same data using conventional path but didn't found any issues for the same.
I have two database DB1 for EBS database and DB2 for Portal database. DB2 is always up.
DB1 uses some Global Temporary tables to write and store session level information.
I have Secondary database also for DB1. Whenever DB1 is down and its secondary database base is up, my requirement is to enable write operation to these Global Temporary Tables. Since secondary database we open Read-Only mode , I can't write to these GTTs.
DB2 is always up so I want to create the copies of these GTTs in DB2 portal database. Is there any harm on doing this.
Is there any harm storing session level information of DB1 database In DB2 database through DB-Link.
What is the best option for GLOBAL TEMPORARY TABLE
1) option create GLOBAL TEMPORARY TABLE with ON COMMIT DELETE ROWS. and wheverever this is used for calculation commit at the end of porcedure.
CREATE GLOBAL TEMPORARY TABLE gtt_test ( A NUMBER )ON COMMIT DELETE ROWS;
CREATE OR REPLACE PROCEDURE my_proc ( p_in in number) as begin
[Code]....
2) create GLOBAL TEMPORARY TABLE without ON COMMIT DELETE ROWS and wheverever this is used use delete from Temp table /Truncate table and then user it.
CREATE GLOBAL TEMPORARY TABLE gtt_test ( A NUMBER );
CREATE OR REPLACE PROCEDURE my_proc ( p_in in number)
this is just a generic question. If a table has multiple Indexes on it say a table of 20 columns where 8 columns have indexes on it (each columns out of the 8 has an index on it and out of which three columns contain unique indexes on it).
If i run a query on this table where only two columns are specified in the where clause are the remaining 6 indexes on the table scanned during query execution.