Server Administration :: Monitor Increment Of Space Of Tablespace Based On Table?

Jun 9, 2011

How to check for the increment of a space of the tablespace based on the particular table. (i.e.) Say a scenario, if am trying to load the data for a particular table, for first I loaded some 10000 records and then again loading 50000 records ,so based on the icrement of the reocrds the tablespace size also increases gradually . so for this scenario how to monitor the increment of the space.

View 13 Replies


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 :: 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 :: Verify Tablespace Space

Jan 9, 2012

Application team requested hosting team to add some space to tablespace as it was exceeding 80% used.Now the hosting team have added the space as per recommendation and the application team wants us to verify if the space was added. How to check the space was added in GB to list of tablesapces ?

View 5 Replies View Related

Server Administration :: Pre-allocate Space For Temporary Tablespace?

Jan 4, 2012

I just want to know whether we have to pre-allocate space for temporary tablespaces?

View 1 Replies View Related

Server Administration :: Added Lots Of Space To A Tablespace

Sep 6, 2011

Mistakenly I added lot of datafiles with autextend on option.. I realized later and then tried to resize the datafiles to a minumum space but got the below error

ORA-03214: File Size specified is smaller than minimum required.

How to resolve this problem to reclain the space back?

View 9 Replies View Related

Server Administration :: Any Relation Of Tablespace Disk Space And Actual Data Present In Tables

Aug 16, 2012

We are using Oracle 10g and have 10 tablespaces defined for our Database which have 108 tables. Size of 108 tables is around 251 MB as seen during importing the dump. While creating these 10 tablespaces I used below parameters for allocation of space


which set the initial space for 10 tablespaces to around 1032Kb each. Now my Question is after importing the dump , how the disk space for 10 tablespaces increases to 398 MB in total ?

Is there any relation of Tablespace disk space and Actual Data present in the tables ?

View 18 Replies View Related

Server Administration :: System And Users Table Space

Oct 15, 2011

a newbie dba here..

sum(BYTES) Total_free_space,
max(BYTES) largest_free_extent
from dba_free_space

result output is attached .txt file.

the SYSTEM & USERS table space shows only <10 mb free space. Is it a bad sign? What I should do ?

View 7 Replies View Related

Server Administration :: Table Is Consuming Unusual Amount Of Space?

Nov 21, 2011

I have table which has 240 columns.

Here is the list of column data type.



The table has 64 partition.. When i count the table(select count(*) from table), i see close to 22 million records.

SQL> select (num_rows*avg_row_len)/1024/1024/1024 GB,num_rows,avg_row_len
2 from dba_tables
3 where table_name='TRX_TAB';
---------- ---------- -----------
74.9393936 21871585 3679

It is supposed to take 75 GB. But this table is consuming 135 GB now. It is occupying 8 GB per day.

View 4 Replies View Related

How To Monitor Space Of Database In RAC And NON-RAC

Nov 17, 2012

I need to monitor the Database daily, so i need to check whether the size of the DB is increasing @ a slow rate or rather than that. I need to do it in RAC & NON-RAC.

View 3 Replies View Related

Server Administration :: Monitor PGA Utilization?

Dec 28, 2011

I have a requirement to monitor the PGA utilization with time to time. Provide a good script which will effectively show the pga utilization.

View 2 Replies View Related

PL/SQL :: Temp Tablespace Space Due To GTT Table

Sep 3, 2012

We are using a GTT table to store the summarize data and display it on same screen(10g Now we are facing temporary tablespace space issue very frequently as our client do not enable the auto extend on for temp table space. We have analyzed the AWR and came to know that there are 900000 inserts per hour on an average. Client DBA Claims that there are sessions(1or 2) which inserts the data in temporary table continuously for 2-3 days.

According to him one session is running from 28th Aug and problem comes on 2nd Sep and after killing the problematic session the application will work fine. Generally this problems come on weekend. I have discussed with our dev team and as per them there is no session leakage issue.

following is the insert statement:
:5,:6,:7,:8,:9,:10,:11,:12,sysdate,:13) ;

View 9 Replies View Related

Server Administration :: Change Tablespace Of A Table Using Dbms_redefinition

Aug 10, 2011

Does dbms_redefinition can move the tablespace to another of a table,including indexes?

View 4 Replies View Related

Server Administration :: How To Monitor Instance Status And Log Switch Summary

Apr 27, 2012

how can i monitor the instance status and log switch summary.

View 8 Replies View Related

SQL & PL/SQL :: Increment Sequence Not Based On Calendar Date?

Sep 24, 2013

i need to be increasing the sequence no by 1 for every calender date.For example lets say if i receive 5 dumps of data for 24/09/2013 it should be as below. For next day the 25th the sequence no should again begin with 1.

24/09/2013 1
24/09/2013 2
24/09/2013 3
24/09/2013 4
24/09/2013 5

25/09/2013 1

View 6 Replies View Related

XML DB :: Increment Counter In Loop Based On Condition?

Jan 24, 2013

I want to increment a counter in a loop based on a condition.

Here is how my xml looks like

<resultset id=1>


I need to look at each and every resultset and check if the value is >400 and if it is then display some text. Something like this

Yes, there is a value greater than 400

In my XSL, I declared a variable called count with value 0. I created a for-loop which goes through these results. Then inside the loop there is a condition to cehck if the value is > 400. If the value is >400, then the counter is incremented.

<xsl:variable name="Count" select="0"></xsl:variable>
<xsl:for-each select="/results/resultset">


There is a problem with the above code is that result is like this

Yes there is a resultset with greater than 400

Is there a way I can just remove the character 1 from the output? Or is there a better way to increment?

View 3 Replies View Related

Server Administration :: Location Of Table In Tablespace / Data File

Jul 6, 2010

I was wondering if there is any way to know in which Tablespace and Datafile my Table is located. I have exported a table and about to delete it as i am partitioning it.

View 9 Replies View Related

How To Monitor ALTER TABLESPACE Ddl Change

Jan 8, 2013

I can use trigger to monitor ALTER TABLESPACE ddl statement against a particular tablespace in a schema.


I think, line no.2 isn't a valid statement. Or can I use AUDIT instead - in fact, I want to monitor/audit only ALTER TABLESPACE ddl statement and that too for a particular tablespace only.

View 3 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 :: Calculate Used Space In A Block

Nov 17, 2011

Is there any way I can calculate percentage of space used in a block.Eg if a table size is 100 blocks,How Can I check the percentage of used space in block.

View 6 Replies View Related

Server Administration :: How To Know Which Block Contains Free Space

Feb 26, 2013

for some reason,i want to know which data block contains free space,or which table/index contains free space.

View 6 Replies View Related

Server Administration :: Segment Space Management?

Oct 13, 2010

am using Oracle on win 2008 server SP2. I would like to know if we can set the Segment Space Management feature to AUTO for RBS and Temporary tablespaces. As the data is not permanent in these tablespaces, will it manage automatically?

Presently its Segment Space Mgmt is manual for System, RBS, Temporary tablespaces.

View 4 Replies View Related

Server Administration :: Managing Space In Drive E

Oct 27, 2010

In my drive E: I'll have a space issue soon because one file is taking all space. SYSTEM01. DBF size is around 25GB. I want to know if I delete some data in my database I'll gain space.

View 2 Replies View Related

Server Administration :: How To Reclaim Wasted Space Thoroughly

Mar 26, 2013

In one of our Data warehousing DB, even though, all the tablespaces' space should keep on at least 1 month, but our leader want us to estimate how much space can free up with db method.

I have referenced

<Administrator Guide> - Reclaiming Wasted Space


I have several questions on reclaim space:

1. It seems that segment adviser give a really cool view to know which segment should be shrink and how much size will free up after shrink. But actually, this need a very a job or manually do this. I have once heard about some query from can estimate this :

A script from MOS, but actually I found this it's not very accurate with segment adviser. This script should report the real space the table occupy, but after shrink space, the space doesn't free up.

SELECT TABLE_NAME , (BLOCKS *8192 / 1024/1024 ) - (NUM_ROWS*AVG_ROW_LEN/1024/1024)
"Data lower than HWM in MB" FROM DBA_TABLES WHERE UPPER(owner) =UPPER('&OWNER') order by 2 desc;

Also there is a script evaluate the tablespace fragments from some people:

SELECT tablespace_name,
SQRT (MAX (blocks) / SUM (blocks))
* (100 / SQRT (SQRT (COUNT (blocks))))
FROM dba_free_space
GROUP BY tablespace_name

if the value is very low, we can coalesce the tablespace. But after I coalesce the tablespace, I can't see any space free up.

From <Concepts>:
Quote:Coalescing extents is not necessary in
locally managed tablespaces, because all contiguous free space is available for
allocation to a new extent regardless of whether it was reclaimed from one or more

2. is there any good way to estimate how much space we should free up? ( After free up the space , dba_free_space should see this result)

View 12 Replies View Related

Server Administration :: How To Delete Flashback Log And Release Space

Feb 15, 2012

How to delete flashback log and to release the space?

I guess that it maybe like archive log ,it can release the space using RMAN.But when i try a test ,it fail.


View 6 Replies View Related

Server Administration :: Free Space In Standby Database?

Jul 23, 2011

How do we find the free space in tablespaces in a standby database

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

Server Administration :: Segment Space Management Is Auto?

Oct 18, 2010

the syntax to make a tablespace's segment space mnagement auto which is segement management is *manual* and exent management is *local autoallocate*.

View 2 Replies View Related

Copyrights 2005-15, All rights reserved