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


ADVERTISEMENT

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

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

Server Administration :: Shrink Tablespace

Jun 7, 2010

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

Server Administration :: Shrink Tablespace Of 100gb Which Has Objects

Jul 6, 2010

i 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

View 2 Replies View Related

Server Administration :: Unable To Calculate Datafile Shrink Possible Size

Jul 28, 2011

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 ?

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

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

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

Server Administration :: Move Partitioned Table Between Table Spaces Of Different Block Size?

Apr 4, 2011

I was about to move some tables from one table space to another but it seems it is not possible to move partitioned tables between table spaces of different block sizes.

So far the only option I have is to export and then import back the data.

know if there is any way to move a partitioned table between table spaces of different block size?

View 14 Replies View Related

Server Administration :: Reorganize A Table And Index After The Deletion Of Records From Table?

Feb 7, 2012

We deleted millions of records from a table.

1.Is it necessary to reorganize a table and index after the deletion of records from table ? Because i see some change in table size after table and index reorganization.

2.Will re org table and index improve the database performance ?

View 7 Replies View Related

Server Administration :: How To Compress Table

Sep 8, 2011

There is a huge table,i want to compress it,how can i do? alter table tb_my_table compress After executed the sql,the size of table have any change.

View 2 Replies View Related

Shrink Tables - HWM?

Aug 22, 2012

if 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

View 1 Replies View Related

How To Shrink A Datafile

Feb 13, 2013

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.

View 8 Replies View Related

Server Administration :: Collect Table Stats?

Oct 10, 2013

We are running 11g (11.2.0.3)We have a "working table" that is empty at the beginning of the day.Then we start adding rows (insert) with a key column called STATE with a value of 100.At the same time, there are other apps that pickup data in state 100 , process that data and change that state to 200 or 300.There is also another app that pickup data in state 200 , process that data and change that state to 300 or 400.

So in summary, the data on that table is at the beginning empty, then all the rows are in state 100, they slowly move to different states (200, 300, etc) and by the end of the day, they are all in 400.

My question is what would be the best way to collect stats on this table?

I was thinking to create an hourly job to collect stats on that table:
exec dbms_stats.gather_table_stats (
ownname => 'SCOTT',
tabname => 'WORK_T

View 6 Replies View Related

Server Administration :: Default Partition In Table?

Oct 17, 2011

How can i check whether a partition tbale have default partition?

View 9 Replies View Related

Server Administration :: Rename A Partitioned Table?

Mar 21, 2007

i want to rename a table that has partitions.

alter table
testora.oldtablename
rename to
testora.newtablename;

ORA-14048: a partition maintenance operation may not be combined with other operations

View 2 Replies View Related

Server Administration :: How To Apply Composite Key On A Table

Jul 21, 2011

How can i apply composite key on a table?

View 2 Replies View Related

Server Administration :: How To Find Updated Table

Mar 9, 2012

how to find a table is updated and when the table is updated.

View 1 Replies View Related

Server Administration :: DDL Used To Create A Table That Is Partitioned By Day

Nov 4, 2010

DDL used to create a table that is partitioned by day, then rolled up to a month using the interval partitioning technique.

View 3 Replies View Related

Server Administration :: How To Reduce Size Of A Table

Oct 2, 2012

One of our solaris machines is running Oracle 8.0.3

A table reached the 2 Gb size and oracle failed due to the operating system file size limitation.

The information in the table is not relevant and can be deleted, but the table contains a lot of indexes.

I would like to know the best procedure to delete the information and reduce the size of the file.

View 3 Replies View Related

Server Administration :: Table Statistics For Oracle 11g

Oct 25, 2012

I want to get the stale stats for Table resides at APPS schema. Is there is any table or view exists to get the details like DBA_STALE_STATS or anything? Currently I am checking LAST_ANALYZED column from DBA_TABLES?

View 2 Replies View Related

Server Administration :: How To Track The Last Access To A Table

Oct 11, 2011

is it possible to track the last access to a table?

View 2 Replies View Related

Server Administration :: How Table Owner Is Changed

Mar 21, 2012

I got query to investigate, how table owner is changed.

SQL> conn test/test
Connected.
SQL> create table guri(name varchar2(9));

Table created.

SQL> select table_name from user_tables;

no rows selected
SQL> conn /as sysdba
Connected.
SQL> select owner,object_name,object_type from dba_objects where object_name = 'GURI';
OWNER OBJECT_NAME OBJECT_TYPE
---------- ----------- ------------
CISREPLICA GURI TABLE

View 8 Replies View Related

Server Administration :: When To Gather Table Statistics

Apr 21, 2012

Why do we gather table statistics manually ?Is it because of database performance.

I know In Oracle Database 10g, Automatic Optimizer Statistics Collection reduces the likelihood of poorly performing SQL statements due to stale or invalid statistics and enhances SQL execution performance by providing optimal input to the query optimizer.

Optimizer gathers statistics when 10% table rows have been changed.

View 9 Replies View Related

Server Administration :: How To See Whether Table Data Using Compression

Mar 13, 2013

1)i have 2 SWP TABLES. while dropping a column, i am getting error -
ORA-39726: unsupported add/drop column operation on compressed tables.

2) when i checked compression status, those were not compressed. But as per our code standard, SWP tables should not be in compress mode.

OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
NOVAR PAYMENT_SWP DISABLED

OWNER TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------
NOVAR PREPAYMENT_SWP DISABLED

3) as a workaround, i compressed these 2 SWP tables with OLTP option, and then i was able to drop the column from these 2 SWP tables.

4) Below statement is correct or not ?
IF A TABLE USING BLOCK LEVEL COMPRESSION, THEN this error will come - ORA-39726: unsupported add/drop column operation on compressed tables.

if above statement is correct, then how to find out whether table data is using block level compression ?

5) we have DBMS_COMPRESSION.GET_COMPRESSION_TYPE. using this i just tried to find out, but i am getting "1" as output. I am not getting the exact meaning of it.

confirm what is the conclusion on this ?

SQL> declare
rid rowid;
n number;
begin
select max(rowid) into rid from NOVAR.PAYMENT_SWP;
n := dbms_compression.get_compression_type('NOVAR','PAYMENT_SWP',rid);
dbms_output.put_line(n);
end;
/
2 3 4 5 6 7 8 9 1

PL/SQL procedure successfully completed.

SQL>
SQL> SET SERVEROUTPUT ON
SQL> /
1

PL/SQL procedure successfully completed.

SQL> SELECT max(rowid) from NOVAR.PAYMENT_SWP;

MAX(ROWID)
------------------
AAsz4fAHSAAAD3IABs

(ii) 2nd table

SQL> set serveroutput on
SQL> declare
rid rowid;
n number;
begin
select max(rowid) into rid from NOVAR.PREPAYMENT_SWP;
n := dbms_compression.get_compression_type('NOVAR','PREPAYMENT_SWP',rid);
dbms_output.put_line(n);
end;
2 3 4 5 6 7 8 9
10 /
1

PL/SQL procedure successfully completed.

SQL> SELECT max(rowid) from NOVAR.INVOICELINE_SWP;

MAX(ROWID)
------------------
AAsz4ZAEkAAAp8XAAA

View 3 Replies View Related

Server Administration :: Size Of Data Of Table

Oct 20, 2011

ops$tkyte@DEV8I.WORLD> select blocks, empty_blocks,
2 avg_space, num_freelist_blocks
3 from user_tables
4 where table_name = 'T'
5 /

BLOCKS EMPTY_BLOCKS AVG_SPACE NUM_FREELIST_BLOCKS
---------- ------------ ---------- -------------------
19 35 2810 3

Ok, the above shows us:

- we have 55 blocks allocated to the table (still)
- 35 blocks are totally empty (above the HWM)
- 19 blocks contains data (the other block is used by the system)
- we have an average of about 2.8k free on each block used.

Therefore, our table

- consumes 19 blocks of storage in total.
- of which 19 blocks * 8k blocksize - 19 block * 2.8k free = 98k is used for our data.

not too sure this calculation is accurate for getting the size (data)of the table.

View 32 Replies View Related

Server Administration :: Enabling Primary Key On A Big Table

Jun 22, 2012

I have a partioned table that has close to 2 billion rows and a PK of all columns. Becuase of time constrains my APP team wants the PK disabled while they pump into hundreds of thousands of rows with a batch process.

Now I am finding when I enable the PK its eating up close to close to 200GB of temp space.

Is there something I can do to reduce the amount of temp space being used?

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

What Influence PCTFREE On Shrink

Oct 2, 2013

What Influence PCTFREE on Shrink?.

View 1 Replies View Related







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