初识oracle数据库
Orange
3月 23, 2021
Install
Manual
- request Oracle Database Express Edition (XE) Release 11.2.0.2.0 (11gR2)
- click
Oracle Database 11gR2 Express Edition for Linux x64 - copy the straight-download-link
https://download.oracle.com/otn/linux/oracle11g/xe/oracle-xe-11.2.0-1.0.x86_64.rpm.zip?AuthParam=... - run
wget -b straight-download-link -P /opt/oracleand wait… - run
unzip /opt/oracle/oracle-xe-11.2.0-1.0.x86_64.rpm.zip - run
rpm -hvi /opt/oracle/oracle-xe-11.2.0-1.0.x86_64.rpmDocker
docker run -d -p 1521:1521 -e ORACLE_ALLOW_REMOTE=true wnameless/oracle-xe-11g-r2username: system
username: sys
password: oracle
sid: xe
Rule
Normal Form Rule
- 1st_Normal_Form
- Single value
| DATA | CORRECT |
|---|---|
| JAVA | T |
| C,C++ | F |
solution: C,C++->2 line
- Same domain
| DATA | CORRECT |
|---|---|
| abc | T |
| 123 | F |
solution: 123->"123"
- Diff column names
- Orderless
- 2nd_Normal_Form extends 1st_Normal_Form
- non-exist partial dependency: all column dependency all primary key
| F1(PK) | F2(PK) | DATA1(dependencyF1+F2) | DATA2(dependencyF1) |
|---|---|---|---|
| 1 | 1 | CORRECT:T | CORRECT:F |
solution: column DATA2 move to F1's table
- 3rd_Normal_Form extends 2nd_Normal_Form
- non-exist transitive dependency: all column no dependency all non-primary key
| F1(PK) | DATA1 | DATA2(dependencyDATA1) |
|---|---|---|
| 1 | abc | CORRECT:F |
solution: column DATA1,DATA2 create to new table, add F2 column
Non-Normal Form Rule
- add TOTAL column
- copy foreign table
- move-in foreign table
- add CURRENT columns
- add foreign’s foreign key column
Table space
Principle
|
|
Default
- users
- system
- sysaux: system auxiliary table space
- undotbs1: undo table space 1
- temp: temp table space for order or sum
Usage
for table space
- 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
- -- readonly; show non-temp *.dbf
SELECT * FROM v$datafile;
-- readonly; show relations between non-temp TableSpace<->*.dbf
SELECT * FROM dba_data_files;
-- readonly; show temp *.dbf
SELECT * FROM v$tempfile;
-- readonly; show relations between temp TableSpace<->*.dbf
SELECT * FROM dba_temp_files;
-- create (muti small data) permanent table space
CREATE SMALLFILE TABLESPACE myts DATAFILE '/tmp/myts.dbf' SIZE 10m AUTOEXTEND on NEXT 2m MAXSIZE unlimited;
-- create (muti small data) temp table space and add to tgroup
CREATE SMALLFILE TEMPORARY TABLESPACE mytmpts TEMPFILE '/tmp/mytmpts.dbf' SIZE 10m AUTOEXTEND on NEXT 2m MAXSIZE unlimited TABLESPACE GROUP tgroup;
-- create single big file table space
CREATE BIGFILE TABLESPACE mytmpts TEMPFILE '/tmp/mytmpts.dbf' SIZE 10m AUTOEXTEND on NEXT 2m MAXSIZE unlimited TABLESPACE GROUP tgroup;
-- create undo table space
CREATE UNDO TABLESPACE myundots DATAFILE '/tmp/myundots.dbf' SIZE 10m;
-- change status to offline & flush cache
ALTER TABLESPACE myts OFFLINE NORMAL;
-- change status to offline for recover
ALTER TABLESPACE myts OFFLINE FOR RECOVER;
-- change status to online
ALTER TABLESPACE myts ONLINE;
-- change enabled to r/w
ALTER TABLESPACE myts READ WRITE;
-- rename, make sure status is online
ALTER TABLESPACE myts RENAME TO myts_rename;
-- resize
ALTER DATABASE DATAFILE '/tmp/myts' RESIZE 5m;
-- append *.dbf
ALTER TABLESPACE myts ADD DATAFILE '/tmp/myts.dbf' SIZE 10m AUTOEXTEND on NEXT 2m MAXSIZE unlimited, '/tmp/myts2.dbf' SIZE 10m AUTOEXTEND on NEXT 2m MAXSIZE unlimited;
-- delete *.dbf
ALTER TABLESPACE myts DROP DATAFILE '/tmp/myts2.dbf';
-- rename *.dbf, make sure offline before
ALTER TABLESPACE myts OFFLINE NORMAL;
ALTER TABLESPACE myts RENAME DATAFILE '/tmp/myts.dbf' TO '/tmp/myts.dbf';
ALTER TABLESPACE myts ONLINE;
-- delete table space but keep *.dbf
DROP TABLESPACE myts INCLUDING CONTENTS;
-- delete table space within *.dbf
DROP TABLESPACE myts INCLUDING CONTENTS AND DATAFILES;
-- delete undo table space, make sure unused before
ALTER SYSTEM SET undo_tablespace = 'undotbs1';
DROP TABLESPACE myundots INCLUDING CONTENTS;
- 1
for table
- 1
2
3
4
5
6
- -- create
CREATE TABLE mytb(column1 INT,column2 VARCHAR2(11)) TABLESPACE myts;
-- change
ALTER TABLE mytb MOVE TABLESPACE myts;
-- change (C/B)LOB Segment
ALTER TABLE mytb MOVE TABLESPACE USERS LOB(lobcolumn1,lobcolumn2) STORE AS(TABLESPACE myts);
- 1
for index
- 1
- ALTER INDEX myid REBUILD TABLESPACE myts;
- 1
Segment
- Data Segment: include (C/B)LOB Segment
- Index Segment: head of table space if need
- Temp Segment: auto delete
- Rollback Segment: only in
systemtable spacedata block
Principle
- 1
2
3
4
5
6
7
8
9
10
- |-----------------------------|
|header(link Segment) |
|-----------------------------|
|table dir(ink table) |
|-----------------------------|
| row dir(link used row space)|
|-----------------------------|
|row space |
|... |
|-----------------------------|
- 1
Usage
Run in SQL*Plus
|
|
Extent(Only in Enterprise Version)
Hash
- 1
2
3
4
5
6
7
8
9
10
11
- -- create
CREATE TABLE mytb(column1 INT,column2 VARCHAR2(11)) PARTITION BY HASH(column1) (
PARTITION mypart1 TABLESPACE myts,
PARTITION mypart2 TABLESPACE myts2
);
-- add, make sure table was extented
ALTER TABLE mytb ADD PARTITION mypart3 TABLESPACE myts3;
-- merge
ALTER TABLE mytb MERGE PARTITIONS mypart1,mypart2 INTO PARTITION mypart1;
-- delete
ALTER TABLE mytb DROP PARTITION mypart1;
- 1
List
- 1
2
3
4
5
6
7
8
9
10
11
12
- -- create
CREATE TABLE mytb(column1 INT,column2 VARCHAR2(11)) PARTITION BY LIST(column2) (
PARTITION mypart1 VALUES('Y') TABLESPACE myts,
PARTITION mypart2 VALUES('N') TABLESPACE myts2
);
-- add, make sure table was extented
ALTER TABLE mytb ADD PARTITION mypart3 VALUES('y') TABLESPACE myts3;
ALTER TABLE mytb ADD PARTITION mypart4 VALUES(DEFAULT) TABLESPACE myts4;
-- merge
ALTER TABLE mytb MERGE PARTITIONS mypart1,mypart2 INTO PARTITION mypart1;
-- delete
ALTER TABLE mytb DROP PARTITION mypart1;
- 1
Range
- 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
- -- create
CREATE TABLE mytb(column1 INT,column2 VARCHAR2(11)) PARTITION BY RANGE(column1) (
PARTITION mypart1 VALUES LESS THAN(10) TABLESPACE myts,
PARTITION mypart2 VALUES LESS THAN(MAXVALUE) TABLESPACE myts2
);
-- add within MAXVALUE, make sure table was extented
ALTER TABLE mytb SPLIT PARTITION mypart2 AT(20) INTO(
PARTITION mypart2 TABLESPACE myts2,
PARTITION mypart3 TABLESPACE myts3
);
-- add without MAXVALUE, make sure table was extented and condition greater than any partitions
ALTER TABLE mytb ADD PARTITION mypart4 VALUES LESS THAN(20) TABLESPACE myts4;
-- merge
ALTER TABLE mytb MERGE PARTITIONS mypart1,mypart2 INTO PARTITION mypart1;
-- delete
ALTER TABLE mytb DROP PARTITION mypart1;
with Hash
- 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
- -- create into 2 range and each range divde into 3 hash
CREATE TABLE mytb(column1 INT,column2 VARCHAR2(11)) PARTITION BY RANGE(column1)
SUBPARTITION BY HASH(column2)
SUBPARTITIONS 3 STORE IN (myts,myts2)
(
PARTITION mypart1 VALUES LESS THAN(10),
PARTITION mypart2 VALUES LESS THAN(MAXVALUE)
);
-- add within MAXVALUE, make sure table was extented
ALTER TABLE mytb SPLIT PARTITION mypart2 AT(20) INTO(
PARTITION mypart2 TABLESPACE myts2,
PARTITION mypart3 TABLESPACE myts3
);
-- add without MAXVALUE, make sure table was extented and condition greater than any partitions
ALTER TABLE mytb ADD PARTITION mypart4 VALUES LESS THAN(20) TABLESPACE myts4;
-- merge
ALTER TABLE mytb MERGE PARTITIONS mypart1,mypart2 INTO PARTITION mypart1;
-- delete
ALTER TABLE mytb DROP PARTITION mypart1;
- 1
with List
- 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
- -- create into 2 range and each range divde into 2 list
CREATE TABLE mytb(column1 INT,column2 VARCHAR2(11)) PARTITION BY RANGE(column1)
SUBPARTITION BY LIST(column2)
SUBPARTITIONS 3 STORE IN (myts,myts2)
(
PARTITION mypart1 VALUES LESS THAN(10) (
SUBPARTITION mypart1_1 VALUES('Y') TABLESPACE myts,
SUBPARTITION mypart1_2 VALUES('N') TABLESPACE myts
),
PARTITION mypart2 VALUES LESS THAN(MAXVALUE) (
SUBPARTITION mypart2_1 VALUES('Y') TABLESPACE myts2,
SUBPARTITION mypart2_2 VALUES('N') TABLESPACE myts2
)
);
-- add within MAXVALUE, make sure table was extented
ALTER TABLE mytb SPLIT PARTITION mypart2 AT(20) INTO(
PARTITION mypart2 TABLESPACE myts2,
PARTITION mypart3 TABLESPACE myts3
);
-- add without MAXVALUE, make sure table was extented and condition greater than any partitions
ALTER TABLE mytb ADD PARTITION mypart4 VALUES LESS THAN(20) TABLESPACE myts4;
-- merge
ALTER TABLE mytb MERGE PARTITIONS mypart1,mypart2 INTO PARTITION mypart1;
-- delete
ALTER TABLE mytb DROP PARTITION mypart1;
- 1
- 1
System
Principle
- Database buffer cache(DB_CACHE_SIZE): default size data block cache
- Shared pool(SHARE_POOL_SIZE)
- Library cache: SQL or PL/SQL and theirs execution plan cache
- Data Dictionary(Default VIEWs) cache: sql and execution plan cache
- U(ser) G(lobal) A(rea): clustor session cache if need
- Large pool: large objects cache for clustor
- U(ser) G(lobal) A(rea): clustor session cache if need
- Keep buffer cache(DB_KEEP_CACHE_SIZE): persistent data block cache
- Recycle buffer cache：ephemeral data block cache
- nK block size buffer(DB_2/4/8/16/32K_CACHE_SIZE): custom size data block cache
Usage
Run in SQL*Plus
|
|
Process
Principle
Usage
Run in SQL*Plus
|
|
DBW[0-9a-z]
db writer process [0-9a-z]
Principle
|
|
Usage
|
|
Run in SQL*Plus
|
|
Data Directory(Default VIEWs)
- Static:
- dba_xxxs
- all_xxxs: authorized info & user_xxx
- user_xxxs
- Dynamic:
- gv$xxx(SELECT xxx FROM x$xxx): distributed
- v$xxx(SELECT xxx FROM gv$xxx WHERE inst_id = USERENV(‘instance’))
Principle
- 1
2
3
4
5
6
- |--------------------| exec |--------------------| instance auto |--------| exec |-------| |-------|
| bootstrap$ |>>>>>>>>>>>>>>>>>>>>>|base table: obj$,...|>>>>>>>>>>>>>>>>>| x$xxx |>>>>>>>>>>>>>|gv$xxx |>>>>| v$xxx |
|(SYSTEM table space)|>>>>>>>>>>>>>>>>>>>>>|(SYSTEM table space)|>>>>>>>>>>>>>>>>>|(Memory)|>>>>>>>>>>>>>|(VIEW) |>>>>|(VIEW) |
|--------------------| sql_text from *.bsq |--------------------| map from kernel |--------| catalog.sql |↑alias↑| |↑alias↑|
|gv_$xxx| |v_$xxx |
|-------| |-------|
- 1
Oracle will automatic modify those base table data after each DDL(CREATE/ALTER/DROP)
WARN: Only SYSDBA can access x$xxx
Usage
|
|
Variable
Run in SQL*Plus
- Manual define
- console: &variable_name or &&variable_name
*.sql: @variable_name, run@ xxx/xxx.sql
- Automic define
H(eap)-o(rganization)-T(able): unordered; physical
rowid- without index(default): replace any unused(such as deleted) block from start to end or append to end when add
- with index: index physical
rowidin index segment then add, update or deletesame as MySQL myISAM
- 1
- CREATE TABLE mytb(column1 INT,column2 VARCHAR2(11)) ORGANIZATION HEAP;
- 1
I(ndex)-o(rganization)-T(able): ordered; logical
rowid; all data are stored in B-Tree type index segment ifP(er)C(en)T_THRESHOLDis 100same as MySQL innoDB
- 1
- CREATE TABLE mytb(column1 INT,column2 VARCHAR2(11)) ORGANIZATION INDEX;
- 1
Cluster-Table: join data segments by foreign key
- 1
- CREATE TABLE mytb(column1 INT,f1 VARCHAR2(11)) CLUSTER mytb2(f1);
- 1
External-Table: readonly external text
- 1
2
3
4
5
6
7
8
- -- path of external text is '/tmp/my.txt'
CREATE DIRECTORY mydir AS '/tmp';
CREATE TABLE mytb(column1 INT,column2 VARCHAR2(11)) ORGANIZATION EXTERNAL(
TYPE oracle_loader
DEFAULT DIRECTORY mydir
ACCESS PARAMETERS(FIELDS TERMINATED BY ',')
LOCATION('my.txt')
);
- 1
Type
- NUMBER(length, length after point)
- INT
- CHAR/NCHAR
- VARCHAR2/NVARCHAR2: In Oracle 11g,
VARCHAR2andNVARCHAR2‘s max size limit is 4000 - DATE
- CLOB/NCLOB
- BLOB
- BFILE
start with ‘N’ means store type must be national charset(‘AF16UTF16’(default) or ‘UTF8’) char
start without ‘N’ means store type can be byte or database charset char
Constraint
- primary/foreign key
- not null
- unique
- check
- 1
2
3
4
5
6
- -- add
ALTER TABLE mytb ADD CHECK (column2 IN ('Y','N'));
-- disable
ALTER TABLE mytb MODIFY CONSTRAINT xxx DISABLE;
-- no-validate exist data
ALTER TABLE mytb MODIFY CONSTRAINT xxx DISABLE NOVALIDATE;
- 1
Column
|
|
Index(of H(eap)-o(rganization)-T(able))
B-Tree
by value(default)
- 1
- CREATE INDEX myid ON mytb(column) TABLESPACE myts;
- 1
by Func
- 1
- CREATE BITMAP INDEX myid ON mytb(FUNC(column)) TABLESPACE myts;
- 1
BitMap for limit status flag(Only in Enterprise Version)- 1
- CREATE BITMAP INDEX myid ON mytb(column) TABLESPACE myts;
- 1
|
|
View
Simple map column support modified without check by default
|
|
Sequence
Synonym
|
|
dualis public synonym of virtual tablesys.dual(keeps one select result)
Log
*.LOG files group by group id, Oracle will automic turn to override next log group if full
Log group
|
|
Log
|
|
Archive
There are 2 override modes:
- without archive(default): each override without package history
- with archive: each override will package history to dests 1-31 before
Run in SQL*Plus- 1
2
3
4
5
6
7
8
9
10
11
12
13
- -- readonly; show default log dir, similar to `ALTER SYSTEM SET log_archive_dest_1 = 'LOCATION=USE_DB_RECOVERY_FILE_DEST';`
SHOW PARAMETER db_recovery_file_dest
-- readonly; show log dir
SHOW PARAMETER log_archive_dest
-- readonly; show current override mode
ARCHIVE LOG LIST;
-- override with archive, must be 'MOUNT' but not 'OPEN' status before, make sure dir is exist
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER SYSTEM SET log_archive_dest_[1-31] = 'LOCATION=/tmp';
ALTER SYSTEM SET log_archive_format = 'redo%S_%R.%T';
ALTER DATABASE OPEN;
- 1
SQL
merge: similar union
|
|
PL/SQL
Constrant
|
|
Variable
|
|
Cursor
|
|
Exception
|
|
Procedure
|
|
Run in SQL*Plus
|
|
Func
has one return
|
|
Run in SQL*Plus
|
|
Trigger
attach
DML(INSERT, UPDATE, DELETE, …) event
- 1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
- -- make sure `SET SERVEROUTPUT ON` before if Run in SQL*Plus
-- create or update before insert
CREATE OR REPLACE TRIGGER mytgbi BEFORE INSERT ON mytb FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('trigger before insert mytb new.column1='||:NEW.column1||',new.column2='||:NEW.column2);
END;
/
-- create or update before update
CREATE OR REPLACE TRIGGER mytgbu BEFORE UPDATE ON mytb FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('trigger before update mytb old.column1='||:OLD.column1||',old.column2='||:OLD.column2||' new.column1='||:NEW.column1||',new.column2='||:NEW.column2);
END;
/
-- create or update after update
CREATE OR REPLACE TRIGGER mytgau AFTER UPDATE ON mytb FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('trigger after update mytb old.column1='||:OLD.column1||',old.column2='||:OLD.column2||' new.column1='||:NEW.column1||',new.column2='||:NEW.column2);
END;
/
-- create or update before delete
CREATE OR REPLACE TRIGGER mytgbd BEFORE DELETE ON mytb FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('trigger before delete mytb old.column1='||:OLD.column1||',old.column2='||:OLD.column2);
END;
/
-- disable
ALTER TRIGGER mytgbi DISABLE;
-- disable for table
ALTER TRIGGER mytb DISABLE ALL TRIGGERS;
-- delete
DROP TRIGGER mytgbi;
- 1
System event
WARN: Only SYSDBA can create system event trigger- 1
2
3
4
5
6
7
- -- make sure `SET SERVEROUTPUT ON` before if Run in SQL*Plus
-- create or update after loggon
CREATE OR REPLACE TRIGGER mytgau AFTER LOGGON ON DATABASE
BEGIN
DBMS_OUTPUT.PUT_LINE('trigger after loggon user='||USER||' date='||SYSDATE);
END;
/
- 1
DDL(CREATE, DROP, ALTER, …) event
WARN: Only SYSDBA can create ddl event trigger- 1
2
3
4
5
6
7
- -- make sure `SET SERVEROUTPUT ON` before if Run in SQL*Plus
-- create or update after create
CREATE OR REPLACE TRIGGER mytgac AFTER CREATE ON DATABASE
BEGIN
DBMS_OUTPUT.PUT_LINE('trigger after create user='||USER||' obj_name='||SYS.DICTIONARY_OBJ_NAME||' obj_type='||SYS.DICTIONARY_OBJ_TYPE||' date='||SYSDATE);
END;
/
- 1
instead
WARN: Only instead events on View- 1
2
3
4
5
6
- -- create or update instead of insert
CREATE OR REPLACE TRIGGER mytgii INSTEAD OF INSERT ON myv FOR EACH ROW
BEGIN
INSERT INTO mytb(column1, column2) VALUES(:NEW.column1, :NEW.column2);
END;
/
- 1
Package
- define
- body
- Procedure
- Func
PUT_LINEis one of procedures inDBMS_OUTPUTpackage
User
Role
Backup
- expdp(Oracle 11g support): hot backup to files
- rman: hot or cold backup to blocks
Flashback(Oracle 11g support)
|
|
- Table commited DML(INSERT, UPDATE, DELETE, …)
- Drop
- Database(off default)
ASM(Oracle 10g support)
RAC
Optimize
Execution Plan
Principle
|
|
Cardinality Feedback will add A(ctual)-Rows hint for the same DQL or DML(INSERT, UPDATE, DELETE, …) when use C(ost)-B(ased)-O(ptimizer) and A(ctual)-Rows large diff than E(xpect)-Rows at first time (Oracle 11.2g support)
Usage
Estimated
Explain plan(key
F5inPL/SQL Developer): store inplan_table(public synonym of session’s temp tableSYS.PLAN_TABLE$) created bycatplan.sql- 1
2
3
4
- -- save execution plan for `SELECT * FROM dual`
EXPLAIN PLAN FOR SELECT * FROM dual;
-- query last execution plan; pretty format `SELECT * FROM PLAN_TABLE`
SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE', null, 'ADVANCED'));
- 1
Auto trace(Run in SQL*Plus): same as Explain plan
- 1
2
3
- -- query execution plan for `SELECT * FROM dual`
SET AUTOTRACE ON;
SELECT * FROM dual;
- 1
Actual
Statistics: store in session’s temp view
v$sql_plan_statistics_all- 1
2
3
4
5
- -- save execution plan for `SELECT * FROM dual`; Also can use hint `SELECT /*+ gather_plan_statistics*/ * FROM dual`
ALTER SESSION SET statistics_level=ALL;
SELECT * FROM dual;
-- query shared pool's last sql cursor's execution plan; pretty format `SELECT * FROM v$sql_plan_statistics_all`
SELECT * FROM TABLE(dbms_xplan.display_cursor(null, null, 'ADVANCED ALLSTATS'));
- 1
A(utomatic) W(orkload) R(epository): store in views
wrm$_*andwrh$_*(instead of Statspack)- 1
2
3
4
5
6
7
- -- save execution plan for `SELECT * FROM dual`, make sure session's statistics_level >= TYPICAL
SELECT * FROM dual;
CALL dbms_workload_repository.create_snapshot();
ALTER SYSTEM FLUSH shared_pool;
-- query shared pool's execution plan; pretty format `SELECT * FROM PLAN_TABLE`
SELECT sql_id FROM v$sqlarea WHERE sql_text LIKE 'SELECT * FROM dual'
SELECT * FROM TABLE(dbms_xplan.display_awr('sql_id', null, null, 'ADVANCED ALLSTATS'));
- 1
Trace Dump: store
*.trcinuser_dump_destdir; you can use tooltkprofpretty format file- 1
2
3
4
5
6
7
8
9
10
- -- query trace dump dir
show parameter user_dump_dest;
-- query SID and Serial#
SELECT * FROM V$SESSION;
-- turn on sql_trace for session; Also can use `ALTER SESSION SET sql_trace=TRUE` for current session
EXECUTE dbms_system.set_sql_trace_in_session(SID, Serial#, TRUE);
-- save execution plan for `SELECT * FROM dual`
SELECT * FROM dual;
-- turn off sql_trace for session; Also can use `ALTER SESSION SET sql_trace=FALSE` for current session
EXECUTE dbms_system.set_sql_trace_in_session(SID, Serial#, FALSE);
- 1
10046 Event: store
*.trcinuser_dump_destdir; you can use tooltkprofpretty format file- lv1: same as Trace Dump
- lv4: lv1 + bind values
- lv8: lv1 + events
- lv12: lv4 + lv8
- 1
2
3
4
5
6
7
8
- -- query trace dump dir
show parameter user_dump_dest;
-- turn on 10046 event trace for current session
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
-- save execution plan for `SELECT * FROM dual`
SELECT * FROM dual;
-- turn off 10046 event trace for current session
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
- 1
本文采用 CC BY-NC-SA 3.0 Unported 协议进行许可
