  |
| Transporting a partition |
- In this example, we see how to use the transportable tablespaces feature to periodically load data into a large fact table in a data warehousing database.
- For this, suppose that an enterprise data warehouse has a large table
sales. - The table is partitioned by date, with data for each month stored in its own partition.
- In addition, local indexes are build so that you dont have to rebuild global indexes when dropping the oldest partition from the table.
- Every month you need to load one month of data into the
salestable, stored in its own partition.
 | Set up the fact table: sales. In the data warehouse database (dwr-db).
|
SQL> create table dwr.sales (invoice_no number, 2 sale_year int not null, 3 sale_month int not null, 4 sale_day int not null) 5 partition by range (sale_year, sale_month, sale_day) 6 (partition jan05 values less than (2005, 2, 1), 7 partition feb05 values less than (2005, 3, 1), 8 partition mar05 values less than (2005, 4, 1), 9 partition apr05 values less than (2005, 5, 1), 10 partition may05 values less than (2005, 6, 1), 11 partition jun05 values less than (2005, 7, 1)) 12 tablespace users; -- create local index. SQL> create index dwr.sales_index on dwr.sales(invoice_no) local; -- Now let's insert some data into table sales. Run the PL/SQL block below to insert some sample data into sales declare
month number :=1;
day number :=1;
begin
for i in 1..30 loop
insert into dwr.sales values (i, 2005, month, day);
if mod(i,5) = 0 then
month := month +1;
day := day +1;
end if;
end loop;
end;
|
-- Check that all partitions (table and index) are in the same tablespace (users):
SQL> select tablespace_name, segment_name, segment_type, partition_name 2 from dba_segments 3 where owner = 'DWR'; TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME ------------------ --------------- ------------------ ------------------ USERS SALES TABLE PARTITION JAN05 USERS SALES TABLE PARTITION FEB05 USERS SALES TABLE PARTITION MAR05 USERS SALES TABLE PARTITION APR05 USERS SALES TABLE PARTITION MAY05 USERS SALES TABLE PARTITION JUN05 USERS SALES_INDEX INDEX PARTITION JAN05 USERS SALES_INDEX INDEX PARTITION FEB05 USERS SALES_INDEX INDEX PARTITION MAR05 USERS SALES_INDEX INDEX PARTITION APR05 USERS SALES_INDEX INDEX PARTITION MAY05 USERS SALES_INDEX INDEX PARTITION JUN05 12 rows selected.
| Prepare the new data for loading in the warehouse The month of July has ended and you need to load the July data into the sales table. In the staging database, perform the steps below:
|
SQL> Create smallfile tablespace ts_jul
datafile '/u01/app/oracle/oradata/test112/ts_jul_1.dbf'
size 5M logging
extent management local
segment space management auto;
SQL> create table hr.jul_sales (invoice_no number,
sale_year int not null,
sale_month int not null,
sale_day int not null)
tablespace ts_jul;
-- Load July data: (run the pl/sql script)
declare
day number :=1;
begin
for i in 40..70 loop
insert into hr.jul_sales values (i, 2005, 7, day);
day := day + 1;
end loop;
end;
-- Create index on jul_sales table:
SQL> create index hr.jul_sale_idx on hr.jul_sales(invoice_no) tablespace ts_jul;
-- Check table and index location
SQL> select tablespace_name, segment_name, segment_type, bytes
from dba_segments
where owner = 'HR'
order by segment_name, segment_type;
TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE BYTES
----------------- --------------- ------------------ ----------
...
TS_JUL JUL_SALES TABLE 65536
TS_JUL JUL_SALE_IDX INDEX 65536
...
| Transport tablespace to the data warehouse database Perform the steps below:
|
SQL> alter tablespace ts_jul read only; Tablespace altered.
(2) export metadata
oracle@quark:~$ expdp system dumpfile=expts_jul.dmp directory=data_pump_dir transport_tablespaces=ts_jul logfile=tts_exptsjul.log Export: Release 11.2.0.1.0 - Production on Fri Oct 08 15:34:45 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01": system/******** dumpfile=expts_jul.dmp directory=data_pump_dir transport_tablespaces=ts_jul logfile=tts_exptsjul.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/INDEX Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded ****************************************************************************** Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is: /u01/app/oracle/admin/tempdb/dpdump/expts_jul.dmp ****************************************************************************** Datafiles required for transportable tablespace TS_JUL: /u01/app/oracle/oradata/tempdb/ts_jul_1.dbf Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 15:35:35
(3)-(4) Copy export and tablespace data files to the dwr server
(5) Import the tablespace ts_jul to the dwr
oracle@dwr-srv:$ impdp system dumpfile=expts_jul.dmp directory=data_pump_dir transport_datafiles=/u01/app/oracle/oradata/dwr/ts_jul_1.dbf remap_schema=hr:dwr logfile=tts_import.log Import: Release 11.2.0.1.0 - Production on Fri Oct 08 18:10:20 2010 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. Password: Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01": system/******** dumpfile=expts_jul.dmp directory=data_pump_dir transport_datafiles=/u01/app/oracle/oradata/dwr/ts_jul_1.dbf remap_schema=hr:crm1 logfile=tts_import.log Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK Processing object type TRANSPORTABLE_EXPORT/TABLE Processing object type TRANSPORTABLE_EXPORT/INDEX Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 18:11:10 -- Check the import SQL> select count(*) from dwr.jul_sales; COUNT(*) ---------- 30
| Attach the July data to the Sales table At this point the tablespace ts_jul has been transported and the table jul_sales is available in the dwr database. It is not yet, however, available as a partition in the large sales table. For this we will need to:
|
SQL> alter table dwr.sales add partition jul05 values less than (2005, 8,1); Table altered.
(2) Attach the transported table jul_sales to the table sales by exchanging it with the new partition:
SQL> alter table dwr.sales exchange partition jul05 with table dwr.jul_sales 2 including indexes 3 without validation; Table altered.
- The exchange partition statement assigns the jul_sales data to the jul05 partition on the sales table.
- The data is not physically moved to the new partition. Instead database pointers and metadata information are updated.
- In fact, after the statement executes successfully, you can check that the data is now assigned to the sales table, and the jul_sales table is empty.
SQL> select count(*) from crm1.jul_sales; COUNT(*) ---------- 0 SQL> select sale_month, count(*) 2 from crm1.sales 3 group by sale_month; SALE_MONTH COUNT(*) ---------- ---------- 1 5 6 5 2 5 4 5 5 5 3 5 7 31 7 rows selected. SQL> select tablespace_name, segment_name, segment_type, partition_name 2 from dba_segments 3 where owner = 'DWR'; TABLESPACE_NAME SEGMENT_NAME SEGMENT_TYPE PARTITION_NAME ----------------- -------------- --------------- ------------------------------ USERS SALES TABLE PARTITION JAN05 USERS SALES TABLE PARTITION FEB05 USERS SALES TABLE PARTITION MAR05 USERS SALES TABLE PARTITION APR05 USERS SALES TABLE PARTITION MAY05 USERS SALES TABLE PARTITION JUN05 TS_JUL SALES TABLE PARTITION JUL05 USERS SALES_INDEX INDEX PARTITION JAN05 USERS SALES_INDEX INDEX PARTITION FEB05 USERS SALES_INDEX INDEX PARTITION MAR05 USERS SALES_INDEX INDEX PARTITION APR05 USERS SALES_INDEX INDEX PARTITION MAY05 USERS SALES_INDEX INDEX PARTITION JUN05 TS_JUL SALES_INDEX INDEX PARTITION JUL05 14 rows selected.
- You can now drop
jul_salestable. - You can also, if needed, make the tablespace ts_jul read write.
SQL> alter tablespace ts_jul read write; Tablespace altered.
