oracle笔记

奇妙探险家

已于 2024-02-28 13:23:00 修改

阅读量1.1k

点赞数

分类专栏： oracle 文章标签： oracle

于 2019-05-31 18:08:36 首次发布

本文链接：https://blog.csdn.net/u013760453/article/details/90720892

版权

oracle 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

普通用户连接oracle
sqlplus 'scott/"scott"'@127.0.0.1:1521/orcl

sys用户登录oracle
sqlplus sysdba/admin@127.0.0.1/orcl as sysdba

用户

--创建可登陆的普通用户
create user lztest identified by "lz=e=stt";
grant create session to lztest;

--创建新用户
drop user user1 CASCADE;
CREATE USER user1 IDENTIFIED BY "111111";
GRANT CONNECT TO user1;
GRANT RESOURCE TO user1;
GRANT CREATE VIEW TO user1;
GRANT UNLIMITED TABLESPACE TO user1;

--授予查看所有表的权限
grant select any table to lztest;

-- 创建公有的普通用户，用户名必须以c##开头,应该优先创建公有用户，因为PDB下创建的用户需要修改数据库配置文件才能连接到数据库
create user c##temp identified by temp;
alter user scott quota unlimited on users;

===========================================
CREATE USER qry_smartbi IDENTIFIED BY "111111";
GRANT CONNECT TO qry_smartbi;
GRANT RESOURCE TO qry_smartbi;
GRANT CREATE VIEW TO qry_smartbi;
GRANT UNLIMITED TABLESPACE TO qry_smartbi;
GRANT SELECT ANY TABLE TO qry_smartbi;
ALTER USER qry_smartbi DEFAULT ROLE ALL;
===========================================

-- 查看是否为可插拔模式
select CDB from v$database;
-- 打开可插拔数据库orclpdb，close为关闭
alter pluggable database orclpdb open;
-- 设置当前容器为可插拔数据库orclpdb，该环境设置会影响上述创建用户的操作
alter session set container=orclpdb;
-- 在可插拔数据库中创建本地用户（不推荐），因为PDB下创建的用户需要修改数据库配置文件才能连接到数据库
create user temp identified by temp;

-- 授权连接登录
grant connect, resource,create session to c##temp;
-- 授予角色
grant dba to c##temp;

-- 查看所有用户、可管理的用户
select * from dba_users; 
select * from all_users;

-- 查看用户拥有的表
select * from all_tables where owner='SCOTT';

--修改当前会话的默认schema
alter session set current_schema=TRADE;

--修改密码，需要用sqlplus登录数据库
sqlplus user1/passwd1@ip1:1521/orcl
alter user user1 identified  by "1818" ;

--查看用户密码有效期、配置文件
select username,user_id,account_status,expiry_date,profile from dba_users where username='SCOTT';
--将配置文件DEFAULT修改为密码永不过期，则使用该文件的用户密码都不会过期
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
--创建名为passwd_unlimit的profile文件，设置其下的密码为永不过期。
create profile  passwd_unlimit limit PASSWORD_LIFE_TIME unlimited;
--指定用户使用特定的密码配置文件
alter user ETL_TEST profile passwd_unlimit;

--查看运行中的sql
SELECT a.username,a.machine, b.sql_id, b.SQL_FULLTEXT
FROM v$session a, v$sqlarea b
WHERE a.sql_address = b.address AND a.SQL_HASH_VALUE = b.HASH_VALUE;

--查看历史上运行的查询
SELECT LAST_ACTIVE_TIME,t.* FROM v$sql t
where last_active_time is not null
ORDER BY t.LAST_ACTIVE_TIME DESC

--查看用户使用中的连接，生成踢出语句，便于删除用户
SELECT sid, serial#,osuser,username,
'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''';' kill_sql
FROM v$session  s1
where s1.username='LZTEST';

--删除用户,可能需要sqlplus连接
drop user u1 CASCADE;

--查看数据库版本
select * from v$version;

--复制用户及名下所有对象
mkdir -p /data/oradata/ORCL
chmod 777 /data/oradata/ORCL
create directory ctest_dir as '/data/oradata/ORCL'
CREATE USER emuser1 IDENTIFIED BY emuser1;
GRANT CONNECT, RESOURCE TO emuser1;
su - oracle
expdp \'sys/oracle as sysdba\' DIRECTORY=CTEST_DIR DUMPFILE=userCtest.dmp SCHEMAS=emuser CONTENT=ALL  
impdp \'sys/oracle as sysdba\' DIRECTORY=CTEST_DIR DUMPFILE=userCtest.dmp REMAP_SCHEMA=emuser:emuser1

权限，查看授权，生成授权语句

--查看用户拥有的权限
SELECT * FROM dba_tab_privs t WHERE table_name='table1' and t.grantee = 'user1';

-- 授权用户A的所有表给用户B，生成授权语句
select 'grant select on DW_STAGE.'||object_name||' to dw_all;' from dba_objects where owner='DW_STAGE' and object_type='TABLE';

--查看表被授予了谁（不显示select any table的用户，只有明确grant select的用户）
select * from (select ue.name grantee, u.name owner, o.name table_name, ur.name grantor, tpm.name privilege,
       decode(mod(oa.option$,2), 1, 'YES', 'NO') grantable,
       decode(bitand(oa.option$,2), 2, 'YES', 'NO') hierarchy
from sys.objauth$ oa, sys."_CURRENT_EDITION_OBJ" o, sys.user$ u, sys.user$ ur,
     sys.user$ ue, table_privilege_map tpm
where oa.obj# = o.obj#
  and oa.grantor# = ur.user#
  and oa.grantee# = ue.user#
  and u.user# = o.owner#
  and oa.col# is null
  and oa.privilege# = tpm.privilege)
where table_name='TTMP_H_GZB_SHZCTG';

dblink

select * from dba_db_links;

create public database link PTEST connect to apps  identified by apps
　　 using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = wind)))';

create database link gjods
       connect to username111 identified by passwd111 
       using '(DESCRIPTION =
                (ADDRESS_LIST =
                  (ADDRESS = (PROTOCOL = TCP)(HOST = LOCALHOST)(PORT = 1521))
                )
                (CONNECT_DATA =
                  (SERVICE_NAME = ods)
                )
              )';

DROP DATABASE LINK GJODS;

查看、修改、搜索表，创建同义词

--创建同义词
CREATE OR REPLACE SYNONYM QRY_ODS.TDATADICT FOR S_GJTG.TDATADICT;

--修改表名(注意新表名前不加模式名)
alter table dw_stage.t1 rename to t2;

--查看创建时间等表信息
select * from dba_objects where object_name = 'table1';

--查看所有表、用户可见的所有表、当前用户拥有的表
select * from dba_objects;
select * from all_objects;
select * from user_objects;

--如果存在则删除
declare
  num number;
begin
  select count(1) into num from user_tables where table_name = upper('sys_area') ;
  if num > 0 then
    execute immediate 'drop table sys_area' ;
  end if;
end;

创建视图，查看视图元数据

--创建视图
create or replace view scott.v_salgrade
as 
select grade,count(*) cnt from scott.salgrade2 group by grade;

--查询视图的建表语句、字段类型结构
select owner,view_name,text from all_views where owner=upper('bd_gjkm') and view_name=upper('Vw_Yjs');
SELECT * FROM all_tab_cols WHERE owner=upper('bd_gjkm') and table_name =upper('Vw_Yjs');

--（废弃）根据视图生成物理表
SELECT 'create table '||o_name||'.'||t_name||'('||cols||')'from 
(select max(owner) o_name,max(table_name) t_name,listagg(col,','||chr(13)||chr(10)) within group ( order by column_id) cols 
FROM 
(select owner,table_name,column_id,column_name||' '||data_type||
(case 
  when data_type='VARCHAR2' then '('||data_length||')'
  when data_type='NUMBER'and data_precision is not null  then '('||data_precision||','||data_scale||')'
  end) col
from all_tab_cols 
WHERE owner=upper('bd_gjkm') and table_name =upper('Vw_Yjs_Rpt_List_Newkm') 
) s1
) s2;

--根据多个视图生成多个物理表建表语句
SELECT 'create table '||o_name||'.'||t_name||'('||cols||')'from 
(select owner o_name,table_name t_name,listagg(replace(col,'VARCHAR2(0)','VARCHAR2(4000)'),','||chr(13)||chr(10)) within group ( order by column_id) cols 
FROM 
(select owner,table_name,column_id,column_name||' '||data_type||
(case 
  when data_type='VARCHAR2' then '('||data_length||')'
  when data_type='NVARCHAR2' then '('||data_length||')'
  when data_type='CHAR' then '('||data_length||')'
  when data_type='NUMBER'and data_precision is not null  then '('||data_precision||','||data_scale||')'
  end) col
from all_tab_cols 
WHERE owner=upper('xir_trd') and table_name in (upper('VTRD_BAL_YSP_SWP_IR'),upper('VTRD_BAL_SWP_IR'))
) s1
group by owner,table_name
) s2;

--根据多个物理表生成建表语句
SELECT 'create table '||o_name||'.'||t_name||'('||cols||');'from 
(select owner o_name,table_name t_name,listagg(replace(col,'VARCHAR2(0)','VARCHAR2(4000)'),','||chr(13)||chr(10)) within group ( order by column_id) cols 
FROM 
(select owner,table_name,column_id,column_name||' '||data_type||
(case 
  when data_type='VARCHAR2' then '('||data_length||')'
  when data_type='NVARCHAR2' then '('||data_length||')'
  when data_type='CHAR' then '('||data_length||')'
  when data_type='NUMBER'and data_precision is not null  then '('||data_precision||','||data_scale||')'
  when data_type='NUMBER' and data_precision is null and data_scale is not null  then '('||data_length||','||data_scale||')'
  when data_type='NUMBER' and data_precision is null and data_scale is null then ''
  end) col
from all_tab_cols 
WHERE owner||'.'||table_name in ('.....','....')
) s1
group by owner,table_name
) s2;

索引、主键约束

添加索引：create index key1 on table_name(col_name);
删除索引：drop index 索引名;
添加唯一主键约束：alter table thqlog add CONSTRAINT PK_ID PRIMARY KEY(id);
删除唯一主键约束：alter table thqlog drop CONSTRAINT PK_ID cascade drop index;
禁用索引：alter index PK_THQLOG unusable;
取消禁用索引：alter index PK_THQLOG rebuild;

列操作

字段的增加（一列/多列）、修改类型、改名、删除
alter table scott.T add v varchar2(10);
alter table scott.T add(col1 varchar2(10),col2 varchar2(10));
alter table scott.T modify v date;
alter table scott.T rename column v to g;
alter table scott.T drop column g;

字段注释
comment on column scott.t1.remark is '备注';

查询时间、系统信息等

-- 查看时间，精确到微秒
select to_char(systimestamp, 'yyyymmdd hh24:mi:ss.ff')from sys.dual;
-- 精确到秒
select to_char(sysdate-2, 'yyyymmdd hh24:mi:ss')from sys.dual;
-- 前一天日期，同时截断时分秒（00:00:00）（date类型）
select trunc(sysdate-1) from dual;
-- 字符串转日期
select to_date('2019-08-18','yyyy-mm-dd') from sys.dual;

--数据库版本
select * from v$version;

--查询表记录最后更新时间（使用oracle自带的伪列ora_rowscn）
select to_char(scn_to_timestamp(ora_rowscn),'yyyy-mm-dd hh24:mi:ss') 
from jg.ACC_MAST
where file_date='20200323'
--where rownum =1
--ora_rowscn时间为数据block的更新时间，即若干行记录都同时变（ora_rowscn分为两种模式:一种是基于block这是默认的模式(块级跟踪,非行依赖性(NOROWDEPENDENCIES))；一种是基于row上，这种模式只能在建里表时指定ROWDEPENDENCIES(行级跟踪)。）；记录更新时间超过5天则查询会报错（ORA_ROWSCN的存在依赖select min(scn) from sys.smon_scn_time ，而SMON_SCN_TIME只记录5天的数据。）

会话：kill、锁表、未提交事务锁表、修改schema

--kill锁定的会话
select session_id from v$locked_object;
-- 56
SELECT sid, serial#, username, osuser FROM v$session where sid =56;

--查看锁表语句
SELECT b.sid oracleID,b.username,sql_text,b.machine 计算机名称
FROM v$process a, v$session b, v$sqlarea c  
WHERE a.addr = b.paddr  
AND b.sql_hash_value = c.hash_value 
and sid in(56,1061);

--查看未提交事务
SELECT A.SID,A.SERIAL#,A.USERNAME,A.EVENT,A.WAIT_CLASS,A.SECONDS_IN_WAIT,A.PREV_EXEC_START,b.LOCKED_MODE,C.OWNER,C.OBJECT_NAME,C.OBJECT_TYPE
FROM V$SESSION A
INNER JOIN V$LOCKED_OBJECT B
ON A.SID=b.SESSION_ID
INNER JOIN DBA_OBJECTS C
ON B.OBJECT_ID=c.OBJECT_ID
WHERE A.WAIT_CLASS='Idle'
AND A.SECONDS_IN_WAIT>10;

-- 56,2088,ghb,fy
ALTER SYSTEM KILL SESSION '56,2088';

--修改当前会话的默认schema
alter session set current_schema=TRADE;

--一站式解决方案
SELECT sid, serial#,osuser,username,s3.object_name,s4.SQL_TEXT,
'ALTER SYSTEM KILL SESSION '''||sid||','||serial#||''';' kill_sql
FROM v$session  s1
join v$locked_object s2
on s1.sid=s2.SESSION_ID
join all_objects s3
on s2.OBJECT_ID=s3.OBJECT_ID
join v$sqlarea s4
on s1.sql_hash_value =s4.hash_value

编译视图、存储过程、包

ALTER procedure ods_info.sp_ashasage COMPILE;
ALTER PACKAGE my_package COMPILE;
ALTER PACKAGE my_package COMPILE BODY;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER VIEW my_view COMPILE;

查看编译错误
select * from all_errors;

查找存在特定字符的存储过程

select * from dba_source where owner ='dw_ods' and text like'%ELIGTESTJOUR%'

查看执行计划

explain plan FOR select * from table1;

select plan_table_output from TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));
先执行第一句，再执行第二句，即可查看执行计划

SQL语句块示例

declare 
-- 定义为普通类型
c number(19,0);
-- 定义为字段的类型
i ods_idcinfo.etl_logs.log_id%type;
-- 定义为行类型
r ods_idcinfo.etl_logs%rowtype;
begin
  select count(1) into c from ods_idcinfo.etl_logs;
  select max(log_id) into i from ods_idcinfo.etl_logs;
-- 行类型必须为1行
  select * into r from ods_idcinfo.etl_logs where log_id=i and rownum=1;
-- 行类型r的使用示例
  dbms_output.put_line(c||' '||i||' '||' '||r.table_name||' '||r.comments);
end;

序列、自增列、为现有表增加自增主键

-- 创建序列
create sequence sq_items
increment by 1
-- 始值
start with 49 
-- 没有最大值
nomaxvalue 
-- 不循环
nocycle; 

-- 使用序列实现自增
insert into T values(sq_items.nextval);

--删除序列
drop sequence sq_items;

--为现有表增加自增主键
alter table t1 add(id int);
update t1 set id=rownum;
alter table t1 add constraint pk_test primary key(id);
CREATE SEQUENCE seq1 INCREMENT BY 1 START WITH 1 MINVALUE 1 NOMAXVALUE NOCYCLE;
CREATE OR REPLACE TRIGGER trigger1
BEFORE INSERT ON t1
FOR EACH ROW
BEGIN
SELECT seq1.nextval INTO:new.id FROM dual;
END;

命令行导出建表语句

sqlplus user/passwd@ip:port/service_name
SET SERVEROUTPUT ON 
SET LINESIZE 1000 
SET FEEDBACK OFF 
SET LONG 99999           
SET PAGESIZE 0  
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',FALSE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'TABLESPACE',FALSE);
EXEC DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES',FALSE);

SELECT DBMS_METADATA.GET_DDL(O.OBJECT_TYPE, O.OBJECT_NAME,O.OWNER) FROM ALL_OBJECTS O
WHERE O.OBJECT_TYPE IN ('TABLE') AND OWNER = 'HSFA' AND OBJECT_NAME='QZXX';

SPOOL中间输入的SQL语句和输出结果会打印到指定文件中
SPOOL C:\Users\xingzhiqiang\Desktop\1.txt
......
SPOOL OFF

特殊语法

-- merge into
merge into 目标表 a
using 源表 b
on(a.条件字段1=b.条件字段1 and a.条件字段2=b.条件字段2 ……)  
when matched then update set a.更新字段=b.字段
when not matched then insert into a(字段1,字段2……)values(值1,值2……)

--列转行
id	a	b	c	d
1	2	3	4	5
-->
id	x	y
1	a	2
1	b	3
1	c	4
1	d	5

select id,x,y 
from table_name
unpivot(y for x in(a,b,c,d));

--拆分一个字符串转多行
select regexp_substr('a,b,c', '[^,]+', 1, level, 'i') 
from dual   
connect by level<=length('a,b,c')-length(regexp_replace('a,b,c', ',',''))+1

--一行变多行，多行变一行
create table new_admin.t1 as
select 'a' a,'a,b,c' b from dual union all
select 'b' a,'1,2,3' b from dual;
-->
a b
a a,b,c
b 1,2,3

create table new_admin.t2 as
select t1.a,regexp_substr(t1.b, '[^,]+', 1, level) b from new_admin.t1
connect by prior rowid = rowid 
and prior dbms_random.value is not null 
and level <= regexp_count(t1.b, '[^,]+');
-->
a b
a a
a b
...
b 2
b 3

select a,listagg(b,',') within group(order by b desc) 
from new_admin.t2
group by a
-->
a b
a c,b,a
b 3,2,1
--另一个版本，解决listagg超长问题，a为分组字段，b为待聚合字段，分隔符可以在||后面指定，rtrim是去除最后一个分隔符，order by是聚合顺序，可以是其他字段
select a,rtrim(xmlagg(xmlparse(content b||',' wellformed) order by b).getclobval() ,',')
from new_admin.t2
group by a

窗口函数取分组排名最新记录（类似于rank）
select * from(
select t.*,
row_number() over(partition by company_id order by updt_dt desc) as rn
from new_admin.ods_PFCOMPY_BASICINFO t)
where rn=1

正则表达式

--正则匹配，只保留字符串中的数字
select regexp_replace('23  456中国3-0 0=.,45','[^0-9]') from dual;
'2345630045'
--只保留数字和空格
select regexp_replace('23  456中国3-0 0=.,45','[^0-9[:blank:]]') from dual;
'23  45630 045'
--非数字非逗号的部分都改为逗号
select regexp_replace('3671,6\7195、135862','[^0-9,]',',')from dual;
'3671,6,7195,135862'

正则表达式匹配特殊字符，使用escape '\'后，'\'后一个字符'_'将被视为普通字符，匹配后缀为_HK的对象
select * from all_objects where object_name like '%\_HK'  escape '\'

树形部门架构

--树形组织架构寻找所有子节点
depid      parent_id
001        001
001002     001
001003     001
002        002
002001     002
001002001  001002
001002002  001002

select * from department start with depid='001002' connect by nocycle prior depid=parent_id;
-->
depid      parent_id
001002     001
001002001  001002
001002002  001002

--树形组织显示上级部门架构，多于三级的架构以此类推，先合并再忽略null拆分列，达到调整数值位置的目的
with t as(
select s1.depid,s1.depcname,s3.depid id1,s2.depid id2,s1.depid id3  
from department s1
left join department s2
on s1.parent_id=s2.depid and s1.depid!=s2.depid
left join department s3
on s2.parent_id=s3.depid and s2.depid!=s3.depid
)
select t.depid,t.depcname,
regexp_substr(id1||','||id2||','||id3,'[0-9A-Z]+',1,1) id1,
regexp_substr(id1||','||id2||','||id3,'[0-9A-Z]+',1,2) id2,
regexp_substr(id1||','||id2||','||id3,'[0-9A-Z]+',1,3) id3
from t 
order by t.depid;
--上句regexp_substr中也可以使用'[^,]+'来匹配非逗号的部分
-->
depid      一级部门  二级部门 三级部门
001        001      
001002     001      001002
001002001  001      001002   001002001
001003     001      001003
002        002
002001     002      002001

其他笔记：

导入excel数据到oracle：需要在oracle中建表，PL/SQL中tool-odbc importer-excel file，设置from、to之后import即可

特别注意：create or replace 不影响原有授权，drop再重建会使原用户失去权限！！生产环境需要特别注意drop后的权限问题！！！

已上线系统表切勿进行drop操作！！增加字段使用alter而不是重建，为了防止丢失授权问题！

instantclient安装

下载instantclient-basic-linux.x64-12.2.0.1.0.zip、instantclient-sqlplus-linux.x64-12.2.0.1.0.zip，解压到同一个文件夹，在该文件夹下touch tnsnames.ora填上内容

orcl =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1 )(PORT = 1521))
    )
   (CONNECT_DATA =
      (SERVICE_NAME = orcl )
    )
 )

修改/etc/profile并source即可

export PATH=$PATH:/oracle/instantclient_12_2
export LD_LIBRARY_PATH=/oracle/instantclient_12_2       
export ORACLE_HOME=/oracle/instantclient_12_2
export TNS_ADMIN=/oracle/instantclient_12_2