SQL & PL/SQL :: Removing Table Fragmentation?

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


ADVERTISEMENT

How To Find Table Fragmentation

Jul 22, 2011

A query to find the table fragmentation.

View 1 Replies View Related

Table Space Fragmentation

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

Performance Tuning :: Remove Table Fragmentation

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

SQL & PL/SQL :: Update Table Removing First 2 Digits From Column?

Apr 10, 2013

I have a table like:

0035987850 P
0035987851 P
0035987852 P

I would like to update removing 00 in the first column. So after update to have:

35987850 P
35987851 P
35987852 P

View 5 Replies View Related

Performance Tuning :: Table Rebuild Should Be Done After Removing Most Of Its Data?

Oct 23, 2012

We are on Oracle 10.2.0.4 on Solaris 10. There is a table in my production db that has 872944 number of rows. Most of its data is now unnecessary, we need to retain, based on a date column in the table just last one month's data and delete rest of the data. So after that the table will have just 3000 rows.

However as the table was huge earlier(872k rows prior to delete) , does the delete of data release its oracle blocks and does the size of the table reduce? If not, will it rebuild the table online (online redefinition) so that the query that does a full scan on this table goes faster?

I checked using an example table that just delete of data does not remove the oracle blocks - they remain in the user_tables for that table and cost of full table scan remains same. We have a query that does the full table scan so I am thinking that after this delete I should do an online table re-definition , is that the right decision?

View 4 Replies View Related

How To Remove Fragmentation

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

SQL & PL/SQL :: Row-movement And Fragmentation?

May 29, 2012

find out whether the rowmovement option in a partitioned table will cause fragmentation or not?

View 3 Replies View Related

Does Update On A Partitioned Column Cause Fragmentation

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

PL/SQL :: Delete Millions Of Rows And Fragmentation

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

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

SQL & PL/SQL :: Horizontal Fragmentation In Oracle 10g Express Edition?

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

Text :: Query To Determine Index Fragmentation?

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

PL/SQL :: Removing Duplicates Except One

Sep 19, 2013

db and dev 10g rel2 ,suppose that i have a table with a lot of duplicate rows ,what i need is to delete the duplicates and retain one row of these duplicates . likecolumn -- with those values...how to delete two (hi's) and retain the third , ?it is all applied to all the duplicate values in the column.

View 5 Replies View Related

Performance Tuning :: Fragmentation Can Reduce Performance In Query Times

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

Removing Duplicate Keys

Jul 3, 2008

Trying to delete duplicate rows from a table. The problem is, they aren't exactly duplicate rows. Let me explain.

I am migrating data from a Oracle 8.1.7 db to a 10.2.1 db. In the older db, this certain table does not have a PK/Unique Index, but in the new db there is a unique index. The fields that the index is unique on are:

SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE.

In the old db, when I run this query I get 1229 rows. With a count of 2 each.

select SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE, count(*)
from customer_id_equip_map
group by SUBSCR_NO, SUBSCR_NO_RESETS, EXTERNAL_ID, EXTERNAL_ID_TYPE, ACTIVE_DATE
having count(*)>1;

They are duplicates on those fields, but they are not totally duplicate rows because there is a field called is_current that has 0 in one row and has 1 in the other. What I need to do, is delete the 1229 rows with is_current=0.

View 4 Replies View Related

SQL & PL/SQL :: Removing Array Index

Sep 10, 2010

TYPE CashRecord IS RECORD(client_id VARCHAR2(100),
account_letter VARCHAR2(100),
cash_amount VARCHAR2(100),
cash_amount_ccy VARCHAR2(100) );

TYPE CashRecordTable IS TABLE OF CashRecord INDEX BY VARCHAR2(100); -- Indexed by client_id~account_letter~ccy

So if I did something like this;

l_cash_records CashRecordTable;

-- say for example that l_cash_rec/l_cash_rec2 has been defined..

l_cash_records('some index') := l_cash_rec;
l_cash_records('some index 2') := l_cash_rec2;
l_cash_records.COUNT would give me 2

How can I somehow remove 'some index 2' so that l_cash_records.COUNT is 1 ?

View 3 Replies View Related

SQL & PL/SQL :: How To Concatenate While Removing Nulls

Jan 26, 2012

This is a simple question, but I cannot seem to find a solution. Here's the basic query:

select distinct accountno, parcelno, streetno||' '|| predirection ||' '|| streetname||' '|| streettype||' '|| postdirection||' '|| unitname||', '|| propertycity
from tblacctpropertyaddress ....

What I want to do is add is this logic: If Predirection is null, then no space between streetno & streetname. Same for postdirection and unitname. (for example, if both postdirection and unitname are null, there are no spaces between streettype and the comma before propertycity)

Also, when unitname is not null, I want to add the string "Unit " prior to the returned value in unitname.

View 5 Replies View Related

How To Evaluate Impact Of Removing CPU

Dec 10, 2012

One of my clients need to remove three(of four) CPU to comply the licensing agreement with Oracle.

To avoid problems and also to list the possible problems that removing the CPU can bring, I wish to make a survey of the possible impacts, especially in performance, that removal can cause.

How can I get this information?

View 8 Replies View Related

Removing Illegal Characters

Dec 27, 2006

I am working with Oracle 10G, and have been working on setting up little pl/sql checks to make sure that the data that is imported is in the correct format.

The wall I have hit is removing illegal characters from the data I import. I have started to set something up where the string for a certain column must be be made of only there characters:

"ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz-" (note that there is a - besides just letters) and I may want to add some other characters later. So basically the script will drop or replace any character not found in my definitions with "", thus removing the illegal character and joining the previous and next characters.

I thought for sure there would be a script posted somewhere online that did this but I can't find it and my syntax skills are lacking.

View 8 Replies View Related

SQL & PL/SQL :: Removing Duplicate In Hierarchy Level

Jul 18, 2013

I have requirement to suppress the duplicate nodes on same level in hierarchy query.
Below given is the script for it.

CREATE TABLE NODE_LVL (PARENT_NODE VARCHAR2(100), CHILD_NODE VARCHAR2(100));

INSERT INTO NODE_LVL VALUES('TBL_APL','TBL_AFL');
INSERT INTO NODE_LVL VALUES('TBL_APP','TBL_ACS');
INSERT INTO NODE_LVL VALUES('TBL_ADD','TBL_ADW');
INSERT INTO NODE_LVL VALUES('TBL_ADP','TBL_ADV');
INSERT INTO NODE_LVL VALUES('TBL_AOP','TBL_AOV');
[code]......

Table 'TBL_APP' is having 2 parent nodes i.e 'TBL_AOV' and 'TBL_ADV'
SELECT * FROM node_lvl WHERE child_node = 'TBL_APP';

At level 5 there is duplicate nodes i.e 'TBL_APP' and 'TBL_ACS' as parent_node and child_node respectively.

SELECT PARENT_NODE, CHILD_NODE, LEVEL FROM NODE_LVL START WITH PARENT_NODE = 'TBL_ACF' CONNECT BY PRIOR CHILD_NODE = PARENT_NODE;

I want to suppress such duplicates. So I added DISTINCT

SELECT DISTINCT PARENT_NODE, CHILD_NODE, LEVEL FROM NODE_LVL START WITH PARENT_NODE = 'TBL_ACF' CONNECT BY PRIOR CHILD_NODE = PARENT_NODE;

BUT requirement is to maintain the same order (of hierarchy) as it was before adding DISTINCT.

View 11 Replies View Related

SQL & PL/SQL :: Removing Subpartitions And Preserving Data?

May 3, 2012

I've a table in data warehouse production with data which is partitioned by RANGE, sub-partitioned by HASH.Later we realized that subpartitioned are not needed as volume is less.

I tried below approah in DEV DB:

1. create table ABC_BAK as select * from ABC;

2. dbms_metadata.get_ddl('TABLE','ABC')

3. Removed the subpartitions details from the output of step 2 and prepared the script of create table without subpartitions

4. Drop table ABC

5. Create table ABC from script step 3.

6. Insert into ABC select * from ABC_BAK

7. Drop table ABC_BAK

View 1 Replies View Related

CLOB Removing Newline Characters

Dec 13, 2008

I am persisting xml with below mention tag which contains preformatted text data.

<SECTION>
<HDG><![CDATABusiness Summary]></HDG>
<BODY>
<![CDATA[(C) 2008 D&B
COPYRIGHT 2008 DUN & BRADSTREET INC. - PROVIDED UNDER CONTRACT
FOR THE EXCLUSIVE USE OF SUBSCRIBER 263763803.
ATTN: null
[code]....

Now, when i retrieve this xml from oracle CLOB column, i am getting data in body tag as below:

(C) 2008 D&B COPYRIGHT 2008 DUN & BRADSTREET INC. - PROVIDED UNDER CONTRACT FOR THE EXCLUSIVE USE OF SUBSCRIBER 263763803. ATTN: null US TEST COMPANY 984 DUNS: 36-252-8379 RATING DS US FICTITIOUS COMPANY 984 BUSINESS SERVICES EMPLOYS UNDETERMINED 899 EATON AVE SIC NO. BETHLEHEM PA 18025 7389 TEL: 610 882-0005 RICHARD DOE, MANAGER RECORD TYPE: DUNS SUPPORT THE "DS" INDICATOR ASSIGNED TO THIS BUSINESS MEANS THAT THE LIMITED INFORMATION CURRENTLY IN THE D&B FILE DOES NOT ALLOW US TO CLASSIFY IT WITHIN OUR RATING SYSTEM. WE ARE PROVIDING THIS INFORMATION TO YOU IN THE INTEREST OF SPEED WITHOUT HAVING COMPLETED AN INVESTIGATION. THEREFORE, THIS REPORT MAY NOT REFLECT THE CURRENT STATUS OF THIS BUSINESS. D&B CAN INVESTIGATE THIS BUSINESS AND UPDATE THIS INFORMATION BASED ON THE RESULTS OF THAT INVESTIGATION.

All text formatting goes for a toss (newline, trailing tab characters are getting trimmed).provide any inputs on how to avoid this. Text formatting needs to be preserved.

View 1 Replies View Related

SQL & PL/SQL :: Commit Interval When Removing Duplicates

May 31, 2012

I am trying to remove duplicates from a table with over 10million records. Below query is working fine but it doesnt contain any COMMIT interval. I have to commit after every 20k or 30k records deletion for which IF loop is necessary.

Query:

delete from
customer
where rowid in
(select rowid from
(select
rowid,
row_number()
over
(partition by custnbr order by custnbr) dup
from customer)
where dup > 1);

View 9 Replies View Related

Properly Removing Old Temp Files From ASM Storage

Dec 16, 2010

The other day, we had a query run amok in our 2-node production cluster. The 3 temp files for the temp tablespace were all still set to autoextend unlimited, something I forgot to change after a recent upgrade. I created 3 new temp files and tried to delete the huge temp files. I did this from sqlplus with this commad:

ALTER TABLESPACE PSTEMP DROP TEMPFILE '+DATA/isis/tempfile/pstemp.291.641298061';

The huge files are still in ASM storage. dba_temp_files reports that the status of them is AVAILABLE but they have no RELATIVE_FNO. Grid Control reports their status as OFFLINE and their size as 0. They are actually close to 20 GB each.

I tested the above alter statement in two test instances, also RACed with ASM storage and the temp files were successfully deleted, but they were much smaller in size. At this point, how do I delete the three 20GB files from ASM in our production instance? Why didn't they delete the first time?

View 1 Replies View Related

Forms :: Delete_record Not Removing Record From Screen

May 13, 2010

I am trying to select a row on the screen and delete a individual row from the screen after hitting the delete button. Whenever i select the row it gets highlighted and also the alert box comes up asking if the record needs to be deleted. When I say yes I notice that the row does not get deleted. The code behind the delete button is as follows

(P.S the block is based of a table and commit_form works and changes are saved to db but not delete_record)

begin
--
Set_Item_Property('my_block.emp_no_copy',current_record_attribute,'va_delete_record');
Set_Item_Property('my_block.emp_LName_copy',current_record_attribute,'va_delete_record');
Set_Item_Property('my_block.emp_FName_copy',current_record_attribute,'va_delete_record');
Set_Item_Property('my_block.last_worked_date_copy',current_record_attribute,'va_delete_record');
--
synchronize;
Set_Alert_Property(alert_id, ALERT_MESSAGE_TEXT, 'Do you want to delete the Highlighted record? {NOT YET}');

[Code]....

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

"DROP TABLESPACE <tablespace name> INCLUDING CONTENTS AND DATAFILES;"

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

Reports & Discoverer :: Removing Spaces From Top Of Report

Aug 18, 2010

I am using 10g reports.

I created a report (its basically a statement) without a header. When I run the report, blank lines appear at the top of the report before the details start printing. How do I get the report to print from the top of the page without leaving any blank lines.

View 3 Replies View Related

SQL & PL/SQL :: Removing Duplicate Rows When Condition Is Matched

Mar 17, 2010

My requirement if id, join_date, join_time, result of table1 is matched with table2 at least one time then if repeating rows associated with the id should not come.Here is the test case.

create table table1
( id number , join_date varchar2(8), join_time varchar2(6), status varchar2(10));
create table table2
( id number , join_date varchar2(8), join_time varchar2(6), status varchar2(10));

insert into table1 values (01, '20010101', '0500', 'PASS');
insert into table1 values (01, '20010102', '0501', 'FAIL');
insert into table1 values (02, '20010103', '0502', 'PASS');
insert into table1 values (03, '20010104', '0503', 'FAIL');
insert into table1 values (04, '20010105', '0504', 'PASS');
insert into table1 values (05, '20010106', '0505', 'FAIL');
[code]...

I have tried the below mentioned query, whether any better query is there than this because in real-time data have 2 millions of record in table 1 and 60 thousand in table2.

select distinct a.id, a.join_date, a.join_time, a.status
from table1 a, table2 b
where a.id = b.id
and (a.id, a.join_date, a.join_time, a.status) not in (select b.id, b.join_date, b.join_time, b.status
from table2 b)
and a.id = (
select distinct a.id
[code]....

View 20 Replies View Related

Backup & Recovery :: RMAN Not Removing Backups

Sep 10, 2012

RMAN is not removing obsolete backups and now the backup directory is nearly full.Retention policy is 1, however, there are backups more than 6 weeks old still in the backup directory.crosscheck and delete obsolete commands run every night, and I have also run these manually, yet the backup files still remain.It is like RMAN is not aware of their existance.How can I confirm that RMAN does not need them and therefore delete them manually using OS commands.

View 17 Replies View Related







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