Wednesday, April 6, 2011

Row Movement in oracle...Oops what is this?


Row Movement in oracle...Oops what is this?

This is actually relocation of a row from a table to another place. Now the question arises when this can be done in oracle.

Let’s take the edge of this topic…

Below are the situations where row movement is comes into play

Partitioned Tables

Many partitioned table examples use regions, cities and states as list examples. What happens if you use a city as a partition key and an office in that city moves elsewhere? Or two offices in the same city (from different groups or business units within the same company) merge into one location? You could split the default partition and add the new location name. How would you move records from the old partitioned into the new one? Short of deleting from one partition and inserting same into a new one, wouldn’t it be easier to be able to perform a single update?
Example:

SQL> CREATE TABLE CITY_OFFICES
  2    (
  3       OFFICE_NUMBER NUMBER       NOT NULL,
  4        CITY_ID       VARCHAR2(12) NOT NULL,
  5     OFFICE_NAME   VARCHAR2(30) NOT NULL
  6      )
  7     PARTITION BY LIST (CITY_ID)
  8   (
  9     PARTITION P282 VALUES ('282'),
 10     PARTITION P283 VALUES ('283'),
 11     PARTITION P284 VALUES ('284'));

Table created.

SQL> insert into city_offices values(1,282,'NOIDA');

1 row created.

SQL> insert into city_offices values(2,282,'NOIDA_TECH');

1 row created.

SQL> insert into city_offices values(3,282,'NOIDA_SOFTWARE');

1 row created.

SQL> insert into city_offices values(4,282,'NOIDA_SOFTECH');

1 row created.

SQL> SELECT * FROM CITY_OFFICES;

OFFICE_NUMBER CITY_ID      OFFICE_NAME
------------- ------------ ------------------------------
            1 282          NOIDA
            2 282          NOIDA_TECH
            3 282          NOIDA_SOFTWARE
            4 282          NOIDA_SOFTECH

SQL> update city_offices set city_id=283 where office_number=4;
update city_offices set city_id=283 where office_number=4
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change


SQL> alter table city_offices enable row movement;

Table altered.

SQL> update city_offices set city_id=283 where office_number=4;

1 row updated.

SQL> SELECT * FROM CITY_OFFICES;

OFFICE_NUMBER CITY_ID      OFFICE_NAME
------------- ------------ ------------------------------
            1 282          NOIDA
            2 282          NOIDA_TECH
            3 282          NOIDA_SOFTWARE
            4 283          NOIDA_SOFTECH

Flashback

The first step to enabling table flashback is to enable row movement on the tables.
This is a flag set in the data dictionary that informs Oracle that rowids may change. A
rowid can never actually change, but a flashback operation may make it appear as
though it has. For instance, in the case of a row that is deleted, the flashback
operation will insert it back into the table: it will have the same primary key value,
but a different rowid.


Example:

In the example that follows, there are two tables: EMP and DEPT. There is a
foreign key relationship between them, stating that every employee in EMP must be
a member of a department in DEPT.

First, insert a new department and an employee in that department, and note the
time:

sql> insert into dept values(50,'SUPPORT','LONDON');

1 row created.

sql> insert into emp values(8000,'WATSON','ANALYST',7566,'27-DEC-
04',3000,null,50);

1  row created.

sql> commit;
Commit complete.

sql> select sysdate from dual;

SYSDATE
-----------------
27-12-04 18:30:11

Next delete the department and the employee, taking care to delete the employee
first to avoid a constraint violation:

sql> delete from emp where empno=8000;

1 row deleted.

sql> delete from dept where deptno=50;

1 row deleted.

sql> commit;

Commit complete.

Now attempt to flash back the tables to the time when the department and
employee existed:

sql> flashback table emp to timestamp to_timestamp('27-12-04
18:30:11','dd-mm-yy hh24:mi:ss');

flashback table emp to timestamp to_timestamp('27-12-04 18:30:11','dd-mm-yy
hh24:mi:ss')
*                
ERROR at line 1:
ORA-08189: cannot flashback the table because row movement is not enabled

This fails because by default row movement, which is a prerequisite for table
flashback, is not enabled for any table, so enable it, for both tables:

sql> alter table dept enable row movement;

Table altered.

sql> alter table emp enable row movement;

Table altered.

and now try the flashback again:

sql> flashback table emp to timestamp to_timestamp('27-12-04
18:30:11','dd-mm-yy hh24:mi:ss');
flashback table emp to timestamp to_timestamp('27-12-04 18:30:11','dd-mm-yy
hh24:mi:ss')
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not
found

This time the flashback fails for a more subtle reason. The flashback is attempting
to reverse the deletion of employee 8000 by inserting him, but employee 8000 was in
department 50, which has been deleted and so does not exist. Hence the foreign key
violation. You could avoid this problem by flashing back the DEPT table first, which
would insert department 50. But if your flashback involves many tables and many

DML statements, it may be logically impossible to find a sequence that will work.
The answer is to flash back both tables together:

sql> flashback table emp,dept to timestamp to_timestamp('27-12-04
18:30:11','dd-mm-yy hh24:mi:ss');

Flashback complete.

This succeeds because both the tables are flashed back in one transaction, and the
constraints are only checked at the end of that transaction, by which time, the data is
logically consistent.

Space Management

Row movement comes into play can be found in shrink operations.

Example:

SQL> SELECT ROWID, OFFICE_NUMBER FROM CITY_OFFICES;
ROWID              OFFICE_NUMBER
------------------ -------------
AAANSfAAEAAAEAnAAA             1
AAANSfAAEAAAEAnAAD             2
AAANSfAAEAAAEAnAAE             3
AAANSgAAEAAAEAvAAA             4

SQL> DELETE FROM CITY_OFFICES WHERE OFFICE_NUMBER IN (2,3);

2 rows deleted.

SQL> COMMIT;

Commit complete.

Even in this small table, we should be able to reclaim some space, so let’s try shrinking the table.

SQL> ALTER TABLE CITY_OFFICES SHRINK SPACE;
ALTER TABLE CITY_OFFICES SHRINK SPACE
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled

Notice that the error number is different from the flashback example, but the message is pretty clear. We’ll alter the table and perform the shrink operation.

SQL> ALTER TABLE CITY_OFFICES ENABLE ROW MOVEMENT;

Table altered.
SQL> ALTER TABLE CITY_OFFICES SHRINK SPACE;

Table altered.

Now that the shrink has taken place, let’s examine the ROWIDs for the remaining two rows. Will the ROWIDs be the same or different?

SQL> SELECT ROWID, OFFICE_NUMBER FROM CITY_OFFICES;
ROWID              OFFICE_NUMBER
------------------ -------------
AAANSfAAEAAAEAkAAA             1
AAANSgAAEAAAEAsAAA             4

Interestingly enough, the ROWIDs for the two remaining rows are different from their original IDs before the delete statement


10 comments:

Anonymous said...

Brokersring.com - Learn how to turn $500 into $5,000 in a month!

[url=http://www.brokersring.com/]Make Money Online[/url] - The Secret Reveled with Binary Option

Binary Options is the way to [url=http://www.brokersring.com/]make money[/url] securely online

Anonymous said...

Оur landlord happens to be his 75th birthday and the busіness feѕtivіties
included the ultіmatе accolаde, the screening of а video
ѕhoot in Pаlmdale, Calif. Chinese do not аppreciate bеing touched as it is sеen through the way work gеtѕ done.
But then at the end 2012. Accordіng to the conventional wisdom thatWindoωѕ 7-based ultrabooks need to be simple.



my weblog; baby-community.com

Anonymous said...

With an methods of website linking services firm can
use. An example would be, Claim your free report
on widgets that shows exactly how you can use to create and
publish a blog. While these practices smack of Big Brother and bring cries
of unwanted control from naysayers, the reasons
for them are far more prosaic. Old refrigerators are full of Freon gas - the chemical used to create your website.


Look at my homepage :: search engine optmization

Anonymous said...

Hey there would you mind sharing which blog platform you're
working with? I'm planning to start my own blog
soon but I'm having a tough time making a decision between BlogEngine/Wordpress/B2evolution and Drupal.
The reason I ask is because your layout seems different then most
blogs and I'm looking for something completely unique.

P.S Apologies for being off-topic but I had to ask!


Here is my web blog - career planning high school students

Anonymous said...

If yοu ave got money tto invest үou cann hire someone to
create a website fօr үou with a good sales pɑge and promote tɦis үour product via clickbank and other similaг affiliate
marketing programs. Ύou'llbe ready to induce started wіth ѕome Һundred
bucks аnd it is a learned skill. Mission 24, А Potent Sting, can be found oon tҺe fifrh tier of Taejin's Tower, ɑnd
unlocks once yоu've completed missions 21-23.
Affiliate Programs аre juust ɑbout inernet marketing, business, autopilot, residual income ɑnd a
new affiliate program, οr starting ɑn affiliate program.

Dwnload Һiѕ free report ԝhich iss ɑ condensed version οf the book at Mike's Blog:.

Anonymous said...

This page certainly has all of the information and facts I needed concerning
this subject and didn't know who to ask.

Also visit my web site :: โซลาร์เซลล์

Anonymous said...

Greetings! Very helpful advice in this particular post!
It's the little changes that produce the most significant changes.
Thanks a lot for sharing!

my web-site buy used cars in newark nj

Anonymous said...

You can find quite a few ways to get games for your i-phone.
One is by using coffee applets. You're able
to perform free i-phone games this way. However, there are brand new, promising websites that supply an one time fee to get
a lifetime account. This enables you an unlimited quantity of downloads for
only the one fee. That is it. Some sites are a lot better than others.
Simply study each website and take a look at user reviews for the best 1.
As more businesses try to compete with each other, you're going to get many different websites with different choices.


Here is my homepage :: how to fix cydia tweaks not working

Blogger said...

Searching for the Best Dating Site? Create an account to find your perfect match.

jeje said...

yeezy boost 350
kobe basketball shoes
cheap nfl jerseys
longchamp handbags
nike lebron 14
longchamps
ysl handbags
nike air force
ultra boost
yeezy boost 350