Oracle 关系数据库

CREATE TABLE [scmname.]tabname (
  colname DBTYPE [NOT NULL | NULL] [DEFAULT DEFAULT_VALUE] [PRIMARY KEY]
);

CREATE TABLE USERS (
  ID NUMBER(16) PRIMARY KEY NOT NULL,
  CREATED_BY VARCHAR2(64) DEFAULT 'SYSMAN' NOT NULL,
  UPDATED_AT DATE DEFAULT SYSDATE NOT NULL,
  VALID CHAR(1) DEFAULT 'Y' NOT NULL,
  -- CODE RULE AND TYPE
  CODE VARCHAR2(32) UNIQUE NOT NULL,
  NAME VARCHAR2(32)
);

COMMENT ON COLUMN USERS.CODE IS '用户编号, 用于登录';
COMMENT ON COLUMN USERS.NAME IS '用户名字, 用于显示';

-- 添加一列
alter table tabname add colname varchar2;
comment on column tabname.colname is 'comments';
alter table t_employee_base add column is_active char(1) default 'n';
comment on column t_employee_base.is_active is '用户是否激活，默认为 n，表示未激活';
-- 删除一列
alter table tabname drop column colname;
-- 添加主键
alter table tabname add primary key ("pk_name");
-- 重命名列
alter table tabname rename column oldcol to newcol;

-- 查看当前数据库
select name from v$database;
-- 查看所有表
select table_name from user_tables order by table_name;
-- 查看所有表和视图，以及注释
select
  a.table_name || ' ' || a.comments
from
  user_tab_comments a
where
  a.table_type in ('table', 'view')
order by
  a.table_name;
-- 查看单个表结构
desc tabname;

create global temporary table tabname (
  id number
) on commit delete rows;

drop table tabname;
truncate table tabname;

-- 单行索引
create index idxname on tabname(colname);
-- 组合索引
create index idxname on tabname(colname1, colname2);

-- 重建索引：如果经常在索引列上执行 dml 操作，需要定期重建索引
alter index idxname rebuild;
-- 删除索引
drop index idxname;

CREATE TABLE TIME_RANGE_SALES (
  PROD_ID NUMBER (6), -- 产品 ID
  CUST_ID NUMBER,     -- 顾客 ID
  TIME_ID DATE,       -- 时间 ID
  CHANNEL_ID CHAR(1), -- 渠道 ID
  PROMO_ID NUMBER(6),
  QUANTITY_SOLD NUMBER (3),
  AMOUNT_SOLD NUMBER (10, 2)
) PARTITION BY RANGE (TIME_ID) (
  PARTITION SALES_1998 VALUES LESS THAN (TO_DATE('1999-01-01', 'YYYY-MM-DD')),
  PARTITION SALES_1999 VALUES LESS THAN (TO_DATE('2000-01-01', 'YYYY-MM-DD')),
  PARTITION SALES_2000 VALUES LESS THAN (TO_DATE('2001-01-01', 'YYYY-MM-DD')),
  PARTITION SALES_2001 VALUES LESS THAN (MAXVALUE)
);

CREATE TABLE LIST_SALES (
  PROD_ID NUMBER(6), -- 产品 ID
  CUST_ID NUMBER,     -- 顾客 ID
  TIME_ID DATE,       -- 时间 ID
  CHANNEL_ID CHAR(1), -- 渠道 ID
  PROMO_ID NUMBER(6),
  QUANTITY_SOLD NUMBER (3),
  AMOUNT_SOLD NUMBER (10, 2)
) PARTITION BY LIST (CHANNEL_ID) (
  PARTITION EVEN_CHANNELS VALUES (2, 4),
  PARTITION ODD_CHANNELS VALUES (3, 9)
);

CREATE TABLE HASH_SALES (
  PROD_ID NUMBER(6), -- 产品 ID
  CUST_ID NUMBER,     -- 顾客 ID
  TIME_ID DATE,       -- 时间 ID
  CHANNEL_ID CHAR(1), -- 渠道 ID
  PROMO_ID NUMBER(6),
  QUANTITY_SOLD NUMBER (3),
  AMOUNT_SOLD NUMBER (10, 2)
) PARTITION BY HASH (PROD_ID) PARTITIONS 2;

-- 创建分区表的局部索引
CREATE INDEX HASH_SALES_IDX
  ON HASH_SALES (TIME_ID) LOCAL;

-- 创建分区表的全局索引
CREATE INDEX TIME_CHANNEL_SALES_IDX
  ON TIME_RANGE_SALES (CHANNEL_ID) GLOBAL
  PARTITION BY RANGE (CHANNEL_ID) (
    PARTITION P1 VALUES LESS THAN (3),
    PARTITION P2 VALUES LESS THAN (4),
    PARTITION P3 VALUES LESS THAN (MAXVALUE)
  );

CREATE VIEW STAFF AS
SELECT
  EMPLOYEE_ID,
  LAST_NAME,
  JOB_ID
FROM
  EMPLOYEES;

CREATE VIEW STAFF_DEPT_10 AS
SELECT
  EMPLOYEE_ID,
  LAST_NAME,
  JOB_ID
FROM
  EMPLOYEES
WHERE
  DEPARTMENT_ID = 10 WITH CHECK OPTION CONSTRAINT STAFF_DEPT_10_CNST;

CREATE VIEW STAFF_DEPT_10_30 AS
SELECT
  E.EMPLOYEE_ID,
  E.LAST_NAME,
  E.JOB_ID,
  D.DEPARTMENT_NAME
FROM
  EMPLOYEE E,
  DEPARTMENT D
WHERE
  E.DEPARTMENT_ID IN (10, 30)
  AND E.DEPARTMENT_ID = D.DEPARTMENT_ID;

CREATE MATERIALIZED VIEW SALES_MV AS
SELECT
  T.CALENDAR_YEAR,
  P.PROD_ID,
  SUM(S.AMOUNT_SOLD) AS SUM_SALES
FROM
  TIMES T,
  PRODUCTS P,
  SALES S
WHERE
  T.TIME_ID = S.TIME_ID
  AND P.PROD_ID = S.PROD_ID
GROUP BY
  T.CALENDAR_YEAR,
  P.PROD_ID;

-- 创建一个序列
CREATE SEQUENCE CUST_SEQ
  START WITH 1000
  INCREMENT BY 1 NOCACHE NOCYCLE;
-- 使用方法
SELECT CUST_SEQ.NEXTVAL FROM DUAL;
-- 查看当前序列值
SELECT CUST_SEQ.CURRVAL FROM DUAL;

CREATE PUBLIC SYNONYM PEOPLE HR.EMPLOYEES;

CREATE TABLE EMPLOYEES (
  ID NUMBER(16) PRIMARY KEY,
  CREATED_BY VARCHAR2(64) DEFAULT 'SYSMAN' NOT NULL, -- 指定当前列为非空
  NAME VARCHAR2(32)
);

-- 给非空约束添加名称
CREATE TABLE EMPLOYEES (
  ID NUMBER(16) PRIMARY KEY,
  CREATED_BY VARCHAR2(64) DEFAULT 'SYSMAN',
  NAME VARCHAR2(32),
  CONSTRAINT EMP_CREATED_BY_UK(CREATED_BY) NOT NULL; -- 指定当前列为非空
);

-- 添加非空约束
ALTER TABLE TABNAME MODIFY COLNAME [CONSTRAINT CONSTRAINT_NAME] NOT NULL;
-- 删除非空约束
ALTER TABLE TABNAME MODIFY COLNAME NULL;

CREATE TABLE EMPLOYEES (
  EMAIL VARCHAR2 (25),
  CONSTRAINT EMP_EMAIL_NN NOT NULL,      -- 非空
  CONSTRAINT EMP_EMAIL_UK UNIQUE (EMAIL) -- 唯一键
);

-- 添加唯一键
ALTER TABLE TABNAME ADD CONSTRAINT EMP_EMAIL_UK UNIQUE (EMAIL);

CREATE TABLE EMPLOYEES (
  ID NUMBER (16) PRIMARY KEY, -- 主键约束
  NAME VARCHAR2 (32)
);

CREATE TABLE EMPLOYEES (
  ID NUMBER (16),
  NAME VARCHAR2 (32),
  CONSTRAINT EMPLOYEE_PK PRIMARY KEY (ID) -- 主键约束
);

CREATE TABLE DEPARTMENTS (
  ID NUMBER (16) PRIMARY KEY
);

CREATE TABLE EMPLOYEES (
  ID NUMBER (16),
  DEPT_ID NUMBER (16),
  -- 添加外键
  CONSTRAINT EMP_DEPT_FK FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENTS (ID)
);

-- 修改表，添加外键
ALTER TABLE EMPLOYEES
  ADD CONSTRAINT EMP_DEPT_FK FOREIGN KEY (DEPT_ID) REFERENCES DEPARTMENTS (ID);

ALTER TABLE EMPLOYEES ADD CONSTRAINT MAX_EMP_SAL_CK CHECK(SALARY < 10001);

SELECT C.TABLE_NAME, C.COLUMN_NAME, C.DATA_TYPE, C.DATA_LENGTH, C.NULLABLE
  FROM USER_TAB_COLUMNS C ORDER BY C.TABLE_NAME;

SELECT C.OWNER, C.CONSTRAINT_NAME, C.TABLE_NAME, C.COLUMN_NAME, C.POSITION
  FROM USER_CONS_COLUMNS C ORDER BY C.TABLE_NAME;

SELECT C.CONSTRAINT_NAME, C.CONSTRAINT_TYPE, C.STATUS
  FROM USER_CONSTRAINTS C
 WHERE UPPER(C.CONSTRAINT_TYPE) IN ('U', 'P')
 ORDER BY C.CONSTRAINT_NAME;

-- 获取所有表注释
SELECT C.TABLE_NAME, C.TABLE_TYPE, C.COMMENTS
  FROM USER_TAB_COMMENTS C ORDER BY C.TABLE_NAME;
-- 获取所有表的对应列的注释
SELECT C.TABLE_NAME, C.COLUMN_NAME, C.COMMENTS
  FROM USER_COL_COMMENTS C ORDER BY C.TABLE_NAME;

SELECT D.TABLE_NAME FROM DICTIONARY D
 WHERE UPPER(D.TABLE_NAME) LIKE 'V$%' ORDER BY D.TABLE_NAME;

-- 所有 DBA_ 开头的数据库对象信息
SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE
  FROM DBA_OBJECTS O ORDER BY O.OWNER, O.OBJECT_NAME;
-- 所有 ALL_ 开头的数据库对象信息
SELECT O.OWNER, O.OBJECT_NAME, O.OBJECT_TYPE
  FROM ALL_OBJECTS O ORDER BY O.OWNER, O.OBJECT_NAME;

-- 所有 USER_ 开头的数据库对象信息SELECT O.OBJECT_NAME, O.OBJECT_TYPE FROM
USER_OBJECTS O ORDER BY O.OBJECT_NAME;

SQL> set role all;
Role set.
SQL> select count(1) from all_objects;
  COUNT(1)
----------
     18474
SQL> set role none;
Role set.
SQL> select count(1) from all_objects;
  COUNT(1)
----------
     15267

SELECT EMAIL,
       DEPARTMENT_NAME
  FROM EMPLOYEES
  JOIN DEPARTMENTS
    ON EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
 WHERE EMPLOYEE_ID IN (100,103)
 ORDER BY EMAIL;

-- 左连接，如果有些员工没有部门 ID 对应的部门名称返回 NULL
SELECT EMAIL,
       DEPARTMENT_NAME
  FROM EMPLOYEES
  LEFT JOIN DEPARTMENTS
    ON EMPLOYEES.DEPARTMENT_ID = DEPARTMENTS.DEPARTMENT_ID
 WHERE EMPLOYEE_ID in (100,103)
 ORDER BY EMAIL;

-- 笛卡尔积，返回所有员工和部门的排列
SELECT EMPLOYEE_NAME,
       DEPARTMENT_NAME
  FROM EMPLOYEES,
       DEPARTMENTS
 ORDER BY EMPLOYEE_NAME,
          DEPARTMENT_NAME;

SELECT FIRST_NAME,
       LAST_NAME
  FROM EMPLOYEES
 WHERE DEPARTMENT_ID IN (
        SELECT DEPARTMENT_ID -- 子查询
          FROM DEPARTMENTS
         WHERE LOCATION_ID = 1800
       );

UPDATE EMPLOYEES SET SALARY = SALARY * 1.1 WHERE LAST_NAME = 'Baer';

SQL> set autotrace on
SQL> select count(1) from t_department;

  COUNT(1)
----------
        26

Execution Plan
----------------------------------------------------------
Plan hash value: 3597430146

----------------------------------------------------------------------------
| Id  | Operation        | Name            | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT |                 |     1 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE  |                 |     1 |            |          |
|   2 |   INDEX FULL SCAN| T_DEPARTMENT_PK |    24 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        526  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> select sql_id, sql_text from v$sql where sql_text like '%hashtag%';

SQL_ID        SQL_TEXT
------------- ----------------------------------------------------------------
d6a6j07p0jfu3 select sql_id, sql_text from v$sql where sql_text like '%hashtag%'

SQL> select /* hashtag */ sysdate from dual;

SYSDATE
-------------------
2020-02-25 03:42:29

SQL> select sql_id, sql_text from v$sql where sql_text like '%hashtag%';

SQL_ID        SQL_TEXT
------------- -----------------------------------------------------------------
d6a6j07p0jfu3 select sql_id, sql_text from v$sql where sql_text like '%hashtag%'
gadbmhc20xhx8 select /* hashtag */ sysdate from dual
-- 这里发生了硬解析

SQL> select /* hashtag */ sysdate from dual;

SYSDATE
-------------------
2020-02-25 03:51:54

SQL> select sql_id, sql_text from v$sql where sql_text like '%hashtag%';

SQL_ID        SQL_TEXT
------------- -----------------------------------------------------------------
d6a6j07p0jfu3 select sql_id, sql_text from v$sql where sql_text like '%hashtag%'
gadbmhc20xhx8 select /* hashtag */ sysdate from dual
-- library cache 命中，这里发生了软解析

SQL> select /* hashtag new */ sysdate from dual;

SYSDATE
-------------------
2020-02-25 04:00:29

SQL> select sql_id, sql_text from v$sql where sql_text like '%hashtag%';

SQL_ID        SQL_TEXT
------------- -----------------------------------------------------------------
d3k0qy7gmnz75 select /* hashtag new */ sysdate from dual
d6a6j07p0jfu3 select sql_id, sql_text from v$sql where sql_text like '%hashtag%'
gadbmhc20xhx8 select /* hashtag */ sysdate from dual
-- 注意，SQL 的哈希值是根据字符串计算的
-- 如果 SQL 的语义一样，但字符串不一样导致哈希值不一样也会发生硬解析

varname varchar2(20);

declare
  v_name varchar(20) := ''; -- 初始化赋值
  v_sal number;
  v_addr varchar(200);
begin
  -- 直接赋值
  v_sal := 2383;

  -- 语句赋值
  select '北京市朝阳区' into v_addr from dual;

  dbms_output.put_line('姓名: ' || v_name || ', 薪水: ' || v_sal || ', 地址 c: ' || v_addr);
end;

create procedure hire_employees (
  p_last_name varchar2,
  p_job_id varchar2,
  p_manager_id number,
  p_hire_date date,
  p_salary number,
  p_commission_pct number,
  p_department_id number
) is
begin
  -- ...
  insert into employees
      (employee_id, last_name, job_id, manager_id,
          hire_date, salary, commission_pct, department_id)
  values (emp_sequence.nextval, p_last_name, p_job_id,
          p_manager_id, p_hire_date, p_salary, p_commission_pct, p_department_id);
  -- ...
end;

execute hire_employees ('TSMITH', 'CLERK', 1037, sysdate, 500, null, 20);

create package employees_management as
  function hire_employees
      (last_name varchar2, job_id varchar2, manager_id number,
         salary number, commission_pct number, department_id number)
             return number; -- 定义函数
  procedure fire_employees(employee_id number); -- 定义过程
  procedure salary_raise(employee_id number, salary_incr number);
  -- ...
  no_sal exception;
end employees_management; -- 包尾的名称需要和前面一致

execute employees_management.hire_employees
    ('tsmith', 'clerk', 1037, sysdate, 500, null, 20);

declare
  v_lname varchar2(25);
begin
  select last_name into v_lname
      from employees where employee_id = 101;
  -- sqlplus 中默认输出是关闭的，可以使用下面方法开启选项
  -- set serveroutput on
  dbms_output.put_line('Employee last name is ' || v_lname);
end;

create trigger trigger_name
  triggering_statement
  [trigger_restriction]
begin
 triggered_action;
end;

create table orders (
  order_id number primary key,
  /* other attributes */
  line_items_count number default 0
);

create table lineitems (
  order_id references orders,
  seq_no number,
  /* other attributes */
  constraint lineitems primary key(order_id, seq_no)
);

create or replace trigger lineitems_trigger
  after insert or update or delete on lineitems
  for each row
begin
  if (inserting or updating)
  then
    update orders set line_items_count = nvl(line_items_count,0)+1
    where order_id = :new.order_id;
  end if;
  if (deleting or updating)
  then
    update orders set line_items_count = nvl(line_items_count,0)-1
    where order_id = :old.order_id;
  end if;
end;
/

-- 请求 TX 的语句
select /* ... */ for update;
insert /* ... */ ;
update /* ... */ ;
delete /* ... */ ;
merge /* ...*/ ;

-- 请求 TM 的语句
lock table /* ... */ ;
select /* ... */ for update;
insert /* ... */ ;
update /* ... */ ;
delete /* ... */ ;
merge /* ... */ ;

show parameter db_block_size;

-- Percentage of Free Space in Data Blocks
create table test_table (n number) pctfree 20;

-- 查看表空间和数据文件的对应关系
COLUMN "Tablespace" FORMAT A20;
COLUMN "Datafile" FORMAT A60;

SELECT A.TABLESPACE_NAME AS "Tablespace",
       A.FILE_NAME AS "Datafile"
  FROM DBA_DATA_FILES A
 ORDER BY A.TABLESPACE_NAME,
          A.FILE_NAME;

-- 看表空间剩余的容量
COLUMN "Tablespace" FORMAT A20;

SELECT A.TABLESPACE_NAME AS "Tablespace",
       SUM(BLOCKS) AS "Blocks",
       ROUND(SUM(BYTES) / 1024 / 1024, 2) AS "Free (M)"
  FROM DBA_FREE_SPACE A
 GROUP BY A.TABLESPACE_NAME
 ORDER BY A.TABLESPACE_NAME;

-- 查看表空间使用量
COLUMN "Tablespace" FORMAT A20;

SELECT SUBSTR(A.TABLESPACE_NAME, 1, 20) AS "Tablespace",
       ROUND(A.BYTES / 1024 / 1024, 2) AS "Size (M)",
       DECODE(B.USED_BYTES, NULL, 0, ROUND(B.USED_BYTES / 1024 / 1024, 2)) AS "Current (M)",
       DECODE(C.FREE_BYTES, NULL, 0, ROUND(C.FREE_BYTES / 1024 / 1024, 2)) AS "Free (M)",
       DECODE(B.USED_BYTES, NULL, 0, ROUND((B.USED_BYTES / A.BYTES) * 100, 2)) AS "Used (%)"
  FROM DBA_DATA_FILES A,
       (
        SELECT FILE_ID,
               SUM(BYTES) USED_BYTES
          FROM DBA_EXTENTS
         GROUP BY FILE_ID
       ) B,
       (
        SELECT SUM(BYTES) FREE_BYTES,
               FILE_ID
          FROM DBA_FREE_SPACE
         GROUP BY FILE_ID
       ) C
 WHERE B.FILE_ID(+) = A.FILE_ID
   AND C.FILE_ID(+) = A.FILE_ID
 ORDER BY A.TABLESPACE_NAME,
          A.FILE_NAME;

-- 查看表空间对应数据文件数量
COLUMN "Tablespace" FORMAT A20;
COLUMN "Datafile" FORMAT A60;

SELECT A.TABLESPACE_NAME AS "Tablespace",
       COUNT(*) AS "Count",
       ROUND(SUM(BYTES) / 1024 / 1024, 2) AS "Current (M)",
       ROUND(SUM(MAXBYTES) / 1024 / 1024, 2) AS "Maximum (M)",
       ROUND(SUM(BYTES) / SUM(MAXBYTES), 2) AS "Used (%)"
  FROM DBA_DATA_FILES A
 GROUP BY A.TABLESPACE_NAME
 ORDER BY A.TABLESPACE_NAME;

-- 创建默认表空间
CREATE TABLESPACE PFILE1
  DATAFILE '/oradata/xe/part1.dbf'
  SIZE 128M AUTOEXTEND OFF
  LOGGING ONLINE PERMANENT
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  BLOCKSIZE 8K
  SEGMENT SPACE MANAGEMENT AUTO
  FLASHBACK ON;

-- 创建临时表空间
CREATE TEMPORARY TABLESPACE TFILE1
  TEMPFILE '/oradata/xe/temp1.dbf'
  SIZE 2048M AUTOEXTEND OFF
  TABLESPACE GROUP ''
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- 查看 sga 和 pga 相关参数
show parameter sga;
show parameter pga;

alter system set sga_max_size = 20g scope = both;
alter system set sga_target = 20g scope = both;
alter system set pga_aggregate_target = 8g scope = both;

alter system set workarea_size_policy = auto scope = both;
alter system set pga_aggregate_target = 3072m scope = both;

-- 查看 Buffer Cache 参数
COLUMN "Name" FORMAT A32;
COLUMN "Value" FORMAT A64;

SELECT T.NAME AS "Name",
       T.VALUE AS "Value"
  FROM V$PARAMETER T
 WHERE T.NAME IN ('db_cache_size', 'db_keep_cache_size', 'db_recycle_cache_size');

-- 查看缓冲区命中率
SELECT 100 * (1 - ((PHYSICAL.VALUE - DIRECT.VALUE - LOBS.VALUE) / LOGICAL.VALUE)) AS "Buffer Cache Hit Ratio (%)"
  FROM V$SYSSTAT PHYSICAL,
       V$SYSSTAT DIRECT,
       V$SYSSTAT LOBS,
       V$SYSSTAT LOGICAL
 WHERE PHYSICAL.NAME = 'physical reads'
   AND DIRECT.NAME = 'physical reads direct'
   AND LOBS.NAME = 'physical reads direct (lob)'
   AND LOGICAL.NAME = 'session logical reads';

-- 获取推荐缓冲区值
column "Name" format a12;
select
  a.name as "Name",
  a.size_for_estimate as "Adviced Size (M)",
  a.estd_physical_reads as "Estimed Physical Reads"
from
  v$db_cache_advice a
where
  block_size = '8192'
  and advice_status = 'ON';

alter system set open_cursors = 500 scope = both;

-- cpu 数量要和物理主机的线程数一致
show parameters cpu_count;

set heading off;
show parameter processes;
show parameter sessions;
show parameter transactions;

COLUMN NAME FORMAT A32;
COLUMN VALUE FORMAT A64;

SELECT T.NAME,
       T.VALUE
  FROM V$PARAMETER T
 WHERE NAME in ('sessions', 'processes', 'transactions');

alter system set processes = 1024 scope = both;
alter system set sessions = 1568 scope = both;
alter system set transactions = 1126 scope = both;

$ tnsping localhost  -- tnsping 工具

TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 26-FEB-2020 17:22:33

Copyright (c) 1997, 2013, Oracle.  All rights reserved.

Used parameter files:
/u01/app/oracle/product/11.2.0/dbhome_1/network/admin/sqlnet.ora

Used EZCONNECT adapter to resolve the alias
Attempting to contact (DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=))(ADDRESS=(PROTOCOL=TCP)(HOST=127.0.0.1)(PORT=1521)))
OK (10 msec)

$ lsnrctl status -- lsnrctl 工具

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 26-FEB-2020 17:22:45

Copyright (c) 1991, 2013, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date                26-FEB-2020 12:36:36
Uptime                    0 days 4 hr. 46 min. 9 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/diag/tnslsnr/ol64-52/listener/alert/log.xml
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ol64-52.localdomain)(PORT=1521)))
Services Summary...
Service "ora11g" has 1 instance(s).
  Instance "ora11g", status READY, has 1 handler(s) for this service...
Service "ora11gXDB" has 1 instance(s).
  Instance "ora11g", status READY, has 1 handler(s) for this service...
The command completed successfully

alter system register;

show user;
select user from dual;

SQL> show user;
USER is "APPLE"
SQL> select user from dual;
APPLE

create user <username> identified by <password>;
alter user <username> account unlock identified by <password>;
alter user hr account unlock identified by hr;

drop user <username>;

alter user <username> identified by <password>;
alter user <username> identified by values <encrypted_password>;

-- 创建角色
create role <rolename> identified by <password>;
-- 授权用户
grant all on <schema>.* to <username>;
-- 解除授权
revoke all on <schema>.* from <username>;

mkdir -p /u01/app/oracle/oradata/XE

-- 创建默认表空间
CREATE TABLESPACE PFILE1
  DATAFILE '/u01/app/oracle/oradata/xe/pfile1.dbf'
  SIZE 128M AUTOEXTEND OFF
  LOGGING
  ONLINE
  PERMANENT
  EXTENT MANAGEMENT LOCAL AUTOALLOCATE
  BLOCKSIZE 8K
  SEGMENT SPACE MANAGEMENT AUTO
  FLASHBACK ON;

-- 创建临时表空间
CREATE TEMPORARY TABLESPACE TFILE1
  TEMPFILE '/u01/app/oracle/oradata/xe/tfile1.dbf'
  SIZE 2048M AUTOEXTEND OFF
  TABLESPACE GROUP ''
  EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

drop user avictor cascade;

create user avictor
  identified by secret
  default tablespace pfile1
  temporary tablespace tfile1
  profile default
  account unlock;

grant connect to avictor;
grant dba to avictor;
alter user avictor default role all;

grant select any dictionary to avictor;
grant select any sequence to avictor;
grant select any table to avictor;
grant select any transaction to avictor;
grant unlimited tablespace to avictor;

alter user avictor identified by secret;

sqlplus avictor/secret@localhost/xe

-- 导出所有的表
exp user/pass@host:port/sid FULL=y FILE=data.dmp

-- 导入所有的表
imp user/pass@host:port/sid FULL=y FILE=data.dmp

-- 查看帮助和可选的参数
exp help=y
imp help=y

USERID=system/oracle@host/sid
FILE=data.dmp
GRANTS=y
INDEXES=y
CONSTRAINTS=y
ROWS=y

-- 导出数据库
exp PARFILE=options.txt LOG=export.log OWNER=owner1

-- 导入数据库
imp PARFILE=options.txt LOG=import.log FROMUSER=owner1 TOUSER=owner2

unzip instantclient-basic-macos.x64-11.2.0.4.0.zip
unzip instantclient-sdk-macos.x64-11.2.0.4.0.zip
unzip instantclient-sqlplus-macos.x64-11.2.0.4.0.zip

sudo mkdir -p /usr/local/java
sudo cp -r instantclient_11_2 /usr/local/java

cd /usr/local/java/instantclient_11_2
mkdir lib
ln -s libclntsh.dylib.11.1 libclntsh.dylib
ln -s libocci.dylib.11.1 libocci.dylib
ln -s $PWD/{libclntsh.dylib.11.1,libnnz11.dylib,libociei.dylib} lib/
ln -s $PWD/{libsqlplus.dylib,libsqlplusic.dylib} lib/

export SQLPATH="/usr/local/java/instantclient_11_2"
export PATH="${SQLPATH}:$PATH"
export DYLD_LIBRARY_PATH="${SQLPATH}/lib${DYLD_LIBRARY_PATH:+:${DYLD_LIBRARY_PATH}}"

unzip instantclient-basic-linux.x64-11.2.0.4.0.zip
unzip instantclient-sdk-linux.x64-11.2.0.4.0.zip
unzip instantclient-sqlplus-linux.x64-11.2.0.4.0.zip

[ ! -d /usr/local/java ] && mkdir -p /usr/local/java
sudo mv instantclient_11_2 /usr/local/java
cd /usr/local/java/instantclient_11_2
sudo mkdir lib

ln -s libclntsh.so libclntsh.so.11.1
ln -s libocci.so libocci.so.11.1

cd /usr/lib
ln -s /usr/local/java/instantclient_11_2/libclntsh.so libclntsh.so.11.1
ln -s /usr/local/java/instantclient_11_2/libocci.so libocci.so.11.1
ln -s /usr/local/java/instantclient_11_2/libnnz11.so libnnz11.so
ln -s /usr/local/java/instantclient_11_2/libociei.so libociei.so
ln -s /usr/local/java/instantclient_11_2/libocijdbc11.so libocijdbc11.so
ln -s /usr/local/java/instantclient_11_2/libsqlplusic.so libsqlplusic.so
ln -s /usr/local/java/instantclient_11_2/libsqlplus.so libsqlplus.so

export SQLPATH="/usr/local/java/instantclient_11_2"
export PATH=${SQLPATH}${PATH:+:${PATH}}
export LD_LIBRARY_PATH=${SQLPLUS}/lib${LD_LIBRARY_PATH:+:${LD_LIBRARY_PATH}}

-- 定义一个变量
define var = text;
-- 读取用户输入，将用户输入放到变量中
accept var;
-- 在变量名前面添加 & 来使用变量
define mydate = 01-FEB-98;
select '&mydate' from dual;

SQL> DEFINE MYDATE = 01-FEB-98;
SQL> SELECT '&MYDATE' FROM DUAL;
原值    1: SELECT '&MYDATE' FROM DUAL
新值    1: SELECT '01-FEB-98' FROM DUAL

'01-FEB-98'
---------------------------
01-FEB-98

SQL>

column colname new_value var

SQL> column len1 new_value l1
SQL> select max(length(t.table_name)) as len1 from user_tab_comments t;

      LEN1
----------
        29
SQL> select &l1 from dual;
old   1: select &l1 from dual
new   1: select         29 from dual

        29
----------
        29
SQL>