I can see the error in alert log like
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.
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.
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.
View 7 Replies View Relatedora 20000 index partition in an unusable state. what can i do
View 6 Replies View RelatedWe are getting an error as below when trying to load data into a table.
INSERT /*+ APPEND parallel(IA_SBSCR_DED_MAX,4) */ INTO EDW.IA_SBSCR_DED_MAX
*
ERROR at line 1:
ORA-12801: error signaled in parallel query server P000
ORA-26026: unique index EDW.XPKIA_SBSCR_DED_MAX_A initially in unusable state
The index has been rebuilt but we still have this issue.
why i encountered the error that the index became unusable.
View 2 Replies View Relatedsplitting a table partition without making its primary key index ar any other indexes unusable.
I think it is possible to do so 10g onwards.
DB Details:
Oracle RDBMS 11.2.0.3, HP-Ux B.11.31, OLTP
I made an Index Unused while doing some update by using sql developer right click 'Make unusealbe'. Now I need make it as useable.
marking as Usable. I have checked in all_indexes state is show as 'UNUSABLE'.
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 ?
How monitor the unusable objects in oracle 10g (10.2.0.1.0) os rhel 5? is it same as invalid objects and can be monitor as same?
View 4 Replies View Relatedwhen 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.
Table DDL
CREATE TABLE "COMMON_DATA"."DISE_MOBILE_INSTALLATION"
( "M_DBASE_ID" VARCHAR2(2),
"COMPANY_NUMBER" NUMBER(3,0),
"M_ACCT_ID" NUMBER(8,0),
"ACCOUNT_CREATION_DATE" DATE,
"ORDER_NUMBER" VARCHAR2(15),
"ORDER_CREATION_DATE" DATE,
"ORDER_CREATION_METHOD" VARCHAR2(1),
"CONTRACT_TERM" VARCHAR2(3),
"GROUP_ID" VARCHAR2(6),
[code]...
I have got a table where the site address is stored as follows -
SOUTH PARK ROAD #822,,,,
HOLLYWOOD,FL,33021,,
USPO BOX 1111,,,,
PHILADELPHIA,PA,19178-3478,,
USNORTH AVE EAST,
P.O. BOX 1234,,,ELIZABETH,NJ,07207-6031,,
US12345 PARK SORRENTO SUITE 222,,,,
CALABASAS,CA,91302,,US
I have got 2 fields to be populated from this - CITYSTATE How can I pick up the 5th and 6th field separated by the comma.
For example: -
Following output should be shown for the above recordsCITY
STATEHOLLYWOOD FLPHILADELPHIA PAELIZABETH NJCALABASAS CA
want to know the following
1. when the oracle session changed from active to inactive?
2. what is the time for active session?
3. session is changed to inactive from active. but it is still showing in v$session.
4. in v$session, can i see the ipaddress of client machine ?
I am on 11.2.0.3 Enterprise Edition. We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really works with mixed queries. We added two number columns using FILTER BY.We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:
Oracle Text, 11.2.0.3.0
14:33:05 06/26/12 begin logging
14:33:05 06/26/12 event
14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
14:33:16 06/26/12
14:33:16 06/26/12
[code]....
I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.
When i am writing a function (Stand alone or inside a Package) i know what i am writing, i know weather i am changing a Package state or weather i am changing a Database State. So what is the use for giving a PRAGMA RESTRICT_REFERENCE?
as for the other PRAGMAs EXECPTION_INIT is needed to Specify an error name to a specifiec error number so that i can use the error name to handle the exception. AUTONOMOUS_TRANSACTION is used to execute the SQL Operation inside a Block as a child Transaction.SERIALLY_REUSABLE states that a package variable doesnot persists throughout the session.
RESTRICT_REFERENCE states that the code should not do the following
RNPS : Read no package state,
WNPS : Write no package state
RNDS : Read no database state
WNDS : Write no database state
what is there to state as i know what my code is doing.
without specifing EXECPTION_INIT, AUTONOMOUS_TRANSACTION or SERIALLY_REUSABLE i cannot get a handler for an exception which doesnot have a handler, i cannot execute SQL Operations from inside the CODE autonomously or i cannot reuse the package variable, but without the RESTRICT_ REFERENCE is can ensure that my code in not doing (RNPS,WNPS,RNDS,WNDS).
I understand the architecture of solid state drives favor different types of I/O. Oracle 11g introduced the database flash cache which allows flash storage to be used as a secondary cache. With database flash cache serving as a secondary cache to my Oracle buffer cache.
My understanding is I need to config two database parameters DB_FLASH_CACHE_FILE and DB_FLASH_CACHE_SIZE. If i want to have certain tables/indexes to take advantage of this extra cache I would set the value KEEP within the parameter.
Is there anything that needs to be done to the tablespaces / datafiles.
so here is the query : For every state, list the most popular product. Here is the query I have so far :
SELECT C.State, P.Product_Name, Count(*) cnt FROM Product P, Customer C, OrderTable O, LineItem L WHERE C.CID = O.CID AND O.OID = L.OID AND L.PID = P.PID GROUP BY C.State, P.Product_Name;
Result :
STATE PRODUCT_NAME COUNT(*)
---------- -------------------- ----------
New Jersey Computer 3
Texas Computer 1
New Jersey Speaker 2
I would need the result to only say New Jersey Computer and Texas Computer because I only want a list of the states with the product name that is sold the most in each state.All I need to do is have the query only select the product name with the max count for each state...
I am getting the following error only for the ""first execution"", even the package is in "valid" state. If the execute the same package again "second execution", it runs ok.
usually we complie all the "invalid objects" in the schema when we move the code to TEST or PRE-PROD env.
but i am getting the following error, even if the package is in "valid state".
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04061: existing state of package "TEST_OWNER.TEST_PKG" has
been invalidated
ORA-04065: not executed, altered or dropped package
"TEST_OWNER.TEST_PKG"
ORA-06508: PL/SQL: could not find program unit being called:
"TEST_OWNER.TEST_PKG"
ORA-06512: at line 2I have gone through the following thread, blu explained the cause of this error,
but do we get this error even when the package is in "valid" state too?
Re: When should be package invalidated?
We are experiencing an issue with ORA-04068: existing state of packages has been discarded. We're running Oracle 10.2.0.4 on Solaris 10.
I'll provide the timeline of what occurred:
1.) Last night during a maintenance window I recompiled a package and package body. I ran "create or replace" statements from a script via sqlplus to recompile both package and package body. Because I am required to do all DDL under my own login (and not the schema owner login) the scripts require that the schema name be included in the "create or replace" statements. Otherwise an object will be incorrectly created in my schema. This is what happened with the package body. I discovered this when the package body would not compile.
2.) I ran a "drop package body" statement to drop the package body from MY schema.
3.) I added the proper schema name to the script and recompiled both the spec and body under the proper schema.
4.) I ran a script to check for any invalid objects under the application user schema. There were none.
5.) During the night a batch process ran which executed the package. There were several failures with the error ORA-04068: existing state of packages has been discarded.
6.) During the early AM today the error stopped. Not knowing this at the time and believing that the issue was still occurring I began searching the internet for information regarding this issue. One site I found said this could be due to DDL being done on the object (which was the case for us) and there could be an entry in the shared pool marked as invalid which our application's session may be accessing. It recommended running "alter package recompile" for the spec and body. I did this but it seems to have triggered the event again.
I also ran utlrp.sql.
I found some information suggesting the use of DBMS_SESSION.MODIFY_PACKAGE_STATE and/or RESET_PACKAGE but I'm not seeing mention of the risks/implications.
I can verify that the object is no longer in my schema and that the proper object owner has no invalid objects. I am not sure what to do to resolve the issue or keep it from occurring again.
I started database in mount state;startup mount; alter tablespace users offline;
I tried to make tablespace offline In another case online but showing error database not open same for command alter tablespace begin backup; do think we can make tablespace offline in mount...
The function is supposed to tell whether numbers stored as a string value evaluates to a date.I copied into an SQL windown in PL/SQL Developer and typed "commit;" after and then tried to view the results as I posted below to make sure the function worked.
However, I get an ora-06575 Package or Function GETDATE is in an invalid state.So would like to know first, if the code is correct? And if it is, do I need to do something else to make Developer take the code? Here is the code that gave me the error I used to try to test the function:
select GetDate ('010312', 'dd-mm-yyyy')
from dual
Here is the code I used to create the function:
create or replace function GetDate (vDate varchar2, vMask varchar2)
return varchar2
is
vReturnDate date;
begin
vReturnDate = to_date(vDate, vMask);
return vReturnDate;
exception
when value_error then
return 0;
end GetDate;
commit;
I am trying to use dbms_jobs in Oracle 8i database.
This is the syntax I am using:
begin
dbms_job.submit(:jobno, 'SYSTEM.SHRINK_ROLLBACK_SEGS', '07-FEB-2007', NULL);
commit;
end;
i have defined the variable jobno as number!
And i am getting the error :
*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04063: package body "SYS.DBMS_JOB" has errors
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 2
I then go and look at the Sys.dbms_job package and see its not compiling
I have this error:
(1): PLS-00201: identifier 'ANY_INSTANCE' must be declared
(2): PL/SQL: Item ignored
(3): PLS-00801: internal error [21076]
(4): PL/SQL: Item ignored
i have the following problem:
At a test database instance of Oracle 11g (11.2.0.3.0) at CentOS 6 i've imported a tablespace from another instance to test my backup strategy.
First i've done a fullbackup with RMAN:
----------
# rman target /
RMAN> BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
----------
Then i thought to test the database i could delete something and restore and recover the database from the backup. So i deleted a table in the imported tablespace:
----------
# sqlplus / as sysdba
SQL> drop table TESTTABLE;
----------
After that i would start the restore and recover process by restoring and recovering the tablespace in which I deleted the table with RMAN. At first i've set the tablespace offline:
----------
SQL> alter tablespace TESTTABLESPACE offline immediate;
----------
Then I go to RMAN and start the restoring:
----------
RMAN> restore tablespace TESTTABLESPACE;
----------
Now there is a problem when i execute the recover command. The recover command includes the recovering from the redolog files, right? As I dropped the table from the tablespace this operation was written in the redolog files. So when i make a :
----------
RMAN> recover tablespace TESTTABLESPACE;
----------
The dropped tablespace would be dropped also, because the last redolog file told RMAN to drop the table. So i think I have to restore and recover my tablespace from an older timestamp, so RMAN would ignore the dropping of the table in the redolog file. Is that right so?
And when it is right how can i restore a tablespace from an older date so RMAN would ignore the dropping?
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.
View 6 Replies View RelatedI'am having a query regarding population of a list item based on another selected list item that is whenever i select the state from the combo box all the city's under that state should be automatically populated.
View 4 Replies View RelatedI've got an interactive report with detail and icon view enabled. How do I get the current display state (user looks at report, detail or icon view) out of the apex dictionary ?Is there any place in APEX_APPLICATION_PAGE_IR_... where I can find it ? I'm on APEX 4.0.2. Upgrade to APEX 4.2.2 is planned this fall.
View 3 Replies View RelatedI need to filter a SQL statement with an application item value retrieved from session state. I am assuming I could do this using a Dynamic Action on a Page Load event but I'm not sure where to go from here. How could I append the filter to an existing report page SQL statement?
View 7 Replies View RelatedI have upgraded from APEX 4.1 to 4.2.all read only items with dynamic action are arising below error:
session state protection violation: this may caused by manual alteration of protected item P2_IDACCSET.
I have a global index and I want to convert it to local index.Is there a way to recreate local index with out dropping the global index.
I can create a local index first and then drop the global index. But is there a way to create it with out dropping the global index, just convert it.
I have a query which had a join:
a.c1=b.c1 and a.c2=@var
where @var is user supplied input at runtime...We had a index on a.c2 . The CBO would use this index to generate an opitimised query plan.We found some records from table "b" were dropping due to inner join. So we made a change in join. It'd be like
a.c1(+)=b.c1 and nvl(a.c2,@var)=@var
This query is no longer using the index, instead its doing a full table scan causing the query to slowdown.I have tried creating index on nvl(a.c2,'31-dec-9999')
But the CBO won't use it.Anyway to create index on this col so that full table scan can be avoided?