I got the error ORA-01653: unable to extend table <OWNER.TABLE_NAME> by <BYTE> in tablespace <TABLESPACE> in my production database. But I could see 4GB of free space available in the tablespace. But this was resolved after increasing the Tablespace size by 1 more GB. So I wanted to know how I can I reclaim the 4GB space ?
While searching in internet I got few tips like there will be a fragmentation in the tablespace, the free space available in the tablespace may not be a continuous block. To avoid this we need to reorganize the tables using ALTER TABLE <TABLE_NAME> MOVE <TABLESPACE>; command.
But in my tablespace there are huge number of tables exists I cannot do reorganization of all the tables. So I need to know how to identify particularly what are the tables has more fragmentation? so that I can go for reorganizing those tables only.
My Database version in 9.2.0.7
Tablespaces are Locally Managed
Oracle DB version : 10.2.0.5.0 OS version HP-UX B.11.31 U ia64 DB size : 2TB
We have the above configuration for one of our oracle database. One of our DBA acts like he is only the person on this earth who is managing the process of fragmentation removal from the tablespaces in order to improve the performance and wastage of the space. He performs that task at weekends and takes one-off day extra. I am not sure how the fragmentation removal improves the performance and deallocates the space.
Is it compulsory to perform the rebuild process weekly in order to remove fragmented space from tablespaces? Do we have any other method to automatically re-organize objects occupying waste space?
Why the reuild of indexes using separate tablespace improves performance? is there any specifi reason for it?
First one : ----------------- 1)Created a backup table from the Fragmented table (This table is a partitioned one). 2)Analyzed this table. 3)DROP the Fragmented table 4)Inserted the backuped up data from backup table to the Re-created table. 5)Analyze this table.
Second method ------------------- 1) Create a backup table newly, with PCTFREE =0 2) Inserted the data from Fragmented table ( This is a partitioned table) to backup table 3) Analyzed this table. 4) Truncate Fragmented table 4) Did Exchange partition of Fragmented table with Backup table.
The second method is not found to be removing the Fragmentation. Before the fragmentation was 28% after Second method the fragmentation is still the same. While the first method the fragmentation reduced to 16%.
Query used to find Fragmentation. select table_name, round((blocks*8),2) "table size kb", round((num_rows*avg_row_len/1024),2) "actual data in table kb", round((blocks*8),2)- round((num_rows*avg_row_len/1024),2) "wasted space kb",
I have tried below steps for removing the table fregmentation but for some table i am not getting good result here.
1. It will collect the data which are having more than 100MB fragmentation.
select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE", round((num_rows*avg_row_len /1024/1024),2)||'Mb' "ACTUAL_SIZE", round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE" from dba_tables where owner in('a','b','c','d') and round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) > 100 order by 8 desc;
2. then move the object(table) to the same tablespace.
alter table abc move; alter table bcd move; alter table efg move;
3. also rebuild the dependent objects.
alter index abc_PK rebuild online;
4. Then analyze the table which are having more than 100MB of fragmentation.
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';
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?
Does "Update on a Partitioned Column" cause fragmentation ?
See example below :
Suppose I have a table as below that has approx 3 million rows and growing :
1) Table ABC : file_id number status_flag varchar2(1) file_Content clob date_created date
2) This table is list partitioned on Column "status_flag".
Column "status_flag" can take 3 values "A" or "B" or "C"
3) A file_id can transition from 1 "Status_flag" to another. [ A--> B , C--> B, B-->C, A --> C, etc ]
This means its possible to UPDATE the "status_flag " - the partitioned column.
Question : ------------- 1) Would the movement of rows from 1 partition to another cause fragmentation ? 2) Or Instead, should this be achieved by maintaining 3 tables - 1 each for every "status_Flag" as Table_A, Table_B, Table_C
This would mean that, if file_id 1, changes "status_flag" from 'A' to 'B' : -- The corresponding row from Table_A will be created in Table_B --- The same will be deleted from Table_A
This would still cause fragmentation ... with the overhead of Inserting a CLOB column from 1 table to another.
3) How to determine how much percent of the table is fragmented ?
I have a tablespace of size 512 GB. On the basis of tables and indexes created on it, the space consumed should be 319GB but when I am retrieving the free space size , I am getting only 124GB of free space. That means around 70GB of space is missing.
I want to do horizontal fragmentation of a table say employee. But fragmentation is often related to distributed databases. So i want to ask how can i do this on my single home computer? list out the steps that should be performed. I saw the concept of link but really failed to understand that.
We are using a GTT table to store the summarize data and display it on same screen(10g 10.2.0.5). 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: INSERT INTO DT_CA_STNDALN_DETAILS_TMP (ORG_ID,BA_PRODTYPE_ID,MAX TENOR,GROSSLIMIT,GROSS_UTILISATION,HAS_MDR,HAS_CLUSTER,SIGN_IN D,GROSS_AVAILABILITY,COLLATERAL,NET_UTILISATION,DT_CA_STNDALN_DE TAILS_TMP_VER,DM_LSTUPDDT,NET_AVAILABILITY) VALUES (:1,:2,:3,:4, :5,:6,:7,:8,:9,:10,:11,:12,sysdate,:13) ;
I need to check the exact amount of space used (in bytes or MB) by a table which is having a BLOB column.I tried the following query but it is not giving the proper usage.
select segment_name , sum(bytes)from dba_extentswhere segment_type='TABLE'and segment_name in ('TEST_CLOB','TEST_BLOB','TEST_CLOB_ADV','TEST_BLOB_ADV') group by segment_name; I even tried the following stored procedure create or replace procedure sp_get_table_size (p_table_name varchar2)as l_segment_name varchar2(30); l_segment_size_blocks number; l_segment_size_bytes number; l_used_blocks number; l_used_bytes number; l_expired_blocks number; l_expired_bytes number; l_unexpired_blocks number; l_unexpired_bytes number; begin select [code].......
But it is giving the error
Error starting at line 298 in command:exec sp_get_table_size ('TEST_CLOB_ADV')Error report:ORA-03213: Invalid Lob Segment Name for DBMS_SPACE packageORA-06512: at "SYS.DBMS_SPACE", line 210ORA-06512: at "SYS.SP_GET_TABLE_SIZE", line 20ORA-06512: at line 103213. 00000 - "Invalid Lob Segment Name for DBMS_SPACE package"*Cause: The Lob Segment specified in the DBMS_SPACE operation does not exist.
*Action: Fix the Segment Specification Although the LOB section is specified in create table syntax.
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?
I am using Oracle 10.2.0.3. Since yesterday i am seeing a session with sid 1160 using undo tablespace but not able to find how much it is using .I need to know which session and from which module and how much is the Undo being used by those sessions. I have tried searching but all the queries provide me with some different results each time.
Also i need the same information for REDO being generated .
while trying to refresh an materialized view.. oracle throws cannot extend temp table space error.. while starting to refresh mivew temp table space is empty but once refresh started temp tablespace is growing and throws cannot extend temp tablespace error,,,size of temp tablesapce is 200GB..when i monter the session it does an sort event of an table(ammt_pol_ag_comm).. only 4% of this sort event is completing after that it throws error bu occupying the entire 200 GB tabespace.. MView script below..
CREATE materialized VIEW ammv_agent_pol_persis_emas NoLogging Parallel 10 Build Immediate Refresh on demand With Primary Key AS
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.
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.
I have a question about database fragmentation.I know that fragmentation can reduce performance in query times. The blocks are distributed in many extents and scans process takes a long time. Oracle engine have to locate the address of the next extent..
I want to know if there is any system view in which you can check if your table or index has high fragmentation. If it's needed I will have to re-create, move or rebulid the table or index, but before I want to know if the degree of fragmentation is high.
Any useful script or query to do this, any interesting oracle system view?
