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


ADVERTISEMENT

Server Utilities :: How To Take Exp / Imp For Index Organized Table

Dec 2, 2011

I am trying to take exp / imp for Index organized table.

View 16 Replies View Related

Export/Import/SQL Loader :: Impdp Index-organized Table And Temp Usage?

Aug 1, 2012

Env: RHEL 5.8 RAC 11.2.0.2

I'm currently moving an IOT from one database to another using expdp/imdp. The IOT is non-partitioned and about 100GB in size containing ~1,1 billion rows.

The dumpfile contains nothing else but the IOT. I'm importing with no special parameters, no pre-created IOT, just ordinary dumpfile import. (impdp username/password dumpfile=impdp:iot.dmp nologfile=y )

During import I got unable to extend TEMP errors from impdp.

ORA-39171: Job is experiencing a resumable wait.
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP
ORA-39171: Job is experiencing a resumable wait.
ORA-01652: unable to extend temp segment by 128 in tablespace TEMP

I had to add 2 additional files to my Temp tablespace (total 96GB of temp) before the import could finish off.

Is this temp usage to be expected when importing IOT's ?

View 4 Replies View Related

SQL & PL/SQL :: Unable To Truncate / Drop Table

May 13, 2011

I am trying to Truncate the table but it doesnt get truncated. When I issue the command it even doesnt throw the error. I also tried to drop the table but cant even able to drop the table. I thought table might be locked. But it allows me delete a row from the table.

I am using Oracle 11g Release 11.1.0.7.0

View 17 Replies View Related

Truncate / Drop Partitions In Table Having Nested Table Columns

Sep 7, 2012

I have a table that has 2 columns of type nested table. Now in the purge process, when I try to truncate or drop a partition from this table, I get error that I can't do this (because table has nested tables). how I will be able to truncate/drop partition from this table? IF I change column types from nested table to varray type, will it work?

Also, is there any short method of moving existing data from a nested table column to a varray column (having same fields as nested table)?

View 1 Replies View Related

PL/SQL :: Materialized View - Update Query Without Drop And Recreate

Aug 24, 2012

i have a little doubt in Materialized view. i created a materialized view and log with the following query,

create table test_mv (a number ,b number )
alter table test_mv add constraint t_pk primary key ( a );
insert into test_mv values (1,2);

[Code]...

Now i want to update the query in the MV as 'Select a from test_mv' . for that i tried with

*'ALTER MATERIALIZED VIEW product_sales_mv AS SELECT a from test_mv;'*

But it throwing error,

Error starting at line 5 in command:

alter  MATERIALIZED VIEW product_sales_mv   AS SELECT  b  from test_mv

Error report:

SQL Error: ORA-00933: SQL command not properly ended +00933. 00000 - "SQL command not properly ended"+

*Cause:+   
*Action:+

i guess i am doing wrong. i want to update it without drop and recreate

View 2 Replies View Related

SQL & PL/SQL :: Use Index To Minimize Lock On Parent Table?

Mar 27, 2013

I want to know that what oracle server do to minimize the lock on parent table, when we use Index on foreign key column ?

View 2 Replies View Related

Alter Index Shrink Space - Table Lock

Oct 18, 2012

alter index test_idx1 shrink space;

I've heard that this statement causes a table lock but cant find any information on this.if it is so, is it a write lock or also a read lock of the table?

View 5 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

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

Backup & Recovery :: Logical Block Corruption - Recreate SYSMAN Schema Index

Mar 3, 2010

I am facing problem of logical block curruption.

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
3 61454 1 1417157166 LOGICAL
3 31256 1 1417011117 LOGICAL

OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ------------------------------
SYSMAN MGMT_METRICS_RAW_PK P MGMT_METRICS_RAW

Can i recreate the SYSMAN schema index by dropping this one?

View 17 Replies View Related

SQL & PL/SQL :: Pulling Data From A Stage Table

Dec 20, 2010

I am trying to insert data from tables that I build from a stage table. I have pasted my package body code below and error message.

create or replace
PACKAGE BODY etl_contact_sales
IS
PROCEDURE insert_contact_sales
IS
start_dt DATE := sysdate;
v_error_message varchar2(150);
v_error_block varchar2(30);
v_exception exception;
v_process_name varchar2(30);
[code]...

View 11 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

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 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

SQL & PL/SQL :: Truncate Master Table?

May 17, 2013

The Scenario is that we have Master and detail table (With Foreign key enabled), we want to TRUNCATE Master table.

1) Is there any option which can Truncate the table without disabling the constraints for child tables...we want to Truncate the table forcefully..
2) What will be best method to truncate a Table having Master detail relation (Foreign key enabled) and we need to truncate the table without disabling the constraint ( if there are records in child table)
3) What will be best method to truncate a Table having Master detail relation (Foreign key enabled) and we need to truncate the table without disabling the constraint ( if there are NO records in child table

View 15 Replies View Related

Truncate Table Before Import

Aug 16, 2012

i need to import into table A. Before that i will do a truncate.

I will disable the constraint.

DO i need to disable any triggers on the table before importing?

View 12 Replies View Related

PL/SQL :: Trigger To Insert In One Table From Other And Truncate 2nd Table

Aug 2, 2012

I have table t1 and t1 , I want a procedure that will insert all records from t1 into table t2 and after successfull insert table t1 should be truncated .

If their is any problem in insert in to table t2 , the truncate command should not work .

Truncate command should work only after successfully insert command .

View 3 Replies View Related

How To Grant Truncate Any Table To A User

Jan 30, 2013

on 11G R2,

We want to grant truncate any table to a user.

How ?

We should create the following procedure ?

1. Create the procedure to truncate the table.

create or replace procedure truncate_table (
table_name varchar2,
storage_type varchar2)

[Code]....

grant execute on <procedure_name> to <user>Is it for only one table ? If yes how to do that for all tables ? Or the tables of a schema ?

View 6 Replies View Related

PL/SQL :: How To Truncate Data In Table Partitions

Aug 9, 2013

I couldn't either DROP or TRUNCATE the table partitions that were created. Here are the DDLs and DMLs I'm using. 

Create table student(no number(2),name varchar(2)) partition by range(no) (partition 
p1 values less than(10), partition p2 values less than(20), partition p3 values less     
than(30),partition p4 values less than(40)); 
Insert into student values(1,'a');
Insert into student values(11,'b');
Insert into student values(21,'c');
Insert into student values(31,'d'); 

When I do the following query, it returns data.

SELECT * FROM STUDENT PARTITION(p1); 

But, when I try to perform any of the following queries, it says invalid partition name. 

ALTER TABLE STUDENT DROP PARTITION p4;
ALTER TABLE STUDENT TRUNCATE PARTITION p3;

I am using Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit

View 9 Replies View Related

Truncate Table That Has 2 Billion Rows

Mar 7, 2013

Our DB is 11g and approximately, how much time it takes and do we need to rebuild indexes after truncate?

View 5 Replies View Related

SQL & PL/SQL :: What Stage Update Data Will Be Checked

Aug 13, 2010

Constraint_type = UNIQUE

If I'm updating a value from 10000 to 555555. And the constraint's status is ENABLED Deferred is IMMEDIATE.

Now my question is at what stage the updating data will be checked?At the entry level or at end of the Updation statement?

View 5 Replies View Related

Server Administration :: Use Of LM ( Lock Mode) In V$lock?

Oct 4, 2012

we know we can see lock mode held in session can be analysed using LM column in v$lock.But i confused in seeing LM column it all shows in numbers from 0 to 6.

eg

0,'None(0)',
1,'Null(1)',
2,'Row Share(2)',
3,'Row Exclu(3)',
4,'Share(4)',
5,'Share Row Ex(5)',
6,'Exclusive(6)')

View 1 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

Backup & Recovery :: Recover Truncate Table Data?

May 24, 2012

Our client has this scenario:May 23th 4:00 PM a user truncate a critical table. The customer need to recover the data before truncate.

The recover materials list like this:
1. A cold backup of this database at May 1th.
2. The archive log except May 1th ~ May 15th.
3. Every day exp at 00:30 and 12:00.

Is there any way to recover the data before truncate table with these material?

View 3 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 Utilities :: Export Data Ordered And Organized

Nov 29, 2011

is any way to export an Oracle database organized in manner that, both tables and constraints would be exported in the correct order.An easy sample:

- An database with 2 tables, with constraints between them. Table 1 has a FK on Table 2.

Is it possible to export both structure and data regarding the constraints, resulting in an script that makes it possibly to import it in a way that would not give me problems about constraints?

View 4 Replies View Related

SQL & PL/SQL :: How To Lock Particular Row In A Table

Jul 19, 2012

how one can lock a particular row in a table.for example i have a employee table in which 50 records. now i want to lock only 10 records of the employee table.

View 8 Replies View Related

Data Guard :: Truncate Table Effect On Physical Standby

Nov 20, 2009

Are there redo generate during truncate operation even my primary database is in FORCE LOGGGING mode? How this will effect on physical standby database.

View 6 Replies View Related







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