Home » RDBMS Server » Server Administration » partition key modification
| partition key modification [message #191679] |
Thu, 07 September 2006 13:36  |
rkl1
Messages: 97
Registered: June 2005
|
Member |
|
|
Dear All,
We have a table let's say TEST which is based on 3 partition keys and hash partitioned. one of the partition key say: TEST_COL_A is Number data type and the table size is around 11GB with 15 partitions.
Now, there is a need to modify the TEST_COL_A to a larger size. since it is part of the partition key, is it possible to modify it. If not, what is the best way to accomplish it. I have thought of two things:
1. export and then rename the table and recreate the new table with modified coloum and then import back.
2. Rename the table and create table "new table with modified column" and insert to it (CTAS) from older table. Finally drop the old table.
There must be a smart way to do it.
BTW: The database is on 9iR2.
Thanks.
|
|
|
|
| Re: partition key modification [message #191870 is a reply to message #191679] |
Fri, 08 September 2006 07:53  |
kimant
Messages: 201
Registered: August 2006
Location: Denmark
|
Senior Member |
|
|
Hi
I guess You have a test system?
This might be too naive, but what happens if You try to modify the column?
I think DBMS_REDEFINITION could do the trick, if this does not work....
Br
Kim
|
|
|
|
| Re: partition key modification [message #191910 is a reply to message #191870] |
Fri, 08 September 2006 10:18 |
goudelly
Messages: 52
Registered: August 2006
Location: India
|
Member |
|
|
Hi,
Performing an online table redefinition
I have a table which has grown so much we now think it would be a good idea to partition it. But we operate 24x7, so I can’t take the table offline for very long to make the change. Is there anything I can do?
Well, if you’ve got version 9i or above, then you're in luck... because those versions have precisely the feature you’re looking for: an online table redefinition -whereby you can muck about with the way a table is constructed (and hence turn a non-partitioned one into being partitioned) with only the briefest moments of exclusive locking. You can use the same feature to rename columns, change a column's data type or length, turn a regular heap table into being an IOT (or vice versa), and so on. It's a powerful feature, and does all that you are asking of it, and more.
Good... so how does that work?
In principle, it works by getting you to create a brand new table that looks like what you want your existing table to become. In your case, you’d specify some partitioning clauses when creating this interim table. Using a new package called DBMS_REDEFINITION, you then arrange for the data to be copied across from the existing table and inserted into the new table. Selecting records like that is, of course, an online operation. Finally, you use the same package to swap the records for the two tables around in the data dictionary: your existing table gets the name of your interim table, and the interim table acquires the name of your original table. That ends up looking, to users, as though the original table has suddenly been redefined to include partitioning, for example. The data dictionary switch is actually an offline operation -an exclusive table lock is held for the duration. But the duration of a two-line switch in the data dictionary is in the order of a second or two, so it’s unlikely anyone would really notice.
Sounds like just exactly what I need. Any chance of you showing me a worked example so I know what to expect?
Certainly. Let’s imagine I need to convert the HR.EMPLOYEES table into a range-partitioned table on DEPARTMENT_ID. Exactly the same principles could be used to convert a table to be an IOT, or to re-configure the table for any other reason and in practically any other way.
The HR.EMPLOYEES table is supplied as part of the Sample Schemas when you create a database. If you don’t have it in your database, you can always retro-fit you database with it by running the script hrmain.sql, which is found in $ORACLE_HOME/demo/schema/human_resources. You need to supply 5 arguments when you call the script so it might all look like this:
@$ORACLE_HOME/demo/schema/human_resources/hr_main.sql hr example temp oracle /logs;
The five arguments I’ve specified here tell Oracle :
HR’s password (in my case, just HR)
The tablespace where the tables will be created (in my case EXAMPLE)
The temporary tablespace the user HR should use (in my case TEMP)
The password for the SYS user (in my case oracle)
And a directory where logs should be written to (in my case /logs)
Obviously, change those parameters if you don’t have an EXAMPLE tablespace, or if SYS’s password is something rather less obvious, and so on. Equally obviously, I hope: you don’t need to do any of that if the HR schema is already part of your database.
OK, I’ve got the HR schema in my database. What do I do next?
Well the next thing I’ll do is check that it’s actually possible to perform an online redefinition of the EMPLOYEES table. For a table to be successfully redefined online, it must satisfy a number of tests. Some of those tests are:
The original table must have a primary key
The original table can’t be owned by SYS or SYSTEM
The original table can’t have any materialised views or materialised view logs built on it
The original table can’t be part of a cluster
The original table can’t have columns which are of data type LONG
You can check whether any of these restrictions apply to your table by doing the following:
connect / as sysdba
exec dbms_redefinition.can_redef_table('HR','EMPLOYEES')
If the procedure completes without warnings, then an online redefinition is possible. But if you try it on a table that doesn’t pass all the tests, you’ll get a response rather more like this one:
exec dbms_redefinition.can_redef_table('SYS','UET$')
BEGIN dbms_redefinition.can_redef_table('SYS','UET$'); END;
*
ERROR at line 1:
ORA-12087: online redefinition not allowed on tables owned by "SYS"
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 247
ORA-06512: at line 1
In my case, however, the EMPLOYEES table can be re-organised just fine. {mospagebreak title=Creating the Interim Table}
So now are we ready to do it?
Sort of!
Remember that the basic principle behind 9i’s online table re-organisation is that I need to first create a table that looks like what I want the original table to become. As I’ve already mentioned, the new ‘this is what you’ll look like’ table is usually called the interim table. It needs to be created in the same schema as the ‘real’ table. Whatever you make the interim table look like, that’s what the real table will end up like, too. However, you don’t put constraints on the interim table at this stage (because doing so slows down the initial creation process) unless you absolutely have to (if you are trying to convert a table to being an IOT, for example, then naturally the interim table will have to have a primary key, because you can’t create an IOT without one).
In my case, I might create an interim table like this (connecting first as the HR user):
create table empinterim (
employee_id number(6),
first_name varchar2(20),
last_name varchar2(25),
email varchar2(25),
phone_number varchar2(20),
hire_date date,
job_id varchar2(10),
salary number(8,2),
commission_pct number(2,2),
manager_id number(6),
department_id number(4))
partition by range (department_id)
(partition SOUTH values less than (50),
partition NORTH values less than (100),
partition OTHER values less than (150),
partition HQ values less than (maxvalue));
If I were now to DESCribe both the EMPLOYEES and EMPINTERIM tables, I’d see that one has a lot of NOT NULLS declared, and the other doesn’t. That’s simply because I haven't defined any constraints at all on my interim table, and I’ll need to sort that out shortly.
Right now, I have a beautfully partitioned table which is completely empty! How does that help me?!
It helps you because now you can use the dbms_redefinition package to describe how the columns in the real table map to the columns in the interim table. When you do that successfully, you’ll find Oracle populating the interim table for you with all the data that’s currently in the real table. To achieve that, I’d type this:
exec dbms_redefinition.start_redef_table
('HR','EMPLOYEES','EMPINTERIM', -
'employee_id employee_id, first_name first_name, -
upper(last_name) last_name, email email, phone_number phone_number,-
hire_date hire_date, job_id job_id, salary*1.1 salary, -
commission_pct commission_pct, manager_id manager_id, -
department_id department_id')
This syntax looks pretty awful, but it isn’t actually too bad when you know what you’re looking for. It breaks down into five parts:
exec dbms_redefinition.start_redef_table
(Schema ,
Real Table,
Interim Table,
Quoted list of original column mapped to interim column with column pairs separated by commas)
The “-” at the end of each line I’ve shown in my example is simply a continuation character. Without it, the thing goes onto one enormous line which is difficult to read correctly, and even more difficult to analyze if there are any errors. Breaking it up onto separate lines makes life a whole lot easier.
Notice that as part of the redefinition, it is possible (but strictly optional) to modify actual data, rather than just the table's definition. In this case, I’m saying that the interim salary column should get its data from the real salary column times 1.1 -in other words, I’m taking the opportunity to give everyone a 10% pay increase. Similarly, I’ve asked for the contents of the interim table’s LAST_NAME column to be the upper case version of what’s contained in the real table’s LAST_NAME column. Any column can have its data modified in this way except for the the primary key column(s) of the real table. I couldn’t, for example, apply a “*2” function to the EMPLOYEE_ID column. Even if it made mathematical sense to do it, you aren’t allowed to play around with the primary key of the real table in any way, shape or form.
So you’re saying that executing that procedure should have done something for me this time. What, exactly, has it done?
Why not take a look?! Try this:
select first_name, last_name, salary
from hr.employees
where employee_id < 103;
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Steven King 24000
Neena Kochhar 17000
Lex De Haan 17000
select first_name, last_name, salary
from hr.empinterim
where employee_id < 103;
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Steven KING 26400
Neena KOCHHAR 18700
Lex DE HAAN 18700
Notice that the interim table has actually got some rows to view, that the last_name column has been upper-cased and that the salary column has had its values increased. From this we can deduce that the procedure
Copies data from the real table to the interim table
Applies any requested functions as it does so
Leaves the real table otherwise untouched
In a sense, therefore, the procedure snapshots the original table into the interim table. That’s just the business of selecting from a table, and that’s an inherently on-line operation: the original table is not, therefore, locked out from further DML whilst that’s going on, however long it takes.{mospagebreak title=Refreshing the Interim Table}
Hang on a second... if the procedure has “snapshotted” the original table, what happens if users now start doing DML to the original table. Presumably, the real table and the interim table start getting out of synch?
Absolutely they do, and you can actually see it happening:
update hr.employees set first_name='Stephen' where employee_id=101;
commit;
select first_name from hr.employees where employee_id=101;
FIRST_NAME
--------------------
Stephen
select first_name from hr.empinterim where employee_id=101;
FIRST_NAME
--------------------
Neena
This is not a show-stopper, however. Oracle provides a procedure to re-synchronise the interim table with the real table:
exec dbms_redefinition.sync_interim-table(‘HR’,’EMPLOYEES’,’EMPINTERIM’)
select first_name from hr.empinterim where employee_id=101;
FIRST_NAME
--------------------
Stephen
...and as you can see, the interim table is now reporting the ‘correct’ results. You can re-synchronise the two tables like this as often as you want. There is no locking when the resynchronisation takes place. Modifications which are not yet committed on the real table at the time of resynchronisation are ignored. The next resynchronisation after that would, however, correctly pick them up.
Neat. So can I now switch the two tables around to finish the thing off?
You could, but it’s really a bit early to do that. After all, when I created my interim table, I deliberately didn’t specify any constraints for it, so that the initial load of data I’ve just performed would go as quickly as possible. If I now went to the ‘swap tables around’ finish line, I’d end up with my real table lacking any constraints... which is not really a very good idea at all.
But with the interim table now loaded with data, I can now add my constraints. I might therefore do something like this:
alter table hr.empinterim add (constraint EMP_PK primary key(employee_id));=
alter table hr.empinterim modify (last_name constraint LN_NN not null);
alter table hr.empinterim add (constraint EMAIL_UQ unique (email));
alter table hr.empinterim modify (hire_date constraint HD_NN not null);
alter table hr.empinterim modify (job_id constraint JOB_NN not null);
Along the same lines (of getting the interim table to look exactly as the real table is supposed to end up looking like), I would probably want to add indexes to particular columns of the interim table at the same time.
Bear in mind that adding constraints and building indexes on a table always locks the table you’re doing it to. Those are very ‘offline’ operations, in other words. But in this case, that’s fine, because it’s only the interim table that’s being locked. The ‘real’ table is open for business by ordinary users oblivious to what I happen to be doing to this completely separate table. So there’s no rush with this part of the procedure. I can do as much tinkering and fine-tuning as I need, and take as much time about it as I want, and my users won’t be inconvenienced in the slightest. Periodic re-runs of the sync_interim_table procedure will mean that, however long I take, the two tables' contents will stay pretty similar to each other throughout.{mospagebreak title=Switch the Tables}
Am I ready to finish the thing off now?
Yes. I can now run one final procedure, which takes just a few moments to complete, which swaps the definitions of the two tables in the data dictionary. What was known as EMPLOYEES will become EMPINTERIM, and what was EMPINTERIM becomes EMPLOYEES. And the command to do that is simply :
exec dbms_redefinition.finish_redef_table(‘HR’,’EMPLOYEES’,’EMPINTERIM’)
For this command to succeed, the real table does, indeed, have to be locked (though it will only be held for a short time whilst the data dictionary swaparound takes place -a matter of a few seconds, really). So pick a relatively quiet time to do it, when you know that no DML is pending. If there is pending DML, the procedure simply hangs until the pending transactions are committed or rolled back -at which point, it takes that brief exclusive table lock so that no new transactions can start.
The procedure can take a very long time to complete, however, if there’s an enormous backlog of DML pending. Before the data dictionary swap can take place, Oracle has to make sure the table and its interim copy are synchronised, so a huge backlog of DML means a lot of re-synchronisation work. It might therefore be a good idea to manually re-synchronise just prior to invoking the finish_redef_table procedure.
At this point, I could select from both the interim and the real tables -and I would then indeed discover that the two tables have been ‘switched around’. EMPINTERIM will contain the lowercase LAST_NAME data, and the old salaries, and EMPLOYEES will have all the revised data... and if I check further, I’ll discover (which was the entire point of the exercise, of course) that EMPLOYEES is now a partitioned table:
select count(*) from hr.employees partition(NORTH);
COUNT(*)
---------
88
Note the use of the PARTITION clause there, which would throw an error for a table which wasn’t truly partitioned. This is proof that the original employees table has genuinely "become" a partitioned table, therefore. The redefinition process has succeeded, and my users would hardly have known it was taking place!
inally, to really finish the job off, I should remember to drop the EMPINTERIM table.
That is extremely clever. Can anything go wrong? Are there any nasty surprises I should be aware of?
It’s good, isn’t it?! As for nasty surprises -well, there are none really to speak of. The worst one I can think of is that online table redefinitions are a bit of a one-way street: if you make any mistakes during the start_redef_table process, there’s no way you can correct them and try again... because one of the first things which that procedure does is to create a materialised view on the real table, and you may recall that tables with a materialised view on them cannot be online-redefined. When you correct your error and try again, therefore, the presence of the materialised view from your first attempt will stymie all efforts to have another go. For example:
connect scott/tiger
desc empinterim
Name Type
--------------- -------------------------------------------------
EMPNO NUMBER(4)
SURNAME VARCHAR2(15)
SALARY NUMBER(8,2)
exec dbms_redefinition.start_redef_table( -
> 'SCOTT','EMP','EMPINTERIM', -
> 'empno empid, ename surname, sal salary')
BEGIN dbms_redefinition.start_redef_table( 'SCOTT','EMP','EMPINTERIM', 'empno empid, ename surname, sal salary'); END;
*
ERROR at line 1:
ORA-12060: shape of prebuilt table does not match definition query
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 146
ORA-06512: at line 1
The procedure has errored out because the column in the interim table is called ‘EMPNO’, but what I typed was ‘EMPID’. No problem... I’ll just re-issue the command using the correct columns names:
exec dbms_redefinition.start_redef_table( -
> 'SCOTT','EMP','EMPINTERIM', -
> 'empno empno, ename surname, sal salary')
BEGIN dbms_redefinition.start_redef_table( 'SCOTT','EMP','EMPINTERIM', 'empno empno, ename surname, sal salary'); END;
*
ERROR at line 1:
ORA-12091: cannot online redefine table "SCOTT"."EMP" with materialized views
ORA-06512: at "SYS.DBMS_REDEFINITION", line 8
ORA-06512: at "SYS.DBMS_REDEFINITION", line 146
ORA-06512: at line 1
Only this fails as well, but for a completely different reason: the first run of the procedure built a materialized view log on EMP... but a table with materialized view logs can’t be the subject of a an online redefinition! I think they call that a 'Catch 22 situation'! Fortunately, there's an easy-enough workaround. Instead of trying to correct a mistake you make with the start_redef_table procedure, you simply have to abandon the entire redefinition exercise and start from scratch. A procedure is provided to completely wipe out any of the effects a start_redef might have caused:
exec dbms_redefinition.abort_redef_table(‘SCOTT’,’EMP’,’EMPINTERIM’)
As with most of the procedures we’ve been discussing on these pages, ABORT_REDEF takes just three arguments: the schema, the real table and the interim table. Executing it causes the materialised view log on the real table to be dropped, and that then allows you to start again, fresh. The interim table is still there, however, though it will be empty of any rows. If the mistake is rather in the way you’ve defined your interim table, the correction is simply to drop it and create it afresh, this time with the right characteristics
Thanks,
Mohan Reddy G
|
|
|
|
|
|
| Re: partition key modification [message #191935 is a reply to message #191925] |
Fri, 08 September 2006 14:52 |
kimant
Messages: 201
Registered: August 2006
Location: Denmark
|
Senior Member |
|
|
Hear hear!
And I really hates the fact that he tries to make it so difficult, when it is so simple:
Just make the columns wider 
Example:
SQL>
SQL> CREATE TABLE t AS SELECT * FROM dual;
Tabel er oprettet.
SQL>
SQL> CREATE MATERIALIZED VIEW m
2 REFRESH COMPLETE ON DEMAND
3 WITH ROWID
4 AS
5 SELECT * FROM t;
Materialiseret view er oprettet.
SQL>
SQL> ALTER TABLE t MODIFY (dummy VARCHAR2(2));
Tabel er ændret.
SQL>
SQL> DESC t;
Navn NULL? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(2)
SQL>
SQL> DESC m;
Navn NULL? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
SQL>
SQL> BEGIN
2 dbms_mview.REFRESH('M');
3 END;
4 /
PL/SQL-procedure er udført.
SQL>
SQL> DESC m;
Navn NULL? Type
----------------------------------------- -------- ----------------------------
DUMMY VARCHAR2(1)
SQL>
SQL> ALTER TABLE m MODIFY (dummy VARCHAR2(2));
Tabel er ændret.
SQL>
SQL> UPDATE t SET dummy = 'XX';
1 række er opdateret.
SQL>
SQL> COMMIT;
Bekræftelse er udført.
SQL>
SQL> BEGIN
2 dbms_mview.REFRESH('M');
3 END;
4 /
PL/SQL-procedure er udført.
SQL>
SQL> SELECT * FROM M;
DU
--
XX
SQL>
SQL> DROP MATERIALIZED VIEW m;
Materialiseret view er droppet.
SQL>
SQL> DROP TABLE t;
Tabel er droppet.
SQL>
Have fun
Kim Anthonisen
|
|
|
|
Goto Forum:
Current Time: Fri Sep 20 09:47:17 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
