Compare and Fix the Object Data in Oracle 11g:
The DBMS_COMPARISON package provides interfaces to compare and sync database objects at different databases. The index columns in a comparison must uniquely identify every row involved in a comparison.
The following constraints satisfy this requirement:
- A primary key constraint
- A unique constraint on one or more non-NULL columns
If these constraints are not present on a table, then use the index_schema_name and index_name parameters in the CREATE_COMPARISON procedure to specify an index whose columns satisfy this requirement.
Simple Test:
drop table test.x PURGE;
drop table test2.x PURGE;
create table test.x (eno number(10), ename varchar2(20), sal number(10));
create table test2.x (eno number(10), ename varchar2(20), sal number(10));
alter table test.x add constraint x_pk primary key (eno);
alter table test2.x add constraint x_pk primary key (eno);
insert into test.x values (100, 'VJ', 100000);
insert into test.x values (101, 'ASHRAY', 100001);
insert into test.x values (102, 'DUMPA', 200000);
insert into test.x values (103, 'REDDY', 300000);
commit;
insert into test2.x values (100, 'VJ', 100000);
insert into test2.x values (101, 'ASHRAY', 100000);
insert into test2.x values (102, 'DUMPA', 200001);
insert into test2.x values (103, 'REDDY', 300000);
commit;
select * from test.x;
select * from test2.x;
-- Drop the existing comparison:
SELECT comparison_name, schema_name FROM dba_comparison;
BEGIN
dbms_comparison.drop_comparison (comparison_name => 'comp1');
END;
/
-- Create the comparison:
-- dblink_name is NULL because both schemas on the same database.
BEGIN
dbms_comparison.create_comparison (comparison_name => 'comp1',
schema_name => 'test',
object_name => 'x',
dblink_name => NULL,
remote_schema_name => 'test2',
remote_object_name => 'x'
);
END;
/
-- Check the difference "YES/NO":
set serveroutput on size 99999;
DECLARE
CONSISTENT BOOLEAN;
scan_info dbms_comparison.comparison_type;
BEGIN
CONSISTENT :=
dbms_comparison.compare (comparison_name => 'comp1',
scan_info => scan_info,
perform_row_dif => TRUE
);
DBMS_OUTPUT.put_line ('Scan ID: ' || scan_info.scan_id);
IF CONSISTENT = TRUE
THEN
DBMS_OUTPUT.put_line ('No differences were found.');
ELSE
DBMS_OUTPUT.put_line ('Differences were found.');
END IF;
END;
/
Scan ID: 11
Differences were found.
PL/SQL procedure successfully completed.
-- Comparison Summary:
-- Where scan_id is from the above dbms_comparison.compare PL/SQL block.
set linesize 120;
COL schema_name for a20;
COL object_name for a20;
COL comparison_name for a20;
SELECT s.scan_id, c.comparison_name, c.schema_name, c.object_name,
s.current_dif_count
FROM user_comparison c, user_comparison_scan_summary s
WHERE c.comparison_name = s.comparison_name AND s.scan_id = 11;
-- Comparison Details:
set linesize 120;
COL record_value for a50;
COL local_rowid for a12;
COL remote_rowid format a12;
SELECT c.column_name, r.index_value record_value,
CASE
WHEN r.local_rowid IS NULL
THEN 'No'
ELSE 'Yes'
END local_rowid,
CASE
WHEN r.remote_rowid IS NULL
THEN 'No'
ELSE 'Yes'
END remote_rowid
FROM dba_comparison_columns c,
dba_comparison_row_dif r,
dba_comparison_scan s
WHERE c.comparison_name = 'COMP1'
AND r.scan_id = s.scan_id
-- AND s.last_update_time > SYSTIMESTAMP - 1 / 24 / 12 -- Last 5 min.
AND r.status = 'DIF'
AND c.index_column = 'Y'
AND c.comparison_name = r.comparison_name
ORDER BY r.index_value;
-- Fix the difference in the remote table:
DECLARE
scan_info dbms_comparison.comparison_type;
BEGIN
dbms_comparison.converge
(comparison_name => 'comp1',
scan_id => 11,
scan_info => scan_info,
converge_options => dbms_comparison.cmp_converge_local_wins
);
DBMS_OUTPUT.put_line ('Local Rows Merged: ' || scan_info.loc_rows_merged);
DBMS_OUTPUT.put_line ('Remote Rows Merged: ' || scan_info.rmt_rows_merged);
DBMS_OUTPUT.put_line ('Local Rows Deleted: ' || scan_info.loc_rows_deleted);
DBMS_OUTPUT.put_line ('Remote Rows Deleted: ' || scan_info.rmt_rows_deleted);
END;
/
Local Rows Merged: 0
Remote Rows Merged: 2
Local Rows Deleted: 0
Remote Rows Deleted: 0
PL/SQL procedure successfully completed.
Results:
Before:
select * from test.x;
select * from test2.x;
SQL> select * from test.x;
ENO ENAME SAL
---------- -------------------- ----------
100 VJ 100000
101 ASHRAY 100001
102 DUMPA 200000
103 REDDY 300000
SQL> select * from test2.x;
ENO ENAME SAL
---------- -------------------- ----------
100 VJ 100000
101 ASHRAY 100000 <=== Old
102 DUMPA 200001 <=== Old
103 REDDY 300000
After:
select * from test.x;
select * from test2.x;
SQL> select * from test.x;
ENO ENAME SAL
---------- -------------------- ----------
100 VJ 100000
101 ASHRAY 100001
102 DUMPA 200000
103 REDDY 300000
SQL> select * from test2.x;
ENO ENAME SAL
---------- -------------------- ----------
100 VJ 100000
101 ASHRAY 100001 <=== New
102 DUMPA 200000 <=== New
103 REDDY 300000
SQL>
Q:
a.
Can I fix the CLOB/BLOB column data?
No, the DBMS_COMPARISON package cannot compare data in columns of the following data types:
- LONG, LONG RAW, ROWID, UROWID, CLOB, NCLOB, BLOB and BFILE
- User-defined types (including object types, REFs, varrays, and nested tables)
- Oracle-supplied types (including any types, XML types, spatial types, and media types)
b.
Can I fix the object data in 11g by comparing the object in 10g?
Yes, The local database that runs the DBMS_COMPARISON package must be 11g but the remote database must be 10gR1 and up.
c.
Can I compare packages/procedure/functions?
No, the DBMS_COMPARISON package can compare the following types of database objects:
Tables, Single-table views, Materialized views, Synonyms for tables, single-table views and materialized views.
d.
Do I need to have primary key on compare object?
No, but each column in the single-column/composite index must either have a NOT NULL constraint or be part of the primary key.
e.
What privileges do I need to compare the objects?
Granting EXECUTE on CREATE_COMPARISON package to selected users or roles. (or) Granting EXECUTE_CATALOG_ROLE to selected users or roles.
Tuesday, May 25, 2010
Tuesday, April 20, 2010
No Segment Vs Invisible Vs Unusable Indexes in Oracle 11gR2:
No Segment Vs Invisible Vs Unusable Indexes in Oracle 11gR2:
No Segment/Virtual Indexes (8i and up):
Virtual indexes allow us to simulate the existence of an index and test its impact without actually building the actual index.
Only sessions marked for Virtual Index usage will be affected by their existence. Their creation does not affect new sessions.
Virtual indexes will be used only when the initialization parameter "_use_nosegment_indexes" is set to TRUE.
The Rule based optimizer does not recognize Virtual Indexes but the CBO does recognize them.
Dictionary view DBA_SEGMENTS will not show entries for Virtual Indexes. [DBA|ALL|USER]_OBJECTS view will have an entry.
They are permanent and continue to exist unless dropped. Make sure to drop virtual indexes after analysis and tuning is completed.
Statistics can be gathered on virtual indexes in the same way as regular indexes.
Oracle will prevent us from creating another virtual index with the same column list, but it will allow us to create a real index with the same column list.
To detect a virtual index in the database run the following SQL (these indexes don't have any columns in dba_ind_columns):
SELECT index_owner, index_name
FROM dba_ind_columns
WHERE index_name NOT LIKE 'BIN$%'
MINUS
SELECT owner, index_name
FROM dba_indexes;
Invisible Indexes (11gR1 and up):
Beginning with Release 11g, you can create invisible indexes. An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level.
ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;
ALTER SYSTEM SET optimizer_use_invisible_indexes=TRUE;
Using invisible indexes, you can do the following:
- Test the optimizer behave on the application before dropping an index.
- Use the index for certain operations or modules of an application without affecting the overall performance of the application.
- It's also useful for some DELETE operations in Database Machine (Oracle EXADATA).
An invisible index is maintained during DML statements.
Statistics can be gathered on an invisible indexes.
The current visibility status of an index is indicated by the VISIBILITY column of the [DBA|ALL|USER]_INDEXES views.
Using ALTER INDEX we can make the index INVISIBLE or VISIBLE.
We can REBUILD an invisible index.
INDEX hit will not work with an invisible index.
/*+ opt_param('optimizer_use_invisible_indexes','TRUE') */ -- we CAN'T use opt_param hint
We CAN'T modify index partition to Invisible, it's at Index level.
Unusable Indexes (11gR2):
Zero Sized Unusable Indexes:
In Oracle 11gR2 when the index or index partition marked as unusable, oracle automatically drop any index segment space.
This means we can release OLD(Not Active) index partition space and we can keep NEW(Active) partition indexes.
ALTER INDEX MODIFY PARTITION UNSABLE;
Once we make an index unusable, we will not see the segment in [DBA|ALL|USER]_SEGMENTS view, but we will have entry in [DBA|ALL|USER]_IND_PARTITIONS view.
The column SEGMENT_CREATED(column value: YES/NO) shows whether a segment exists for that partition.
unlike previous oracle releases(< 11gR2), we can query the unusable partition data, but it uses full table scan instead of an index scan, because the corresponding index partition is unusable and cannot be used by the optimizer.
When you query against NEW(Active) partition, it uses the corresponding index scan.
If a table is truncated, the unusable index partition will become usable again and Oracle Database will re-create the segment.
No Segment/Virtual Indexes (8i and up):
Virtual indexes allow us to simulate the existence of an index and test its impact without actually building the actual index.
Only sessions marked for Virtual Index usage will be affected by their existence. Their creation does not affect new sessions.
Virtual indexes will be used only when the initialization parameter "_use_nosegment_indexes" is set to TRUE.
The Rule based optimizer does not recognize Virtual Indexes but the CBO does recognize them.
Dictionary view DBA_SEGMENTS will not show entries for Virtual Indexes. [DBA|ALL|USER]_OBJECTS view will have an entry.
They are permanent and continue to exist unless dropped. Make sure to drop virtual indexes after analysis and tuning is completed.
Statistics can be gathered on virtual indexes in the same way as regular indexes.
Oracle will prevent us from creating another virtual index with the same column list, but it will allow us to create a real index with the same column list.
To detect a virtual index in the database run the following SQL (these indexes don't have any columns in dba_ind_columns):
SELECT index_owner, index_name
FROM dba_ind_columns
WHERE index_name NOT LIKE 'BIN$%'
MINUS
SELECT owner, index_name
FROM dba_indexes;
Invisible Indexes (11gR1 and up):
Beginning with Release 11g, you can create invisible indexes. An invisible index is an index that is ignored by the optimizer unless you explicitly set the OPTIMIZER_USE_INVISIBLE_INDEXES initialization parameter to TRUE at the session or system level.
ALTER SESSION SET optimizer_use_invisible_indexes=TRUE;
ALTER SYSTEM SET optimizer_use_invisible_indexes=TRUE;
Using invisible indexes, you can do the following:
- Test the optimizer behave on the application before dropping an index.
- Use the index for certain operations or modules of an application without affecting the overall performance of the application.
- It's also useful for some DELETE operations in Database Machine (Oracle EXADATA).
An invisible index is maintained during DML statements.
Statistics can be gathered on an invisible indexes.
The current visibility status of an index is indicated by the VISIBILITY column of the [DBA|ALL|USER]_INDEXES views.
Using ALTER INDEX we can make the index INVISIBLE or VISIBLE.
We can REBUILD an invisible index.
INDEX hit will not work with an invisible index.
/*+ opt_param('optimizer_use_invisible_indexes','TRUE') */ -- we CAN'T use opt_param hint
We CAN'T modify index partition to Invisible, it's at Index level.
Unusable Indexes (11gR2):
Zero Sized Unusable Indexes:
In Oracle 11gR2 when the index or index partition marked as unusable, oracle automatically drop any index segment space.
This means we can release OLD(Not Active) index partition space and we can keep NEW(Active) partition indexes.
ALTER INDEX
Once we make an index unusable, we will not see the segment in [DBA|ALL|USER]_SEGMENTS view, but we will have entry in [DBA|ALL|USER]_IND_PARTITIONS view.
The column SEGMENT_CREATED(column value: YES/NO) shows whether a segment exists for that partition.
unlike previous oracle releases(< 11gR2), we can query the unusable partition data, but it uses full table scan instead of an index scan, because the corresponding index partition is unusable and cannot be used by the optimizer.
When you query against NEW(Active) partition, it uses the corresponding index scan.
If a table is truncated, the unusable index partition will become usable again and Oracle Database will re-create the segment.
Saturday, September 26, 2009
OEM Grid Control Maintenance Tasks - Weekly/Monthly:
OEM Grid Control Maintenance Tasks - Weekly/Monthly:
OMS Monthly Maintenance Tasks:
1. OEM database partition Maintenance:
a. Shut down all OMSs:
$OMS_HOME/bin/emctl stop oms
b. Connect as SYSMAN to the OEM database and run following two procedures:
SQL>connect sysman/xxxx
SQL>set timing on;
SQL>exec emd_maintenance.analyze_end_schema('SYSMAN');
SQL>commit;
SQL>exec emd_maintenance.partition_maintenance;
SQL>commit;
If you are in a scenario where you hit Bug 5357916 and can't apply the patch or upgrade to a version where the patch is available you need to do the following:
SQL>connect / as sysdba
SQL>alter system set job_queue_processes = 0;
SQL>connect sysman/xxxx
SQL>set timing on;
SQL>exec emd_maintenance.remove_em_dbms_jobs;
SQL>exec emd_maintenance.partition_maintenance;
SQL>@/sysman/admin/emdrep/sql/core/latest/admin/admin_recompile_invalid.sql
SQL>alter system set job_queue_processes = 10;
SQL>exec emd_maintenance.submit_em_dbms_jobs;
SQL>commit;
Note: The partition maintenance will be performed automatically if you have the following configuration:
Grid Control 10gR3 (10.2.0.3) or later with the repository installed in a 10.2.0.2 database or later.
c. Confirm the Partition maintenance was successful:
select count(1) from sysman.mgmt_metrics_raw where collection_timestamp < sysdate -9;
d. Restart all OMSs:
$OMS_HOME/bin/emctl start oms
2. Rebuild/Shrink tables and indexes as required:
OMS Weekly Maintenance Tasks:
1. Check and Clear OMS and OEM database system errors.
a. OMS:
Goto: Navigate in the Grid Console to Setup >> Management Services and Repository >> 'Errors' Tab
b. OEM Database alertlog errors:
Goto: $ORACLE_BASE/POEM/bdump/alert_POEM[12].log
2. Fix target metric collection errors.
a. Cluster agent:
$AGENT_HOME/bin/agentca -d -c catlmsxt261 -- Discover
$AGENT_HOME/bin/agentca -f -c catlmsxt261 -- Reconfigure
$AGENT_HOME/bin/emctl clearstate agent
$AGENT_HOME/bin/emctl status agent
b. Standalone agent:
$AGENT_HOME/bin/agentca -f
$AGENT_HOME/bin/emctl clearstate agent
$AGENT_HOME/bin/emctl status agent
3. Start or remove targets in DOWN status and also remove all the Duplicate Targets.
4. Force delete all the "Deleted Targets"
Goto: Navigate in the Grid Console to Setup >> Management Services and Repository >> 'Overview' Tab >> Deleted Targets list
(or DELETE FROM SYSMAN.MGMT_TARGETS_DELETE; COMMIT ;)
5. Clear Critical and Warning alerts:
Goto: Alerts tab on the OEM main page.
6. Analyze Metric Rollup Tables as required:
MGMT_METRICS_RAW
MGMT_METRICS_1DAY
MGMT_METRICS_1HOUR
exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_RAW', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);
exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_1HOUR', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);
exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_1DAY', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);
Reference:
Doc ID: 456101.1
Doc ID: 370695.1
OMS Monthly Maintenance Tasks:
1. OEM database partition Maintenance:
a. Shut down all OMSs:
$OMS_HOME/bin/emctl stop oms
b. Connect as SYSMAN to the OEM database and run following two procedures:
SQL>connect sysman/xxxx
SQL>set timing on;
SQL>exec emd_maintenance.analyze_end_schema('SYSMAN');
SQL>commit;
SQL>exec emd_maintenance.partition_maintenance;
SQL>commit;
If you are in a scenario where you hit Bug 5357916 and can't apply the patch or upgrade to a version where the patch is available you need to do the following:
SQL>connect / as sysdba
SQL>alter system set job_queue_processes = 0;
SQL>connect sysman/xxxx
SQL>set timing on;
SQL>exec emd_maintenance.remove_em_dbms_jobs;
SQL>exec emd_maintenance.partition_maintenance;
SQL>@
SQL>alter system set job_queue_processes = 10;
SQL>exec emd_maintenance.submit_em_dbms_jobs;
SQL>commit;
Note: The partition maintenance will be performed automatically if you have the following configuration:
Grid Control 10gR3 (10.2.0.3) or later with the repository installed in a 10.2.0.2 database or later.
c. Confirm the Partition maintenance was successful:
select count(1) from sysman.mgmt_metrics_raw where collection_timestamp < sysdate -9;
d. Restart all OMSs:
$OMS_HOME/bin/emctl start oms
2. Rebuild/Shrink tables and indexes as required:
OMS Weekly Maintenance Tasks:
1. Check and Clear OMS and OEM database system errors.
a. OMS:
Goto: Navigate in the Grid Console to Setup >> Management Services and Repository >> 'Errors' Tab
b. OEM Database alertlog errors:
Goto: $ORACLE_BASE/POEM/bdump/alert_POEM[12].log
2. Fix target metric collection errors.
a. Cluster agent:
$AGENT_HOME/bin/agentca -d -c catlmsxt261 -- Discover
$AGENT_HOME/bin/agentca -f -c catlmsxt261 -- Reconfigure
$AGENT_HOME/bin/emctl clearstate agent
$AGENT_HOME/bin/emctl status agent
b. Standalone agent:
$AGENT_HOME/bin/agentca -f
$AGENT_HOME/bin/emctl clearstate agent
$AGENT_HOME/bin/emctl status agent
3. Start or remove targets in DOWN status and also remove all the Duplicate Targets.
4. Force delete all the "Deleted Targets"
Goto: Navigate in the Grid Console to Setup >> Management Services and Repository >> 'Overview' Tab >> Deleted Targets list
(or DELETE FROM SYSMAN.MGMT_TARGETS_DELETE; COMMIT ;)
5. Clear Critical and Warning alerts:
Goto: Alerts tab on the OEM main page.
6. Analyze Metric Rollup Tables as required:
MGMT_METRICS_RAW
MGMT_METRICS_1DAY
MGMT_METRICS_1HOUR
exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_RAW', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);
exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_1HOUR', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);
exec dbms_stats.gather_table_stats('SYSMAN', 'MGMT_METRICS_1DAY', null, .000001, false, 'for all indexed columns', null, 'global', true, null, null, null);
Reference:
Doc ID: 456101.1
Doc ID: 370695.1
Monday, September 21, 2009
How to automate partition (Range - Seconds to Years) creation using Oracle Interval Partitioning in 11g:
How to automate partition (Range - Seconds to Years) creation using Oracle Interval Partitioning in 11g:
1. Create a composite Interval-List/Interval-Range partition table:
drop table x;
create table x (create_date date, site_id number, all_data varchar2(100))
PARTITION BY RANGE (create_date)
INTERVAL (NUMTODSINTERVAL(1,'HOUR'))
SUBPARTITION BY LIST (site_id)
(
PARTITION part_01 values LESS THAN (TO_DATE('18-SEP-2009 16:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_01_01 VALUES (01),
SUBPARTITION part_01_126 VALUES (126),
SUBPARTITION part_01_132 VALUES (132),
SUBPARTITION part_01_135 VALUES (135)
),
PARTITION part_02 values LESS THAN (TO_DATE('18-SEP-2009 17:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_02_01 VALUES (01),
SUBPARTITION part_02_126 VALUES (126),
SUBPARTITION part_02_132 VALUES (132),
SUBPARTITION part_02_135 VALUES (135)
),
PARTITION part_03 values LESS THAN (TO_DATE('18-SEP-2009 18:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_03_01 VALUES (01),
SUBPARTITION part_03_126 VALUES (126),
SUBPARTITION part_03_132 VALUES (132),
SUBPARTITION part_03_135 VALUES (135)
),
PARTITION part_04 values LESS THAN (TO_DATE('18-SEP-2009 19:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_04_01 VALUES (01),
SUBPARTITION part_04_126 VALUES (126),
SUBPARTITION part_04_132 VALUES (132),
SUBPARTITION part_04_135 VALUES (135)
),
PARTITION part_05 values LESS THAN (TO_DATE('18-SEP-2009 20:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_05_01 VALUES (01),
SUBPARTITION part_05_126 VALUES (126),
SUBPARTITION part_05_132 VALUES (132),
SUBPARTITION part_05_135 VALUES (135)
),
PARTITION part_06 values LESS THAN (TO_DATE('18-SEP-2009 21:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_06_01 VALUES (01),
SUBPARTITION part_06_126 VALUES (126),
SUBPARTITION part_06_132 VALUES (132),
SUBPARTITION part_06_135 VALUES (135)
),
PARTITION part_07 values LESS THAN (TO_DATE('18-SEP-2009 22:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_07_01 VALUES (01),
SUBPARTITION part_07_126 VALUES (126),
SUBPARTITION part_07_132 VALUES (132),
SUBPARTITION part_07_135 VALUES (135)
),
PARTITION part_08 values LESS THAN (TO_DATE('18-SEP-2009 23:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_08_01 VALUES (01),
SUBPARTITION part_08_126 VALUES (126),
SUBPARTITION part_08_132 VALUES (132),
SUBPARTITION part_08_135 VALUES (135)
)
);
2. Create bitmap LOCAL indexes:
create bitmap index idx1_x on x(create_date) local;
create bitmap index idx2_x on x(site_id) local;
3. Insert and Check the data in each partition:
truncate table x;
insert into x values(TO_DATE('18-SEP-2009 15:30:00','DD-MON-YYYY HH24:MI:SS'),01,'18/SEP/09 of 15''Th HR - SITE 01');
insert into x values(TO_DATE('18-SEP-2009 16:30:00','DD-MON-YYYY HH24:MI:SS'),126,'18/SEP/09 of 16''Th HR - SITE 126');
insert into x values(TO_DATE('18-SEP-2009 17:30:00','DD-MON-YYYY HH24:MI:SS'),132,'18/SEP/09 of 17''Th HR - SITE 132');
insert into x values(TO_DATE('18-SEP-2009 18:30:00','DD-MON-YYYY HH24:MI:SS'),135,'18/SEP/09 of 18''Th HR - SITE 125');
insert into x values(TO_DATE('18-SEP-2009 19:30:00','DD-MON-YYYY HH24:MI:SS'),01,'18/SEP/09 of 19''Th HR - SITE 01');
insert into x values(TO_DATE('18-SEP-2009 20:30:00','DD-MON-YYYY HH24:MI:SS'),126,'18/SEP/09 of 20''Th HR - SITE 126');
insert into x values(TO_DATE('18-SEP-2009 21:30:00','DD-MON-YYYY HH24:MI:SS'),132,'18/SEP/09 of 21''Th HR - SITE 132');
insert into x values(TO_DATE('18-SEP-2009 22:30:00','DD-MON-YYYY HH24:MI:SS'),135,'18/SEP/09 of 22''Th HR - SITE 135');
commit;
set linesize 200;
col all_data for a50;
SELECT * FROM x PARTITION (PART_01);
SELECT * FROM x PARTITION (PART_02);
SELECT * FROM x PARTITION (PART_03);
SELECT * FROM x PARTITION (PART_04);
SELECT * FROM x PARTITION (PART_05);
SELECT * FROM x PARTITION (PART_06);
SELECT * FROM x PARTITION (PART_07);
SELECT * FROM x PARTITION (PART_08);
4. Check and record the HIGH VALUE of the partition table:
col table_name for a10;
col partition_name for a15;
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';
DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
8 rows selected.
5. Check and record the HIGH VALUE of the Subpartition table:
col subpartition_name for a20;
col high_value for a10;
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';
DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
X PART_01 PART_01_135 135
X PART_01 PART_01_132 132
X PART_01 PART_01_126 126
X PART_01 PART_01_01 01
X PART_02 PART_02_135 135
X PART_02 PART_02_132 132
X PART_02 PART_02_126 126
X PART_02 PART_02_01 01
X PART_03 PART_03_135 135
X PART_03 PART_03_132 132
X PART_03 PART_03_126 126
X PART_03 PART_03_01 01
X PART_04 PART_04_135 135
X PART_04 PART_04_132 132
X PART_04 PART_04_126 126
X PART_04 PART_04_01 01
X PART_05 PART_05_135 135
X PART_05 PART_05_132 132
X PART_05 PART_05_126 126
X PART_05 PART_05_01 01
X PART_06 PART_06_135 135
X PART_06 PART_06_132 132
X PART_06 PART_06_126 126
X PART_06 PART_06_01 01
X PART_07 PART_07_135 135
X PART_07 PART_07_132 132
X PART_07 PART_07_126 126
X PART_07 PART_07_01 01
X PART_08 PART_08_135 135
X PART_08 PART_08_132 132
X PART_08 PART_08_126 126
X PART_08 PART_08_01 01
32 rows selected.
6. Insert new record (Next day 15'th Hour) and validate the partition creation:
insert into x values(TO_DATE('19-SEP-2009 15:30:00','DD-MON-YYYY HH24:MI:SS'),01,'19/SEP/09 of 15''Th HR - SITE 01');
commit;
col high_value for a10
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';
DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
..
..
..
X PART_08 PART_08_01 01
X SYS_P42 SYS_SUBP41 DEFAULT <------- New Partition
33 rows selected.
col high_value for a80
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';
DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P42 TO_DATE(' 2009-09-19 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA <------- New Partition
9 rows selected.
7. Insert new record(Next day 17'th Hour) and validate the partition creation:
insert into x values(TO_DATE('19-SEP-2009 17:30:00','DD-MON-YYYY HH24:MI:SS'),132,'19/SEP/09 of 17''Th HR - SITE 132');
commit;
col high_value for a10
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';
DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
..
..
..
X PART_08 PART_08_01 01
X SYS_P42 SYS_SUBP41 DEFAULT
X SYS_P44 SYS_SUBP43 DEFAULT <------- New Partition
34 rows selected.
col high_value for a80
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';
DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P42 TO_DATE(' 2009-09-19 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P44 TO_DATE(' 2009-09-19 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA <------- New Partition
10 rows selected.
8. Insert new record (Next day 16'th Hour) and validate the partition creation:
# This record data is one hour less than the previous record in Step 7.
# No problem with creating the partition even with jumbled hourly data!!!!
insert into x values(TO_DATE('19-SEP-2009 16:30:00','DD-MON-YYYY HH24:MI:SS'),126,'19/SEP/09 of 16''Th HR - SITE 126');
commit;
col high_value for a10
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';
DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
..
..
..
X PART_08 PART_08_01 01
X SYS_P42 SYS_SUBP41 DEFAULT
X SYS_P44 SYS_SUBP43 DEFAULT
X SYS_P46 SYS_SUBP45 DEFAULT <------- New Partition
35 rows selected.
col high_value for a80
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';
DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P42 TO_DATE(' 2009-09-19 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P44 TO_DATE(' 2009-09-19 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P46 TO_DATE(' 2009-09-19 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA <------- New Partition
11 rows selected.
Side Note:
If you want to create different intervals, here are the available options:
NUMTODSINTERVAL - An Interval Day to Second:
'DAY' - INTERVAL (NUMTODSINTERVAL(1,'DAY')) or INTERVAL (NUMTODSINTERVAL(7,'DAY'))
'HOUR' - INTERVAL (NUMTODSINTERVAL(1,'HOUR')) or INTERVAL (NUMTODSINTERVAL(8,'HOUR'))
'MINUTE' - INTERVAL (NUMTODSINTERVAL(1,'MINUTE')) or INTERVAL (NUMTODSINTERVAL(15,'MINUTE'))
'SECOND' - INTERVAL (NUMTODSINTERVAL(1,'SECOND')) or INTERVAL (NUMTODSINTERVAL(30,'SECOND'))
NUMTOYMINTERVAL - An Interval Year to Month:
'YEAR' - INTERVAL (NUMTOYMINTERVAL(1,'YEAR')) or INTERVAL (NUMTOYMINTERVAL(3,'YEAR'))
'MONTH' - INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) or INTERVAL (NUMTOYMINTERVAL(4,'MONTH'))
1. Create a composite Interval-List/Interval-Range partition table:
drop table x;
create table x (create_date date, site_id number, all_data varchar2(100))
PARTITION BY RANGE (create_date)
INTERVAL (NUMTODSINTERVAL(1,'HOUR'))
SUBPARTITION BY LIST (site_id)
(
PARTITION part_01 values LESS THAN (TO_DATE('18-SEP-2009 16:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_01_01 VALUES (01),
SUBPARTITION part_01_126 VALUES (126),
SUBPARTITION part_01_132 VALUES (132),
SUBPARTITION part_01_135 VALUES (135)
),
PARTITION part_02 values LESS THAN (TO_DATE('18-SEP-2009 17:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_02_01 VALUES (01),
SUBPARTITION part_02_126 VALUES (126),
SUBPARTITION part_02_132 VALUES (132),
SUBPARTITION part_02_135 VALUES (135)
),
PARTITION part_03 values LESS THAN (TO_DATE('18-SEP-2009 18:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_03_01 VALUES (01),
SUBPARTITION part_03_126 VALUES (126),
SUBPARTITION part_03_132 VALUES (132),
SUBPARTITION part_03_135 VALUES (135)
),
PARTITION part_04 values LESS THAN (TO_DATE('18-SEP-2009 19:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_04_01 VALUES (01),
SUBPARTITION part_04_126 VALUES (126),
SUBPARTITION part_04_132 VALUES (132),
SUBPARTITION part_04_135 VALUES (135)
),
PARTITION part_05 values LESS THAN (TO_DATE('18-SEP-2009 20:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_05_01 VALUES (01),
SUBPARTITION part_05_126 VALUES (126),
SUBPARTITION part_05_132 VALUES (132),
SUBPARTITION part_05_135 VALUES (135)
),
PARTITION part_06 values LESS THAN (TO_DATE('18-SEP-2009 21:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_06_01 VALUES (01),
SUBPARTITION part_06_126 VALUES (126),
SUBPARTITION part_06_132 VALUES (132),
SUBPARTITION part_06_135 VALUES (135)
),
PARTITION part_07 values LESS THAN (TO_DATE('18-SEP-2009 22:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_07_01 VALUES (01),
SUBPARTITION part_07_126 VALUES (126),
SUBPARTITION part_07_132 VALUES (132),
SUBPARTITION part_07_135 VALUES (135)
),
PARTITION part_08 values LESS THAN (TO_DATE('18-SEP-2009 23:00:00','DD-MON-YYYY HH24:MI:SS'))
( SUBPARTITION part_08_01 VALUES (01),
SUBPARTITION part_08_126 VALUES (126),
SUBPARTITION part_08_132 VALUES (132),
SUBPARTITION part_08_135 VALUES (135)
)
);
2. Create bitmap LOCAL indexes:
create bitmap index idx1_x on x(create_date) local;
create bitmap index idx2_x on x(site_id) local;
3. Insert and Check the data in each partition:
truncate table x;
insert into x values(TO_DATE('18-SEP-2009 15:30:00','DD-MON-YYYY HH24:MI:SS'),01,'18/SEP/09 of 15''Th HR - SITE 01');
insert into x values(TO_DATE('18-SEP-2009 16:30:00','DD-MON-YYYY HH24:MI:SS'),126,'18/SEP/09 of 16''Th HR - SITE 126');
insert into x values(TO_DATE('18-SEP-2009 17:30:00','DD-MON-YYYY HH24:MI:SS'),132,'18/SEP/09 of 17''Th HR - SITE 132');
insert into x values(TO_DATE('18-SEP-2009 18:30:00','DD-MON-YYYY HH24:MI:SS'),135,'18/SEP/09 of 18''Th HR - SITE 125');
insert into x values(TO_DATE('18-SEP-2009 19:30:00','DD-MON-YYYY HH24:MI:SS'),01,'18/SEP/09 of 19''Th HR - SITE 01');
insert into x values(TO_DATE('18-SEP-2009 20:30:00','DD-MON-YYYY HH24:MI:SS'),126,'18/SEP/09 of 20''Th HR - SITE 126');
insert into x values(TO_DATE('18-SEP-2009 21:30:00','DD-MON-YYYY HH24:MI:SS'),132,'18/SEP/09 of 21''Th HR - SITE 132');
insert into x values(TO_DATE('18-SEP-2009 22:30:00','DD-MON-YYYY HH24:MI:SS'),135,'18/SEP/09 of 22''Th HR - SITE 135');
commit;
set linesize 200;
col all_data for a50;
SELECT * FROM x PARTITION (PART_01);
SELECT * FROM x PARTITION (PART_02);
SELECT * FROM x PARTITION (PART_03);
SELECT * FROM x PARTITION (PART_04);
SELECT * FROM x PARTITION (PART_05);
SELECT * FROM x PARTITION (PART_06);
SELECT * FROM x PARTITION (PART_07);
SELECT * FROM x PARTITION (PART_08);
4. Check and record the HIGH VALUE of the partition table:
col table_name for a10;
col partition_name for a15;
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';
DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
8 rows selected.
5. Check and record the HIGH VALUE of the Subpartition table:
col subpartition_name for a20;
col high_value for a10;
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';
DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
X PART_01 PART_01_135 135
X PART_01 PART_01_132 132
X PART_01 PART_01_126 126
X PART_01 PART_01_01 01
X PART_02 PART_02_135 135
X PART_02 PART_02_132 132
X PART_02 PART_02_126 126
X PART_02 PART_02_01 01
X PART_03 PART_03_135 135
X PART_03 PART_03_132 132
X PART_03 PART_03_126 126
X PART_03 PART_03_01 01
X PART_04 PART_04_135 135
X PART_04 PART_04_132 132
X PART_04 PART_04_126 126
X PART_04 PART_04_01 01
X PART_05 PART_05_135 135
X PART_05 PART_05_132 132
X PART_05 PART_05_126 126
X PART_05 PART_05_01 01
X PART_06 PART_06_135 135
X PART_06 PART_06_132 132
X PART_06 PART_06_126 126
X PART_06 PART_06_01 01
X PART_07 PART_07_135 135
X PART_07 PART_07_132 132
X PART_07 PART_07_126 126
X PART_07 PART_07_01 01
X PART_08 PART_08_135 135
X PART_08 PART_08_132 132
X PART_08 PART_08_126 126
X PART_08 PART_08_01 01
32 rows selected.
6. Insert new record (Next day 15'th Hour) and validate the partition creation:
insert into x values(TO_DATE('19-SEP-2009 15:30:00','DD-MON-YYYY HH24:MI:SS'),01,'19/SEP/09 of 15''Th HR - SITE 01');
commit;
col high_value for a10
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';
DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
..
..
..
X PART_08 PART_08_01 01
X SYS_P42 SYS_SUBP41 DEFAULT <------- New Partition
33 rows selected.
col high_value for a80
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';
DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P42 TO_DATE(' 2009-09-19 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA <------- New Partition
9 rows selected.
7. Insert new record(Next day 17'th Hour) and validate the partition creation:
insert into x values(TO_DATE('19-SEP-2009 17:30:00','DD-MON-YYYY HH24:MI:SS'),132,'19/SEP/09 of 17''Th HR - SITE 132');
commit;
col high_value for a10
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';
DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
..
..
..
X PART_08 PART_08_01 01
X SYS_P42 SYS_SUBP41 DEFAULT
X SYS_P44 SYS_SUBP43 DEFAULT <------- New Partition
34 rows selected.
col high_value for a80
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';
DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P42 TO_DATE(' 2009-09-19 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P44 TO_DATE(' 2009-09-19 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA <------- New Partition
10 rows selected.
8. Insert new record (Next day 16'th Hour) and validate the partition creation:
# This record data is one hour less than the previous record in Step 7.
# No problem with creating the partition even with jumbled hourly data!!!!
insert into x values(TO_DATE('19-SEP-2009 16:30:00','DD-MON-YYYY HH24:MI:SS'),126,'19/SEP/09 of 16''Th HR - SITE 126');
commit;
col high_value for a10
select table_name, partition_name, subpartition_name, high_value
from user_tab_subpartitions
where table_name = 'X';
DB Output:
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME HIGH_VALUE
---------- --------------- -------------------- ----------
..
..
..
X PART_08 PART_08_01 01
X SYS_P42 SYS_SUBP41 DEFAULT
X SYS_P44 SYS_SUBP43 DEFAULT
X SYS_P46 SYS_SUBP45 DEFAULT <------- New Partition
35 rows selected.
col high_value for a80
select table_name, partition_name, high_value
from user_tab_partitions
where table_name = 'X';
DB Output:
TABLE_NAME PARTITION_NAME HIGH_VALUE
---------- --------------- --------------------------------------------------------------------------------
X PART_01 TO_DATE(' 2009-09-18 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_02 TO_DATE(' 2009-09-18 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_03 TO_DATE(' 2009-09-18 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_04 TO_DATE(' 2009-09-18 19:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_05 TO_DATE(' 2009-09-18 20:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_06 TO_DATE(' 2009-09-18 21:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_07 TO_DATE(' 2009-09-18 22:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X PART_08 TO_DATE(' 2009-09-18 23:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P42 TO_DATE(' 2009-09-19 16:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P44 TO_DATE(' 2009-09-19 18:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
X SYS_P46 TO_DATE(' 2009-09-19 17:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA <------- New Partition
11 rows selected.
Side Note:
If you want to create different intervals, here are the available options:
NUMTODSINTERVAL - An Interval Day to Second:
'DAY' - INTERVAL (NUMTODSINTERVAL(1,'DAY')) or INTERVAL (NUMTODSINTERVAL(7,'DAY'))
'HOUR' - INTERVAL (NUMTODSINTERVAL(1,'HOUR')) or INTERVAL (NUMTODSINTERVAL(8,'HOUR'))
'MINUTE' - INTERVAL (NUMTODSINTERVAL(1,'MINUTE')) or INTERVAL (NUMTODSINTERVAL(15,'MINUTE'))
'SECOND' - INTERVAL (NUMTODSINTERVAL(1,'SECOND')) or INTERVAL (NUMTODSINTERVAL(30,'SECOND'))
NUMTOYMINTERVAL - An Interval Year to Month:
'YEAR' - INTERVAL (NUMTOYMINTERVAL(1,'YEAR')) or INTERVAL (NUMTOYMINTERVAL(3,'YEAR'))
'MONTH' - INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) or INTERVAL (NUMTOYMINTERVAL(4,'MONTH'))
Friday, April 17, 2009
How to Restore Database BCV backup copy to ASM storage:
How to Restore Database BCV backup copy to ASM storage:
How big is the database: 2TB
How often we take BCV copy: Daily
How often we delete Archive log's: Every 30 mns.
How much redo, does this DB generate per Hour: 70GB/24= ~3GB
Is this RAC database: Yes.
1.
Stop all instances of database:
srvctl stop database -d ORADB
2.
Check the services are offline:
crs_stat -t
3.
Dismount diskgroups on all instances:
alter diskgroup ORADB_T1_BACKUP_01 dismount;
alter diskgroup ORADB_T1_DATA_01 dismount;
alter diskgroup ORADB_T1_DATA_02 dismount;
alter diskgroup ORADB_T1_DATA_03 dismount;
alter diskgroup ORADB_T1_DATA_04 dismount;
alter diskgroup ORADB_T1_DATA_05 dismount;
alter diskgroup ORADB_T1_REDO_01 dismount;
4.
Have SysAdmin restore the database from BCV:
Is there any difference in restoring BCV copy to file system Vs ASM?
There is technically no difference in restoring BCV copy to file system Vs ASM.
Can ASM disk names be different?
No, should be same between source and target.
5.
Mount back diskgroups on all instances:
alter diskgroup ORADB_T1_BACKUP_01 mount;
alter diskgroup ORADB_T1_DATA_01 mount;
alter diskgroup ORADB_T1_DATA_02 mount;
alter diskgroup ORADB_T1_DATA_03 mount;
alter diskgroup ORADB_T1_DATA_04 mount;
alter diskgroup ORADB_T1_DATA_05 mount;
alter diskgroup ORADB_T1_REDO_01 mount;
6.
Startup and Recover the database: -- Source to DB instance
SQL> startup;
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2084296 bytes
Variable Size 419430968 bytes
Database Buffers 1174405120 bytes
Redo Buffers 14692352 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'+ORADB_T1_DATA_01/oradb/datafile/system.256.667735945'
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
7.
Restart all services/instances:
crs_start -all
8.
Check the services are online:
crs_stat -t
How big is the database: 2TB
How often we take BCV copy: Daily
How often we delete Archive log's: Every 30 mns.
How much redo, does this DB generate per Hour: 70GB/24= ~3GB
Is this RAC database: Yes.
1.
Stop all instances of database:
srvctl stop database -d ORADB
2.
Check the services are offline:
crs_stat -t
3.
Dismount diskgroups on all instances:
alter diskgroup ORADB_T1_BACKUP_01 dismount;
alter diskgroup ORADB_T1_DATA_01 dismount;
alter diskgroup ORADB_T1_DATA_02 dismount;
alter diskgroup ORADB_T1_DATA_03 dismount;
alter diskgroup ORADB_T1_DATA_04 dismount;
alter diskgroup ORADB_T1_DATA_05 dismount;
alter diskgroup ORADB_T1_REDO_01 dismount;
4.
Have SysAdmin restore the database from BCV:
Is there any difference in restoring BCV copy to file system Vs ASM?
There is technically no difference in restoring BCV copy to file system Vs ASM.
Can ASM disk names be different?
No, should be same between source and target.
5.
Mount back diskgroups on all instances:
alter diskgroup ORADB_T1_BACKUP_01 mount;
alter diskgroup ORADB_T1_DATA_01 mount;
alter diskgroup ORADB_T1_DATA_02 mount;
alter diskgroup ORADB_T1_DATA_03 mount;
alter diskgroup ORADB_T1_DATA_04 mount;
alter diskgroup ORADB_T1_DATA_05 mount;
alter diskgroup ORADB_T1_REDO_01 mount;
6.
Startup and Recover the database: -- Source to DB instance
SQL> startup;
ORACLE instance started.
Total System Global Area 1610612736 bytes
Fixed Size 2084296 bytes
Variable Size 419430968 bytes
Database Buffers 1174405120 bytes
Redo Buffers 14692352 bytes
Database mounted.
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1:
'+ORADB_T1_DATA_01/oradb/datafile/system.256.667735945'
SQL> recover database;
Media recovery complete.
SQL> alter database open;
Database altered.
SQL> select status from v$instance;
STATUS
------------
OPEN
7.
Restart all services/instances:
crs_start -all
8.
Check the services are online:
crs_stat -t
Subscribe to:
Posts (Atom)
