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


ADVERTISEMENT

Server Administration :: Possible To Run SHRINK SPACE Against Table With Function Based Index

Jun 27, 2013

It is not possible to run SHRINK SPACE against a table with a function based index. This is documented, and I've tested on the current release. I've reverse engineered it a bit, and the issue is in fact that you cannot SHRINK SPACE if there is an index on a virtual column:SQL> SQL> create table t1(c1 number, c2 as (c1*2)) segment creation immediate;

Table created.
SQL> alter table t1 enable row movement;
Table altered.
SQL> alter table t1 shrink space;
Table altered.
SQL> create index i1 on t1(c2);
Index created.

SQL> alter table t1 shrink space;
alter table t1 shrink space

ERROR at line 1: ORA-10631: SHRINK clause should not be specified for this object.

View 2 Replies View Related

How To Shrink Table Space

Jun 11, 2013

I am trying to Shrink Table space using the following SQl. As we are dropping large datasets. Later i am trying to shrink the table space

Alter Tablespace table_name Shrink Space Keep 20M;

Is this the best way to do in oracle 11G

View 1 Replies View Related

Alter Table Fails - Ran Out Of Undo Space?

Jun 11, 2009

I want to alter a very large table.

ALTER TABLE MYTABLE ADD
(
ENTRY_TSTMP DATE DEFAULT SYSDATE NOT NULL
)

My table is very large and I am getting an error saying I am out of undo space.

The dba says the undo space is as big as the table.

View 1 Replies View Related

Use Shrink To Reclaim Space On Some Tables

Sep 4, 2012

I am trying to use shrink to reclaim space on some tables:

-First of all i am using this query to verify which tables are eligible to shrink:
select segment_owner, segment_name, segment_type,
trunc(((allocated_space)/1024)/1024) "aloc",
trunc(((used_space)/1024)/1024) "used" ,
trunc(((reclaimable_space)/1024)/1024) "reclaim"
from table(DBMS_SPACE.ASA_RECOMMENDATIONS()) where segment_owner='&owner' and segment_type='TABLE' order by reclaimable_space desc;

I'll show the toP1 below:

SEGMENT_OWNER SEGMENT_NAME SEGMENT_TYPE aloc used reclaim
-------------------------- --------------------------- ------------------ ---------------- --------
USR_CONTROLFIN CFI_PORTADOR TABLE 744 0 743

Fine, then i shrinked it:

10:06:39 brunos@gf> alter table usr_controlfin.cfi_portador enable row movement;

Table altered

Executed in 0,047 seconds

10:06:57 brunos@gf> alter table usr_controlfin.

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

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

How To Shrink Tablespace Index

Mar 10, 2011

Well, I have a oracle database 10g and the tablespace INDX was getting up to 32 GB size. Now I added second datafile to the space, but can I shrink this space? In my view this space is responsible for indexes, right? There is a command to rebuild the indexes or there's another trick?

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

Extra Space In File_location For Alter Command

Nov 7, 2010

I do below alter command

alter tablespace RCA_MB_IDX add datafile '/gxsdb/database/oradata5/RCA/RCA_REF_MB_IDX_01.dbf ' size 1024M

But there is extra space in the file location '/gxsdb/database/oradata5/RCA/RCA_REF_MB_IDX_01.dbfX'

I mark X for that space ...

The command is commit .

But there is problem when do backup .

View 2 Replies View Related

Server Administration :: Extra Space In File_location For Alter Command?

Nov 7, 2010

I do below alter command alter tablespace RCA_MB_IDX add datafile '/gxsdb/database oradata5/RCA/RCA_REF_MB_IDX_01.dbf ' size 1024M

But there is extra space in the file location '/gxsdb/database/oradata5 RCA/RCA_REF_MB_IDX_01.dbfX' I mark X for that space ...

The command is commit .

But there is problem when do backup ...as the file cannot be find since there is extra space after ".dbf" How can I fall back ?

View 4 Replies View Related

SQL & PL/SQL :: How To Create Dynamically ALTER INDEX Command

Nov 15, 2010

how I can create an index dynamically? The DBA wants me to put this code below in the beginning of a PL/SQL package. At first, I tried by just putting the below syntax in the code, but I get an 'Alter' is not a valid identifier.

alter index ZZAP_selected_invoices_n1 rebuild online;

And then once the job completes...

alter index ZZAP_selected_invoices_n1 unusable;

View 8 Replies View Related

Server Administration :: Table Cannot Shrink?

Sep 26, 2011

My table can not shrink, why?

SQL> Alter Table tb_hxl_user Shrink Space Cascade;
Alter Table tb_hxl_user Shrink Space Cascade
*
ERROR at line 1:
ORA-10635: Invalid segment or tablespace type

SQL> desc tb_hxl_user;
Name Null? Type
----------------------------------------- -------- ----------------------------
STATEDATE NOT NULL DATE
USERNUMBER NOT NULL VARCHAR2(13)
PROVCODE NOT NULL NUMBER

[code]...

View 9 Replies View Related

Server Administration :: How To Shrink Table Size

Jan 4, 2012

I have a table: desc STG_XML

Name Null Type
------------------------------ -------- ------------------------
ENTITY_ID NOT NULL VARCHAR2(100 CHAR)
ENTITY_TYPE_ID NOT NULL NUMBER
SOURCE_ID NOT NULL VARCHAR2(512 CHAR)
XML_SCHEMA_ID NOT NULL NUMBER
JOB_ID NOT NULL NUMBER
FINGERPRINT NOT NULL VARCHAR2(100 CHAR)
ENTITY_XML_DATA CLOB()
ARCHIVED NUMBER(1)
CREATION_DATE TIMESTAMP(6)
MODIFICATION_DATE TIMESTAMP(6)
ARCHIVING_DATE TIMESTAMP(6)
CREATED_BY VARCHAR2(50 CHAR)
MODIFIED_BY VARCHAR2(50 CHAR)

The problem is that the data of the table are 40GB while on the DB the table holds 400GB! How can I shrink and reuse that space except from drop/recreate and drop/import?

The table has no initial data, so that I can play with the INITIAL parameter. Data are inserted, updated and deleted all the time. I have run DBMS_ADVISOR which recommended to SHRINK table. I have performed the shrink :

alter table STG_XML shrink space COMPACT;

but I haven't gained any space.

View 12 Replies View Related

Any Way To Free Space From Sysaux Table Space?

Oct 3, 2012

Just now sysaux resized to 600m from 250m >>

Sysaux Tablespace is running low. WE SET AWR RETENTION TIME=60 DAYS. WE ARE NOT INTEREST TO EXTEND SYSAUX TABLESPACE SIZE.
Usually we take AWR weekly once. Some times we did ADDM report and ASH.

CODEsql>select TABLESPACE_NAME, FILE_NAME, BYTES/(1024*1024), AUTOEXTENSIBLE, MAXBYTES/(1024*1024)  from dba_data_files where tablespace_name = 'SYSAUX';

TABLESPACE_NAME       FILE_NAME                                             BYTES/(1024*1024)     AUT         MAXBYTES/(1024*1024)
SYSAUX                  /u01/app/oracle/oradata/test/sysaux01.dbf           600                  YES                 32767.9844
CODEsql> @SCRIPT.SQ

TABLESPACE   TOTAL_SPACE(MB)    USED_SPACE(MB)   FREE_SPACE(MB)     % Used    % Free
SYSAUX          600                  248            352               41.33        58.67

1. What's the best SOLUTION ?
2. Can i shrink sysaux tablespace ?
3. I think , The size for all occupants in sysaux tablespace is less than 200 MB => how to find actual content of sysaux tablespace ?
4. What could be the reason for growth? Is there any way to free the space from sysaux table space?

View 9 Replies View Related

Performance Tuning :: Difference Between Move And Shrink Table?

Jul 22, 2011

What is different between move table and shrink table?

View 2 Replies View Related

Server Administration :: Recover Space After Index Rebuild?

Apr 26, 2011

We have separate tablespaces for Tables and Indexes. Also Temp is in different temporary tablespace and UNDO also in UNDO tablespace and Index tablespace contains only Indexes. The tablespace usage for tables is 80% and Index is 91%.There is not enough disk space to allocate for the datafile on our system. I ran a rebuild on one of the Index but now notice the Index tablespace is 98% used soon after the rebuild finished.

How can i free up space for Index tablespace and why does the size of Index tablespace increased after the rebuild.

View 8 Replies View Related

SQL & PL/SQL :: Difference Between Alter Session And Alter System

Jun 19, 2013

What is the difference between alter session and alter system?

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

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

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

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

SQL & PL/SQL :: How To Release Lock On Table

Apr 12, 2010

How to Release Lock on table ? ( without killing session )

create table x ( a number);
insert into x(a) values (1);

Lets lock table.

declare
cursor c is select * from x for update;
begin
open c ;
end;
[code]....

View 15 Replies View Related

SQL & PL/SQL :: Lock Table Manually?

Jul 15, 2010

How can one lock a table manually?

View 7 Replies View Related

SQL & PL/SQL :: Grant Alter Table?

Jul 26, 2011

if a user have alter table gant but could not alter .. what additional grant it need

SQL> alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3));
alter table HRS_PERS_FIELDS_INC modify(PER0000252 NUMBER(19,3))
*
ERROR at line 1:
ORA-00942: table or view does not exist

View 11 Replies View Related

SQL & PL/SQL :: DDL Trigger After Alter Table

Jul 22, 2010

I created a DDL trigger which manipulates columns of a table after an "alter table" statement.

For us this was all the time
"alter table XXX add NEW_COL varchar2(20)"
or
"alter table XXX modify NEW_COL varchar2(20)".

Unfortunatly we have now a requirement which statements "alter table XXX drop NEW_COL varchar2(20)".

My trigger works fine - you see no problem directly after statement execution but the table is then in an invalid status.

The connection will be destroyed after you tried to see the data of this table.

Also the export (creating a dump) will not work.

The problem I see is that I the database will do a "modify" on a column which has internal the status "dropped".

How I can get the information what kind of alter table statement will be executed?

Or is there any chance to select only columns from USER_TAB_COLUMNS without the "dropped" flag?

create or replace trigger TRIGGER
after alter
on SCHEMA
declare
vCharSet NLS_DATABASE_PARAMETERS.VALUE%TYPE;
begin
-- Select all columns of a table

select COLUMN_NAME, DATA_TYPE, DATA_LENGTH
from USER_TAB_COLUMNS
where TABLE_NAME = vTABLE_NAME; ...

-- Loop for all columns from our select
...
vSTATEMENT := 'alter table ' || vTABLE_NAME || ' modify ' || VOBJECTREC.COLUMN_NAME || ' ' || VOBJECTREC.DATA_TYPE || '(' || vNEW_DATA_LENGTH || ')';

--For example:

-- alter table XXX modify NEW_COL varchar2(20)

execute immediate vSTATEMENT;

exception
...
end;
/

View 11 Replies View Related

Alter Table Column And Its Contents?

May 4, 2011

I have a table with usernames and passwords. The passwords are stored in plaintext. I would like to issue an ALTER command on the password field to store a hash instead, and then repopulate those fields with an encrypted version of the plaintext passwords that were there before.

I would prefer to do this in a procedure, as I am going to perform it in a test environment first, then eventually in the production environment.

View 2 Replies View Related

SQL & PL/SQL :: Update Column - Add Two 00 After Alter Table

Apr 14, 2012

I have a table PR in that some data is there for instance, My Mr_NO was Char(11) I modify MR_No to Char(13)My Table Structure now is:

MR_No Char(13),
Mr_Date Date

My Previous data is MR_NO=APN00209085

I want to add two 00 after alter the table I want my result data like APN0000209085.I am updating through this command

update PR set Substr(MR_No,4,2)='00'

ERROR at line 1: ORA-00927: missing equal sign

Result I want is APN0000209085

View 13 Replies View Related

SQL & PL/SQL :: Alter Queue Table Column?

Mar 23, 2011

I need to Modify the column(MSGID) data type from RAW to BLOB for a Queue Table, I'm getting the following error.

BANNER
---------------
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE11.2.0.2.0Production

YUV >

YUV > DESC PDA_REPORT_MESSAGE_QTAB
Name Null?Type
----------------------------------------------------- -------- ------------------------------------
Q_NAME VARCHAR2(30)
MSGID NOT NULL RAW(16)

YUV >
YUV >
YUV > ALTER TABLE PDA_REPORT_MESSAGE_QTAB MODIFY (MSGID BLOB);
ALTER TABLE PDA_REPORT_MESSAGE_QTAB MODIFY (MSGID BLOB)
*
ERROR at line 1:
ORA-22858: invalid alteration of datatype

YUV >
YUV >
YUV >
YUV >
YUV > ALTER TABLE PDA_REPORT_MESSAGE_QTAB ADD (MSGID_NEW BLOB);
ALTER TABLE PDA_REPORT_MESSAGE_QTAB ADD (MSGID_NEW BLOB)
*
ERROR at line 1:
ORA-24005: Inappropriate utilities used to perform DDL on AQ table PDADBA.PDA_REPORT_MESSAGE_QTAB
YUV >

View 10 Replies View Related







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