PL/SQL :: Drop Context Index

Nov 9, 2012

i tried creating an context index. But it is aborted in the middle. do to reuse the spce i want to drop the index. But it shows an error that " cannot drop index marked as process" how to delete the index.

View 1 Replies


ADVERTISEMENT

Text :: Creating Single Context Index On One-to-many And Lookup Table?

Apr 16, 2013

I've been successfully setting up text indexes on multiple columns on the same table (using MULTI_COLUMN_DATASTORE preferences), but now I have a situation with a one-to-many data collection table (with a FK to a lookup table), and I need to search columns across both of these tables. Sample code below, more of my chattering after the code block:

CREATE TABLE SUBMISSION
( SUBMISSION_ID             NUMBER(10)          NOT NULL,
  SUBMISSION_NAME           VARCHAR2(100)       NOT NULL
);
CREATE TABLE ADVISOR_TYPE

[code]...

I've looked at DETAIL_DATASTORE and USER_DATASTORE, but the examples in Oracle Docs for DETAIL_DATASTORE leave me a little bit perplexed. It seems like this should be pretty straightforward. I'm trying to avoid creating new columns, and keeping the trigger adjustments to a minimum.

View 3 Replies View Related

SQL & PL/SQL :: Determine Index Drop?

Apr 3, 2012

How to find out who has dropped and when was the index created in database?

View 39 Replies View Related

SQL & PL/SQL :: Drop Index Which Has Constraint On Table?

May 18, 2011

How to drop an index which has a constraint on the table?

while droping the index on the table wheather i should remove the

constraint or disable the constraint or without droping the

constraint

View 1 Replies View Related

Server Administration :: Drop Index Hangs On SAP?

Apr 14, 2010

On a SAP system, am trying to drop six indexes, largest is 300MB and smallest is 50MB.

I tried running drop index sapusername.index_name on the 50MB index via SQL*Plus and it seems to be taking forever. anything I can check on the database on why it is taking such a long time?

I can leave it to run overnight but worried that when I come back the next day, it will still be hanged. Is there any quick way of dropping the index, .i.e. drop immediate ...

Am not using SAP's BRTOOLs as it is also hanging from there and the SAP-ADMIN had approved for the DBA to drop it from our end instead.

View 4 Replies View Related

Drop Index Tablespace - File Is Non-empty

Oct 15, 2012

db 10.2.0.4
AIX 5.2

I am trying to drop a index tablespace I moved all indexes to new tablespace there is no indexes in that tablespace even any objects but still it is not dropping.it is giving error as below.

SQL> drop tablespace ret_index including contents;
drop tablespace ret_index including contents
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-02429: cannot drop index used for enforcement of unique/primary key

SQL> alter tablespace ret_index drop datafile '/path/index3.dbf';
alter tablespace ret_index drop datafile '/path/ret_index3.dbf'
*
ERROR at line 1:
ORA-03262: the file is non-empty

try to resize

SQL> alter database datafile 4 resize 100m;
alter database datafile 4 resize 100m
*
ERROR at line 1:
ORA-03297: file contains used data beyond requested RESIZE value

Tablespace size is 280gb

View 32 Replies View Related

PL/SQL :: Index-Organized Table Truncate Vs Lock / Stage / Drop / Recreate?

Apr 24, 2013

I ran into an issue in a project where a function is recreating an index-organized table by doing:Table Structure:

CREATE TABLE table_iot(
...)
ORGANIZATION INDEX
OVERFLOW ...;

Recreate Steps:

1) Populate global temporary staging table (gtt) with data
-- where gtt is staging for target index-organized table (iot)
2) Lock the target index-organized table (iot)
3) Copy old iot data to gtt
-- gtt now contains old and new data
4) Create new index-organized table (iot2) from gtt
-- iot2 now contains old and new data

[code]...

Because index-organized tables are primarily stored in a B-tree index, you can encounter fragmentation as a consequence of incremental updates. However, you can use the ALTER TABLE...MOVE statement to rebuild the index and reduce this fragmentation.The following statement rebuilds the index-organized table admin_docindex:

ALTER TABLE admin_docindex MOVE;

View 3 Replies View Related

SQL & PL/SQL :: Usage Of Context Variables Inside The Trigger?

Feb 17, 2010

Am trying to use the SYS_CONTEXT variables inside a trigger. After reading the documentation I found that the below query when used inside the trigger code will return the trigger owner.

Select Username from User_Users

Will any of the below variables return the trigger owner when used inside the trigger code

sys_context('USERENV','CURRENT_USER')
or
sys_context('USER','CURRENT_SCHEMA')

View 13 Replies View Related

Application Express :: JTree With Context Menu

Nov 2, 2012

I am trying to implement a Jtree with Context Menu. When Context Menu is implemented the nodes wont expand or collapse.This happens with Sample Trees Packaged application for Project Documentation tree.

View 0 Replies View Related

Create Context Sensitive Menu In Oracle Forms 10g

Jan 18, 2010

I'm trying to find out if I can create a context sensitive menu in Oracle Forms 10g. So far I haven't found much in the way of info. where I can find some sample code or a demo I can learn from?

The idea being that when a user right clicks an item they are then presented with relevent options for that item.

View 2 Replies View Related

Bulk Collect Cursor That Can Reduce Number Of Context Switches

Oct 4, 2008

Create a table with 100 records.Then write a BULK COLLECT Cursor that can reduce the number of context switches by using a bulk fetch to query records in sets or all at once.

View 1 Replies View Related

C++ Call Interface :: Calling OCIBreak() And OCIReset() From A Threaded Context

Aug 6, 2012

We have code that occasionally errors with ORA-12151, with tracing indicating some funk surrounding OCI calls to OCIPing(), OCIBreak(), and OCIReset(). What the code is trying to do here is release a connection from a pool (our implementation, not OCIPool) and before releasing it trying to make sure nothing is actively running on the connection etc. The environment is created in THREADED mode (ie not non-blocking).

Reading the documentation its not really clear to me if OCIBreak() and OCIReset() are really intended to be called while in THREADED mode.

Is it a bad idea to call break/reset as part of a general connection-reset routine?

The basic control flow (minus error handling etc) is:

<pre>
reset() {
int rc = OCIBreak(connection_, err_);
rc = OCIReset(connection_, err_);
if (OCIPing(connection_, err_, OCI_DEFAULT) != OCI_SUCCESS) {
return errors;
}
return 0;
}
</pre>

View 0 Replies View Related

Application Express :: Create Items Context Help Editable Report Or Report And Form App

May 9, 2013

How do I create Items context help editable report or report & form app?

what is its table?

View 2 Replies View Related

Text :: Index For Domain Index With Composite Domain Index (CDI) Very Slow

Jun 27, 2012

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.

View 5 Replies View Related

Performance Tuning :: Local Index Versus Global Index On Partitioned Table

Jun 28, 2011

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 Related

Server Administration :: Convert Global Index To Local Index

Jun 23, 2011

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.

View 5 Replies View Related

SQL & PL/SQL :: ORA-01502 - Index Or Partition Of Such Index Is In Unusable State?

Nov 29, 2010

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.

View 3 Replies View Related

Performance Tuning :: Index With NVL / Query Is No Longer Using Index

Nov 19, 2010

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?

View 2 Replies View Related

Performance Tuning :: Force Index If Table Not Using Index?

Aug 9, 2013

How to force an index if the table not using the index?

View 10 Replies View Related

When Index Lock Contention Occurs / How To Know Which Index Is Causing

Aug 27, 2012

We have occurrences of enq : TX - index contentions in the database. Using the SQL ID, we have identified the INSERT statement and the table which they are trying to insert.

This table has almost 25 different indexes, some of which are unique as well.I am wondering how to identify the actual index causing issue, out of these 25 indexes.

Is there any way to pin point to the name of index which is causing the lock?My plan is, once the index is identified, I would like to check the extents and inittrans and other attributes of this index to fix.

View 5 Replies View Related

SQL & PL/SQL :: Create Non-cluster Index On A Clustered Index?

Jul 11, 2012

Can we create non-cluster index on a clustered index?

View 5 Replies View Related

SQL & PL/SQL :: Difference Between Index And Clustered Index

Aug 3, 2010

Any on give explanation for difference between Index and Clustered Index?

It will be great if i get explanation how memory allocation and Execution takes place.?

View 4 Replies View Related

Performance Tuning :: Difference Between Index Rebuild And Index Rebuild Online?

Jan 10, 2011

What is the difference between index rebuild and index rebuild online.

View 3 Replies View Related

Sum Of All Drop Down Values

Nov 28, 2012

I have a dropdown in my jsp with all users from database. When I select a user I'm able to get the details of the corresponding result, but when I click on all it should give the total individually. Below is the code that I use to get individually.

<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@include file="DBCon.jsp" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<meta charset="utf-8" />
[code]...

View 1 Replies View Related

How To Drop Column

Aug 25, 2010

i am tring to drop column but it is taking much time . And there is not locking session also. The table size is 500GB. We have any way to drop to column in fast?

View 2 Replies View Related

SQL & PL/SQL :: Drop A Table?

Apr 6, 2010

When drop a table what happen to view,sequence,synonyms,index,constraint for the table

View 9 Replies View Related

PL/SQL :: Drop All Procedures At One Go?

Aug 29, 2012

I have a schema sys_eg .I dont have any DBA privileges .How can i delete all the procedure with one sql or pl/sql.

ORACLE 11G
I am using sql developer 3.2 version tool

View 6 Replies View Related

PL/SQL :: Index Range Scan And Table Access By Index Rowid Versus Table Access Full

Oct 5, 2013

Let's consider such table that all rows fit into single block:

SQL> create table test as select rownum id, '$'||rownum name from dual connect by level <= 530;
Table created.
SQL> create index i_test on test(id);
Index created.
SQL>
SQL> begin

[code].....

why does approach with full scan take longer even if table occupies only one data block? PS. 11gR2

View 8 Replies View Related

Oracle 11.2.0.2 - How To Drop Partition

Apr 19, 2012

we have our production database running on 11.2.0.2. I have a user table which has partitions. i would like to drop the partition and seeing weird issue:

The command to drop the partition i have is:

alter table SA.accounts drop partition dec_20111203 update global indexes;

Say after 10 minutes, the session is terminated and we are seeing the following: ORA-00028. We don't have a process that kills the session so we are trying to find the root cause why the session is being killed. what might be going on? also as an alternative - i was looking to do the following and want to know - if this works or not:

ALTER TABLE sa.accounts TRUNCATE PARTITION dec_20111203;

Then try to do the drop partition again.

View 7 Replies View Related

Drop All Tables In Oracle 8

Dec 17, 2012

i want drop all tables in oracle8

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved