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 RepliesI have a tablespace with 5GB size, data in the tablespace is 4.8GB. if i want to shrink the tablespace then much size could be shrinked.
View 4 Replies View Relatedi am trying to shrink tablespace of 100gb which has objects.
i tried coalesce,then i try to shrink and also tried to resize the datafile with no luck
error message can't resize
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?
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.
Aim was to shrink datafile and got "ORA-03297: file contains used data beyond requested RESIZE value". Datafile size 7GB but used 128MB.
So created new Tablespace and moved all tables and index partitions to new TS. Now I see some entries in USER_INDEXES, which I need to move to new TS. But they are IOT's.
INDEX_NAMEINDEX_TYPETABLE_OWNERTABLE_NAME
SYS_IOT_TOP_1082946IOT - TOPDSI_DPIPESYS_IOT_TRNS_1082943
SYS_IOT_TOP_1082949IOT - TOPDSI_DPIPESYS_IOT_TRNS_162982
how to move these indexes to new TS.
Is there a way to find the tablespace used by an index?
View 4 Replies View RelatedI have created queue table using below script
BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE_TABLE (
QUEUE_TABLE => 'scott.NG_MSG_QUEUE_TBL',
QUEUE_PAYLOAD_TYPE => 'SYS.AQ$_JMS_TEXT_MESSAGE',
COMPATIBLE => '10.0.0',
[code].......
Indexes
--------
INDEX_NAME TABLESPACE_NAME
------------------------------ --------------------------
AQ$_NG_MSG_QUEUE_TBL_T MEDIUM_DATA
AQ$_NG_MSG_QUEUE_TBL_I MEDIUM_DATA
SYS_IL0001100359C00037$$ MEDIUM_DATA
SYS_IL0001100359C00040$$ MEDIUM_DATA
SYS_IL0001100359C00041$$ MEDIUM_DATA
SYS_C0073180 MEDIUM_DATA
I want all the index to be created in different tablespace like 'medium_index. can we specify tablespace for index when we are creating queue table.
I have already created large no of indexes on my Database without specifying specific tablespace, now I would like to move all the created indexes into particular tablespace.
View 3 Replies View RelatedHow to saperate inndexes and tables in two different tablespcae.......???
View 2 Replies View RelatedIn our production database server Index tablespace is not growing now... On average index tablespace increases 200 MB to 250 MB per day. Last two weeks index tablespace does not increase..
I am giving you all a statistics of our production db Tablespace usage..
Date Globusdataxml(MB)Globusindexxml(MB)
23-4-2012 26321025 1581.94
24-4-2012 24633.31 1584.25
25-4-2012 23410.88 1576.25
26-4-2012 22186.19 1573.25
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
I need to move my indexes (around 300) from data tablespace to index tablespace. What's the best way to do it?
View 9 Replies View Relatedif the command is successful:>alter table my_table shrink;The segment will be defragmented and the High Water Mark will be moved.But what is the importance of the HWM?
Whats the difference between commands?
>alter table my_table shrink; -- move HWM
>alter table my_table shrink compact; -- not move HWV
Database Version :11gR2
I have a tablespace which has around 32gb space consumed. But if i check the used space then its only 16GB. When i tried to resize the datafile it throws the error
ORA-03297: file contains used data beyond requested RESIZE valueAs per my understanding there are not continous blocks which are there in datafile due to fragmentation may be and there by not able to resize it. If i export the tablespace using datapump and reimport this will release the space.
But i want to know if there are any alternative ways to do the same.
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
What Influence PCTFREE on Shrink?.
View 1 Replies View Related 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]...
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.
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.
What is different between move table and shrink table?
View 2 Replies View RelatedI am trying to discern the difference between Shrink and Move and their impact on the High Water Mark of a table.
My understanding is that MOVE in effect rewrites every row of a table ( hence why it can deal with row chaining ) whereas SHRINK basically moves existing rows in a table 'down' the table into any available free space. This is why MOVE takes a table lock whereas SHRINK takes a row lock. What I am trying to discern is - does MOVE and SHRINK effect the high water mark and does both reallocate space and give it back to free space for the tablespace ? I believe MOVE does reduce the HWM and give freed space back to the tablespace. I am not so sure about SHRINK.
I need to resize my datafile as i have allocated more space and need to reduce ( i.e.data load completed now). my tablespace is having 11.74 gb free space now. it has 3 datafile.
TABLESPACE TOTAL USED FREE PCT_FREE LARGEST FRAGMENTS
------------------------ ---------- ---------- ---------- ---------- ---------- ----------
CFC_DATA 150528 138780.6 11747.4 7.80412946 1251 992
TABLESPACE_NAME FILE_ID FILE_NAME Size(MB)
------------------ ---------- ------------------------------------------------------- ----------
CFC_DATA 71 +DATA/dedw/datafile/cfc_data.4074.731085435 65535.9688
CFC_DATA 334 +DATA/dedw/datafile/cfc_data.4473.757566557 20480
CFC_DATA 1710 +DATA/dedw/datafile/cfc_data.2012.728095695 64512I used below script to find out HWM in order to resize the datafile.
db_block_size is 16KB.
[code]....
in TOAD, we have an option, that is "Minimum size" button against each datafile.. Need the SQL which is running behind when we press this button from TOAD ?
How to import dump into specific tablespace instead of default tablespace users.
I want to import my dump file to newly created tablespace ,so how can i do that . I have created new user called cvm and while creating it i mentioned default tablespace to newly created tablespace . But when i try to import my dumo file it goes to users tablespace .
i have a tablespace which contains 121 datafile(max limit reached) as a dba what we have to do?
creating a new tablespace with a datafile and assign the users to the current tablespace which i created now.iif the above process is correct,after some time the tablespace which was filled up got freed up.now can i give the access to the users previous (i.e. freed up tablespace) and current tablespaces
My database version is
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
My os version is
Linux damdat01 2.6.18-128.7.1.el5 #1 SMP Wed
Aug 19 04:00:49 EDT 2009 x86_64 x86_64 x86_64
GNU/Linux
My database is OLP system.
My question is what are the advantages and disadvantages having one single tablespace versus multiple tablespace?
Easy to maintain when you have single tablespace. but hard to track the IO issues if you have one single tablespace.
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.
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 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 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.