Oracle知识小结--持续更新

Oracle知识小结–持续更新

1.创建用户

#登录 ：
C:\Users\zhang>sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on Fri Mar 20 14:18:38 2020

Copyright (c) 1982, 2010, Oracle.  All rights reserved.
#切换用户 ：
SQL>conn /as sysdba;
#创建用户 ：
SQL> create user test identified by test  --test/test;
#授权 ：
SQL> grant connect, resource ,dba to test;
#connect 临时权限,不能建表
#resource 正式用户 可以建表
#dba 管理员用户 各种权限

2.导入数据

#cmd命令窗口内输入 （imp文件）
C:\Users\zhang>imp userid=test/test@orcl file=C:\Users\zhang\Desktop\test.dmp full=y
#cmd命令窗口内输入 （impdp文件）
C:\Users\zhang>impdp test/test@orcl directory=DATA_PUMP_DIR dumpfile=test.DMP remap_tablespace=TSP_test:TSP_test  logfile=blog.log remap_schema=user:password;

将test.DMP放入到C:\app\zhang\admin\orcl\dpdump 
执行以下命令
若出现表空间错误
则执行
create tablespace TSP_test
  datafile 'C:\app\zhang\oradata\orcl\TSP_test.dbf'
  size 20M
  reuse
  autoextend on
  next 5M
  maxsize unlimited
LOGGING   
PERMANENT   
EXTENT MANAGEMENT LOCAL AUTOALLOCATE   
BLOCKSIZE 8K   
SEGMENT SPACE MANAGEMENT MANUAL   
FLASHBACK ON;
再执行导入语句

3.创建视图表

CREATE [OR REPLACE]  (如果视图存在,则替换旧视图)
[{FORCE|NOFORCE}]  
(FORCE：即使基表不存在，也可以创建该视图，但是该视图不能正常使用，当基表创建成功后，视图才能正常使用
NOFORCE：如果基表不存在，无法创建视图，该项是默认选项)
VIEW view_name(视图名称) + AS +查询语句 + WITH READ ONLY(只读视图)
注意 ；括号内为解释 

示例 ：
CREATE OR REPLACE VIEW view_name
AS

SELECT EMPNO,ENAME,JOB,HIREDATE,EMP.DEPTNO,DNAME
FROM EMP JOIN DEPT ON EMP.DEPTNO=DEPT.DEPTNO

WITH READ ONLY

4.查询该用户是否有这个表，视图

user_views   该用户下的所有视图 
dba_tables   所有表,包括系统表
all_tables      所有表
user_tables   该用户的表

select * from all_tab_comments -- 查询所有用户的表,视图等。

select * from user_tab_comments -- 查询本用户的表,视图等。

select * from all_col_comments  --查询所有用户的表的列名和注释。

select * from user_col_comments -- 查询本用户的表的列名和注释。

select * from all_tab_columns --查询所有用户的表的列名等信息。

select * from user_tab_columns --查询本用户的表的列名等信息。

5.导出空表

导出语句某用户下的所有表 : cmd 下 : exp username/password@xxxx.xxxx.x.xx/orcl file = d:/test.dmp owner=username 
导出某张表数据 : cmd : exp username/password@xxxx.xxxx.x.xx/orcl file = d:/test.dmp tables = ()
1.select 'alter table '||table_name||' allocate extent;'      from user_tables where num_rows=0;
2.复制里面的数据执行
3.alter system set deferred_segment_creation=false

6.创建dblink

create database link TestDblink
 connect to dbName identified by dbPassword
  using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.2.158)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';
 
--TestDblink ： 表示dblink名字

--dbName ：表示 远程数据库的用户

--dbPassword：表示 远程数据库的密码

--HOST ： 表示远程数据库IP

--PORT ： 表示远程数据库端口

--SERVICE_NAME ： 远程数据库的实例名

-- 查询、删除和插入数据和操作本地的数据库是一样的，只不过表名需要写成“表名@dblink服务器”而已。 

--例如：如果想在本地数据库中通过dblink访问远程数据库'orcl'中dbName.tb_test表,sql语句如下所示
select * from db.tb_test@TestDblink;

--DBLINK其他相关的知识：
--1、查看所有的数据库链接，登录管理员查看  
select owner,object_name from dba_objects where object_type='DATABASE LINK';
--2.删除数据库连接
drop database link TestDblink;

7.创建触发器

-- Create table 创建存放触发器数据的表
create table DELETE_MASTER
(
  user_id       VARCHAR2(30),--当前操作数据库用户
  object_type   VARCHAR2(20),
  host_name   VARCHAR2(50), --存放客户端主机名
  ip_path  VARCHAR2(100), --存放ip地址
  creation_date DATE --更新时间
)
tablespace USERS
  pctfree 10
  initrans 1
  maxtrans 255
  storage
  (
    initial 64
    next 8
    minextents 1
    maxextents unlimited
  );
--创建触发器，当lcp_test产生删除操作时保存数据到delete_master表中
--LogCreations是触发器名
CREATE OR REPLACE TRIGGER LogCreations
  AFTER DELETE ON 表名

for each row
  
declare
  ip varchar2(20);
  user varchar2(20);
  msg varchar2(50);
BEGIN
  select sys_context('userenv','ip_address'),sys_context('userenv','SESSION_USER'),REPLACE(SUBSTR(sys_context('USERENV','HOST'),1,30),'\',':') into ip,user,msg from dual;
  INSERT INTO delete_master (user_id, object_type, host_name,
                             ip_path, creation_date)
    VALUES (user, '', msg, ip, SYSDATE);
END LogCreations;

--触发器2 , 当某表某个字段从1变成了空或者不是1的数了,触发,保存对应电脑的ip及用户
CREATE OR REPLACE TRIGGER LogCreations
  AFTER update  ON 表名
for each row
declare
  ip varchar2(20);
  user varchar2(20);
  msg varchar2(50);
BEGIN
  if :old.字段名= 1 and (:old.字段名<> :new.字段名or :new.字段名is null) then

  select sys_context('userenv','ip_address'),sys_context('userenv','SESSION_USER'),REPLACE(SUBSTR(sys_context('USERENV','HOST'),1,30),'\',':') into ip,user,msg from dual;
  INSERT INTO delete_master (user_id, object_type, host_name,
                             ip_path, creation_date)
    VALUES (user, :old.用户名, msg, ip, SYSDATE);
    end if;
END LogCreations;

示例：对某张表执行删除和更新操作后记录动作(复制来，并未使用过)

create or replace trigger emp_bud
before update or delete on emp
for each row
declare
  v_action varchar2(50);
  v_log    varchar2(500) := '';
begin
  if :new.empno is not null then
    begin
      v_log := v_log || '操作' || :new.empno ;
      if :new.ename <> :old.ename then
        v_log := v_log || ' ename 由' || :old.ename || '更改为' || :new.ename;
      end if;
      if :new.job <> :old.job then
        v_log := v_log || ' job 由' || :old.job || '更改为' || :new.job;
      end if;
      if :new.mgr <> :old.mgr then
        v_log := v_log || ' mgr 由' || :old.mgr || '更改为' || :new.mgr;
      end if;
      if :new.hiredate <> :old.hiredate then
        v_log := v_log || ' hiredate 由' || :old.hiredate || '更改为' ||
                 :new.hiredate;
      end if;
      if :new.sal <> :old.sal then
        v_log := v_log || ' sal 由' || :old.sal || '更改为' || :new.sal;
      end if;
      if :new.comm <> :old.comm then
        v_log := v_log || ' comm 由' || :old.comm || '更改为' || :new.comm;
      end if;
      if :new.deptno <> :old.deptno then
        v_log := v_log || ' deptno 由' || :old.deptno || '更改为' || :new.deptno;
      end if;
      if UPDATING then
        v_action := 'update';
      elsif deleting then
        v_action := 'delete';
      end if;
      insert into emp_log
        (id, action, time, log, actor)
      values
        (EMPLOG_SEQ.NEXTVAL, v_action, sysdate, v_log, USER);
      /*pkg_emp.v_emp_obj.empno := :new.empno;
      select *
      into   pkg_emp.v_emp_obj
      from   emp
      where  empno = pkg_emp.v_emp_obj.empno;
      dbms_output.put_line ( 'ename = ' || pkg_emp.v_emp_obj.ename || ' job = ' || pkg_emp.v_emp_obj.job );*/
    exception
      when others then
        dbms_output.put_line(SQLCODE || '-before-' || SQLERRM);
    end;
  else
    pkg_emp.v_emp_obj.empno := 0;
  end if;
end;

8.plsql左侧对象下各文件夹说明

function：一般是存储函数或者方法
procedures:一般是存放存储过程
Table：一般是存放建的表
packages:包（头） 
package bodies:包体
types:类型 
type bodies:类型体 
triggers:触发器 
jobs:作业(工作) 
queues:队列 
queue tables:队列表 
tables:表 
views:视图 
sequences:序列号
users:用户 
roles:权限 
database links:数据库链接 
tablespaces:表空间

9.oracle中dual详解（用于查询一些内容）

Dual 是 Oracle中的一个实际存在的表，任何用户均可读取，常用在没有目标表的Select语句块中
--查看当前连接用户
select user from dual;
--查看当前日期、时间
select sysdate from dual;
--当作计算器用
select 1+2 from dual;
--查看序列值
 create sequence aaa increment by 1 start with 1;
 select aaa.nextval from dual;

dual中的sys_context()函数

第一个参数为'USERENV'，是固定的，第二个参数也是固定的，但是是多选固定，可选的值如下所示：

select sys_context('USERENV','AUTHENTICATION_TYPE') from dual;--用户的认证类型
select sys_context('USERENV','AUTHENTICATION_DATA') from dual;--未知
select sys_context('USERENV','BG_JOB_ID') from dual;--当前指定id的会话是否为oracle后台程序建立，不是则返回null
select sys_context('USERENV','CLIENT_INFO') from dual;--通过dbms_application_info包可以存储高达64字节的用户会话信息
select sys_context('USERENV','CURRENT_SCHEMA') from dual;--默认的schema将被当做当前的schema。当在当前会话中使用ALTER SESSION SET CURRENT_SCHEMA语句的时候，它的查询返回值将被改变
select sys_context('USERENV','CURRENT_SCHEMAID') from dual;--当前schema的id
select sys_context('USERENV','CURRENT_USER') from dual;--当前的登陆用户
select REPLACE(SUBSTR(sys_context('USERENV','HOST'),1,30),'\',':') from dual;--当前会话主机操作系统名
select sys_context('USERENV','CURRENT_USERID') from dual;--当前登陆的用户的id
select sys_context('USERENV','DB_DOMAIN') from dual;--为数据库的域指定初始化参数
select sys_context('USERENV','DB_NAME') from dual;--数据库实例名
select sys_context('USERENV','ENTRYID') from dual;--可用的审计标示符。不能再分布式sql语句中使用此选项。使用USERENV关键字必须置AUDIT_TRAIL的初始化参数为真。
select sys_context('USERENV','EXTERNAL_NAME') from dual;--数据库用户的扩展名
select sys_context('USERENV','FG_JOB_ID') from dual;--返回作业id当此会话是客户端进程创建。否则，返回null
select sys_context('USERENV','INSTANCE') from dual;--当前数据库实例的标示id

select sys_context('USERENV','ISDBA') from dual;--当前用户是否是以dba身份登录
select sys_context('USERENV','LANG') from dual;--iso对‘LANGUAGE’的简称，查询的参数比“LANGUAGE”短
select sys_context('USERENV','LANGUAGE') from dual;--结果为当前数据库使用的存储语言，跟上面查询意义一样
select sys_context('USERENV','NETWORK_PROTOCOL') from dual;--用于通信的网络协议
select sys_context('USERENV','NLS_CALENDAR') from dual;--当前会话使用的，格林尼治时间
select sys_context('USERENV','NLS_CURRENCY') from dual;--本地化的货币符，如人民币为￥，美元符为$
select sys_context('USERENV','NLS_DATE_FORMAT') from dual;--当前使用的日期格式，一般中国为dd-mon-rr
select sys_context('USERENV','NLS_DATE_LANGUAGE') from dual;--表示日期的语言，如中文简体SIMPLIFIED CHINESE

select sys_context('USERENV','NLS_TERRITORY') from dual;--数据库服务器所在区域，如中国CHINA
select sys_context('USERENV','OS_USER') from dual;--操作系统的用户名
select sys_context('USERENV','PROXY_USER') from dual;--是否使用代理用户。否返回null
select sys_context('USERENV','PROXY_USERID') from dual;--代理用户id
select sys_context('USERENV','SESSION_USER') from dual;--当前认证的数据库用户名
select sys_context('USERENV','SESSION_USERID') from dual;--当前认证的数据库用户名id
select sys_context('USERENV','SESSIONID') from dual;--当前会话id
select sys_context('USERENV','TERMINAL') from dual;--操作系统用户组
select sys_context('USERENV','IP_ADDRESS') from dual;--当前会话主机ip
select sys_context('USERENV','HOST') from dual;--当前会话主机操作系统名

10.sql语句

 (1) 根据特殊字符截取数据
 --例,查询结果根据 _ 截取某字段前后内容分割为两列
 select v.user_no, --例 32658_12
 SUBSTR(v.patient_no,1,INSTR(v.user_no,'_',1,1)-1) as user_id,--32658
 SUBSTR(v.patient_no,INSTR(v.user_no,'_',1,1)+1) as visit_id --12
 from user v ;
 (2) 生成随机数(转)
   1)从表中随机取记录
     select * from (select * from staff order by dbms_random.random)      where rownum < 4
     表示从STAFF表中随机取3条记录

   2)产生随机数
     SELECT DBMS_RANDOM.RANDOM FROM DUAL;          
     产生一个任意大小的随机数
     SELECT ABS(MOD(DBMS_RANDOM.RANDOM,100)) FROM DUAL;          
     产生一个100以内的随机数
     SELECT TRUNC(100+900*dbms_random.value) FROM dual;          
     产生一个100～1000之间的随机数
     SELECT dbms_random.value FROM dual;          
     产生一个0～1之间的随机数
     SELECT dbms_random.value(10,20) FROM dual;          
     产生一个10～20之间的随机数
     SELECT dbms_random.normal FROM dual;          
     NORMAL函数返回服从正态分布的一组数。此正态分布标准偏差为1，期望值为0。这个函数返回的数值中有68%是介于-1与+1之间，95%介于-2与+2之间，99%介于-3与+3之间。

   3)产生随机字符串
     select dbms_random.string('P',20) from dual;          
     第一个参数 P 表示 printable，即字符串由任意可打印字符构成          
     第二个参数表示返回字符串长度

   4)ceil( n )函数是返回大于或等于n的最小整数。
     DBMS_RANDOM.VALUE()是随机产生( 0,1 )之间的数。
     要产生两位的随机数，可以DBMS_RANDOM.VALUE()*100，这样产生( 0,100 )的随机数，当产生( 0,10)之间的数时，只要加上10就可以保证产生的数都是两位了。

   ORACLE的PL/SQL提供了生成随机数和随机字符串的多种方式，罗列如下:
   1、小数( 0 ~ 1)
     select dbms_random.value from dual
   2、指定范围内的小数 ( 0 ~ 100 )      
     select dbms_random.value(0,100) from dual
   3、指定范围内的整数 ( 0 ~ 100 )
     select trunc(dbms_random.value(0,100)) from dual
   4、长度为20的随机数字串
     select substr(cast(dbms_random.value as varchar2(38)),3,20) from dual
   5、正态分布的随机数
     select dbms_random.normal from dual
   6、随机字符串
     select dbms_random.string(opt, length) from dual
     opt可取值如下：       
     'u','U'    :    大写字母       
     'l','L'    :    小写字母       
     'a','A'    :    大、小写字母       
     'x','X'    :    数字、大写字母       
     'p','P'    :    可打印字符
   7、随机日期
     select to_date(2454084+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J') from dual
     通过下面的语句获得指定日期的基数
     select to_char(sysdate,'J') from dual
   8、生成GUID
     select sys_guid() from dual
--生成带分隔符(-)的GUID的自定义函数 create or replace function my_guid return varchar2 is     guid varchar(36);     temp varchar(32); begin     temp:=sys_guid();     guid:= substr(temp,1,8) || '-'          ||substr(temp,9,4) || '-'          ||substr(temp,13,4)|| '-'          ||substr(temp,17,4)|| '-'          ||substr(temp,21,12);     return guid; end;
 

11.若数据库表中索引不生效

查询sql是否走索引
EXPLAIN PLAN FOR  sql语句 ;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
使用一下语句重新构建表优化器:
analyze table tablename compute statistics;
analyze index indexname compute statistics;

Oracle11g各个服务项说明

oracle安装完成后共七个服务，含义分别为：
1. Oracle ORCL VSS Writer Service：Oracle卷映射拷贝写入服务，VSS（Volume Shadow Copy Service）能够让存储基础设备（比如磁盘，阵列等）创建高保真的时间点映像，即映射拷贝（shadow copy）。它可以在多卷或者单个卷上创建映射拷贝，同时不会影响到系统的系统能。（非必须启动）
 
2. OracleDBConsoleorcl：Oracle数据库控制台服务，orcl是Oracle的实例标识，默认的实例为orcl。在运行Enterprise Manager（企业管理器OEM）的时候，需要启动这个服务。（非必须启动）
 
3. OracleJobSchedulerORCL：Oracle作业调度（定时器）服务，ORCL是Oracle实例标识。（非必须启动）
 
4. OracleMTSRecoveryService：服务端控制。该服务允许数据库充当一个微软事务服务器MTS、COM/COM+对象和分布式环境下的事务的资源管理器。（非必须启动）
 
5. OracleOraDb11g_home1ClrAgent：Oracle数据库.NET扩展服务的一部分。 （非必须启动）
 
6. OracleOraDb11g_home1TNSListener：监听器服务，服务只有在数据库需要远程访问的时候才需要。（非必须启动）
 
7. OracleServiceORCL：数据库服务(数据库实例)，是Oracle核心服务该服务，是数据库启动的基础， 只有该服务启动，Oracle数据库才能正常启动。(必须启动)
 
对新手来说，要是只用Oracle自带的sql*plus的话，只要启动OracleServiceORCL即可，要是使用PL/SQL Developer等第三方工具的话，OracleOraDb11g_home1TNSListener服务也要开启。OracleDBConsoleorcl是进入基于web的EM必须开启的，其余服务很少用。