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


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


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

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 :: 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 ( load completed now). my tablespace is having 11.74 gb free space now. it has 3 datafile.

------------------------ ---------- ---------- ---------- ---------- ---------- ----------
CFC_DATA 150528 138780.6 11747.4 7.80412946 1251 992

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

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

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

Server Administration :: Creating A New Tablespace With Datafile / Assign Users To Current Tablespace

May 27, 2011

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 can i give the access to the users previous (i.e. freed up tablespace) and current tablespaces

View 9 Replies View Related

Server Administration :: Single Big Tablespace Versus Multiple Tablespace?

Jan 26, 2011

My database version is

Oracle Database 11g Enterprise Edition Release - 64bit Production
PL/SQL Release - Production
CORE Production
TNS for Linux: Version - Production
NLSRTL Version - 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

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.

View 7 Replies View Related

Server Administration :: Convert Dictionary Managed Tablespace To Locally Managed Tablespace?

Jan 6, 2012

I have one database which is recently upgraded from oracle 8.1.5 to oracle database is having around 300 tablespace and total size of the database is 1.5 TB.

The database was created in oracle 8i and all the teblespace were DMT(Dictionary Managed Tablespace) .Usually after up gradation all the tablespace are in DMT mode. Now my requirement is to convert all the tablespace into LMT (Locally Managed Tablespace) so that I can AVAIL ALL THE FEATURES OF LMT.

This database is a mission critical database and very less downtime can be allowed.

View 6 Replies View Related

Server Administration :: Create Tablespace For Administration

Nov 29, 2010

i'm a student currently learning database administration security.

I need to create a tablespace for administration of database but i don't know what datafile settings are best suited for admin usage.

I have attached the schema that was given to me for this assignment.

View 12 Replies View Related

Server Administration :: System TableSpace Is So Big?

Sep 26, 2011

Today I see my system tablespace is 98%, 480 MB used. Why the system tablespace is so big?

View 5 Replies View Related

Server Administration :: Tablespace On Certain Workstations

Mar 5, 2012

I am NOT a db admin but I do 'maintain' my application that runs with oracle 8i. I am now getting the error on certain workstations:

ORA-03232: unable to allocate an extent of 16 blocks from tablespace 3

I have access to DBA Studio and all the tables in the STORAGE tab show no more than 65% usage

View 7 Replies View Related

Server Administration :: Tablespace Creation

Jun 24, 2012

Problem Description: There are five range partitioned tables which are partitioned by date,designed by previous DBA.Four tables are in one tablespace and one more table is in another tablespace.For every three months there are two tablespaces are created autamatically in new names for future dates.There is no scheduled job for this.How they are created?.This is our question.

View 3 Replies View Related

Server Administration :: Max Size Of Tablespace

Jan 4, 2010

I have one tablespace PSINDEX with Maxsize of 6 GB. But when I query the tablespace its showing the BYTES is greater than MAXBYTES.

View 5 Replies View Related

Server Administration :: System Tablespace Had Been Used More?

May 4, 2012

In my database ,the size of the system tablespace is i found its free space is 477.875M.

--------------- -------
SYSTEM 477.875

In system tablespace ,the max size segment is IDL_UB1$,it is 167M

------------- -----
IDL_UB1$ 167M

QUESTION: what's the table IDL_UB1$ used for ?

View 4 Replies View Related

Server Administration :: Alter Tablespace

Jan 4, 2011

My tablespace contains two datafiles dfile1.dbf and dfile2.dbf on D drive of my filesystem. Now i copied these dbf files on pasted it on a location in E drive. Now i want my tablespace to use dbf files pasted in new location.

Will i have to Alter Tablespace Add Datafile with Reuse clause

View 2 Replies View Related

Server Administration :: RBS Tablespace Is Not Releasing Space

Aug 27, 2012

In My database rollback segment space is not releasing space even though, there is no transaction is using RBS. RBS tablespace size is around 70GB. Unfortunately still our environment is running in 9i due to application code

SQL> show parameter undo

------------------------------------ ----------- ------------------------------
undo_management string MANUAL
undo_retention integer 1800
undo_suppress_errors boolean FALSE

View 15 Replies View Related

Server Administration :: Locally Managed Tablespace

Feb 26, 2010

For a locally managed tabelspace, the dropped segment is changed to a Temp. Segment, to prevent bitmap from being updated.

View 2 Replies View Related

Server Administration :: How To Query A Tablespace Being Used By Which Object

Jul 6, 2013

My Oracle db have this tablespace named INDX which is 80% full. I tried to query on the table, user using it but there is no one using this tablespace

May I know if there is a query that i can used to find out which dba_object is consuming the 80% of this tablespace.

View 5 Replies View Related

Server Administration :: Move Datafile To Another Tablespace

Feb 16, 2012

Erroneously created datafile, re: "/path/../large_rbs_03.dbf" was created under the SYSTEM tablespace which is supposedly be in the LARGE_RBS tablespace.

How do I make the said datafile be under LARGE_RBS?

View 3 Replies View Related

Server Administration :: How To Undo Tablespace Size

Feb 7, 2011

As the undo segments are used in round robin fashion, Is it possible that with varying load (concurrent users, size and number of transactions), the size of Undo tablespace on a particular day is less than the Undo tablespace size few days back, by any chance?

As a basic understanding I know that Undo is preserved for read consistency and transaction, instance recovery So if there are lot of transaction on a database on 05 Feb and before that, but there aren't any transactions on 6,7,8,9, then on 10th Feb can we see the Undo tablespace size is less than that of 05 Feb?

In the following case when data belonging to table is not required for any queries, transactions, even then the undo size is not restored upon dropping the table.

As such for large operations and batch processes shall we keep undo tablespace with files as 'Autoextend' with 'Maxsize' as 'Unlimited'?

SQL> select b.tablespace_name, Total_Kbytes_Available/1024 Tot_Mbytes_Available,
Kbytes_alloc/1024 Mbytes_allocated, kbytes_free/1024 Mbytes_Free_from_allocated,
((Kbytes_alloc - kbytes_free)*100/ Total_Kbytes_Available) Pctused
2 from ( select sum(bytes)/1024 Kbytes_free,
3 tablespace_name
4 from sys.dba_free_space


View 12 Replies View Related

Server Administration :: Increasing Tablespace Size?

Mar 7, 2010

I want to increase the size of the tablespace but when i login as sysdba or admin user i can just see the 21 tables in the dba_tablespaces or user_tablespaces. I want to see the tablespaces related to the application.

View 8 Replies View Related

Server Administration :: Removing A Tablespace And Its Associated Datafiles?

Jul 12, 2010

Due to improper documentations of a certain project, I need to drop a DEFAULT tablespace of a newly created instance including it's associated datafiles by using this command:


The default tablespace name is QWER (qwer01.dbf) and I added 2 datafiles in it, re: OPD_SML01.dbf & EXYT_SML01.dbf.

Do I have to do it online or offline?

View 1 Replies View Related

Server Administration :: How To Resize Temp Tablespace

Jun 30, 2010

i got a error temp tablespace cannot be resized. How to Resize Temp Tablespace?

View 10 Replies View Related

Server Administration :: Creating Tablespace In Raw Partition?

Aug 17, 2012

i want to create tablespace in raw partion on windows.I have added a hard disk and added extended partition to it. Then created 4 logical partitions of 256 MB with diskpart.exe.I have assigned a drive letter to one of them as X:

However, I am having trouble in creating tablespace on it. I referred to [URL]... I get following error,

SQL*Plus: Release Production on Fri Aug 17 15:32:17 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Enter user-name: SYSTEM
Enter password:

Connected to:
Oracle Database 11g Enterprise Edition Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing opti

SQL> create tablespace exampletb datafile '\.X:accounting_1' size 100;
create tablespace exampletb datafile '\.X:accounting_1' size 100

ERROR at line 1:
ORA-03214: File Size specified is smaller than minimum required
SQL> create tablespace exampletb datafile '\.X:accounting_1' size 502;
create tablespace exampletb datafile '\.X:accounting_1' size 502


View 1 Replies View Related

Server Administration :: TEMP Tablespace Estimation

Oct 12, 2011

We have a database running with Oracle on Solaris 5.10 [SUN SPARC v240] where the assigned TEMP tablespace size looks to be quite huge ( = 8GB!). There are not much SQL queries being run on the database, but what can I do/find/investigate to be sure of how much TEMP space is 'actually' required for the application to run OK?

Note. I have seen the previous DBA's have marked AUTOEXTEND = ON for the TEMP datafiles as well.

Separate query (but linked to TEMP tablespace):

Even if the TEMP datafiles are created with AUTOEXTEND = ON, sometimes I am seeing 'ORA-01652: unable to extend temp segment by 128'.

View 4 Replies View Related

Server Administration :: Reducing Tablespace Size?

Jan 24, 2012

we have a tablespace of size 900 GB where 90% of space is occupied by two tables having BLOB data and now i need to drop these two tables and then to recover the space, i need to resize the tablespace (datafiles).

View 3 Replies View Related

Server Administration :: System Tablespace Resize?

Dec 23, 2012

My system tablespace size is 800mb and it sends an alert saying that it has exceeded 80% of its usage.However its total max size is 30gb.

Its autoextensible is set to 'YES'.
2 Node RAC database.
File system is ASM

My question here is... is it advicable to resize a system tablespace to some size when autoextensible is set to 'YES'
or genrally can we resize a system tablespace when autoextensible is set to 'No' ?

SQL> col FILE_NAME format a40
SQL> /
FILE_NAME BYTES/1024/1024 AUT MAXBYTES/1024/1024
---------------------------------------- --------------- --- ------------------
+DATA/ge01114/datafile/system.556.2254454 800 YES 30000.9844

View 4 Replies View Related

Server Administration :: Why Can't SYSTEM Tablespace Extend

Mar 19, 2010

The SYSTEM tablespace refuses to autoextend (3 segments in SYSTEM tablespace unable to extend). It sits on its current size of 559MB (auto set to 2GB, increment 10M).

I guess there are no free extends that are that big as 10M.

View 5 Replies View Related

Copyrights 2005-15, All rights reserved