oracle tip

alter system flush shared_pool 删除共享池 Alter system flush buffer_cache 删除buffer cache 只能在10g上应用 在database buffer cache中的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 until 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. 获得用户的所有表 Select * from user_tables; 获得用户的物化视图 select * from user_mviews Oracle通过特殊表格获得系统信息 ORACLE: 利用特殊表格获得系统信息 Oracle 开发者可以依赖于一个名字为 Dual 的表格作为一个虚表用来在书写 SQL 查询的时候可以从系统变量中获得专有名词. 下面的两个例子演示了这种方式: Select 'Test Query Run on Date ' || Sysdate from Dual; 上面的语句返回如下输出: Test Query Run on Date 2001-FEB-01 获得服务器Ip select sys_context('userenv','ip_address')from dual Dual 表格是一个具备单行单列的表格, 定义如下: Create table Dual (dummy varchar2(1)); Insert into dual values 'X'; 如果该表删除，那么就要建立后重新赋于权限 grant select on SYS.DUAL to PUBLIC with grant option; 在 SQL Server, 你可以使用如下 T-SQL 编码: 在sql中不需要 Select getdate() GO ----------- Tim Quinlan 如何由于权限 -- Grant/Revoke object privileges grant select on SYS.DUAL to PUBLIC with grant option; 获得物化视图的DDL Select dbms_metadata.get_ddl('MATERIALIZED_VIEW','MVIEW_NAME') from dual Select dbms_metadata.getdll(); set serveroutput on By default, SQL*PLUS doesn't 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 single 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; / 在sqlplus中，可以用rownum来限制输出记录的行数例如： select * from pdb_user.client where rownum <10 关于物化视图 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 shall 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. You should 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.还是默认的好 PRIMARY KEY and ROWID Clause WITH PRIMARY KEY is used to create a primary key materialized view i.e. 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 cannot 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用来确定数据库何时执行主表到本地基本表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; Materialized view created. In the above example, the first copy of the materialized view is made at SYSDATE and the interval at which the refresh has to be performed is every two days. Summary Materialized Views thus offer us flexibility of basing a view on Primary key or ROWID, specifying refresh methods and specifying time of automatic refreshes. 一个物化视图的例子 create materialized view ztestview refresh start with sysdate next sysdate + 1/48 as select * from ztest 获得(YYYY-MM-DD HH:MM:SS )格式的系统日期 select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;