For example these operations can now be done online by adding the ONLINE keyword to the DDL:
ALTER TABLE ... SET UNUSED <column>
ALTER TABLE ... MOVE PARTITION <tablespace>
ALTER DATABASE MOVE DATAFILE
ALTER TABLE. ... DROP CONSTRAINT <constraint>
We will explore example of the first three.
Example 1 Move a table partition online:
Container database : CDB1
PDB with target table: SALESPDB
Partitioned table owner/table name : sales.sales
Step 1. Create partitioned table
The table is range-partitioned on date, and has 6 partitions.
Each partition is for a quarter of 2014 and will be stored in the same tablespace, ts_sales.
The table ddl is shown below. There are 6 partitions and all reside in the same tablespace TS_SALES.
CREATE TABLE
"SALES"."SALES"
(
"TIME_ID" DATE,
"AMOUNT" NUMBER(5,0)
) PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255
PARTITION BY RANGE
("TIME_ID")
(
PARTITION "P1"
VALUES LESS THAN (TO_DATE('2014-01-01', 'YYYY-MM-DD')) TABLESPACE
"TS_SALES" ,
PARTITION "P2"
VALUES LESS THAN (TO_DATE('2014-03-31 00:00:00', 'YYYY-MM-DD'))
TABLESPACE "TS_SALES" ,
PARTITION "P3"
VALUES LESS THAN (TO_DATE('2014-06-30 00:00:00', 'YYYY-MM-DD'))
TABLESPACE "TS_SALES" ,
PARTITION "P4"
VALUES LESS THAN (TO_DATE('2014-09-30 00:00:00', 'YYYY-MM-DD'))
TABLESPACE "TS_SALES" ,
PARTITION "P5"
VALUES LESS THAN (TO_DATE('2014-12-31 00:00:00', 'YYYY-MM-DD'))
TABLESPACE "TS_SALES" ,
PARTITION "PMAX"
VALUES LESS THAN (MAXVALUE)
);
Step 2. Populate it with some trial data (optional)
insert into sales
select to_date('01012014','ddmmyyyy') + rownum, 100 from dual connect by level <=365;
commit;
Step 3. Create a tablespace to which you will move a partition even as its avaialble for DML
SQL> conn
sys/oracle@localhost/salespdb as sysdba
Connected.
SQL> create tablespace ts_trial
datafile '/u01/app/oracle/oradata/cdb1/salespdb/ts_trial.ora' size 5m
autoextend on;
Tablespace created.
Step 4. Simulate an ongoing workload inserting into a specific partition
The script below creates 0.5 million rows in partition p4 by hard coding the time_id to 20th Sept 2014.
insert into sales select
to_date('20140920','yyyymmdd'),300
from dual connect by
level<=500000;
commit;
Step 5. In another session move the partition which is being accessed (partition p4)
First tried it without the ONLINE keyword -- it fails:
SQL> alter table sales.sales move partition
p4 tablespace ts_trial;
alter table sales.sales move partition p4
tablespace ts_trial
*
ERROR at line 1:
ORA-00054: resource busy and acquire with
NOWAIT specified or timeout expired
Next try it with the ONLINE keyword, repeat the insert to make sure its inserting - this time, it is successful
SQL> alter table
sales.sales move partition p4 tablespace ts_trial online;
Table altered.
Step 6. Count the number of records in it after the simulated workload has completed (optional)
SQL> select count(*) from
sales.sales partition(p4);
COUNT(*)
----------
500092
The ONLINE ALTER TABLE capability makes table available for applications even when administrative actions are being done on it.
Some exceptions which prevent online ALTER TABLE:
Below are some of the conditions which prevent online ALTER TABLE modification.
- Supplemental logging enabled
- Table being altered has bitmap indexes
Example 2: Performing an ALTER TABLE... SET UNUSED (COLUMN) online
In this example a third column is being added to the sales table and it will be set to UNUSED state even as a session is accessing the column.
SQL>
alter table sales.sales add region_id number(3);
Table
altered.
Like the first example, dml operation involving the soon-to-be-dropped column is started in a different session.
SQL> insert into sales
2 select
to_date('22092014','ddmmyyyy'),333,3 from dual
3 connect by level
<=500000;
500000 rows created.
After the operation was underway, the alter table operation is attempted in the original session.
It results in an error.
SQL>
alter table sales.sales set unused (region_id);
alter
table sales.sales set unused (region_id)
*
ERROR
at line 1:
ORA-00054:
resource busy and acquire with NOWAIT specified or timeout expired
Restart the dml operation and retry the alter table with the online modifier -- it succeeds this time after the dml session commits. It waits and processes after the DML in the other session commits.
SQL> alter table sales set
unused(region_id) online;
Table altered.
SQL>
desc sales;
Name
Null? Type
-----------------------------------------
-------- ----------------------------
TIME_ID DATE
AMOUNT NUMBER(5)
Table columns can be set to UNUSED before dropping them in a maintenance window because dropping columns on large tables can be time consuming and resource intensive.
Example 3: Move a datafile online.
Starting Oracle Database 12c, it is possible to move a database datafile to a different location even as it is being accessed by the applications. To explore this feature using the test database, a trial workload is created in one session first to insert 900k records into the sales table. The contents of the test script ins.sql which will be run to simulate an active workload:
insert
into sales select to_date('10092014','ddmmyyyy'),444
from dual connect by level<=900000;
commit;
Starting the trial workload :
[oracle@laboms
~]$ sqlplus sales/sales@localhost:1521/salespdb
SQL*Plus:
Release 12.1.0.1.0 Production on Wed Apr 23 17:08:43 2014
Copyright
(c) 1982, 2013, Oracle. All rights
reserved.
Last
Successful login time: Wed Apr 23 2014 16:54:27 +08:00
Connected
to:
Oracle
Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With
the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and
Unified Auditing options
SQL>
SQL>
@ins.sql;
900000
rows created.
In another session, the tablespace into which the trial workload is inserting data is moved using the alter ... move datafile. The datafile ts_trial.ora is being moved to a different location.
SQL
> alter database move datafile
'/u01/app/oracle/oradata/cdb1/salespdb/ts_trial.ora' to
'/u01/app/oracle/oradata/tmp/ts_trial.ora';
Database
altered.
The datafile move was possible even as the workload was executing.
Checking the content of v$datafile after the alter the highlighted file has been moved to the requested location:
NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/cdb1/root/undo.ora
/u01/app/oracle/oradata/cdb1/salespdb/system.ora
/u01/app/oracle/oradata/cdb1/salespdb/sysaux.ora
/u01/app/oracle/oradata/cdb1/salespdb/users.ora
/u01/app/oracle/oradata/cdb1/salespdb/ts_sales.ora
/u01/app/oracle/oradata/tmp/ts_trial.ora
6 rows selected.
No comments:
Post a Comment