普通用户连接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