alter system flush shared_pool delete a shared pool Alter system flush buffer_cache delete buffer cache can only be applied on 10g database buffer cache in MRU and LRU blocks Blocks within the buffer cache are ordered from MRU (most recently used) blocks to LRU (least recently used) blocks. Whenever a block is accessed, the block goes to the MRU end of the list, thereby shifting the other blocks down towards the LRU end. When a block is read from disk and when there is no buffer available in the db buffer cache, one block in the buffer cache has to "leave". It will be the block on the LRU end in the list. However, blocks read during a full table (multi block reads) are placed on the LRU side of the list instead of on the MRU side. buffer busy wait If two processes try (almost) simultaneously the same block and the block is not resident in the buffer cache, one process will allocate a buffer in the buffer cache and lock it and the read the block into The buffer. The Other process is locked untric the block is read. this wait is refered to as buffer busy wait Controlling the Report You can control the report by setting the AUTOTRACE system variable SET AUTOTRACE OFF -.. No AUTOTRACE report is generated This is the default SET AUTOTRACE ON EXPLAIN -.. The AUTOTRACE report shows only the optimizer execution path SET AUTOTRACE ON sTATISTICS -. The AUTOTRACE report shows only the SQL statement execution statistics SET AUTOTRACE ON -. The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics SET AUTOTRACE TRACEONLY -. Like SET AUTOTRACE ON, but suppresses The printing of the user's query output, if any. Get all tables for the user Select * from user_tables;
Get the user's materialized view Select * from user_mviews Oracle Getting System Information Oracle through special forms: Using special table Getting system information Oracle developers can rely on a name for DUAL as a virtual table to write SQL query, There is a proprietary noun in the system variable. The following two examples demonstrate this way: select 'test query run on date' || sysdate from dual; the above statement returns the following output: Test Query Run on Date 2001-Feb-01 Get server IP SELECT SYS_CONTEXT ('useerenv', 'ip_address') from Dual Dual Table is a single-line form, defined as follows: Create Table Dual (Dummy Varchar2 (1)); Insert Into Dual Values 'X'; if Table is deleted, then re-emphasize the permissions grant selection on sys.dual to public with grant option; in SQL Server, you can use the T-SQL encoding: SQL does not need select getdate () Go --- -------- TIM Quinlan How to Get the DDL SELECT DBMS_METADATA.GET_DDL ('MATERIZED_VIEW', 'MVIEW_NAME') from dual Select dbms_metadata.getdll (); set serveroutput on By default, SQL * PLUS does not read what a pl / sql programm has written with dbms_output with set serveroutput on, this behaviour is changed to be finished Package dbms_ * gET_LINE.. Returns a Singl e line of buffered information dbms_output.get_line (line OUT VARCHAR2, status OUT INTEGER); set serveroutput on DECLARE buffer VARCHAR2 (100); status INTEGER; BEGIN dbms_output.put_line ( 'This is'); dbms_output.put_line (' a test. '); dbms_output.get_Line (Buffer, Status); DBMS_OUTPUT.PUT_LINE (' buffer: '|| buffer); dbms_output.put_line (' status: '|| to_char (status)); end; / In SQLPLUS, you can use ROWNUM limits the number of lines of output records, for example: select * from pdb_user.client where rownum <
10 on materialized views Materialized Views in Oracle By Ajay Gursahani A materialized view 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. materialized views, which store data based on remote tables are also, know as snapshots. A materialized view can query tables, views, and other materialized views. collectively these are called master tables (a replication term) or detail tables (a data warehouse term ). For replication purposes, materialized views allow you to maintain copies of remote data on your local node. These copies are read-only. If you want to update the local copies, you have to use the Advanced Replication feature. you can select data from a materialized view as you would from a table or view. For data warehousing purposes, the materialized views commonly created are aggregate views, single-table aggregate views, and join views. In this article, we sh all see how to create a Materialized View and discuss Refresh Option of the view. In replication environments, the materialized views commonly created are primary key, rowid, and subquery materialized views. Primary Key Materialized Views The following statement creates the primary-key materialized view on the table emp located on a remote database SQL> CREATE mATERIALIZED VIEW mv_emp_pk REFRESH FAST START WITH SYSDATE NEXT SYSDATE 1/48 WITH PRIMARY KEY AS SELECT * FROM emp @ remote_db; materialized view created Note:.. When you create a materialized view Using the fast option you Will NEED TO CREATE A View Log on the master tables (s) as shown Below: SQL> Create Materialized View Log on EMP;
Materialized view log created Rowid Materialized Views The following statement creates the rowid materialized view on table emp located on a remote database:.. SQL> CREATE MATERIALIZED VIEW mv_emp_rowid REFRESH WITH ROWID AS SELECT * FROM emp @ remote_db; Materialized view log created Subquery Materialized Views The following statement creates a subquery materialized view based on the emp and dept tables located on the remote database: SQL> CREATE MATERIALIZED VIEW mv_empdept AS SELECT * FROM emp @ remote_db e WHERE EXISTS (SELECT * FROM dept @ remote_db d WHERE e.dept_no = d.dept_no) SQL> CREATE MATERIALIZED VIEW mv_emp_pk REFRESH FAST START WITH SYSDATE NEXT SYSDATE 1/48 WITH PRIMARY KEY AS SELECT * FROM emp @ remote_db; REFRESH CLAUSE [refresh [fast | complete | force] [on demand | commit] [ start with date] [next date] [with {primary key | rowid}]] The refresh option specifies: a The refresh method used by Oracle to refresh data in materialized view b Whether the view is primary key based or.. row-id based c The time and interval at which the view is to be refreshed Refresh Method -. FAST Clause The FAST refreshes use the materialized view logs (as seen above) to send the rows that have changed from master tables to the materialized view YOULD CREATE A MATERIALIZED View Log for the master Tables if You Specify The Refresh Fast Clause. SQL> CREATE MATERIALIZED View LOG ON EMP
. Materialized view log created Materialized views are not eligible for fast refresh if the defined subquery contains an analytic function Refresh Method -. COMPLETE Clause The complete refresh re-creates the entire materialized view If you request a complete refresh, Oracle performs a complete refresh. even if a fast refresh is possible Refresh Method -.. FORCE clause When you specify a FORCE clause, Oracle will perform a fast refresh if one is possible or a complete refresh otherwise If you do not specify a refresh method (FAST, COMPLETE, or FORCE), FORCE is the default. acquiesced good PRIMARY KEY and ROWID Clause WITH PRIMARY KEY is used to create a primary key materialized view ie the materialized view is based on the primary key of the master table instead of ROWID (for ROWID clause). PRIMARY KEY is the default option. To use the PRIMARY KEY clause you should have defined PRIMARY KEY on the master table or else you should use ROWID based materialized views. Primary key materialized views allow materialized view master tables to be reorganized without affecting the eligibility of the materialized view for fast refresh Rowid materialized views should have a single master table and can not contain any of the following:. • Distinct or aggregate functions • GROUP BY Subqueries, Joins & Set operations Timing the refresh the START wITH clause tells the database when to perform the first replication from the master table to the local base table. Start with the database used to determine when execution of the main table to the local table base It should evaluate to a future point in . time The NEXT clause specifies the interval between refreshes SQL> CREATE MATERIALIZED VIEW mv_emp_pk REFRESH FAST START WITH SYSDATE NEXT SYSDATE 2 WITH PRIMARY KEY AS SELECT * FROM emp @ remote_db;
