Hemant K Chitale

Subscribe to Hemant K Chitale feed
I am an Oracle Database Specialist in Singapore.
EU visitors : Please note that this site uses cookies.

Updated: 7 hours 23 min ago

Basic Replication -- 8 : REFRESH_MODE ON COMMIT

Sat, 2019-10-19 09:26
So far, in previous posts in this series, I have demonstrated Materialized Views that set to REFRESH ON DEMAND.

You can also define a Materialized View that is set to REFRESH ON COMMIT -- i.e. every time DML against the Source Table is committed, the MV is also immediately updated.  Such an MV must be in the same database  (you cannot define an ON COMMIT Refresh across two databases  -- to do so, you have to build your own replication code, possibly using Database Triggers or external methods of 2-phase commit).

Here is a quick demonstration, starting with a Source Table in the HEMANT schema and then building a FAST REFRESH MV in the HR schema.

SQL> show user
USER is "HEMANT"
SQL> create table hemant_source_tbl (id_col number not null primary key, data_col varchar2(30));

Table created.

SQL> grant select on hemant_source_tbl to hr;

Grant succeeded.

SQL> create materialized view log on hemant_source_tbl;

Materialized view log created.

SQL> grant select on mlog$_hemant_source_tbl to hr;

Grant succeeded.

SQL>
SQL> grant create materialized view to hr;

Grant succeeded.

SQL> grant on commit refresh on hemant_source_tbl to hr;

Grant succeeded.

SQL>
SQL> grant on commit refresh on mlog$_hemant_source_tbl to hr;

Grant succeeded.

SQL>


Note : I had to grant the CREATE MATERIALIZED VIEW privilege to HR for this test case. Also, as the MV is to Refresh ON COMMIT, two additional object-level grants on the Source Table and the Materialized View Log are required as the Refresh is across schemas.

SQL> connect hr/HR@orclpdb1
Connected.
SQL> create materialized view hr_mv_on_commit
2 refresh fast on commit
3 as select id_col as primary_key_col, data_col as value_column
4 from hemant.hemant_source_tbl;

Materialized view created.

SQL>


Now that the Materialized View is created successfully, I will test DML against the table and check that an explicit REFRESH call (e.g. DBMS_MVIEW.REFRESH or DBMS_REFRESH.REFRESH) is not required.

SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> insert into hemant_source_tbl values (1,'First');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from hr.hr_mv_on_commit;

PRIMARY_KEY_COL VALUE_COLUMN
--------------- ------------------------------
1 First

SQL> connect hr/HR@orclpdb1
Connected.
SQL> select * from hr_mv_on_commit;

PRIMARY_KEY_COL VALUE_COLUMN
--------------- ------------------------------
1 First

SQL>


The Materialized View in the HR schema was refreshed immediately, without an explicit REFRESH call.

Remember : An MV that is to REFRESH ON COMMIT must be in the same database as the Source Table.




Categories: DBA Blogs

Basic Replication -- 7 : Refresh Groups

Fri, 2019-10-11 23:24
So far, all my blog posts in this series cover "single" Materialized Views (even if I have created two MVs, they are independent of each other and can be refreshed at different schedules).

A Refresh Group is what you would define if you want multiple MVs to be refreshed to the same point in time.  This allows for
(a) data from transaction that touch multiple tables
or
(b) views of multiple tables
to be consistent in the target MVs.

For example, if you have SALES_ORDER and LINE_ITEMS tables and the MVs on these are refreshed at different times, you might see the ORDER (Header) without the LINE_ITEMs (or, worse, in the absence of Referential Integrity constraints, LINE_ITEMs without the ORDER (Header) !).

Here's a demo, using the HR  DEPARTMENTS and EMPLOYEES table with corresponding MVs built in the HEMANT schema.

SQL> show user
USER is "HR"
SQL> select count(*) from departments;

COUNT(*)
----------
27

SQL> select count(*) from employees;

COUNT(*)
----------
107

SQL>
SQL> grant select on departments to hemant;

Grant succeeded.

SQL> grant select on employees to hemant;

Grant succeeded.

SQL>
SQL> create materialized view log on departments;

Materialized view log created.

SQL> grant select, delete on mlog$_departments to hemant;

Grant succeeded.

SQL>
SQL> create materialized view log on employees;

Materialized view log created.

SQL> grant select, delete on mlog$_employees to hemant;

Grant succeeded.

SQL>
SQL>


Having created the source MV Logs  note that I have to grant privileges to the account (HEMANT) that will be reading and deleting from the MV Logs.

Next, I setup the MVs and the Refresh Group

SQL> show user
USER is "HEMANT"
SQL>
SQL> select count(*) from hr.departments;

COUNT(*)
----------
27

SQL> select count(*) from hr.employees;

COUNT(*)
----------
107

SQL>
SQL>
SQL> create materialized view mv_dept
2 refresh fast on demand
3 as select department_id as dept_id, department_name as dept_name
4 from hr.departments
5 /

Materialized view created.

SQL>
SQL> create materialized view mv_emp
2 refresh fast on demand
3 as select department_id as dept_id, employee_id as emp_id,
4 first_name, last_name, hire_date
5 from hr.employees
6 /

Materialized view created.

SQL>
SQL> select count(*) from mv_dept;

COUNT(*)
----------
27

SQL> select count(*) from mv_emp;

COUNT(*)
----------
107

SQL>
SQL> execute dbms_refresh.make(-
> name=>'HR_MVs',-
> list=>'MV_DEPT,MV_EMP',-
> next_date=>sysdate+0.5,-
> interval=>'sysdate+1');

PL/SQL procedure successfully completed.

SQL>
SQL> commit;

Commit complete.

SQL>


Here, I have built two MVs and then a Refresh Group called "HR_MVS".  The first refresh will be 12hours from now and every subsequent refresh will be after 24hours.  (The Refresh Interval must be set to what would be larger than the time taken to execute the actual Refresh).

However, I can manually execute the Refresh after new rows are populated into the source tables. First, I insert new rows

SQL> show user
USER is "HR"
SQL> insert into departments (department_id, department_name)
2 values
3 (departments_seq.nextval, 'New Department');

1 row created.

SQL> select department_id
2 from departments
3 where department_name = 'New Department';

DEPARTMENT_ID
-------------
280

SQL> insert into employees(employee_id, first_name, last_name, email, hire_date, job_id, department_id)
2 values
3 (employees_seq.nextval, 'Hemant', 'Chitale', 'hkc@myenterprise.com', sysdate, 'AD_VP', 280);

1 row created.

SQL> select employee_id
2 from employees
3 where first_name = 'Hemant';

EMPLOYEE_ID
-----------
208

SQL> commit;

Commit complete.

SQL>


Now that there are new rows, the target MVs must be refreshed together.

SQL> connect hemant/hemant@orclpdb1
Connected.
SQL> execute dbms_refresh.refresh('HR_MVS');

PL/SQL procedure successfully completed.

SQL> select count(*) from mv_dept;

COUNT(*)
----------
28

SQL> select count(*) from mv_emp;

COUNT(*)
----------
108

SQL>
SQL> select * from mv_dept
2 where dept_id=280;

DEPT_ID DEPT_NAME
---------- ------------------------------
280 New Department

SQL> select * from mv_emp
2 where emp_id=208;

DEPT_ID EMP_ID FIRST_NAME LAST_NAME HIRE_DATE
---------- ---------- -------------------- ------------------------- ---------
280 208 Hemant Chitale 12-OCT-19

SQL>


Both MVs have been Refresh'd together as an ATOMIC Transaction.  If either of the two MVs had failed to refresh (e.g. unable to allocate extent to grow the MV), both the INSERTs would be rolled back.  (Note : It is not a necessary requirement that both source tables have new / updated rows, the Refresh Group works even if only one of the two tables has new / updated rows).

Note : I have used DBMS_REFRESH.REFRESH (instead of DBMS_MVIEW.REFRESH) to execute the Refresh.

You can build multiple Refresh Groups, each consisting of *multiple* Source Tables from the same source database.
You would define each Refresh Group to maintain consistency of data across multiple MVs (sourced from different tables).
Besides the Refresh Group on two HR tables, I could have, within the HEMANT schema, more Refresh Groups on FINANCE schema tables as well.

(Can you have a Refresh Group sourcing from tables from different schemas ?  Try that out !)


What's the downside of Refresh Groups ?    
Undo and Redo !  Every Refresh consists of INSERT/UPDATE/DELETE operations on the MVs.  And if any one of the MVs fails to Refresh, the entire set of DMLs (across all the MVs in the Refresh Group) has to *Rollback* !


Categories: DBA Blogs

Basic Replication -- 6 : COMPLETE and ATOMIC_REFRESH

Sun, 2019-09-29 09:36
Till 9i, if you did a COMPLETE Refresh of a Single Materialized View, Oracle would do a TRUNCATE followed by an INSERT.
If you did a COMPLETE Refresh of a *group* of Materialized Views, Oracle would execute DELETE and INSERT so that all the MVs would be consistent to the same point in time.  Thus, if one of the MVs failed to refresh (e.g. the SELECT on the Source Table failed or the INSERT failed, it would be able to do a ROLLBACK of all the MVs to revert them to the status (i.e. all rows that were present) as of the time before the Refresh began.  This would also allow all MVs to be available for queries with the rows as of before the Refresh began, even as the Refresh was running (because the Refresh of the multiple MVs was a single transaction).

In 10g, the behaviour for a *group* of Materialized Views remained the same.  However, for a single MV, the default was now to do a DELETE and INSERT as well.  This would allow the MV to be queryable as well while the Refresh was running.
This change came as a surprise to many customers (including me at a site where I was managing multiple single MVs) !
This change meant that the single MV took longer to run (because DELETEing all the rows takes a long time !) and required much more Undo and Redo space !!

Here's a demonstration in 19c (as in the previous posts in this series) :

First, I start with a new, larger, Source Table  and then build two MVs on it :

SQL> create table source_table_2
2 as select *
3 from dba_objects
4 where object_id is not null
5 /

Table created.

SQL> alter table source_table_2
2 add constraint source_table_2_pk
3 primary key (object_id)
4 /

Table altered.

SQL> select count(*)
2 from source_table_2
3 /

COUNT(*)
----------
72366

SQL>
SQL> create materialized view new_mv_2_1
2 as select object_id, owner, object_name, object_type
3 from source_table_2
4 /

Materialized view created.

SQL> create materialized view new_mv_2_2
2 as select object_id, owner, object_name, object_type
3 from source_table_2
4 /

Materialized view created.

SQL>
SQL> select mview_name, refresh_mode, refresh_method, last_refresh_type, fast_refreshable
2 from user_mviews
3 where mview_name like 'NEW_MV%'
4 order by 1
5 /

MVIEW_NAME REFRESH_M REFRESH_ LAST_REF FAST_REFRESHABLE
---------------- --------- -------- -------- ------------------
NEW_MV_2_1 DEMAND FORCE COMPLETE DIRLOAD_DML
NEW_MV_2_2 DEMAND FORCE COMPLETE DIRLOAD_DML

SQL>


Note that it *IS* possible to have two Materialized Views with exactly the same QUERY co-existing.  They may have different REFRESH_METHODs (here both are the same) and/or may have different frequencies of Refresh calls when the REFRESH_MODE is 'DEMAND'

Note also that I did not specify any "refresh on demand" clause so both defaulted to FORCE and DEMAND.

(Question 1 : Why might I have two MVs with the same QUERY and the same REFRESH_METHOD but different frequency or different times when the Refresh is called ?)

(Question 2 : What is DIRLOAD_DML ?)


Now, let me issue two different COMPLETE Refresh calls and trace them.

SQL> execute dbms_mview.refresh('NEW_MV_2_1','C');
SQL> execute dbms_mview.refresh('NEW_MV_2_2','C',atomic_refresh=>FALSE); -- from a different session


Now, I look at the trace files.

For the NEW_MV_2_1  (where ATOMIC_REFRESH defaulted to TRUE), I see :

/* MV_REFRESH (DEL) */ delete from "HEMANT"."NEW_MV_2_1"

/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "HEMANT"."NEW_MV_2_1"("OBJECT_ID","OWNER","OBJECT_NAME","OBJECT_TYPE") SELECT "SOURCE_TABLE_2"."OBJECT_ID","SOURCE_TABLE_2"."OWNER","SOURCE_TABLE_2"."OBJECT_NAME","SOURCE_TABLE_2"."OBJECT_TYPE" FROM "SOURCE_TABLE_2" "SOURCE_TABLE_2"



And for the NEW_MV_2_2 (where ATOMIC_REFRESH was set to FALSE), I see :

LOCK TABLE "HEMANT"."NEW_MV_2_2" IN EXCLUSIVE MODE  NOWAIT

/* MV_REFRESH (DEL) */ truncate table "HEMANT"."NEW_MV_2_2" purge snapshot log

/* MV_REFRESH (INS) */INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO "HEMANT"."NEW_MV_2_2"("OBJECT_ID","OWNER","OBJECT_NAME","OBJECT_TYPE") SELECT "SOURCE_TABLE_2"."OBJECT_ID","SOURCE_TABLE_2"."OWNER","SOURCE_TABLE_2"."OBJECT_NAME","SOURCE_TABLE_2"."OBJECT_TYPE" FROM "SOURCE_TABLE_2" "SOURCE_TABLE_2"


So, the default ATOMIC_REFRESH=TRUE caused a DELETE followed by an INSERT while the ATOMIC_REFRESH=FALSE caused a DELETE followed by an INSERT APPEND (a Direct Path Insert).  The second method is much faster.



More information from a tkprof for the NEW_MV_2_1 (ATOMIC_REFRESH=TRUE) is :

INSERT INTO "HEMANT"."NEW_MV_2_1"("OBJECT_ID","OWNER","OBJECT_NAME",
"OBJECT_TYPE") SELECT "SOURCE_TABLE_2"."OBJECT_ID","SOURCE_TABLE_2"."OWNER",
"SOURCE_TABLE_2"."OBJECT_NAME","SOURCE_TABLE_2"."OBJECT_TYPE" FROM
"SOURCE_TABLE_2" "SOURCE_TABLE_2"


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 66 0 0
Execute 0 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1 0.01 0.01 0 66 0 0




delete from "HEMANT"."NEW_MV_2_1"


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 1.47 1.77 151 173 224377 72366
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.47 1.77 151 173 224377 72366

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 DELETE NEW_MV_2_1 (cr=178 pr=151 pw=0 time=1783942 us starts=1)
72366 72366 72366 INDEX FAST FULL SCAN SYS_C_SNAP$_82SOURCE_TABLE_2_PK (cr=157 pr=150 pw=0 time=54982 us starts=1 cost=42 size=361830 card=72366)(object id 73111)




INSERT /*+ BYPASS_RECURSIVE_CHECK */ INTO "HEMANT"."NEW_MV_2_1"("OBJECT_ID",
"OWNER","OBJECT_NAME","OBJECT_TYPE") SELECT "SOURCE_TABLE_2"."OBJECT_ID",
"SOURCE_TABLE_2"."OWNER","SOURCE_TABLE_2"."OBJECT_NAME",
"SOURCE_TABLE_2"."OBJECT_TYPE" FROM "SOURCE_TABLE_2" "SOURCE_TABLE_2"


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 4 0
Execute 1 0.71 0.71 0 2166 152128 72366
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.71 0.71 0 2166 152132 72366

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD TABLE CONVENTIONAL NEW_MV_2_1 (cr=2257 pr=0 pw=0 time=723103 us starts=1)
72366 72366 72366 TABLE ACCESS FULL SOURCE_TABLE_2 (cr=1410 pr=0 pw=0 time=30476 us starts=1 cost=392 size=3980130 card=72366)




Note that the first INSERT was only Parsed but *not* Executed.


While that for NEW_MV_2_2 (ATOMIC_REFRESH=FALSE) shows :

LOCK TABLE "HEMANT"."NEW_MV_2_2" IN EXCLUSIVE MODE  NOWAIT


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.00 0.00 0 0 0 0




truncate table "HEMANT"."NEW_MV_2_2" purge snapshot log



call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 1 0
Execute 1 0.06 0.56 13 15 511 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.06 0.57 13 15 512 0



INSERT /*+ BYPASS_RECURSIVE_CHECK APPEND SKIP_UNQ_UNUSABLE_IDX */ INTO
"HEMANT"."NEW_MV_2_2"("OBJECT_ID","OWNER","OBJECT_NAME","OBJECT_TYPE")
SELECT "SOURCE_TABLE_2"."OBJECT_ID","SOURCE_TABLE_2"."OWNER",
"SOURCE_TABLE_2"."OBJECT_NAME","SOURCE_TABLE_2"."OBJECT_TYPE" FROM
"SOURCE_TABLE_2" "SOURCE_TABLE_2"


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.09 0 43 0 0
Execute 1 0.22 0.56 3 1487 1121 72366
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.24 0.65 3 1530 1121 72366

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106 (recursive depth: 1)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
0 0 0 LOAD AS SELECT NEW_MV_2_2 (cr=3688 pr=7 pw=586 time=953367 us starts=1)
72366 72366 72366 OPTIMIZER STATISTICS GATHERING (cr=3337 pr=0 pw=0 time=142500 us starts=1 cost=392 size=3980130 card=72366)
72366 72366 72366 TABLE ACCESS FULL SOURCE_TABLE_2 (cr=1410 pr=0 pw=0 time=40841 us starts=1 cost=392 size=3980130 card=72366)




ALTER INDEX "HEMANT"."SYS_C_SNAP$_83SOURCE_TABLE_2_PK" REBUILD NOPARALLEL


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.08 0 1 1 0
Execute 1 0.11 0.48 586 626 680 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 0.11 0.56 586 627 681 0

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 106 (recursive depth: 2)
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
1 1 1 INDEX BUILD UNIQUE SYS_C_SNAP$_83SOURCE_TABLE_2_PK (cr=632 pr=586 pw=150 time=392351 us starts=1)(object id 0)
72366 72366 72366 SORT CREATE INDEX (cr=590 pr=586 pw=0 time=148023 us starts=1)
72366 72366 72366 MAT_VIEW ACCESS FULL NEW_MV_2_2 (cr=590 pr=586 pw=0 time=86149 us starts=1 cost=166 size=361830 card=72366)



So, the ATOMIC_REFRESH=FALSE caused
a. TRUNCATE
b. INSERT APPEND (i.e. Direct Path Insert, minimizing Undo and reducing Redo)
c. REBUILD INDEX

I am not comparing Execution Time for the two Refresh's.  I would rather that you focus on the fact that the DELETE (in ATOMIC_REFRESH=TRUE) can be very expensive (think Undo and Redo) when it has delete, say, millions of rows.  Also, that the INSERT is a regular operation that also causes Undo and Redo to be generated.

ATOMIC_REFRESH=FALSE makes a significant difference to the Undo and Redo generation and will be faster for large Materialized Views.

What is the downside of ATOMIC_REFRESH=FALSE ?  Firstly, the MV will not present any rows to a query that executes against it while the Refresh is in progress.  Secondly, if the Refresh fails, the MV is left in a Truncated state (without rows) until another Refresh is executed.
The ATOMIC_REFRESH=TRUE avoids  these two pitfalls, at the expense of resources (Undo and Redo) and time to refresh.

For more information, see Oracle Support Document #553464.1


Categories: DBA Blogs

Basic Replication -- 5 : REFRESH_METHOD : FAST or FORCE ?

Wed, 2019-09-25 10:14
In the previous blog post, I had a remark "We'll explore the implications of "REFRESH FAST" and just "REFRESH" alone in a subsequent blog post."

This is in the context of whether it is a FORCE or FAST that shows up as the REFRESH_METHOD.  A FORCE attempts a FAST and, if it can't do so (e.g. the Materialized View Log is not accessible), attempts a COMPLETE Refresh from all the rows of the Source Table.

Other than a MV Log being a requirement, there are constraints on which types of Materialized Views can do a FAST Refresh.

SQL> create materialized view mv_fast_not_possible
2 refresh fast on demand
3 as select id, data_element_2, sysdate
4 from source_table
5 /
as select id, data_element_2, sysdate
*
ERROR at line 3:
ORA-12015: cannot create a fast refresh materialized view from a complex query


SQL> !oerr ora 12015
12015, 00000, "cannot create a fast refresh materialized view from a complex query"
// *Cause: Neither ROWIDs and nor primary key constraints are supported for
// complex queries.
// *Action: Reissue the command with the REFRESH FORCE or REFRESH COMPLETE
// option or create a simple materialized view.

SQL>


Thus, a "complex" query -- here one that adds a SYSDATE column -- cannot use a FAST Refresh.
(For all the restrictions, see Paragraph "5.3.8.4 General Restrictions on Fast Refresh" in the 19c documentation. )

SQL> create materialized view mv_fast_not_possible
2 refresh force on demand
3 as select id, data_element_2, sysdate
4 from source_table
5 /

Materialized view created.

SQL> select refresh_mode, refresh_method, last_refresh_type
2 from user_mviews
3 where mview_name = 'MV_FAST_NOT_POSSIBLE'
4 /

REFRESH_M REFRESH_ LAST_REF
--------- -------- --------
DEMAND FORCE COMPLETE

SQL>
SQL> insert into source_table
2 values (2000,'TwoThousand','NewTwoTh',sysdate);

1 row created.

SQL> select * from source_table order by date_col ;

ID DATA_ELEMENT_1 DATA_ELEMENT_2 DATE_COL
---------- --------------- --------------- ---------
101 First One 18-AUG-19
103 Third Three 18-AUG-19
104 Fourth Updated 09-SEP-19
5 Fifth Five 16-SEP-19
6 Sixth TwoHundred 19-SEP-19
7 Seventh ThreeHundred 19-SEP-19
2000 TwoThousand NewTwoTh 25-SEP-19

7 rows selected.

SQL>
SQL> commit;

Commit complete.

SQL> exec dbms_mview.refresh('MV_OF_SOURCE');

PL/SQL procedure successfully completed.

SQL> exec dbms_mview.refresh('MV_2');

PL/SQL procedure successfully completed.

SQL> exec dbms_mview.refresh('MV_FAST_NOT_POSSIBLE');

PL/SQL procedure successfully completed.

SQL>
SQL> select mview_name, refresh_mode,refresh_method,last_refresh_type, last_refresh_date
2 from user_mviews
3 order by last_refresh_date
4 /

MVIEW_NAME REFRESH_M REFRESH_ LAST_REF LAST_REFR
--------------------- --------- -------- -------- ---------
MV_OF_SOURCE DEMAND FAST FAST 25-SEP-19
MV_2 DEMAND FORCE FAST 25-SEP-19
MV_FAST_NOT_POSSIBLE DEMAND FORCE COMPLETE 25-SEP-19

SQL>


MV_FAST_NOT_POSSIBLE will always undergo a COMPLETE Refresh using REFRESH_METHOD='FORCE'.

MV_2 has REFRESH_METHOD='FORCE' because it was created with "refresh on demand" with the "fast" keyword missing.  Nevertheless, it is a "simple" Materialized View so does a FAST Refresh.

MV_OF_SOURCE was created with "refresh fast on demand", so it is already configured as REFRESH_METHOD='FAST'



Categories: DBA Blogs

Basic Replication -- 4 : Data Dictionary Queries

Tue, 2019-09-17 08:58
Now that we have two Materialized Views against a Source table, how can we identify the relationship via the data dictionary ?

This is the query to the data dictionary in the database where the Source Table exists :

SQL> l
1 select v.owner MV_Owner, v.name MV_Name, v.snapshot_site, v.refresh_method,
2 l.log_table MV_Log_Name, l.master MV_Source,
3 to_char(l.current_snapshots,'DD-MON-RR HH24:MI:SS') Last_Refresh_Date
4 from dba_registered_snapshots v, dba_snapshot_logs l
5 where v.snapshot_id = l.snapshot_id
6* and l.log_owner = 'HEMANT'
SQL> /

MV_OWNER MV_NAME SNAPSHOT_SITE REFRESH_MET MV_LOG_NAME MV_SOURCE LAST_REFRESH_DATE
-------- ---------------- ------------------ ----------- ------------------ --------------------- ------------------
HEMANT MV_OF_SOURCE ORCLPDB1 PRIMARY KEY MLOG$_SOURCE_TABLE SOURCE_TABLE 16-SEP-19 22:41:04
HEMANT MV_2 ORCLPDB1 PRIMARY KEY MLOG$_SOURCE_TABLE SOURCE_TABLE 16-SEP-19 22:44:37

SQL>


I have run the query on the DBA_REGISTERED_SNAPSHOTS and DBA_SNAPSHOT_LOGS because the join on SNAPSHOT_ID is not available between DBA_REGISTERED_MVIEWS and DBA_MVIEW_LOGS.  Similarly, the CURRENT_SNAPSHOTS column is also not available in DBA_MVIEW_LOGS.  These two columns are important when you have *multiple* MViews against the same Source Table.

Note the "Snapshot_Site" is required because the Materialized View can be in a different database.  In this example, the MViews are in the same database as the Source Table. 

The target database containing the MViews will not have the Source Table "registered" into a data dictionary view.  The Source Table will be apparently from the QUERY column of DBA_MVIEWS (also, if the Source Table is in a different database, look at the MASTER_LINK column to identify the Database Link that connects to the source database).


UPDATE :  In case you are wondering what query you'd write against the database containing the Materialized View(s), you can simply query DBA_MVIEWS.

SQL> l
1 select mview_name, query, master_link, refresh_mode, refresh_method,
2 last_refresh_type, to_char(last_refresh_date,'DD-MON-RR HH24:MI:SS') Last_Refresh_Date
3 from dba_mviews
4 where owner = 'HEMANT'
5* order by 1 desc
SQL> /

MVIEW_NAME
------------
QUERY
--------------------------------------------------------------------------------
MASTER_LINK REFRESH_M REFRESH_ LAST_REF LAST_REFRESH_DATE
------------ --------- -------- -------- ---------------------------
MV_OF_SOURCE
SELECT "SOURCE_TABLE"."ID" "ID","SOURCE_TABLE"."DATA_ELEMENT_1" "DATA_ELEMENT_1"
,"SOURCE_TABLE"."DATA_ELEMENT_2" "DATA_ELEMENT_2","SOURCE_TABLE"."DATE_COL" "DAT
E_COL" FROM "SOURCE_TABLE" "SOURCE_TABLE"
DEMAND FAST FAST 16-SEP-19 22:41:04

MV_2
select id, data_element_2
from source_table
DEMAND FORCE FAST 16-SEP-19 22:44:37


SQL>


Here, the MASTER_LINK would specify the name of the Database Link used to connect to the Master (i.e. Source) table, if it was a different database.

REFRESH_MODE is ON DEMAND so that the MVs can be refreshed by either scheduled jobs or manually initiated calls -- as I've done in previous blog posts.  (The alternative can be ON COMMIT, if the Source Table and MV are in the same database).

LAST_REFRESH_TYPE is FAST, meaning that the refresh was able to use the MV Log on the Source Table to identify changes and merge them into the MV.  See the entries from the trace file that I've shown in the previous blog post.

Note the difference in the two REFRESH_METHOD values for the two MVs.
MV_OF_SOURCE was created as "refresh fast on demand" while "MV_2" was created as "refresh on demand".

We'll explore the implications of "REFRESH FAST" and just "REFRESH" alone in a subsequent blog post.

Question : Why does the QUERY look so different between MV_OF_SOURCE and MV_2 ?



Categories: DBA Blogs

Basic Replication -- 3 : Multiple Materialized Views

Mon, 2019-09-16 09:53
You can define multiple Materialized Views against the same Source Table with differences in :
a) the SELECT clause column list
b) Predicates in the WHERE clause
c) Joins to one or more other Source Table(s) in the FROM clause
d) Aggregates in the SELECT clause

Thus, for my Source Table, I can add another Materialized View :

SQL> create materialized view mv_2
2 refresh on demand
3 as select id, data_element_2
4 from source_table;

Materialized view created.

SQL>
SQL> select count(*) from mlog$_source_table;

COUNT(*)
----------
0

SQL> insert into source_table
2 values (5, 'Fifth','Five',sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from mlog$_source_table;

COUNT(*)
----------
1

SQL>
SQL> execute dbms_mview.refresh('MV_OF_SOURCE');

PL/SQL procedure successfully completed.

SQL> select * from mv_of_source;

ID DATA_ELEMENT_1 DATA_ELEMENT_2 DATE_COL
---------- --------------- --------------- ---------
5 Fifth Five 16-SEP-19
101 First One 18-AUG-19
103 Third Three 18-AUG-19
104 Fourth Updated 09-SEP-19

SQL> select count(*) from mlog$_source_table;

COUNT(*)
----------
1

SQL>


Now that there are two MVs referencing the Source Table, the MV Log is not completely purged when only one of the two MVs is refreshed.  Oracle still maintains entries in the MV Log for the second MV to be able to execute a Refresh.

SQL> select * from mlog$_source_table;

ID SNAPTIME$ D O
---------- --------- - -
CHANGE_VECTOR$$
--------------------------------------------------------------------------------
XID$$
----------
5 16-SEP-19 I N
FE
5.6299E+14


SQL> execute dbms_mview.refresh('MV_2');

PL/SQL procedure successfully completed.

SQL> select * from mlog$_source_table;

no rows selected

SQL> select * from mv_2;

ID DATA_ELEMENT_2
---------- ---------------
101 One
103 Three
104 Updated
5 Five

SQL>


The MV Log is "purged" only when the second (actually the last) MV executes a Refresh.  Of course, if more rows were inserted / updated in the Source Table between the Refresh of MV_OF_SOURCE and MV_2, there would be corresponding entries in the MV Log.

So, Oracle does use some mechanism to track MVs that execute Refresh's and does continue to "preserve" rows in the MV Log for MVs that haven't been refreshed yet.

As I've noted in two earlier posts, in 2007 and 2012, the MV Log (called "Snapshot Log" in the 2007 post) can keep growing for a long time if you have one or more Materialized Views that just aren't executing their Refresh  calls.


Categories: DBA Blogs

Basic Replication -- 2b : Elements for creating a Materialized View

Mon, 2019-09-09 09:02
Continuing the previous post, what happens when there is an UPDATE to the source table ?

SQL> select * from source_table;

ID DATA_ELEMENT_1 DATA_ELEMENT_2 DATE_COL
---------- --------------- --------------- ---------
1 First One 18-AUG-19
3 Third Three 18-AUG-19
4 Fourth Four 18-AUG-19

SQL> select * from mlog$_source_table;

no rows selected

SQL> select * from rupd$_source_table;

no rows selected

SQL>
SQL> update source_table
2 set data_element_2 = 'Updated', date_col=sysdate
3 where id=4;

1 row updated.

SQL> select * from rupd$_source_table;

no rows selected

SQL> commit;

Commit complete.

SQL> select * from rupd$_source_table;

no rows selected

SQL> select * from mlog$_source_table;

ID SNAPTIME$ D O
---------- --------- - -
CHANGE_VECTOR$$
--------------------------------------------------------------------------------
XID$$
----------
4 01-JAN-00 U U
18
8.4443E+14


SQL>

So, it is clear that UPDATES, too, go to the MLOG$ table.

What about multi-row operations ?

SQL> update source_table set id=id+100;

3 rows updated.

SQL> select * from rupd$_source_table;

no rows selected

SQL> select * from mlog$_source_table;

ID SNAPTIME$ D O
---------- --------- - -
CHANGE_VECTOR$$
--------------------------------------------------------------------------------
XID$$
----------
4 01-JAN-00 U U
18
8.4443E+14

1 01-JAN-00 D O
00
1.4075E+15

101 01-JAN-00 I N
FF
1.4075E+15

3 01-JAN-00 D O
00
1.4075E+15

103 01-JAN-00 I N
FF
1.4075E+15

4 01-JAN-00 D O
00
1.4075E+15

104 01-JAN-00 I N
FF
1.4075E+15


7 rows selected.

SQL>



Wow ! Three rows updated in the Source Table translated to 6 rows in the MLOG$ table ! Each update row was represented by an DMLTYPE$$='D' and OLD_NEW$$='O'  followed by a DMLTYPE$$='I' and OLD_NEW$$='N'.   So that should mean "delete the old row from the materialized view and insert the new row into the materialized view" ??

(For the time being, we'll ignore SNAPTIME$$ being '01-JAN-00').

So an UPDATE to the Source Table of a Materialized View can be expensive during the UPDATE (as it creates two entries in the MLOG$ table) and for subsequent refresh's as well !

What happens when I refresh the Materialized View ?

SQL> execute dbms_session.session_trace_enable;

PL/SQL procedure successfully completed.

SQL> execute dbms_mview.refresh('MV_OF_SOURCE');

PL/SQL procedure successfully completed.

SQL> execute dbms_session.session_trace_disable;

PL/SQL procedure successfully completed.

SQL>


The session trace file shows these operations (I've excluded a large number of recursive SQLs and SQLs that were sampling the data for optimisation of execution plans):

update "HEMANT"."MLOG$_SOURCE_TABLE" 
set snaptime$$ = :1
where snaptime$$ > to_date('2100-01-01:00:00:00','YYYY-MM-DD:HH24:MI:SS')

/* QSMQ VALIDATION */ ALTER SUMMARY "HEMANT"."MV_OF_SOURCE" COMPILE

select 1 from "HEMANT"."MLOG$_SOURCE_TABLE"
where snaptime$$ > :1
and ((dmltype$$ IN ('I', 'D')) or (dmltype$$ = 'U' and old_new$$ in ('U', 'O')
and sys.dbms_snapshot_utl.vector_compare(:2, change_vector$$) = 1))
and rownum = 1

SELECT /*+ NO_MERGE(DL$) ROWID(MAS$) ORDERED USE_NL(MAS$) NO_INDEX(MAS$) PQ_DISTRIBUTE(MAS$,RANDOM,NONE) */
COUNT(*) cnt
FROM ALL_SUMDELTA DL$, "HEMANT"."SOURCE_TABLE" MAS$
WHERE DL$.TABLEOBJ# = :1 AND DL$.TIMESTAMP > :2 AND DL$.TIMESTAMP <= :3
AND MAS$.ROWID BETWEEN DL$.LOWROWID AND DL$.HIGHROWID

select dmltype$$, count(*) cnt from "HEMANT"."MLOG$_SOURCE_TABLE"
where snaptime$$ > :1 and snaptime$$ <= :2
group by dmltype$$ order by dmltype$$

delete from "HEMANT"."MLOG$_SOURCE_TABLE"
where snaptime$$ <= :1


and this being the refresh (merge update) of the target MV
DELETE FROM "HEMANT"."MV_OF_SOURCE" SNAP$ 
WHERE "ID" IN
(SELECT * FROM (SELECT MLOG$."ID"
FROM "HEMANT"."MLOG$_SOURCE_TABLE" MLOG$
WHERE "SNAPTIME$$" > :1 AND ("DMLTYPE$$" != 'I'))
AS OF SNAPSHOT(:B_SCN) )

/* MV_REFRESH (MRG) */ MERGE INTO "HEMANT"."MV_OF_SOURCE" "SNA$" USING
(SELECT * FROM (SELECT CURRENT$."ID",CURRENT$."DATA_ELEMENT_1",CURRENT$."DATA_ELEMENT_2",CURRENT$."DATE_COL"
FROM (SELECT "SOURCE_TABLE"."ID" "ID","SOURCE_TABLE"."DATA_ELEMENT_1" "DATA_ELEMENT_1","SOURCE_TABLE"."DATA_ELEMENT_2" "DATA_ELEMENT_2","SOURCE_TABLE"."DATE_COL" "DATE_COL"
FROM "SOURCE_TABLE" "SOURCE_TABLE") CURRENT$,
(SELECT DISTINCT MLOG$."ID" FROM "HEMANT"."MLOG$_SOURCE_TABLE" MLOG$ WHERE "SNAPTIME$$" > :1
AND ("DMLTYPE$$" != 'D')) LOG$ WHERE CURRENT$."ID" = LOG$."ID") AS OF SNAPSHOT(:B_SCN) )"AV$" ON ("SNA$"."ID" = "AV$"."ID")
WHEN MATCHED THEN UPDATE SET "SNA$"."DATA_ELEMENT_1" = "AV$"."DATA_ELEMENT_1","SNA$"."DATA_ELEMENT_2" = "AV$"."DATA_ELEMENT_2","SNA$"."DATE_COL" = "AV$"."DATE_COL"
WHEN NOT MATCHED THEN INSERT (SNA$."ID",SNA$."DATA_ELEMENT_1",SNA$."DATA_ELEMENT_2",SNA$."DATE_COL")
VALUES (AV$."ID",AV$."DATA_ELEMENT_1",AV$."DATA_ELEMENT_2",AV$."DATE_COL")


So, we see a large number of intensive operations against the MLOG$ Materialized View Log object.

And on the MV, there is a DELETE followed by a MERGE (UPDATE/IINSERT)


Two takeaways :
1.  Updating the Source Table of a Materialized View can have noticeable overheads
2.  Refreshing a Materialized View takes some effort on the part of the database

(Did you notice the strange year 2100 date in the update of the MLOG$ table?
.
.
.
.
.
.
Categories: DBA Blogs

Basic Replication -- 2a : Elements for creating a Materialized View

Sun, 2019-08-18 04:02
The CREATE MATERIALIZED VIEW statement is documented here.  It can look quite complex so I am presenting only the important elements here.  In this post, I begin with only the basic elements.

(EDIT: These SQL operations, queries and results were in a 19c Database)

First, I recreate the SOURCE_TABLE properly, with a Primary Key :

SQL> drop table source_table;

Table dropped.

SQL> create table source_table
2 (id number not null,
3 data_element_1 varchar2(15),
4 data_element_2 varchar2(15),
5 date_col date)
6 /

Table created.

SQL> create unique index source_table_pk
2 on source_table(id);

Index created.

SQL> alter table source_table
2 add constraint source_table_pk
3 primary key (id)
4 /

Table altered.

SQL>


Then I create a Materialized View Log on SOURCE_TABLE.  This will capture all DML against this table and will be read by the target Materialized View to identify "changed" rows at every refresh.

SQL> create materialized view log on source_table;

Materialized view log created.

SQL>


I then identify the objects that were created.

SQL> select object_id, object_name, object_type
2 from user_objects
3 where created > sysdate-1
4 order by object_id
5 /

OBJECT_ID OBJECT_NAME OBJECT_TYPE
---------- ------------------------------ -----------------------
73055 SOURCE_TABLE TABLE
73056 SOURCE_TABLE_PK INDEX
73057 MLOG$_SOURCE_TABLE TABLE
73058 RUPD$_SOURCE_TABLE TABLE
73059 I_MLOG$_SOURCE_TABLE INDEX

SQL>
SQL> desc mlog$_source_table;
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
ID NUMBER
SNAPTIME$$ DATE
DMLTYPE$$ VARCHAR2(1)
OLD_NEW$$ VARCHAR2(1)
CHANGE_VECTOR$$ RAW(255)
XID$$ NUMBER

SQL> desc rupd$_source_table;
Name Null? Type
------------------------------------------------------------------------ -------- -------------------------------------------------
ID NUMBER
DMLTYPE$$ VARCHAR2(1)
SNAPID NUMBER(38)
CHANGE_VECTOR$$ RAW(255)

SQL>


Interesting that the "CREATE MATERIAIZED VIEW LOG" statement created 3 database objects.

What happens after I perform DML on the SOURCE_TABLE ?

SQL> insert into source_table
2 values (1,'First','One',sysdate);

1 row created.

SQL> insert into source_table
2 values (2,'Second','Two',sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> delete source_table
2 where id=2
3 /

1 row deleted.

SQL>
SQL> commit;

Commit complete.

SQL> select * from mlog$_source_table;

ID SNAPTIME$ D O
---------- --------- - -
CHANGE_VECTOR$$
------------------------------------------------------------------------------------------------------------------------------------
XID$$
----------
1 01-JAN-00 I N
FE
2.8158E+14

2 01-JAN-00 I N
FE
2.8158E+14

2 01-JAN-00 D O
00
2.5334E+15


SQL>
SQL> select * from rupd$_source_table;

no rows selected

SQL>


So the MLOG$_SOURCE_TABLE is the log that captures 2 INSERT statements and 1 DELETE statement.  (OR is it 2 INSERT *rows* and 1 DELETE *row* ??)
We don't know what the RUPD$_SOURCE_TABLE captures yet.

Let me create a Materialized View and then query MLOG$_SOURCE_TABLE (which is the "MV Log")

SQL> create materialized view
2 mv_of_source
3 refresh fast on demand
4 as select * from source_table
5 /

Materialized view created.

SQL> select * from mv_of_source
2 /

ID DATA_ELEMENT_1 DATA_ELEMENT_2 DATE_COL
---------- --------------- --------------- ---------
1 First One 18-AUG-19

SQL>
SQL> select * from mlog$_source_table;

no rows selected

SQL>


So, the CREATE MATERIALIZED VIEW statement has also done a cleanup of the MV Log entries with a SNAPTIME$ older than when it was created.

Let me insert two new rows and then refresh the Materialized View and check the MV Log again.

SQL> insert into source_table
2 values (3,'Third','Three',sysdate);

1 row created.

SQL> insert into source_table
2 values (4,'Fourth','Four',sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from mlog$_source_table;

ID SNAPTIME$ D O
---------- --------- - -
CHANGE_VECTOR$$
------------------------------------------------------------------------------------------------------------------------------------
XID$$
----------
3 01-JAN-00 I N
FE
1.6889E+15

4 01-JAN-00 I N
FE
1.6889E+15


SQL>
SQL> execute dbms_mview.refresh('MV_OF_SOURCE');

PL/SQL procedure successfully completed.

SQL> select * from mlog$_source_table;

no rows selected

SQL> select * from mv_of_source;

ID DATA_ELEMENT_1 DATA_ELEMENT_2 DATE_COL
---------- --------------- --------------- ---------
1 First One 18-AUG-19
3 Third Three 18-AUG-19
4 Fourth Four 18-AUG-19

SQL>


So, the 2 single-row INSERTs did create two entries in the MV Log and the REFRESH of the Materialized View did a cleanup of those two entries.

I haven't yet explored :
a.  UPDATEs
b. Multi-Row Operations
Categories: DBA Blogs

Basic Replication -- 1 : Introduction

Thu, 2019-08-15 23:24
Basic Replication, starting with Read Only Snapshots has been available in Oracle since  V7.   This was doable with the "CREATE SNAPSHOT" command.

In 8i, the term was changed from "Snapshot" to "Materialized View"  and the "CREATE MATERIALIZED VIEW" command was introduced, while "CREATE SNAPSHOT" was still supported.

Just as CREATE SNAPSHOT is still available in 19c,  DBMS_SNAPSHOT.REFRESH is also available.


























Not that I recommend that you use CREATE SNAPSHOT and DBMS_SNAPSHOT anymore.  DBAs and Developers should have been using CREATE MATERIALIZED VIEW and DBMS_REFRESH since 8i.

In the next few blog posts (this will be a very short series) I will explore Basic Replication.  Let me know if you want to see it in 11.2 and 12c as well.



Categories: DBA Blogs

2million PageViews

Wed, 2019-08-07 23:53

This blog has now achieved 2million PageViews :




(The "drop" at the end is the count for only the first week of August 2019).

Although this blog began in December 2006, the PageViews counts start with 8,176 in July 2010.  So, effectively, this blog has had 2million PageViews in 9years.

The first 1million PageViews were achieved in March 2015.

Unfortunately, the rate at which I have been publishing has declined since 2017 (36 posts in 2017, 30 in 2018 and only 8 so far this year).  I apologise for this.  Hopefully, I should be able to add more posts in the coming months.




Categories: DBA Blogs

Partitioning -- 16 : Hybrid Partitioning

Tue, 2019-05-07 02:55
Oracle 19c introduces Hybrid Partitioning whereby you can have external and internal Partitions co-existing.  External Partitions are on storage (filesystem) outside the database.

Let's say we have a List Partitioned table for the widgets that we manufacture. The table is Partitioned by WIDGET_CLASS_ID, based on an ISO standard.  So all companies that manufacture widgets adopt the same WIDGET_CLASS_ID:

SQL> desc widgets_list
Name Null? Type
----------------------------------------- -------- ----------------------------
WIDGET_CLASS_ID VARCHAR2(5)
WIDGET_ID VARCHAR2(32)
WIDGET_NAME VARCHAR2(32)
WIDGET_DESCRIPTION VARCHAR2(128)

SQL>
SQL> l
1 select table_name, partitioning_type, partition_count
2 from user_part_tables
3* where table_name = 'WIDGETS_LIST'
SQL> /

TABLE_NAME PARTITION PARTITION_COUNT
-------------------------------- --------- ---------------
WIDGETS_LIST LIST 3

SQL>
SQL> l
1 select partition_name,high_value, num_rows
2 from user_tab_partitions
3* where table_name = 'WIDGETS_LIST'
SQL> /

PARTITION_NAME HIGH_VALUE NUM_ROWS
---------------- ---------------- ----------
P_A 'A' 1520
P_B 'B' 520
P_C 'C' 119

SQL>


Later, another widget manufacturer that manufactures widgets of CLASS_ID 'X' is acquired.  The WIDGETS_LIST table is in a non-Oracle database and is received as a CSV file.  We accept the CSV file onto a filesystem location :

sh-4.2$ pwd
/home/oracle/ACQUIRED_COMPANY
sh-4.2$ cat AC_Widgets_List.CSV
'X','ABCXX2','The1','cddfdaxx'
'X','XXD2','The2','dda3'
'X','XRC34','The3','ff33355312'
sh-4.2$


So, we have a CSV file "AC_Widgets_List.CSV" listing the widgets manufactured by this company. We want to add it to our WIDGETS_LIST table.

Enter user-name: / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> alter session set container=ORCLPDB1;

Session altered.

SQL> create directory acquired_company as '/home/oracle/ACQUIRED_COMPANY';

Directory created.

SQL> grant read, write on directory acquired_company to hemant;

Grant succeeded.

SQL>
SQL> connect hemant/hemant@ORCLPDB1
Connected.
SQL>
SQL> l
1 alter table widgets_list
2 add external partition attributes (
3 type oracle_loader
4 default directory acquired_company
5 access parameters (
6 fields terminated by ','
7 (widget_class_id, widget_id, widget_name, widget_description)
8 )
9* )
SQL> /

Table altered.

SQL>
SQL> l
1 alter table widgets_list
2 add partition P_ACQ_CO values ('X')
3* external location ('AC_Widgets_List.CSV')
SQL> /

Table altered.

SQL>
SQL> exec dbms_stats.gather_table_stats('','WIDGETS_LIST');

PL/SQL procedure successfully completed.

SQL>
SQL> l
1 select partition_name, high_value, num_rows
2 from user_tab_partitions
3 where table_name = 'WIDGETS_LIST'
4* order by partition_position
SQL> /

PARTITION_NAME HIGH_VALUE NUM_ROWS
-------------------------------- ---------------- ----------
P_A 'A' 1520
P_B 'B' 520
P_C 'C' 119
P_ACQ_CO 'X' 3

SQL>
SQL> l
1* select * from widgets_list partition (P_ACQ_CO)
SQL> /

WIDGET_CLASS WIDGET_ID WIDGET_NAME
------------ -------------------------------- --------------------------------
WIDGET_DESCRIPTION
--------------------------------------------------------------------------------
'X' 'ABCXX2' 'The1'
'cddfdaxx'

'X' 'XXD2' 'The2'
'dda3'

'X' 'XRC34' 'The3'
'ff33355312'


SQL>


The rows in the "AC_Widgets_List.CSV" file are now visible as rows in a *Partition* in our Oracle Table WIDGETS_LIST.
Of course, these being external, cannot be modified by INSERT/UPDATE/DELETE DML.

The External Attribute Type that I used is ORACLE_LOADER to use the SQL Loader libraries on a filesystem file.  Oracle 19c also supports ORACLE_DATAPUMP, ORACLE_HDFS and ORACLE_HIVE to reference files stored in other types of storage.

Hybrid Partitions are supported with single-level Range and List partitioning methods.  ALTER TABLE to ADD, DROP and RENAME Partitions is supported.

An External Partition can be Exchanged with an External Non-Partitioned Table only.
.
.
UPDATE :  Later, if I update the CSV file (using an external editor) to remove the quotation mark :

sh-4.2$ cat AC_Widgets_List.CSV
X,ABCXX2,The1,cddfdaxx
X,XXD2,The2,dda3
X,XRC34,The3,ff33355312
sh-4.2$

SQL> l
1* select * from widgets_list partition (P_ACQ_CO)
SQL> /

WIDGET_CLASS_ID WIDGET_ID WIDGET_NAME
---------------- -------------------------------- --------------------------------
WIDGET_DESCRIPTION
------------------------------------------------------------------------------------
X ABCXX2 The1
cddfdaxx

X XXD2 The2
dda3

X XRC34 The3
ff33355312


SQL>


So, it is possible to edit the External Partition using other methods (here I used "vi" on Linux)
.
.
.

Categories: DBA Blogs

Partitioning -- 15 : Online Modification of Partitioning Type (Strategy)

Mon, 2019-04-08 03:59
Oracle 18c introduces the ability to convert a Partitioned Table from one Type to another -- e.g. from Hash Partitioning to Range Partitioning.  This is effectively a change of the Partitioning strategy for a table without actually having to manually rebuild the table.

I start with a Hash Partitioned Table.

SQL> create table customers(customer_id number, customer_name varchar2(200), customer_city_code number)
2 partition by hash (customer_id) partitions 4;

Table created.

SQL> select partitioning_type from user_part_tables
2 where table_name = 'CUSTOMERS'
3 /

PARTITION
---------
HASH

SQL> select partition_name from user_tab_partitions
2 where table_name = 'CUSTOMERS'
3 /

PARTITION_NAME
--------------------------------------------------------------------------------
SYS_P221
SYS_P222
SYS_P223
SYS_P224

SQL>
SQL> insert into customers
2 select dbms_random.value(1,1000001), dbms_random.string('X',25), mod(rownum,5)
3 from dual
4 connect by level < 1000001
5 /

1000000 rows created.

SQL> commit;

Commit complete.

SQL> exec dbms_stats.gather_table_stats('','CUSTOMERS');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows
2 from user_tab_partitions
3 where table_name = 'CUSTOMERS'
4 /

PARTITION_NAME NUM_ROWS
---------------- ----------
SYS_P221 250090
SYS_P222 249563
SYS_P223 250018
SYS_P224 250329

SQL>


I now want to convert this Hash Partitioned Table to a Range Partitioned Table online.

SQL> alter table customers
2 modify
3 partition by range (customer_id)
4 (partition P_100K values less than (100001),
5 partition P_200K values less than (200001),
6 partition P_300K values less than (300001),
7 partition P_400K values less than (400001),
8 partition P_500K values less than (500001),
9 partition P_600K values less than (600001),
10 partition P_700K values less than (700001),
11 partition P_800K values less than (800001),
12 partition P_900K values less than (900001),
13 partition P_1MIL values less than (1000001),
14 partition P_2MIL values less than (2000001),
15 partition P_MAXVALUE values less than (MAXVALUE))
16 online;

Table altered.

SQL>
SQL> select partitioning_type
2 from user_part_tables
3 where table_name = 'CUSTOMERS'
4 /

PARTITION
---------
RANGE

SQL> exec dbms_stats.gather_table_stats('','CUSTOMERS');

PL/SQL procedure successfully completed.

SQL> col high_value format a12
SQL> select partition_name, high_value, num_rows
2 from user_tab_partitions
3 where table_name = 'CUSTOMERS'
4 order by partition_position
5 /

PARTITION_NAME HIGH_VALUE NUM_ROWS
---------------- ------------ ----------
P_100K 100001 100116
P_200K 200001 99604
P_300K 300001 99941
P_400K 400001 100048
P_500K 500001 99841
P_600K 600001 99920
P_700K 700001 100081
P_800K 800001 100024
P_900K 900001 100123
P_1MIL 1000001 100302
P_2MIL 2000001 0
P_MAXVALUE MAXVALUE 0

12 rows selected.

SQL>


The Hash Partitioned Table is now converted to a Range Partitioned Table.  The number of Partitions has been changed.  And the operation was performed online with the ONLINE keyword added to the ALTER TABLE ... statement.  The UPDATE INDEXES clauses can also be used to update existing Indexes on the Table.





Categories: DBA Blogs

Everyone should read this

Thu, 2019-04-04 02:10
An excellent article that anyone promising, developing, maintaining or using any system that is non-trivial should read :

https://embeddedartistry.com/blog/2019/4/1/what-can-software-organizations-learn-from-the-boeing-737-max-saga




Categories: DBA Blogs

Partitioning -- 14 : Converting a non-Partitioned Table to a Partitioned Table

Mon, 2019-03-25 09:59
Pre-12cRelease2, there were only three methods to convert a non-Partitioned Table to a Partitioned Table

(a) Create a new, empty, Partitioned Table and copy (using INSERT .... AS SELECT ... ) all the data from the non-Partitioned Table to the new, Partitioned Table (and subsequently rename the new Partitioned Table after renaming or dropping the old non-Partitioned Table)

(b) Create a new, empty, Partitioned Table and use EXCHANGE PARTITION to switch the non-Partitioned Table into the Partitioned Table (and then run subsequent SPLIT PARTITION or ADD PARTITION commands as needed to create the additional Partitions)

(c) Create an interim Partitioned Table and use DBMS_REDEFINITION to do an online copy of the data to the interim Partitioned Table and automatically switch the name at the end


12.2 introduced the ability to use ALTER TABLE  ... MODIFY PARTITION ... to convert a non-Partitioned Table to a Partitioned Table

I start with a non-Partitioned Table :

SQL> select table_name, partitioned
2 from user_tables
3 where table_name = 'SALES_DATA_NONPARTITIONED'
4 /

TABLE_NAME PAR
------------------------------ ---
SALES_DATA_NONPARTITIONED NO

SQL> select index_name, uniqueness, partitioned
2 from user_indexes
3 where table_name = 'SALES_DATA_NONPARTITIONED'
4 /

INDEX_NAME UNIQUENES PAR
------------------------------ --------- ---
SALES_DATA_UK UNIQUE NO

SQL>


I then convert it to a Range-Partitioned Table.

SQL> alter table sales_data_nonpartitioned
2 modify
3 partition by range (sale_date)
4 (
5 partition P_2015 values less than (to_date('01-JAN-2016','DD-MON-YYYY')),
6 partition P_2016 values less than (to_date('01-JAN-2017','DD-MON-YYYY')),
7 partition P_2017 values less than (to_date('01-JAN-2018','DD-MON-YYYY')),
8 partition P_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY')),
9 partition P_2019 values less than (to_date('01-JAN-2020','DD-MON-YYYY')),
10 partition p_MAXVALUE values less than (MAXVALUE)
11 )
12 online
13 update indexes
14 /

Table altered.

SQL>
SQL> alter table sales_data_nonpartitioned rename to sales_data;

Table altered.

SQL>
SQL> select partition_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME HIGH_VALUE
---------------- --------------------------
P_2015 TO_DATE(' 2016-01-01 00:00
P_2016 TO_DATE(' 2017-01-01 00:00
P_2017 TO_DATE(' 2018-01-01 00:00
P_2018 TO_DATE(' 2019-01-01 00:00
P_2019 TO_DATE(' 2020-01-01 00:00
P_MAXVALUE MAXVALUE

6 rows selected.

SQL>
SQL> select index_name, partitioned, uniqueness, status
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 /

INDEX_NAME PAR UNIQUENES STATUS
------------------------------ --- --------- --------
SALES_DATA_UK NO UNIQUE VALID

SQL>


The SALES_DATA_NONPARTITIONED was converted to a Range Partitioned Table.  If I didn't have to rename the table (e.g. if the table name was actually, properly SALES_DATA only), then there would be no need to lock the table as the RENAME command does.



Categories: DBA Blogs

Partitioning -- 13d : TRUNCATE and DROP Partitions and Global Indexes

Wed, 2019-03-20 07:11
A TRUNCATE or DROP Partition makes Global Indexes on a Partitioned Table UNUSABLE.

You may be lucky if the target partition was empty, resulting in Oracle maintaining Global Indexes as valid.  However, the accepted rule is that you either (a) use the UPDATE INDEXES clause [resulting in the TRUNCATE or DROP taking longer to run, effectively locking the table partitions] OR  (b) do a REBUILD of the Indexes that become UNUSABLE after the TRUNCATE or DROP.

12c has introduced what it calls Asynchronous Global Index Maintenance.  With this feature present, the TRUNCATE or DROP runs much faster as a DDL without actually removing the target rows from the Global Indexes [but still requires the UPDATE INDEXES clause to be specified]

So, now in my 12.2 database I have these two Indexes on SALES_DATA :

SQL> select index_name, partitioned, status
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 order by 2,1
5 /

INDEX_NAME PAR STATUS
------------------------------ --- --------
SALES_DATA_PK NO VALID
SALES_DATA_LCL_NDX_1 YES N/A

SQL>


I then TRUNCATE a non-empty Partition and check the Indexes

SQL> alter table sales_data truncate partition P_2015 update indexes;

Table truncated.

SQL>
SQL> select index_name, partitioned, status, orphaned_entries
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 order by 2,1
5 /

INDEX_NAME PAR STATUS ORP
------------------------------ --- -------- ---
SALES_DATA_PK NO VALID YES
SALES_DATA_LCL_NDX_1 YES N/A NO

SQL>


The ORPHANED_ENTRIES column indicates that SALES_DATA_PK is subject to Asynchronous Index Maintenance.

This is the job that will do the Index Maintenance at 2am  :

SQL> l
1 select owner, job_name, last_start_date, next_run_Date
2 from dba_scheduler_jobs
3* where job_name = 'PMO_DEFERRED_GIDX_MAINT_JOB'
SQL> /

OWNER
---------------------------------------------------------------------------
JOB_NAME
---------------------------------------------------------------------------
LAST_START_DATE
---------------------------------------------------------------------------
NEXT_RUN_DATE
---------------------------------------------------------------------------
SYS
PMO_DEFERRED_GIDX_MAINT_JOB
20-MAR-19 10.18.51.215433 AM UTC
21-MAR-19 02.00.00.223589 AM UTC


SQL> !date
Wed Mar 20 20:05:24 SGT 2019

SQL>


So, I could
(1) wait for the next run of the job OR
(2) manually trigger the job (which will scan the entire database for all indexes that require such maintenance) OR
(3) Execute  DBMS_PART.CLEANUP_GIDX  to initiate the maintenance for the specific index OR
(4) Execute an ALTER INDEX REBUILD to make the Index USABLE again.

SQL> execute dbms_part.cleanup_gidx('HEMANT','SALES_DATA');

PL/SQL procedure successfully completed.

SQL> select index_name, partitioned, status, orphaned_entries
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 order by 2,1
5 /

INDEX_NAME PAR STATUS ORP
------------------------------ --- -------- ---
SALES_DATA_PK NO VALID NO
SALES_DATA_LCL_NDX_1 YES N/A NO

SQL>


Note that the argument to CLEANUP_GIDX is the *Table Name*, not an Index Name.


Here I have demonstrated a TRUNCATE Partition, but the same method would be usable for a DROP Partition.




Categories: DBA Blogs

Partitioning -- 13c : Merging Partitions

Tue, 2019-03-12 07:18
The reverse of SPLITting a Partition is to MERGE two adjacent partitions.

I reverse the SPLIT that I did in the previous blog post.

SQL> l
1 select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4* order by partition_position
SQL> /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016 ARCHIVE_SALES_DATA TO_DATE(' 2017-01-01 00:00
P_2017 TBS_YEAR_2017 TO_DATE(' 2018-01-01 00:00
P_2018 TBS_YEAR_2018 TO_DATE(' 2019-01-01 00:00
P_2019_H1 TBS_YEAR_2019 TO_DATE(' 2019-07-01 00:00
P_2019_H2 TBS_YEAR_2019 TO_DATE(' 2020-01-01 00:00
P_2020 TBS_YEAR_2020 TO_DATE(' 2021-01-01 00:00
P_MAXVALUE USERS MAXVALUE

7 rows selected.

SQL>
SQL> alter table sales_data
2 merge partitions P_2019_H1, P_2019_H2
3 into partition P_2019
4 update indexes
5 /

Table altered.

SQL>
SQL> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016 ARCHIVE_SALES_DATA TO_DATE(' 2017-01-01 00:00
P_2017 TBS_YEAR_2017 TO_DATE(' 2018-01-01 00:00
P_2018 TBS_YEAR_2018 TO_DATE(' 2019-01-01 00:00
P_2019 HEMANT TO_DATE(' 2020-01-01 00:00
P_2020 TBS_YEAR_2020 TO_DATE(' 2021-01-01 00:00
P_MAXVALUE USERS MAXVALUE

6 rows selected.

SQL>


But, we find that the new Partition was created in the default "HEMANT"  tablespace !  So, we have to be careful about specifying target tablespace(s).

Let me reverse the action and try again.

SQL> alter table sales_data
2 split partition P_2019 at (to_date('01-JUL-2019','DD-MON-YYYY'))
3 into (partition P_2019_H1 tablespace TBS_YEAR_2019, partition P_2019_H2 tablespace TBS_YEAR_2019)
4 update indexes
5 /

Table altered.

SQL>
SQL> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016 ARCHIVE_SALES_DATA TO_DATE(' 2017-01-01 00:00
P_2017 TBS_YEAR_2017 TO_DATE(' 2018-01-01 00:00
P_2018 TBS_YEAR_2018 TO_DATE(' 2019-01-01 00:00
P_2019_H1 TBS_YEAR_2019 TO_DATE(' 2019-07-01 00:00
P_2019_H2 TBS_YEAR_2019 TO_DATE(' 2020-01-01 00:00
P_2020 TBS_YEAR_2020 TO_DATE(' 2021-01-01 00:00
P_MAXVALUE USERS MAXVALUE

7 rows selected.

SQL> alter table sales_data
2 merge partitions P_2019_H1, P_2019_H2
3 into partition P_2019 tablespace TBS_YEAR_2019
4 update indexes
5 /

Table altered.

SQL> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016 ARCHIVE_SALES_DATA TO_DATE(' 2017-01-01 00:00
P_2017 TBS_YEAR_2017 TO_DATE(' 2018-01-01 00:00
P_2018 TBS_YEAR_2018 TO_DATE(' 2019-01-01 00:00
P_2019 TBS_YEAR_2019 TO_DATE(' 2020-01-01 00:00
P_2020 TBS_YEAR_2020 TO_DATE(' 2021-01-01 00:00
P_MAXVALUE USERS MAXVALUE

6 rows selected.

SQL>


So, when running Table Partition DDL, be careful about the intended and actual target Tablespace(s).  (What about Index Partitions ?  The UPDATE INDEXES clause can specify target tablespaces for each Index Partition of each Index as well ... something like : (this is from the documentation on the ALTER TABLE command)
UPDATE INDEXES (cost_ix (PARTITION c_p1 tablespace tbs_02, 
PARTITION c_p2 tablespace tbs_03))
Categories: DBA Blogs

Partitioning -- 13b : Splitting a Partition

Sun, 2019-01-13 05:46
Let's say the business anticipates growing sales volume in 2019 and new reporting requirements.  IT analyses the requirements and decides that the SALES_DATA Table that is currently Partitioned by YEAR, needs to be Partitioned by HALF-YEAR from 2019 onwards.

SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_2016 ARCHIVE_SALES_DATA
P_2017 TBS_YEAR_2017
P_2018 TBS_YEAR_2018
P_2019 TBS_YEAR_2019
P_2020 TBS_YEAR_2020
P_MAXVALUE USERS

6 rows selected.

SQL>
SQL> alter table sales_data
2 split partition P_2019 at (to_date('01-JUL-2019','DD-MON-YYYY'))
3 into (partition P_2019_H1, partition P_2019_H2)
4 update indexes
5 /

Table altered.

SQL>
SQL> col high_value format a26 trunc
SQL> select partition_name, tablespace_name, high_value
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------
P_2016 ARCHIVE_SALES_DATA TO_DATE(' 2017-01-01 00:00
P_2017 TBS_YEAR_2017 TO_DATE(' 2018-01-01 00:00
P_2018 TBS_YEAR_2018 TO_DATE(' 2019-01-01 00:00
P_2019_H1 TBS_YEAR_2019 TO_DATE(' 2019-07-01 00:00
P_2019_H2 TBS_YEAR_2019 TO_DATE(' 2020-01-01 00:00
P_2020 TBS_YEAR_2020 TO_DATE(' 2021-01-01 00:00
P_MAXVALUE USERS MAXVALUE

7 rows selected.

SQL>


I used the UPDATE INDEXES clause to ensure that all (specifically Global) Indexes affected by the SPLIT are updated so that they don't go into an UNUSABLE state.

I could have optionally used a TABLESPACE clause for each of the two new Partitions P_2019_H1 and P_2019_H2


(Also see a previous BlogPost on using SPLIT PARTITION to add a new Partition at the "end" of the table by splitting the last Partition)



Categories: DBA Blogs

Partioning -- 13a : Relocating a Partition

Sun, 2018-12-16 05:19
When you want to / need to move a Partition to a different Tablespace (e.g. as part of a LifeCycle Management Policy), you may need downtime to relocate the Partition.  However, version 12cRelease1 allows Online Relocation of a Partition.

Let's say I have a SALES_DATA table and I need to move the Year 2016 data to a tablespace with datafiles on "cheaper" (lesss-performant) storage :

SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NAME TABLESPACE_NAME
------------------------------ ------------------------------
P_2016 TBS_YEAR_2016
P_2017 TBS_YEAR_2017
P_2018 TBS_YEAR_2018
P_2019 TBS_YEAR_2019
P_2020 TBS_YEAR_2020
P_MAXVALUE USERS

6 rows selected.

SQL>
SQL> select index_name, tablespace_name, status
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 /

INDEX_NAME TABLESPACE_NAME STATUS
------------------------------ ------------------------------ --------
SYS_C0017514 HEMANT VALID
SALES_DATA_LCL_NDX_1 N/A
SALES_DATA_LCL_NDX_2 N/A

SQL>
SQL> l
1 select index_name, partition_name, tablespace_name, status
2 from user_ind_partitions
3 where index_name in
4 (select index_name
5 from user_indexes
6 where table_name = 'SALES_DATA')
7* order by index_name, partition_position
SQL> /

INDEX_NAME PARTITION_NA TABLESPACE_NAM STATUS
------------------------------ ------------ -------------- --------
SALES_DATA_LCL_NDX_1 P_2016 TBS_YEAR_2016 USABLE
SALES_DATA_LCL_NDX_1 P_2017 TBS_YEAR_2017 USABLE
SALES_DATA_LCL_NDX_1 P_2018 TBS_YEAR_2018 USABLE
SALES_DATA_LCL_NDX_1 P_2019 TBS_YEAR_2019 USABLE
SALES_DATA_LCL_NDX_1 P_2020 TBS_YEAR_2020 USABLE
SALES_DATA_LCL_NDX_1 P_MAXVALUE USERS USABLE
SALES_DATA_LCL_NDX_2 P_2016 TBS_YEAR_2016 USABLE
SALES_DATA_LCL_NDX_2 P_2017 TBS_YEAR_2017 USABLE
SALES_DATA_LCL_NDX_2 P_2018 TBS_YEAR_2018 USABLE
SALES_DATA_LCL_NDX_2 P_2019 TBS_YEAR_2019 USABLE
SALES_DATA_LCL_NDX_2 P_2020 TBS_YEAR_2020 USABLE
SALES_DATA_LCL_NDX_2 P_MAXVALUE USERS USABLE

12 rows selected.

SQL>


I then want to move the Year 2016 data to the Tablespace ARCHIVE_SALES_DATA :

SQL> alter table SALES_DATA
2 move partition P_2016
3 tablespace ARCHIVE_SALES_DATA
4 /

Table altered.

SQL>
SQL> select partition_name, tablespace_name
2 from user_tab_partitions
3 where table_name = 'SALES_DATA'
4 order by partition_position
5 /

PARTITION_NA TABLESPACE_NAME
------------ ------------------
P_2016 ARCHIVE_SALES_DATA
P_2017 TBS_YEAR_2017
P_2018 TBS_YEAR_2018
P_2019 TBS_YEAR_2019
P_2020 TBS_YEAR_2020
P_MAXVALUE USERS

6 rows selected.

SQL>
SQL> select index_name, tablespace_name, partitioned, status
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 order by index_name
5 /

INDEX_NAME TABLESPACE_NAME PAR STATUS
------------------------------ ------------------ --- --------
SALES_DATA_LCL_NDX_1 YES N/A
SALES_DATA_LCL_NDX_2 YES N/A
SYS_C0017514 HEMANT NO UNUSABLE

SQL> alter index SYS_C0017514 rebuild ;

Index altered.

SQL> select index_name, tablespace_name, partitioned, status
2 from user_indexes
3 where table_name = 'SALES_DATA'
4 order by index_name
5 /

INDEX_NAME TABLESPACE_NAME PAR STATUS
------------------------------ ------------------ --- --------
SALES_DATA_LCL_NDX_1 YES N/A
SALES_DATA_LCL_NDX_2 YES N/A
SYS_C0017514 HEMANT NO VALID

SQL>
SQL> l
1 select index_name, partition_name, tablespace_name, status
2 from user_ind_partitions
3 where index_name in
4 (select index_name
5 from user_indexes
6 where table_name = 'SALES_DATA')
7* order by index_name, partition_position
SQL> /

INDEX_NAME PARTITION_NA TABLESPACE_NAME STATUS
------------------------------ ------------ ------------------ --------
SALES_DATA_LCL_NDX_1 P_2016 TBS_YEAR_2016 UNUSABLE
SALES_DATA_LCL_NDX_1 P_2017 TBS_YEAR_2017 USABLE
SALES_DATA_LCL_NDX_1 P_2018 TBS_YEAR_2018 USABLE
SALES_DATA_LCL_NDX_1 P_2019 TBS_YEAR_2019 USABLE
SALES_DATA_LCL_NDX_1 P_2020 TBS_YEAR_2020 USABLE
SALES_DATA_LCL_NDX_1 P_MAXVALUE USERS USABLE
SALES_DATA_LCL_NDX_2 P_2016 TBS_YEAR_2016 UNUSABLE
SALES_DATA_LCL_NDX_2 P_2017 TBS_YEAR_2017 USABLE
SALES_DATA_LCL_NDX_2 P_2018 TBS_YEAR_2018 USABLE
SALES_DATA_LCL_NDX_2 P_2019 TBS_YEAR_2019 USABLE
SALES_DATA_LCL_NDX_2 P_2020 TBS_YEAR_2020 USABLE
SALES_DATA_LCL_NDX_2 P_MAXVALUE USERS USABLE

12 rows selected.

SQL>
SQL> alter index SALES_DATA_LCL_NDX_1
2 rebuild partition P_2016
3 tablespace ARCHIVE_SALES_DATA
4 /

Index altered.

SQL>
SQL> alter index SALES_DATA_LCL_NDX_2
2 rebuild partition P_2016
3 tablespace ARCHIVE_SALES_DATA
4 /

Index altered.

SQL>
SQL> l
1 select index_name, partition_name, tablespace_name, status
2 from user_ind_partitions
3 where index_name in
4 (select index_name
5 from user_indexes
6 where table_name = 'SALES_DATA')
7* order by index_name, partition_position
SQL> /

INDEX_NAME PARTITION_NA TABLESPACE_NAME STATUS
------------------------------ ------------ ------------------ --------
SALES_DATA_LCL_NDX_1 P_2016 ARCHIVE_SALES_DATA USABLE
SALES_DATA_LCL_NDX_1 P_2017 TBS_YEAR_2017 USABLE
SALES_DATA_LCL_NDX_1 P_2018 TBS_YEAR_2018 USABLE
SALES_DATA_LCL_NDX_1 P_2019 TBS_YEAR_2019 USABLE
SALES_DATA_LCL_NDX_1 P_2020 TBS_YEAR_2020 USABLE
SALES_DATA_LCL_NDX_1 P_MAXVALUE USERS USABLE
SALES_DATA_LCL_NDX_2 P_2016 ARCHIVE_SALES_DATA USABLE
SALES_DATA_LCL_NDX_2 P_2017 TBS_YEAR_2017 USABLE
SALES_DATA_LCL_NDX_2 P_2018 TBS_YEAR_2018 USABLE
SALES_DATA_LCL_NDX_2 P_2019 TBS_YEAR_2019 USABLE
SALES_DATA_LCL_NDX_2 P_2020 TBS_YEAR_2020 USABLE
SALES_DATA_LCL_NDX_2 P_MAXVALUE USERS USABLE

12 rows selected.

SQL>


When I relocated the P_2016 Partition (to the ARCHIVE_SALES_DATA Tablespace), the ROWIDs for rows in that Partition changed.  So the Non-Partitioned Index SYS_C0017514 and the corresponding Local Partitions of the two Partitioned Indexes became "UNUSABLE".  These had to be rebuilt. Alternatively, I could have added the UPDATE INDEXES clause to to the ALTER TABLE ... MOVE PARTITION .. statement to reset the Indexes to Usable but this would not have relocated the Local Partitions for those two Indexes to the new Tablespace.

Note that for Table Partitions, the MOVE clause relocates the Partition but for Index Partition the REBUILD clause is used to relocate (as well as make Usable) the Partition.

I would encourage you to view documentation and examples of the MOVE ONLINE facility in 12c to relocate a Table Partition without downtime.


Categories: DBA Blogs

Pages