select count(*) from empttt; ==> 108筆
delete from empttt; --刪除
commit; --COMMIT
以下回復五分鐘前的刪除資料
insert into empttt (select * from empttt as of timestamp(sysdate - 5/1440));
commit;
select count(*) from empttt; ==> 108筆
處理DROP 誤刪:
從10g版本開始，當使用者下達DROP命令時，並不會直接刪除TABLE，僅僅只是將原有的TABLE改名，讓使用者無法存取。
drop table empttt;
flashback table empttt to before drop;
在執行前，必須由SYS進行授權
以下以SYS身分執行
GRANT SELECT ON v$session TO hr; grant select on v$sql_plan_statistics_all to hr; grant select on v$sql_plan to hr; grant select on v$sql to hr; OR GRANT SELECT ON v_$session TO hr; grant select on v_$sql_plan_statistics_all to hr; grant select on v_$sql_plan to hr; grant select on v_$sql to hr;以下使用HR身分執行
explain plan for select d.department_name, avg(e.salary) from departments d, emp e where d.department_id=e.department_id group by d.department_name; select plan_table_output from table(dbms_xplan.display(NULL,NULL,'basic')); OR select plan_table_output from table(dbms_xplan.display_cursor(NULL,NULL,'basic'));產生結果如下圖
explain plan set statement_id='foo01' for select d.department_name, avg(e.salary) from departments d, emp e where d.department_id=e.department_id group by d.department_name;另外一種方式，是在SELECTION裡面加上HINT
explain plan set statement_id='foo02' for select /*+GATHER_PLAN_STATISTICS */ d.department_name, avg(e.salary) from departments d, emp e where d.department_id=e.department_id group by d.department_name;然後使用SQL查詢PLAN_TABLE_OUTPUT (參考此文章 )
select plan_id,
operation,
options,
cost,
cpu_cost,
io_cost,
temp_space,
access_predicates,
bytes,
object_name,
object_alias,
optimizer,
object_type
from plan_table
start with parent_id is null and statement_id = 'foo01'
connect by prior id = parent_id;
產生的結果如下圖所示
select plan_table_output from
TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'basic +predicate +cost'));
OR
select plan_table_output from
TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'typical -cost -bytes'));
OR
select plan_table_output from
TABLE(DBMS_XPLAN.DISPLAY('plan_table',null,'basic +note'));
TABLE: dbmshp_runs, dbmshp_function_info, dbmshp_parent_child_info
SEQUENCE: dbmshp_runnumber
上述SQL位置在 /oracle/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/
必須在DB主機，使用 SQLPLUS 以 SYSDBA 身分執行
CREATE OR REPLACE DIRECTORY HPROF_DIR AS '/home/oracle/logs/';3. 以SYS身分執行側寫分析，在目錄物件所指定的目錄產生 TRC 檔案 (TOAD)
假設在HR中，欲對某PROCEDURE側寫分析，該PROCEDURE如下(以HR身分)
CREATE OR REPLACE PROCEDURE HR.hprof_test
IS
BEGIN
FOR v_Lp IN 1..4 LOOP
simple_procedure; --呼叫另一PROCEDURE
END LOOP;
END hprof_test;
側寫分析的區塊必須以下列指令包圍之 DBMS_HPROF.START_PROFILING('目錄物件', 'TRC檔案'); 及
DBMS_HPROF.STOP_PROFILING;
以下以SYS身分
BEGIN
DBMS_HPROF.START_PROFILING('HPROF_DIR', 'hprof_test.trc');
hprof_test;
DBMS_HPROF.STOP_PROFILING;
END;
側寫完畢，以SYS身分在TOAD執行DBMS_HPROF.ANALYZE，它會回傳一個NUMBER，可用該回傳值，從dbmshp_runs, dbmshp_function_info, dbmshp_parent_child_info
這三個TABLE中，撈取所要的資料
DECLARE
v_hprun NUMBER;
BEGIN
v_hprun := DBMS_HPROF.analyze(
LOCATION => 'HPROF_DIR', -- 目錄物件
FILENAME => 'hprof_test.trc'); -- TRC檔案
DBMS_OUTPUT.PUT_LINE('v_hprun: ' || v_hprun);
END;
執行完DBMS_HPROF.ANALYZE以後，回傳值及訊息如下:
DBMSHP_RUNS
CREATE TABLE SYS.DBMSHP_RUNS ( RUNID NUMBER, RUN_TIMESTAMP TIMESTAMP(6), TOTAL_ELAPSED_TIME INTEGER, RUN_COMMENT VARCHAR2(2047 BYTE) ) TABLESPACE SYSTEMDBMSHP_FUNCTION_INFO
CREATE TABLE SYS.DBMSHP_FUNCTION_INFO ( RUNID NUMBER, SYMBOLID NUMBER, OWNER VARCHAR2(32 BYTE), MODULE VARCHAR2(32 BYTE), TYPE VARCHAR2(32 BYTE), FUNCTION VARCHAR2(4000 BYTE), LINE# NUMBER, HASH RAW(32) DEFAULT NULL, NAMESPACE VARCHAR2(32 BYTE) DEFAULT NULL, SUBTREE_ELAPSED_TIME INTEGER DEFAULT NULL, FUNCTION_ELAPSED_TIME INTEGER DEFAULT NULL, CALLS INTEGER DEFAULT NULL ) TABLESPACE SYSTEMDBMSHP_PARENT_CHILD_INFO
CREATE TABLE SYS.DBMSHP_PARENT_CHILD_INFO ( RUNID NUMBER, PARENTSYMID NUMBER, CHILDSYMID NUMBER, SUBTREE_ELAPSED_TIME INTEGER DEFAULT NULL, FUNCTION_ELAPSED_TIME INTEGER DEFAULT NULL, CALLS INTEGER DEFAULT NULL ) TABLESPACE SYSTEM執行完DBMS_HPROF.ANALYZE以後，回傳值的RUNID為1 ，因此透過該 RUNID 來搜尋合乎該 RUNID 的資料
SELECT run_timestamp, total_elapsed_time FROM dbmshp_runs where runid = 1;結果如下
SELECT owner, type, function, line#, subtree_elapsed_time AS ST_TIME, function_elapsed_time AS FN_TIME, calls FROM dbmshp_function_info where runid = 1;結果如下
SELECT parentsymid, childsymid, subtree_elapsed_time AS ST_TIME, function_elapsed_time as FN_TIME, calls FROM dbmshp_parent_child_info where runid = 1;結果如下
CONSOLE 命令列的指令是：PLSHPROF，
需設定以下參數： TNS_ADMIN、NLS_LANGUAGE，並且須將OCI.DLL路徑設定於PATH參數中
TNS_ADMIN:
例如，下列查詢取得某些SEQUENCE的定義
SELECT dbms_metadata.get_ddl (object_type, object_name, USER)
FROM user_objects
WHERE object_type LIKE 'SEQUENCE' AND
object_name LIKE '%TEMPLATE%';
上述查詢共有六個SEQUENCE，每個均以 CLOB 型態儲存
將下列程式碼使用TOAD執行 (PLSAL視窗)
DECLARE
v_hnd NUMBER;
v_th NUMBER;
v_sql CLOB;
BEGIN
v_hnd := DBMS_METADATA.OPEN('TABLE');
DBMS_METADATA.SET_FILTER(v_hnd, 'SCHEMA','HR');
DBMS_METADATA.SET_FILTER (v_hnd, 'NAME','NEW_EMPLOYEES');
v_th := DBMS_METADATA. ADD_TRANSFORM (v_hnd,'MODIFY');
DBMS_METADATA.SET_REMAP_PARAM(v_th,'REMAP_SCHEMA','HR','DAVID');
/* 會將原有的 HR.NEW_EMPLOYEES 表格，匯出成為 DAVID.NEW_EMPLOYEES */
v_th := DBMS_METADATA.ADD_TRANSFORM(v_hnd,'DDL');
DBMS_METADATA.SET_TRANSFORM_PARAM(v_th,'SEGMENT_ATTRIBUTES',false);
v_sql := DBMS_METADATA.FETCH_CLOB(v_hnd);
DBMS_METADATA.close(v_hnd);
DBMS_OUTPUT.PUT_LINE(v_sql);
END;
執行結果如圖所示
但是編譯過程會將資訊寫入SYSAUX的表格空間，因此使用此設定，應該要以SYSTEM身分執行
此參數可以在三個階層去設定
1. SYSTEM LEVEL
ALTER SYSTEM SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';
2. SESSION LEVEL
ALTER SESSION SET PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';
3. OBJECT LEVEL
在編譯物件的時候使用之，
ALTER PROCEDURE get_emp_data COMPILE PLSCOPE_SETTINGS = 'IDENTIFIERS:ALL';
例如，以下使用SYSTEM身分登入
ALTER SESSION SET PLSCOPE_SETTINGS='IDENTIFIERS:ALL';
ALTER PACKAGE HR.initpkg COMPILE package;
SELECT name, type, usage, usage_id, line, col FROM all_identifiers WHERE object_name='INITPKG'
結果分析如下
select * from employees where hire_date='2005/9/21'; <錯>
select * from employees where hire_date='21-SEP-2005'; <錯>
select * from employees where hire_date=to_date('2005/9/21','YYYY/MM/DD'); <對>
上例是直接透過EXPLICIT的轉換函數將日期字串轉換為日期型態。
select * from employees where hire_date=to_date('21-SEP-2005','DD-MM-YYYY'); <錯>
這次會錯又是因為 NLS_DATE_LANGUAGE 在中文環境底下，被改為繁體中文
select * from v$nls_parameters
NLS_LANGUAGE TRADITIONAL CHINESE
alter session set nls_date_language='american';
修改完畢以後，如果使用字元LITERAL查詢日期可以嗎? 又回到剛才第一個方式查詢 select * from employees where hire_date='2005/9/21'; <錯>
select * from employees where hire_date='21-SEP-2005'; <對>
為何不一樣 ? 結論是，看參數設定 NLS_DATE_FORMAT
NLS_DATE_FORMAT DD-MON-RR
前例中，第二個日期的字串，與格式字串相符，所以能查詢到。第一個的格式不符，因此不行。