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'.
I have read and used the AWR script (mentioned in the page Finding unused index for finding unused customised (Z) indexes in our SAP system using oracle 10.2.0.2 as the SAP database.
But this returns no rows. Is there any precondition? I want to know how much / many times the indexes are used...We are smelling that lot of unused index are there in the database.
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 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.
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.
A way to identify variables declared in a procedure that are not being used? I had thought plsql_warnings might do the trick but it does not.
I have inherited a lot of code that was ill conceived and unfortunately have started to notice a trend.
I have a problem...
I created a tablespace called my_ts:
CODECREATE TABLESPACE my_ts DATAFILE 'C:\Oracle\oradata\db\my_ts.dbf' SIZE 5M EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;
ALTER DATABASE DATAFILE 'C:\Oracle\oradata\db\my_ts.dbf' AUTOEXTEND ON;
Its was sucessfully created and my_ts.dbf file has 5MB
charging with data...
CODEcreate table big_table tablespace my_ts as select * from dba_objects;
select * from big_table;
begin
for i in 1..10 loop
insert into big_table select * from dba_objects;
end loop;
end;
Now the my_ts.dbf file has 90MB
Now I want drop this table:
CODEdrop table big_table purge;
And my tablespace file still has 90MB.
I already tried to restart the database but doesn't works...
how can I reduce the size to the original (5MB)?
I have been trying to drop an unused column in a partitioned table, and the number of records stored in this unused column was very high. I kept on running into errors as follows:
ORA-01562: failed to extend rollback segment number 10
ORA-01650: unable to extend rollback segment R09 by 256 in tablespace RBS
I tried to "SET TRANSACTION USE ROLLBACK SEGMENT <name>" with a larger rollback segment, but it still did not work. Can I drop the "unused column" from each partition instead?
How to apply that? Or, what are my options besides increasing the size of the rollback segment?
If you mark a column unused, is there any way to project it? I know the docs say you can't, but as the data is still there I would have thought it should be possible. I can see the column in dba_tab_cols, but the obvious ways of making it usable don't work:
orcl> select column_name,hidden_column from user_tab_cols where table_name='DEPT';
COLUMN_NAME HID
------------------------------ ---
LOC NO
DNAME NO
DEPTNO NO
orcl> alter table dept set unused column loc;
Table altered.
orcl> select column_name,hidden_column from user_tab_cols where table_name='DEPT';
COLUMN_NAME HID
------------------------------ ---
SYS_C00003_13071316:19:02$ YES
DNAME NO
DEPTNO NO
orcl> select "SYS_C00003_13071316:19:02$" from dept;
select "SYS_C00003_13071316:19:02$" from dept
*
ERROR at line 1:
ORA-00904: "SYS_C00003_13071316:19:02$": invalid identifier
orcl> alter table dept rename column "SYS_C00003_13071316:19:02$"
2 to loc;
alter table dept rename column "SYS_C00003_13071316:19:02$"
*
ERROR at line 1:ORA-00904: "SYS_C00003_13071316:19:02$": invalid identifier
orcl> alter table dept modify "SYS_C00003_13071316:19:02$"
2 visible;
alter table dept modify "SYS_C00003_13071316:19:02$"
*
ERROR at line 1: ORA-00904: "SYS_C00003_13071316:19:02$": invalid identifier
I am in the task of clean up of tables. I need to find the list unused tables and procedures. Is there any way where i can find when was the last time the table queried?
Give sql query to find the list of unused tables and procedures.
SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL> alter table F_TRADING_COLLATERAL_SR drop unused columns; Table altered. SQL> SELECT * FROM DBA_UNUSED_COL_TABS; OWNER TABLE_NAME COUNT ------------------------------ ------------------------------ ---------- CRIS_WAREHOUSE_USER F_TRADING_COLLATERAL_SR 1 SQL>
View 6 Replies View Relatedwhat happens if you mark a column unused in a compressed table and then alter table drop unused columns? We had a customer do this and Oracle threw a -3113 (end of communication) error. They did a system restore before contacting us and blew away any evidence in alert logs/trace files. They did this on a 400GB compressed table.
My question is, when you drop an unused column off a compressed table, does it uncompress? Where does this uncompression occur? In the instances default tablespace? In the tablespace configured for the table?
Basically, we are wondering whether the error was due to poor error-handling of the system running out of space during decompression and trying to see if we can reproduce it. This was on an 11.1.0.7 system.
is there a way to find out which unused columns in oracle?
View 8 Replies View RelatedI 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 Related