初识oracle数据库

Author Avatar
Orange 3月 23, 2021
  • 在其它设备中阅读本文章

Install

Manual

  1. request Oracle Database Express Edition (XE) Release 11.2.0.2.0 (11gR2)
  2. click Oracle Database 11gR2 Express Edition for Linux x64
  3. 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=...
  4. run wget -b straight-download-link -P /opt/oracle and wait…
  5. run unzip /opt/oracle/oracle-xe-11.2.0-1.0.x86_64.rpm.zip
  6. run rpm -hvi /opt/oracle/oracle-xe-11.2.0-1.0.x86_64.rpm

    Docker

    docker run -d -p 1521:1521 -e ORACLE_ALLOW_REMOTE=true wnameless/oracle-xe-11g-r2

    username: system

    username: sys

    password: oracle

    sid: xe

    also see https://github.com/wnameless/docker-oracle-xe-11g

Rule

Normal Form Rule

  1. 1st_Normal_Form
    1. Single value
DATA CORRECT
JAVA T
C,C++ F 
solution: C,C++->2 line
  1. Same domain
DATA CORRECT
abc T
123 F 
solution: 123->"123"
  1. Diff column names
  2. Orderless
    1. 2nd_Normal_Form extends 1st_Normal_Form
  3. 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
  1. 3rd_Normal_Form extends 2nd_Normal_Form
    1. 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

  1. add TOTAL column
  2. copy foreign table
  3. move-in foreign table
  4. add CURRENT columns
  5. add foreign’s foreign key column

Table space

Principle

1
2
3
4
5
6
7
8
9
10
11
12
|-Database---------------------|
| |-TableSpace-(muti *.dbf)--| |
| | |-Segment--------------| | |
| | | |-Extent-----------| | | |
| | | |   |----------|   | | | |
| | | |   |data block||  | | | |
| | | |   |----------||  | | | |
| | | |    |----------|  | | | |
| | | |------------------| | | |
| | |----------------------| | |
| |--------------------------| |
|------------------------------|

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;

  • 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);

  • for index

    1
    ALTER INDEX myid REBUILD TABLESPACE myts;

Segment

  • Data Segment: include (C/B)LOB Segment
  • Index Segment: head of table space if need
  • Temp Segment: auto delete
  • Rollback Segment: only in system table space

    data 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                    |
    |...                          |
    |-----------------------------|

Usage

Run in SQL*Plus

1
2
-- readonly
SHOW PARAMETER db_block_size

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;

  • 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;

  • 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;

    • 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;

System

Principle

S(ystem) G(lobal) A(rea)

  • 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

1
2
3
4
5
6
7
8
9
-- readonly
SHOW PARAMETER SGA_MAX_SIZE;
SHOW PARAMETER SGA_TARGET;
-- turn on A(utomatic) S(hared) M(emory) M(anagement), make sure SGA_MAX_SIZE >= SGA_TARGET; Also can use `ALTER SESSION SET statistics_level=ALL`
ALTER SESSION SET statistics_level=TYPICAL;
ALTER SYSTEM SET SGA_MAX_SIZE=y;
ALTER SYSTEM SET SGA_TARGET=x;
-- turn off A(utomatic) S(hared) M(emory) M(anagement)
ALTER SYSTEM SET SGA_TARGET=0;

Process

Principle

P(rogram) G(lobal) A(rea)

Usage

Run in SQL*Plus

1
2
3
4
5
6
-- readonly
SHOW PARAMETER PGA_AGGREGATE_TARGET;
-- turn on A(utomatic) P(rogram) M(emory) M(anagement), 20% of SGA_TARGET default 
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=x;
-- turn off A(utomatic) P(rogram) M(emory) M(anagement)
ALTER SYSTEM SET PGA_AGGREGATE_TARGET=0;

DBW[0-9a-z]

db writer process [0-9a-z]

Principle

1
2
3
4
5
     |---------------|  full |------------| DBW[0-9a-z] process |------|
>>R>>|LRU Cache block||>>R>>>| DIRTY LIST |>>>>>>>>>>R>>>>>>>>>>|*.dbf ||
<<W<<| (PGA Memery)  ||<<W<<<|(PGA Memery)|<<<<<<<<<<W<<<<<<<<<<|(Disk)||
     |---------------||      |------------|                     |------||
      |---------------|                                          |------|

Usage

1
2
-- readonly; PADDR == 00 means not start
SELECT RAWTOHEX(PADDR), PSERIAL#, NAME, DESCRIPTION, ERROR FROM v$bgprocess;

Run in SQL*Plus

1
2
-- readonly
SHOW PARAMETER db_writer_process

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 |
                                                                                                               |-------|    |-------|

Oracle will automatic modify those base table data after each DDL(CREATE/ALTER/DROP)
WARN: Only SYSDBA can access x$xxx

Usage

1
2
3
4
-- list all x$xxx
SELECT * FROM v$fixed_table;
-- list all Data Directories
SELECT * FROM dict;

Variable

Run in SQL*Plus

  • Manual define
    • console: &variable_name or &&variable_name
    • *.sql: @variable_name, run @ xxx/xxx.sql
  • Automic define
    • DEF(INE) key=value

      Schema

      • Oracle(owned by user): datas + objects
      • MySQL: = database
      • SQLServer: name of tables, fields, types and primary/foreign keys

      Object

      Table

      organization type:

  • 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 rowid in index segment then add, update or delete

      same as MySQL myISAM

      1
      CREATE TABLE mytb(column1 INT,column2 VARCHAR2(11)) ORGANIZATION HEAP;

  • I(ndex)-o(rganization)-T(able): ordered; logical rowid; all data are stored in B-Tree type index segment if P(er)C(en)T_THRESHOLD is 100

    same as MySQL innoDB

    1
    CREATE TABLE mytb(column1 INT,column2 VARCHAR2(11)) ORGANIZATION INDEX;

  • Cluster-Table: join data segments by foreign key

    1
    CREATE TABLE mytb(column1 INT,f1 VARCHAR2(11)) CLUSTER mytb2(f1);

  • 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')
    );

Type

  • NUMBER(length, length after point)
    • INT
  • CHAR/NCHAR
  • VARCHAR2/NVARCHAR2: In Oracle 11g, VARCHAR2 and NVARCHAR2‘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;

Column

1
2
3
4
5
6
7
8
9
10
11
-- add
ALTER TABLE mytb ADD column1 varchar2;
ALTER TABLE mytb ADD column2 varchar2;
-- remove
ALTER TABLE mytb DROP (column1,column2);
-- rename
ALTER TABLE mytb RENAME COLUMN column1 TO column3;
-- change type
ALTER TABLE mytb MODIFY column1 varchar2;
-- hide, manual modify base table if need undo
ALTER TABLE mytb SET UNUSED (column1,column2);

Index(of H(eap)-o(rganization)-T(able))

  • B-Tree

    • by value(default)

      1
      CREATE INDEX myid ON mytb(column) TABLESPACE myts;

    • by Func

      1
      CREATE BITMAP INDEX myid ON mytb(FUNC(column)) TABLESPACE myts;

  • BitMap for limit status flag(Only in Enterprise Version)

    1
    CREATE BITMAP INDEX myid ON mytb(column) TABLESPACE myts;
1
2
3
4
-- merge and prune
ALTER INDEX myid COALESCE DEALLOCATE UNUSED;
-- prune rebuild
ALTER INDEX myid REBUID;

View

Simple map column support modified without check by default

1
2
-- checkout if modifiable
SELECT * FROM user_updatable_columns WHERE table_name = 'myv';

Sequence

Synonym

1
2
3
4
5
6
7
8
9
10
11
12
-- create or replace user's synonym
CREATE OR REPLACE SYNONYM mysy FOR mytb;
SELECT * FROM sys.mysy;
-- create public synonym
CREATE OR REPLACE PUBLIC SYNONYM mysy FOR mytb;
SELECT * FROM mysy;
-- recreate
ALTER SYNONYM mysy COMPILE;
-- delete user's synonym
DROP SYNONYM mysy;
-- delete public synonym
DROP PUBLIC SYNONYM mysy;

dual is public synonym of virtual table sys.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

1
2
3
4
5
6
7
8
9
10
-- show log group<->*.log
SELECT * FROM v$logfile;
-- create log group
ALTER DATABASE ADD LOGFILE GROUP 4 ('/tmp/redo01.log','/tmp/redo02.log') SIZE 5m;
-- delete log group
ALTER DATABASE DROP LOGFILE GROUP 4;
-- clear log group's data
ALTER DATABASE CLEAR UNACHIVED LOGFILE GROUP 4;
-- turn next log group
ALTER SYSTEM SWITCH LOGFILE;

Log

1
2
3
4
-- add log to group, size must be equals
ALTER DATABASE ADD LOGFILE MEMBER '/tmp/redo03.log' TO GROUP 4,'/tmp/redo04.log' TO GROUP 4;
-- remove log from group, make sure group not in 'CURRENT' status
ALTER DATABASE DROP LOGFILE MEMBER '/tmp/redo04.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;

SQL

merge: similar union

1
MERGE INTO mytb1 USING mytb2 ON mytb2.column1 = mytb1.column1 WHEN MATCHED THEN UPDATE SET mytb1.column2 = mytb2.column2 WHEN NOT MATCHED THEN INSERT (column1,column2) VALUES(mytb2.column1,mytb2.column2);

PL/SQL

Constrant

1
2
3
4
5
6
7
8
9
10
11
12
13
14
-- make sure `SET SERVEROUTPUT ON` before if Run in SQL*Plus
DECLARE
  c CONSTANT VARCHAR2(1) := 'Y';
BEGIN
  DBMS_OUTPUT.PUT_LINE('c:'||c);
END;
/
-- use column type
DECLARE
  c CONSTANT mytb.column2%TYPE := 'Y';
BEGIN
  DBMS_OUTPUT.PUT_LINE('c:'||c);
END;
/

Variable

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
44
45
46
47
48
49
50
51
52
53
54
55
56
57
-- make sure `SET SERVEROUTPUT ON` before if Run in SQL*Plus
DECLARE
  v1 INT;
  v2 VARCHAR2(1) := 'Y';
BEGIN
  DBMS_OUTPUT.PUT_LINE('v1:'||v1||' and v2:'||v2);
END;
/
-- use column type
DECLARE
  v1 mytb.column1%TYPE;
  v2 mytb.column2%TYPE := 'Y';
BEGIN
	DBMS_OUTPUT.PUT_LINE('before v1:'||v1||' and v2:'||v2);
  SELECT column1,column2 INTO v1,v2 FROM mytb WHERE column1 = 1;
  DBMS_OUTPUT.PUT_LINE('after v1:'||v1||' and v2:'||v2);
END;
/
-- use single row data
DECLARE
  r mytb%ROWTYPE;
BEGIN
  SELECT * INTO r FROM mytb WHERE column1 = 1;
  DBMS_OUTPUT.PUT_LINE('row.column1:'||r.column1||'row.column2'||r.column2);
END;
/
-- use single custom data
DECLARE
  TYPE myrdtype IS RECORD(column1 INT,column2 VARCHAR2(11));
  t myrdtype;
BEGIN
  SELECT column1, column2 INTO t FROM mytb WHERE column1 = 1;
  DBMS_OUTPUT.PUT_LINE('column1:'||t.column1||' and column2:'||t.column2);
END;
/
-- use multi data
DECLARE
  TYPE mytbtype1 IS TABLE OF INT INDEX BY BINARY_INTEGER;
  TYPE mytbtype2 IS TABLE OF mytb%ROWTYPE INDEX BY BINARY_INTEGER;
  TYPE myrdtype IS RECORD(column1 INT,column2 VARCHAR2(11));
  TYPE mytbtype3 IS TABLE OF myrdtype INDEX BY BINARY_INTEGER;
  tb1 mytbtype1;
  tb2 mytbtype2;
  tb3 mytbtype3;
BEGIN
  tb1(1) := 1;
  tb1(2) := 2;
  tb1(3) := 3;
  tb1.DELETE(3);
  tb2(1).column1 := 1;
  tb2(1).column2 := 'Y';
  SELECT * INTO tb3 FROM mytb;
  DBMS_OUTPUT.PUT_LINE('count:'||tb1.COUNT||'tb1[1]:'||tb1(1)||' tb1[2]:'||tb1(2));
  DBMS_OUTPUT.PUT_LINE('tb2[1].column1:'||tb2(1).column1||'tb2[1].column2:'||tb2(1).column2);
  DBMS_OUTPUT.PUT_LINE('tb3[1].column1:'||tb3(1).column1||'tb3[1].column2:'||tb3(1).column2);  
END;
/

Cursor

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
-- make sure `SET SERVEROUTPUT ON` before if Run in SQL*Plus
-- readonly
DECLARE
  /* define; CAN NOT SET LENGTH OF params TYPE */
  CURSOR mycs(param1 INT,param2 VARCHAR2 := 'Y') IS
    SELECT * FROM mytb WHERE column1 = param1 and column2 = param2;
  -- r mytb%ROWTYPE;
BEGIN
  -- OPEN mycs;
  FOR r IN mycs
  LOOP
    -- FETCH mycs INTO r;
    -- EXIT WHEN mycs%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('id:'||mycs%ROWCOUNT||'row.column1:'||r.column1||'row.column2'||r.column2);
  END LOOP;
  --  CLOSE mycs;
END;
/
-- updatable
DECLARE
  /* define; CAN NOT SET LENGTH OF params TYPE; NOWAIT means return fail immediately if OPEN parallel */
  CURSOR mycs(param1 INT,param2 VARCHAR2 := 'Y') IS
    SELECT * FROM mytb WHERE column1 = param1 and column2 = param2 FOR UPDATE OF column1,column2 NOWAIT;
  -- r mytb%ROWTYPE;
BEGIN
  -- OPEN mycs;
  FOR r IN mycs
  LOOP
    -- FETCH mycs INTO r;
    -- EXIT WHEN mycs%NOTFOUND;
    UPDATE mytb SET column2 = 'N' WHERE CURRENT OF mycs;
    DBMS_OUTPUT.PUT_LINE('id:'||mycs%ROWCOUNT||'row.column1:'||r.column1||'row.column2'||r.column2);
  END LOOP;
  --  CLOSE mycs;
END;
/

Exception

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- make sure `SET SERVEROUTPUT ON` before if Run in SQL*Plus
DECLARE
  v VARCHAR2(3):='数1';
  e EXCEPTION;
  PRAGMA EXCEPTION_INT(e, -2290);
BEGIN
  CAST(v as INT);
  EXCEPTION WHEN VALUE_ERROR THEN
    DBMS_OUTPUT.PUT_LINE('catch VALUE_ERROR');
  INSERT INTO mytb(column2) VALUES(v);
  EXCEPTION WHEN e THEN
    DBMS_OUTPUT.PUT_LINE('catch check option fail code -2290');
  WHEN OTHERS THEN
    /* error code [-20000 - -20999] */
    RISE_APPLICATION_ERROR(-20000, 'uncatched exception');
END;
/

Procedure

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
-- list all Procedures
SELECT * FROM user_source;
-- make sure `SET SERVEROUTPUT ON` before if Run in SQL*Plus
-- create or update without params, make sure name not end with '()'
CREATE OR REPLACE PROCEDURE myp AS
  c CONSTANT VARCHAR2(1) := 'Y';
BEGIN
  DBMS_OUTPUT.PUT_LINE('c:'||c);
END;
/
-- create or update with input params
/* CAN NOT SET LENGTH OF params TYPE */
CREATE OR REPLACE PROCEDURE myp(param1 IN INT, param2 IN VARCHAR2 DEFAULT 'Y') AS
  c CONSTANT VARCHAR2(1) := param2;
BEGIN
  DBMS_OUTPUT.PUT_LINE('c:'||c);
END;
/
-- create or update with output params
/* CAN NOT SET LENGTH OF params TYPE */
CREATE OR REPLACE PROCEDURE myp(param1 OUT INT, param2 OUT VARCHAR2) AS
  c CONSTANT VARCHAR2(1) := param2;
BEGIN
  DBMS_OUTPUT.PUT_LINE('c:'||c);
END;
/
-- create or update with input and output params
/* CAN NOT SET LENGTH OF params TYPE; CAN NOT SET DEFAULT VALUE */
CREATE OR REPLACE PROCEDURE myp(param1 IN OUT INT, param2 IN OUT VARCHAR2) AS
  c CONSTANT VARCHAR2(1) := param2;
BEGIN
  param1 := 2
  param2 := 'F'
  DBMS_OUTPUT.PUT_LINE('c:'||c);
END;
/
--- run without params
CALL myp();
--- run with input params
CALL myp(1, 'F');
-- delete
DROP PROCEDURE myp;

Run in SQL*Plus

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
/* transform EXEC into `BEGIN ... END;` */
--- run without params
EXEC myp;
--- run with input params
EXEC myp(1, 'F');
EXEC myp(param2 => 'F', param1 => 1);
--- run with output params
VARIABLE v1 NUMBER;
VARIABLE v2 VARCHAR2(1);
CALL myp(:v1, :v2);
SELECT :v1, :v2 FROM mytb;
--- run with input and output params
VARIABLE v1 NUMBER;
VARIABLE v2 VARCHAR2(1);
CALL myp(:v1, :v2);
SELECT :v1, :v2 FROM mytb;

Func

has one return

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
44
-- make sure `SET SERVEROUTPUT ON` before if Run in SQL*Plus
-- create or update without params, make sure name not end with '()'
CREATE OR REPLACE FUNCTION myf RETURN VARCHAR2 AS
  c CONSTANT VARCHAR2(1) := 'Y';
BEGIN
  DBMS_OUTPUT.PUT_LINE('c:'||c);
  RETURN c;
END;
/
-- create or update with input params
/* CAN NOT SET LENGTH OF params TYPE */
CREATE OR REPLACE FUNCTION myf(param1 IN INT, param2 IN VARCHAR2 DEFAULT 'Y') RETURN VARCHAR2 AS
  c CONSTANT VARCHAR2(1) := param2;
BEGIN
  DBMS_OUTPUT.PUT_LINE('c:'||c);
  RETURN c;
END;
/
-- create or update with output params
/* CAN NOT SET LENGTH OF params TYPE */
CREATE OR REPLACE FUNCTION myf(param1 OUT INT, param2 OUT VARCHAR2) RETURN VARCHAR2 AS
  c CONSTANT VARCHAR2(1) := param2;
BEGIN
  DBMS_OUTPUT.PUT_LINE('c:'||c);
  RETURN c;
END;
/
-- create or update with input and output params
/* CAN NOT SET LENGTH OF params TYPE; CAN NOT SET DEFAULT VALUE */
CREATE OR REPLACE FUNCTION myf(param1 IN OUT INT, param2 IN OUT VARCHAR2) RETURN VARCHAR2 AS
  c CONSTANT VARCHAR2(1) := param2;
BEGIN
  param1 := 2
  param2 := 'F'
  DBMS_OUTPUT.PUT_LINE('c:'||c);
  RETURN c;
END;
/
--- run without params
SELECT myf() FROM mytb;
--- run with input params
SELECT myf(1, 'F') FROM mytb;
-- delete
DROP FUNCTION myf;

Run in SQL*Plus

1
2
3
4
5
6
7
8
9
10
--- run with output params
VARIABLE v1 NUMBER;
VARIABLE v2 VARCHAR2(1);
SELECT myf(:v1, :v2) FROM mytb;
SELECT :v1, :v2 FROM mytb;
--- run with input and output params
VARIABLE v1 NUMBER;
VARIABLE v2 VARCHAR2(1);
SELECT myf(:v1, :v2) FROM mytb;
SELECT :v1, :v2 FROM mytb;

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;

    • 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;
      /

    • 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;
      /

  • 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;
    /

Package

  • define
  • body
    • Procedure
    • Func

      PUT_LINE is one of procedures in DBMS_OUTPUT package

User

Role

Backup

  • expdp(Oracle 11g support): hot backup to files
  • rman: hot or cold backup to blocks

Flashback(Oracle 11g support)

1
SELECT xxx, version_operation, version_starttime, version_endtime FROM mytb VERSION BETWEEN MINVALUE AND MAXVALUE;
  • Table commited DML(INSERT, UPDATE, DELETE, …)
  • Drop
  • Database(off default)

ASM(Oracle 10g support)

RAC

Optimize

Execution Plan

Principle

SQL process principle

1
2
3
4
5
6
     |-------|              DQL                 |---------------------------------| Execution |----------|  |------|
 SQL | Query |>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>|R(ule)/C(ost)-B(ased)-O(ptimizer)|>>>>>>>>>>>|Share Pool|>>|      ||
>>>>>|Parsing| DML(INSERT, UPDATE, DELETE, ...) |---------------------------------|    Plan   |  (SGA)   |  |Result||
     |       |                                  DDL/DCL                                       |----------|  |      ||
     |-------|>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>|------||
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<|------|

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 F5 in PL/SQL Developer): store in plan_table(public synonym of session’s temp table SYS.PLAN_TABLE$) created by catplan.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'));

    • 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;

  • 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'));

    • A(utomatic) W(orkload) R(epository): store in views wrm$_* and wrh$_*(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'));

    • Trace Dump: store *.trc in user_dump_dest dir; you can use tool tkprof pretty 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);

    • 10046 Event: store *.trc in user_dump_dest dir; you can use tool tkprof pretty 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';

CC许可协议署名非商业性使用相同方式共享
本文采用 CC BY-NC-SA 3.0 Unported 协议进行许可

由 Hexo 强力驱动
Theme - Material