| row chaining [message #194661] |
Sat, 23 September 2006 12:08  |
vijaychauhan
Messages: 106
Registered: December 2005
|
Senior Member |
|
|
|
If a row splits when you update it, across two blocks, does the part of the row that goes in new block have a header as well?
|
|
|
|
| Re: row chaining [message #194669 is a reply to message #194661] |
Sat, 23 September 2006 16:12  |
nmacdannald
Messages: 460
Registered: July 2005
Location: Stockton, California - US...
|
Senior Member |
|
|
I belive all Oracle pages have headers and footers. It is within these pages it keeps your data.
Just a thought.
You can find chained rows, test first!
:
*****
* File: ChainedRowsFix.txt
* Created: 06/27/2002 DBA at large.
* Owner: GNU
*****
*****************************************
*** APPOINTMENT QUERY
*****************************************
CREATE TABLE TEMP_APPOINTMENT
PCTFREE 10
PCTUSED 60
INITRANS 30
MAXTRANS 121
TABLESPACE DATA_21
STORAGE (
INITIAL 1 M
NEXT 1 M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
AS SELECT * FROM APPOINTMENT
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'APPOINTMENT');
COMMIT;
DELETE FROM APPOINTMENT
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'APPOINTMENT');
COMMIT;
INSERT INTO APPOINTMENT
SELECT * FROM TEMP_APPOINTMENT;
COMMIT;
DROP TABLE TEMP_APPOINTMENT;
COMMIT;
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'APPOINTMENT';
COMMIT;
*****************************************
*****************************************
-----------------------------------------------------------------
*****************************************
*** HR_ACCOUNT_DISTRIBUTION
*****************************************
CREATE TABLE TEMP_HR_ACCOUNT_DISTRIBUTION
PCTFREE 10
PCTUSED 60
INITRANS 30
MAXTRANS 121
TABLESPACE DATA_21
STORAGE (
INITIAL 1 M
NEXT 1 M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
AS SELECT * FROM HR_ACCOUNT_DISTRIBUTION
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'HR_ACCOUNT_DISTRIBUTION');
COMMIT;
DELETE FROM HR_ACCOUNT_DISTRIBUTION
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'HR_ACCOUNT_DISTRIBUTION');
COMMIT;
INSERT INTO HR_ACCOUNT_DISTRIBUTION
SELECT * FROM TEMP_HR_ACCOUNT_DISTRIBUTION;
COMMIT;
DROP TABLE TEMP_HR_ACCOUNT_DISTRIBUTION;
COMMIT;
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'HR_ACCOUNT_DISTRIBUTION';
COMMIT;
*****************************************
*****************************************
-----------------------------------------------------------------
*****************************************
*** HR_ACCT_DIST_POSTED
*****************************************
CREATE TABLE TEMP_HR_ACCT_DIST_POSTED
PCTFREE 10
PCTUSED 60
INITRANS 30
MAXTRANS 121
TABLESPACE DATA_21
STORAGE (
INITIAL 1 M
NEXT 1 M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
AS SELECT * FROM HR_ACCT_DIST_POSTED
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'HR_ACCT_DIST_POSTED');
COMMIT;
DELETE FROM HR_ACCT_DIST_POSTED
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'HR_ACCT_DIST_POSTED');
COMMIT;
INSERT INTO HR_ACCT_DIST_POSTED
SELECT * FROM TEMP_HR_ACCT_DIST_POSTED;
COMMIT;
DROP TABLE TEMP_HR_ACCT_DIST_POSTED;
COMMIT;
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'HR_ACCT_DIST_POSTED';
COMMIT;
*****************************************
*****************************************
-----------------------------------------------------------------
*****************************************
*** HR_JOB_TITLE
*****************************************
CREATE TABLE TEMP_HR_JOB_TITLE
PCTFREE 10
PCTUSED 60
INITRANS 30
MAXTRANS 121
TABLESPACE DATA_21
STORAGE (
INITIAL 1 M
NEXT 1 M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
AS SELECT * FROM HR_JOB_TITLE
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'HR_JOB_TITLE');
COMMIT;
DELETE FROM HR_JOB_TITLE
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'HR_JOB_TITLE');
COMMIT;
INSERT INTO HR_JOB_TITLE
SELECT * FROM TEMP_HR_JOB_TITLE;
COMMIT;
DROP TABLE TEMP_HR_JOB_TITLE;
COMMIT;
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'HR_JOB_TITLE';
COMMIT;
*****************************************
*****************************************
-----------------------------------------------------------------
*****************************************
*** PROCEDURE_STRING
*****************************************
CREATE TABLE TEMP_PROCEDURE_STRING
PCTFREE 10
PCTUSED 60
INITRANS 30
MAXTRANS 121
TABLESPACE DATA_21
STORAGE (
INITIAL 1 M
NEXT 1 M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
AS SELECT * FROM PROCEDURE_STRING
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'PROCEDURE_STRING');
COMMIT;
DELETE FROM PROCEDURE_STRING
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'PROCEDURE_STRING');
COMMIT;
INSERT INTO PROCEDURE_STRING
SELECT * FROM TEMP_PROCEDURE_STRING;
COMMIT;
DROP TABLE TEMP_PROCEDURE_STRING;
COMMIT;
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'PROCEDURE_STRING';
COMMIT;
*****************************************
*****************************************
-----------------------------------------------------------------
*****************************************
*** PR_TIMECARD_PERIOD
*****************************************
CREATE TABLE TEMP_PR_TIMECARD_PERIOD
PCTFREE 10
PCTUSED 60
INITRANS 30
MAXTRANS 121
TABLESPACE DATA_21
STORAGE (
INITIAL 1 M
NEXT 1 M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
AS SELECT * FROM PR_TIMECARD_PERIOD
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'PR_TIMECARD_PERIOD');
COMMIT;
DELETE FROM PR_TIMECARD_PERIOD
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'PR_TIMECARD_PERIOD');
COMMIT;
INSERT INTO PR_TIMECARD_PERIOD
SELECT * FROM TEMP_PR_TIMECARD_PERIOD;
COMMIT;
DROP TABLE TEMP_PR_TIMECARD_PERIOD;
COMMIT;
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'PR_TIMECARD_PERIOD';
COMMIT;
*****************************************
*****************************************
-----------------------------------------------------------------
*****************************************
*** SECTION_TABLE
*****************************************
CREATE TABLE TEMP_SECTION_TABLE
PCTFREE 10
PCTUSED 60
INITRANS 30
MAXTRANS 121
TABLESPACE DATA_21
STORAGE (
INITIAL 1 M
NEXT 1 M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
AS SELECT * FROM SECTION_TABLE
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'SECTION_TABLE');
COMMIT;
DELETE FROM SECTION_TABLE
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'SECTION_TABLE');
COMMIT;
INSERT INTO SECTION_TABLE
SELECT * FROM TEMP_SECTION_TABLE;
COMMIT;
DROP TABLE TEMP_SECTION_TABLE;
COMMIT;
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'SECTION_TABLE';
COMMIT;
*****************************************
*****************************************
-----------------------------------------------------------------
*****************************************
*** SERVICE_UTILIZATION
*****************************************
CREATE TABLE TEMP_SERVICE_UTILIZATION
PCTFREE 10
PCTUSED 60
INITRANS 30
MAXTRANS 121
TABLESPACE DATA_21
STORAGE (
INITIAL 1 M
NEXT 1 M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
AS SELECT * FROM SERVICE_UTILIZATION
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'SERVICE_UTILIZATION');
COMMIT;
DELETE FROM SERVICE_UTILIZATION
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'SERVICE_UTILIZATION');
COMMIT;
INSERT INTO SERVICE_UTILIZATION
SELECT * FROM TEMP_SERVICE_UTILIZATION;
COMMIT;
DROP TABLE TEMP_SERVICE_UTILIZATION;
COMMIT;
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'SERVICE_UTILIZATION';
COMMIT;
*****************************************
*****************************************
-----------------------------------------------------------------
*****************************************
*** STUDENT_DSPS
*****************************************
CREATE TABLE TEMP_STUDENT_DSPS
PCTFREE 10
PCTUSED 60
INITRANS 30
MAXTRANS 121
TABLESPACE DATA_21
STORAGE (
INITIAL 1 M
NEXT 1 M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
AS SELECT * FROM STUDENT_DSPS
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'STUDENT_DSPS');
COMMIT;
DELETE FROM STUDENT_DSPS
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'STUDENT_DSPS');
COMMIT;
INSERT INTO STUDENT_DSPS
SELECT * FROM TEMP_STUDENT_DSPS;
COMMIT;
DROP TABLE TEMP_STUDENT_DSPS;
COMMIT;
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'STUDENT_DSPS';
COMMIT;
*****************************************
*****************************************
-----------------------------------------------------------------
*****************************************
*** STUDENT_EOPS
*****************************************
CREATE TABLE TEMP_STUDENT_EOPS
PCTFREE 10
PCTUSED 60
INITRANS 30
MAXTRANS 121
TABLESPACE DATA_21
STORAGE (
INITIAL 1 M
NEXT 1 M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
AS SELECT * FROM STUDENT_EOPS
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'STUDENT_EOPS');
COMMIT;
DELETE FROM STUDENT_EOPS
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'STUDENT_EOPS');
COMMIT;
INSERT INTO STUDENT_EOPS
SELECT * FROM TEMP_STUDENT_EOPS;
COMMIT;
DROP TABLE TEMP_STUDENT_EOPS;
COMMIT;
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'STUDENT_EOPS';
COMMIT;
*****************************************
*****************************************
-----------------------------------------------------------------
*****************************************
*** STUDENT_TRANSCRIPT_NOTE
*****************************************
CREATE TABLE TEMP_STUDENT_TRANSCRIPT_NOTE
PCTFREE 10
PCTUSED 60
INITRANS 30
MAXTRANS 121
TABLESPACE DATA_21
STORAGE (
INITIAL 1 M
NEXT 1 M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
AS SELECT * FROM STUDENT_TRANSCRIPT_NOTE
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'STUDENT_TRANSCRIPT_NOTE');
COMMIT;
DELETE FROM STUDENT_TRANSCRIPT_NOTE
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'STUDENT_TRANSCRIPT_NOTE');
COMMIT;
INSERT INTO STUDENT_TRANSCRIPT_NOTE
SELECT * FROM TEMP_STUDENT_TRANSCRIPT_NOTE;
COMMIT;
DROP TABLE TEMP_STUDENT_TRANSCRIPT_NOTE;
COMMIT;
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'STUDENT_TRANSCRIPT_NOTE';
COMMIT;
*****************************************
*****************************************
-----------------------------------------------------------------
*****************************************
*** WORK_ORDER
*****************************************
CREATE TABLE TEMP_WORK_ORDER
PCTFREE 10
PCTUSED 60
INITRANS 30
MAXTRANS 121
TABLESPACE DATA_21
STORAGE (
INITIAL 1 M
NEXT 1 M
PCTINCREASE 0
MINEXTENTS 1
MAXEXTENTS UNLIMITED
)
AS SELECT * FROM WORK_ORDER
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'WORK_ORDER');
COMMIT;
DELETE FROM WORK_ORDER
WHERE ROWID IN
(SELECT HEAD_ROWID FROM CHAINED_ROWS
WHERE TABLE_NAME = 'WORK_ORDER');
COMMIT;
INSERT INTO WORK_ORDER
SELECT * FROM TEMP_WORK_ORDER;
COMMIT;
DROP TABLE TEMP_WORK_ORDER;
COMMIT;
DELETE FROM CHAINED_ROWS
WHERE TABLE_NAME = 'WORK_ORDER';
COMMIT;
*****************************************
*****************************************
Neil.
[Updated on: Sat, 23 September 2006 16:12] Report message to a moderator |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
