2013年1月23日
摘要：
If you need to remove duplicate rows from an Oracle Database Table, you can use different approaches. For example, you can use the DELETE command with a criteria that selects only duplicates. Usually it is faster to create a new table with distinct rows and replace the old table with the new one.-- 阅读全文
摘要：
This example illustrates how you can create and use dynamic cursor in Oracle PL/SQL. The example is pretty simple, but I hope you can get the idea and apply it to more complicated cases.DECLARE t_cursor IS REF CURSOR; my_cursor t_cursor; v_customer RECORD ( customer_id NUMBER(18), amount NUM... 阅读全文
摘要：
Oracle Database 11g introduced new feature – virtual column. Instead storing data, the virtual column is calculated based on an expression stored in data dictionary. Let’s assume you have a table that stores customer information. The customer names are stored in two separate columns – first_name and 阅读全文
摘要：
You declare PL/SQL variables, constants and types in declare block. The syntax is <name> [CONSTANT] <datatype> [NOT NULL] [:= | DEFAULT <expr>]<name> is the name of the variable or constant;<datatype> may be scalar, composite datatype, reference or LOB;<expr> is a 阅读全文
摘要：
引用自:http://blog.csdn.net/ggjjzhzz/archive/2005/10/17/507880.aspx在某些场合下，存储过程或触发器里的SQL语句需要动态生成。Oracle的DBMS_SQL包可以用来执行动态SQL语句。本文通过一个简单的例子来展示如何利用DBMS_SQL包执行动态SQL语句：DECLARE v_cursor NUMBER; v_stat NUMBER; v_row NUMBER; v_id NUMBER; v_no VARCHAR(100); v_date DATE; v_sql VARCHAR(200); s_id NUMBER; s_date D 阅读全文
摘要：
Here is a simple recipe. The following SQL finds the top ten SQL statements with the greatest elapsed time.SELECT sql_id, child_number, sql_text, elapsed_time FROM (SELECT sql_id, child_number, sql_text, elapsed_time, cpu_time, d... 阅读全文
摘要：
ProblemYou need to find when a PL/SQL procedure in an Oracle Database was updated.SolutionThe following SQL query returns information about a FIND_USER procedure, defined in the current user’s schema. The information returned includes procedure name, date procedure was created and date procedure was 阅读全文
摘要：
1. Table and tablespace locationFinding out who owns a table and what tablespace it is in is a pretty common need of the DBA. In this query, we use the dba_tables view to find the owner and tablespace name of the EMP table.SELECT table_name, tablespace_name, STATUS FROM user_tables WHERE table_name. 阅读全文