Fragmentation / Defragmentation Of Space In Database
Jul 31, 2012
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?
View 72 Replies
ADVERTISEMENT
May 4, 2011
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
View 1 Replies
View Related
Sep 1, 2012
What are all the differences between Coalesce and De-fragmentation? When i need to use Coalesce?
View 4 Replies
View Related
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
Jul 1, 2013
I want to replace double space with single space and also remove junk characters from the data. How can I do that?
CREATE TABLE test07013
(
NAME VARCHAR2(50)
);
INSERT INTO VALUES ('WARREN,'); -- REMOVE ","
INSERT INTO VALUES ('CLARK H'); -- REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
INSERT INTO VALUES ('BRYAN A.'); -- REMOVE "."
INSERT INTO VALUES ('CARTER JR. ROBERT'); -- REMOVE "."," AND REPLACE "DOUBLE SPACE" WITH "SINGLE SPACE"
View 8 Replies
View Related
Sep 24, 2012
I have table of 60 gb(indexes 60gb ) and it is subject to fragmentation around 10gb .
I m going to remove fragmentation . As i know i have three options
1.expdp/impdp .
2.CTAS (create table ........as.......) with parallel option .
3.Moving table into another tablespace with parallel option .
I have 4 physical and 4 logical cores(total 8 cores) on server .
View 1 Replies
View Related
May 29, 2012
find out whether the rowmovement option in a partitioned table will cause fragmentation or not?
View 3 Replies
View Related
Jan 5, 2008
I am having an issue with the LOBs in our Database. Our production DB is about 71G right now - if i do export/import using the DataPump - I see the data volume becomes 40G. I am sure its because of the LOBs in my Database. Our application uses lots of LOB datatype and I think the space is not being released.
The challenge is I cant do a full export/import in my Production DB as its a 24/7 system and to do export/import I need min. 5 hrs - which my business will not permit. I can I claim the unused space here. I have used Segment Advisor - but it has only given me 6G of space gain.
Is there any script I can try to use to reclaim space in the database.
View 4 Replies
View Related
Sep 27, 2012
11.1.0.7
Is this the command to check the total size of disk space a database have used.
select sum(bytes) from dba_data_files;
Is the temp size included?
View 11 Replies
View Related
Jul 22, 2011
A query to find the table fragmentation.
View 1 Replies
View Related
Aug 5, 2012
To remove fragmentation which is the best method.
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",
[code]...
View 9 Replies
View Related
Jun 29, 2011
I want to remove white space from oracle database, and i don't want to use trim() or replace(). can i use jdbc driver? if yes, then how?
View 9 Replies
View Related
Apr 11, 2008
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 ?
View 5 Replies
View Related
Mar 18, 2013
i have deleted 20 lak rows from a table which has 30 lak rows,
and i wanted to release the fragmented space, what is the procedure other than exp/imp or alter table move
and also the recommended way to do this prod env... (coalesce /alter move etc.. )
db version is 11.2
View 7 Replies
View Related
Jul 23, 2011
How do we find the free space in tablespaces in a standby database
View 4 Replies
View Related
Oct 3, 2012
Just now sysaux resized to 600m from 250m >>
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';
TABLESPACE_NAME FILE_NAME BYTES/(1024*1024) AUT MAXBYTES/(1024*1024)
SYSAUX /u01/app/oracle/oradata/test/sysaux01.dbf 600 YES 32767.9844
CODEsql> @SCRIPT.SQ
TABLESPACE TOTAL_SPACE(MB) USED_SPACE(MB) FREE_SPACE(MB) % Used % Free
SYSAUX 600 248 352 41.33 58.67
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?
View 9 Replies
View Related
Feb 28, 2012
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.
View 14 Replies
View Related
Dec 29, 2011
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.
exec dbms_stats.gather_table_stats('a','abc');
exec dbms_stats.gather_table_stats('b','bcd');
exec dbms_stats.gather_table_stats('c','cdf');
after that when check the table fragmentation, i am getting the same result, which i have collected from the 1st query.
View 7 Replies
View Related
Mar 27, 2013
Is this the right query to determine index fragmentation ?
SELECT AVG (tfrag)
FROM (SELECT /*+ ORDERED USE_NL(i) INDEX(i DR$TEXT_IDX$X) */
i.token_text,
(1
[code]...
The reason I am asking that before index rebuild it returned 86% but after rebuild (ALTER INDEX .. REBUILD) it returned
96% which does not make sense.
I did try ctx_report.index_stats but it takes more time to run.
View 5 Replies
View Related
Oct 3, 2011
select sum(bytes/1024/1024) from dba_segments where owner='IPPS';
Does the above finding means that IPPS has use up 'this amount' of space in the database from all his objects?
View 7 Replies
View Related
Nov 3, 2010
I am trying to find the space occupied on disk by the tablespaces of the database that contain tables, some (and not all) of whose columns are encrypted. My query is like this:
select distinct a.tablespace_name, file_name, bytes /(1024*1024*1024) File_Size_In_GB
from dba_data_files a, dba_tables b,
(select distinct owner, table_name from DBA_ENCRYPTED_COLUMNS) c
where
a.tablespace_name = b.tablespace_name and
b.owner = c.owner and
b.table_name = c.table_name
order by a.tablespace_name;
The output of the query is as shown in the attached file:
TABLESPACE_NAMEFILE_NAMEFILE_SIZE_IN_GB
DMS_DATAM:ORACLEORADATASPOPRODDMS_DATA_0044.DBF29.296875
DMS_DATAM:ORACLEORADATASPOPRODDMS_DATA_0045.DBF29.296875
DMS_DATAM:ORACLEORADATASPOPRODDMS_DATA_0051.DBF29.296875
DMS_DATAN:ORACLEORADATASPOPRODDMS_DATA_0012.DBF19.53125
[code]...
Since the output (under the heading Total Size of the tablespace) is probably the sum of all the datafiles returned by the query and is obviously incorrect, I have not given the rest of it. I also tried the following:
select distinct a.tablespace_name, file_name, bytes /(1024*1024*1024) File_Size_In_GB,
sum (bytes/(1024*1024*1024))over (partition by a.tablespace_name order by file_name) "Total Size of the tablespace"
from dba_data_files a, dba_tables b,
(select distinct owner, table_name from DBA_ENCRYPTED_COLUMNS) c
where
a.tablespace_name = b.tablespace_name and
b.owner = c.owner and
b.table_name = c.table_name
order by a.tablespace_name ;
[code]...
Here, the fig. under the heading "Total Size of the tablespace" are probably the sum of all the records returned by the query if distinct is not used i.e all the data file sizes returned by the query.
tune my query and get the desired results? I think this can be achieved by group by with rollup, cube, order by and grouping functions, but am not sure how to proceed. I know that I can get the results by using Enterprise Mgr. Console in 2 mins., but would still like to get the results with the queries.
View 11 Replies
View Related
Nov 13, 2010
In OEM10 I can click on the database size in the database page. When I do this I am redirected by the OEM to teh Database space usage report.
I have 3 questions about reports in OEM10g:
1. Can I create drill-down reports in OEM10g?I want to create a report that shows the space usage per host and drill down to the databases (targets) that are on that host.
2.Can I create a link to the Database space usage report? I would like to redirect to the report from the report in question 1 from a target. So that I can see the space usage of the database that was selected.
3. Can I create a copy of the Database Space Usage summary report that the OEM shows? I can't find it in the report tab of the OEM.
View 1 Replies
View Related
Jun 24, 2013
I'm new into Oracle, consider the below scenario and respond it accordingly.
I want to add 500GB data file at primary database side, but doesn't have space at standby database side. My STANDBY_FILE_MANAGEMENT is set to Auto.
View 3 Replies
View Related
Oct 24, 2012
I want to change space allocation for character columns in my database, So it will store them as 'CHAR' and not 'BYTE'.my character set is
SQL> SELECT VALUE FROM V$NLS_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';
VALUE
----------------------------------------------------------------
AL32UTF8
SQL> alter system set NLS_LENGTH_SEMANTICS='CHAR' scope=both;
System altered.I bounced the instance just to make sure
System altered.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Connected to an idle instance.
SQL>
SQL>
SQL> startup
ORACLE instance started.
[code].....
And then I want to see that when I create a table with some varchar2 column,The space for it will be allocated by chars, and not by bytes! However, when I run a check of create table, this is what I get:
SQL> drop table check_char;
Table dropped.
SQL> create table check_char (some_name varchar2(10));
Table created.
SQL> select a.char_used
2 from all_tab_columns a
3 where table_name='CHECK_CHAR'
4 and a.owner='SYS';
C
-
B
SQL>
What is the reason for space allocation to remain in BYTES and not CHAR, or what else I can check?
View 4 Replies
View Related
Jun 16, 2010
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?
View 2 Replies
View Related
Oct 20, 2010
query string in such like that index.php?name=tejaspatel
i have table in record is available below
select * from emp where name = :name // it is query string parameter
emp
name
tejas patel
then how to match this record ?
View 8 Replies
View Related
Jan 5, 2012
I have table like below.
Name Gender
----- ------
f1 Female
f2 Female
m1 Male
m2 Male
f3 Female
m3 Male
m4 Male
but I need the output like below
Male Female
----- ------
m1 f1
m2 f2
m3 f3
m4
I have tried to get the above O/p but getting along with null values.
SQL> ed
Wrote file afiedt.buf
1 select case when gender='male' then name end male,
2 case when gender='female' then name end female
3* from details s1
4 /
MALE FEMALE
--------------- ---------------
f1
f2
m1
m2
f3
m3
m4
7 rows selected.
how the get the above o/p with out null values.
View 9 Replies
View Related
Apr 14, 2013
i have data about 3 gb but my files in e:oraclexeapporacleoradataXE has grown to 16 gb
1)my e: drive has less space ,pls tel how can i fee some space and give it back to OS
2)is there any other place where i can free up some space from oracle and give it back to OS.
-----------------------
windows (2008)
---
Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
"CORE 11.2.0.2.0 Production"
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production (2008)
NLSRTL Version 11.2.0.2.0 - Production
[code]....
View 3 Replies
View Related
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
Jun 19, 2008
i have a column called name in a table. now what iwould like to do is to check if it has two parts "paulh some" and then output the second part!
SELECT LTRIM(name,' '), length(name) length
FROM list
WHERE INSTR(name,' ') = 1;
but that doesnt work.. the fucntion is NOT checking for the space! if i use another character (a or b etc) it works..
View 2 Replies
View Related
