oracle tip

原创 2005年03月04日 13:15:00
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;


我们可以为我们的链接加上这样的效果,以更加体现网页的亲和力与易用性。我们也可以在提供下载的链接下提供这样的tip提示。我们看这样的鼠标悬停TIP效果是如何实现的。看下面的XHTML代码: ...
  • ufojoan
  • ufojoan
  • 2013年07月30日 17:53
  • 1770


用javascript实现的tip效果 原文地址 document.body.onmousemove=quickalt; document.body.onmouseover=getalt...
  • guobing965816
  • guobing965816
  • 2012年01月19日 08:44
  • 744

流行的jquery 提示插件 tip

  • yibing548
  • yibing548
  • 2015年05月12日 11:19
  • 1321


DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"> html> head> title>动态随鼠标移动的Tip...
  • hjxgood
  • hjxgood
  • 2014年07月25日 15:56
  • 876


有此组件可以通过设置toolTip的配置来显示tip,有些没有这些配置的可以用下面的方式 实现方式是在组件渲染的时候,监听render事件,如本例子中在formpanel的items中添加如下rad...
  • gtchenxw
  • gtchenxw
  • 2012年04月20日 17:51
  • 1521

js 文本框 内容提示 tip

文本框内显示文本提示,当点击后提示消失!实现代码: test
  • lzy_1515
  • lzy_1515
  • 2010年07月21日 09:35
  • 3229


  • cxmscb
  • cxmscb
  • 2016年09月27日 23:22
  • 3281


根据w3school整理 HTML 水平线  标签在 HTML 页面中创建水平线。 hr 元素可用于分隔内容。 提示:使用水平线 ( 标签) 来分隔文章中的小节是一个办法(但并不是唯一的办法)...
  • u014694759
  • u014694759
  • 2014年04月13日 21:02
  • 771


1。Java控制台输入,输出 /* * Created on 2005-4-5 * * TODO To change the template for this generated file go t...
  • yethyeth
  • yethyeth
  • 2006年01月12日 03:29
  • 1528


第一种方式:适合于全部HTML标签 acronym title="World Wide Web"> WWW acronym> ...
  • husongs
  • husongs
  • 2007年09月17日 13:50
  • 279
您举报文章:oracle tip