undo - ORACLEAGENT BLOG

Oracle Materialized View

A materialized view in Oracle is a database object that contains the results of a 
query.They are local copies of data located remotely, or are used to create summary 
tables based on aggregations of a table’s data.

A materialized view can query tables, views, and other materialized views.
A materialized view, or snapshot as they were previously known, is a table segment 
whose contents are periodically refreshed based on a query, either against a local 
or remote table.

Syntax

CREATE MATERIALIZED VIEW view-name BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ][[ENABLE | DISABLE] QUERY REWRITE] AS SELECT …;

Methods

BUILD

IMMEDIATE : The materialized view is populated immediately.This option is default one.
DEFERRED : The materialized view is populated on the first requested refresh.

Refresh Types

FAST: A fast refresh is attempted only there is a change in the base table. If materialized view logs are not present against the source tables in advance, the creation fails. To maintain the history of change in the base table, it is known as materialized view log.It is named as MLOG$_<base_table>. Materialized view log will be located in the source database in the same schema as the master table. Refresh fast will perform refresh according to the changes that occurred in the master table.
COMPLETE: The table segment supporting the materialized view is truncated and repopulated completely using the associated query.
FORCE: A fast refresh is attempted. If one is not possible a complete refresh is performed.

ON COMMIT : The refresh is triggered by a committed data change in one of the 
dependent tables.
ON DEMAND : The refresh is initiated by a manual request or a scheduled task.

GRANT CREATE MATERIALIZED VIEW TO INDIA;
Grant succeeded.

GRANT CREATE DATABASE LINK TO INDIA;
Grant succeeded.

Creating Database link to point remote database 

SQL> CREATE DATABASE LINK PROD_LINK CONNECT TO ph IDENTIFIED BY ph USING 'orcl';

Database link created.

COMPLETE REFRESH

Creating Materialized View

SQL> CREATE MATERIALIZED VIEW dept_mv BUILD IMMEDIATE REFRESH COMPLETE AS 
SELECT * FROM ph.emp@PROD_LINK;

Materialized view created.

SQL> select * from emp;

        NO NAME                     SALARY
---------- -------------------- ----------
         1 apple                     20000
         2 orange                    23000
         3 mango                     32000

Table Data present in Remote database

SQL> insert into emp values (6,'ice',30000);

1 row created.

SQL> commit;

Commit complete.

SQL> select rowid,no,name,salary from dept_mv;

ROWID                      NO NAME                     SALARY
------------------ ---------- -------------------- ----------
AAAR36AABAAAbURAAA          1 apple                     20000
AAAR36AABAAAbURAAB          2 orange                    23000
AAAR36AABAAAbURAAC          3 mango                     32000

Manually using complete refresh using the DBMS_VIEW package

SQL>  execute DBMS_MVIEW.REFRESH( LIST => 'dept_mv', METHOD => 'C' );

PL/SQL procedure successfully completed.

SQL> select rowid,no,name,salary from dept_mv;

ROWID                      NO NAME                     SALARY
------------------ ---------- -------------------- ----------
AAAR36AABAAAbURAAD          1 apple                     20000
AAAR36AABAAAbURAAE          2 orange                    23000
AAAR36AABAAAbURAAF          3 mango                     32000
AAAR36AABAAAbURAAG          6 ice                       30000

DEFERRED

Initially drop the existing materialized view

SQL> drop MATERIALIZED VIEW dept_mv;

Materialized view dropped.

Materialized view creation using DEFERRED option

SQL> CREATE MATERIALIZED VIEW dept_mv BUILD DEFERRED REFRESH COMPLETE AS 
SELECT * FROM ph.emp@PROD_LINK;

Materialized view created.


SQL> execute DBMS_MVIEW.REFRESH( LIST => 'dept_mv', METHOD => 'C' );

PL/SQL procedure successfully completed.

QL> select rowid,no,name,salary from dept_mv;

ROWID                      NO NAME                     SALARY
------------------ ---------- -------------------- ----------
AAAR39AABAAAbURAAA          1 apple                     20000
AAAR39AABAAAbURAAB          2 orange                    23000
AAAR39AABAAAbURAAC          3 mango                     32000
AAAR39AABAAAbURAAD          6 ice                       30000

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter: https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

FLASHBACK CONCEPTS

FLASHBACK VERSION QUERY

FLASHBACK DROP TABLE

FLASHBACK QUERY

FLASHBACK TABLE

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

FLASHBACK DROP TABLE (RECYCLE BIN)

FLASHBACK DROP TABLE

The recycle bin is a logical collection of previously dropped objects. This feature doesn’t use flashback logs or undo, so it is distinct from the other flashback technologies.

Enable/Disable Recycle Bin

SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      OFF

SQL> alter session set recyclebin = on;

Session altered.

SQL> show parameter recyclebin;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      ON

(RECYCLE BIN)

SQL> CREATE TABLE flashback_drop_test (id  NUMBER(10));

Table created.

SQL> INSERT INTO flashback_drop_test (id) VALUES (1);

1 row created.

SQL> commit;

Commit complete.


SQL> DROP TABLE flashback_drop_test;

Table dropped.

SQL> show recyclebin;

ORIGINAL NAME    RECYCLEBIN NAME                OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
FLASHBACK_DROP_T BIN$TstgCMiwQA66fl5FFDTBgA==$0 TABLE        2004-03-29:11:09:07
EST

SQL> FLASHBACK TABLE flashback_drop_test TO BEFORE DROP;

SELECT * FROM flashback_drop_test;

        ID
----------
         1
Rename Table

SQL> show RECYCLEBIN;

ORIGINAL NAME	 RECYCLEBIN NAME		OBJECT TYPE  DROP TIME
---------------- ------------------------------ ------------ -------------------
S		 BIN$x3mKgoseD/7gUxkBqMAnAA==$0 TABLE	     2021-07-19:17:20:17
SQL> create table s(n number);

Table created.

SQL> FLASHBACK TABLE s TO BEFORE DROP;
FLASHBACK TABLE s TO BEFORE DROP
*
ERROR at line 1:
ORA-38312: original name is used by an existing object


SQL> FLASHBACK TABLE s TO BEFORE DROP rename to b;

Flashback complete.

SQL> select * from b;

no rows selected

SQL> show RECYCLEBIN;

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Feb, 2021

Oracle database Health check scripts

PERFOMANCE TUNING SCRIPTS

MONITOR SESSION
SQL PROFILE & BASELINE
ALL IN ONE SCRIPT
HISTORY OF EXECUTED QUERIES
SQL HISTORY
WAIT EVENTS, AWR ,ASH , TIME MODEL SCRIPTS
MONITOR SQL’S

MAIL SCRIPTS

Get DDL ‘s For All objects in oracle database

TEMP TABLESPACE scripts

UNDO TABLESPACE scripts

Thank you for giving your valuable time to read the above information.
If you want to be updated with all our articles send us the Invitation or Follow us:
Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/
LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/
Facebook Page: https://www.facebook.com/Oracleagent-344577549964301
Ramkumar’s Twitter : https://twitter.com/ramkuma02877110
Ramkumar’s Telegram: https://t.me/oracleageant
Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

UNDO scripts

v$parameter for undo:

select inst_id,name,value from gv$parameter where name like '%undo%';

UNDO tablespaces Free As per expired segments:

SELECT d.tablespace_name, round(((NVL(f.bytes,0) + (a.maxbytes - a.bytes))/1048576+ u.exp_space),2) as max_free_mb, round(((a.bytes - (NVL(f.bytes,0)+ (10241024u.exp_space)))100/a.maxbytes),2) used_pct FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes, sum(greatest(maxbytes,bytes)) maxbytes from sys.dba_data_files group by tablespace_name) a, (select tablespace_name, sum(bytes) bytes from sys.dba_free_space group by tablespace_name) f , (select tablespace_name , sum(blocks)8/(1024) exp_space from dba_undo_extents where status NOT IN ('ACTIVE','UNEXPIRED') group by tablespace_name) u WHERE d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND d.tablespace_name=u.tablespace_name AND d.contents = 'UNDO' AND u.tablespace_name = (select UPPER(value) from v$parameter where name = 'undo_tablespace');

does undo have expired segments?

select status, count(1) from dba_undo_extents group by status;

User Generated UNDO

col sql_text format a40 set lines 130 select sq.sql_text sql_text, t.USED_UREC Records, t.USED_UBLK Blocks, (t.USED_UBLK*8192/1024) KBytes from v$transaction t, v$session s, v$sql sq where t.addr = s.taddr and s.sql_id = sq.sql_id and s.username = '<user>' /

UNDO tablespace usage by USER

SELECT TO_CHAR(s.sid)||','||TO_CHAR(s.serial#) sid_serial, NVL(s.username, 'None') orauser, s.program, r.name undoseg, t.used_ublk * TO_NUMBER(x.value)/1024||'K' "Undo" FROM sys.v_$rollname r, sys.v_$session s, sys.v_$transaction t, sys.v_$parameter x WHERE s.taddr = t.addr AND r.usn = t.xidusn(+) AND x.name = 'db_block_size'; (or) select username, t.used_ublk ,t.used_urec from gv$transaction t,gv$session s where t.addr=s.taddr and s.sid='&sid';

Recreating the undo tablespace or resolving to undo issue

select a.name,b.status , d.username , d.sid , d.serial# from v$rollname a,v$rollstat b, v$transaction c , v$session d where a.name IN ( select segment_name from dba_segments where tablespace_name = 'UNDOTBS1') and a.usn = b.usn and a.usn = c.xidusn and c.ses_addr = d.saddr;

Ref: https://community.oracle.com/tech/developers/discussion/1098943/create-new-undo-tablespace-and-remove-the-old-one

ORA-30019: Illegal rollback Segment operation in Automatic Undo mode

ALTER SYSTEM SET UNDO_SUPPRESS_ERRORS=TRUE scope=both;

If TRUE means it will not show any undo errors, So some times job may complete successfully.

Find active, expired, unexpired undo

select status, round(sum_bytes / (1024*1024), 0) as MB, round((sum_bytes / undo_size) * 100, 0) as PERC from ( select status, sum(bytes) sum_bytes from dba_undo_extents group by status ), ( select sum(a.bytes) undo_size from dba_tablespaces c join v$tablespace b on b.name = c.tablespace_name join v$datafile a on a.ts# = b.ts# where c.contents = 'UNDO' and c.status = 'ONLINE' ); select count(status) from dba_undo_extents where status = 'EXPIRED'; select count(status) from dba_undo_extents where status = 'UNEXPIRED'; select count(status) from dba_undo_extents where status = 'ACTIVE';

Find active, expired, and unexpired undo by Size in MB

select status,TABLESPACE_NAME,sum(bytes) / 1024 / 1024 || 'MB' from DBA_UNDO_EXTENTS group by status,TABLESPACE_NAME; select status, sum(bytes) / 1024 / 1024 || 'MB' from DBA_UNDO_EXTENTS group by status;

Undo advisor:

SELECT d.undo_size/(10241024) "ACTUAL UNDO SIZE [MByte]", SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]", ROUND((d.undo_size / (to_number(f.value) * g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]" FROM ( SELECT SUM(a.bytes) undo_size FROM v$datafile a, v$tablespace b, dba_tablespaces c WHERE c.contents = 'UNDO' AND c.status = 'ONLINE' AND b.name = c.tablespace_name AND a.ts# = b.ts# ) d, v$parameter e, v$parameter f, ( SELECT MAX(undoblks/((end_time-begin_time)3600*24)) undo_block_per_sec FROM v$undostat ) g WHERE e.name = 'undo_retention' AND f.name = 'db_block_size'; (or) SELECT dbms_undo_adv.required_undo_size(1800, SYSDATE-30, SYSDATE) FROM dual;

Thank you for giving your valuable time to read the above information. If you want to be updated with all our articles send us the Invitation or Follow us: Ramkumar’s LinkedIn: https://www.linkedin.com/in/ramkumardba/ LinkedIn Group: https://www.linkedin.com/in/ramkumar-m-0061a0204/ Facebook Page: https://www.facebook.com/Oracleagent-344577549964301 Ramkumar’s Twitter : https://twitter.com/ramkuma02877110 Ramkumar’s Telegram: https://t.me/oracleageant Ramkumar’s Facebook: https://www.facebook.com/ramkumarram8

Tag Archives: undo

Oracle Materialized View

Syntax

Methods

BUILD

Refresh Types

COMPLETE REFRESH

Creating Materialized View

Table Data present in Remote database

Manually using complete refresh using the DBMS_VIEW package

DEFERRED

Initially drop the existing materialized view

Materialized view creation using DEFERRED option

FLASHBACK DROP TABLE

Enable/Disable Recycle Bin

(RECYCLE BIN)

Feb, 2021

v$parameter for undo:

UNDO tablespaces Free As per expired segments:

does undo have expired segments?

User Generated UNDO

UNDO tablespace usage by USER

Recreating the undo tablespace or resolving to undo issue

ORA-30019: Illegal rollback Segment operation in Automatic Undo mode

Find active, expired, unexpired undo

Find active, expired, and unexpired undo by Size in MB

Undo advisor: