Oracle

创建存储过程

 create or replace procedure proc_add_test as
begin
  insert into a8 values (to_char(sysdate, 'yyyy-mm-dd hh:mi'));/*向测试表插入数据*/
  commit;
end;

调用存储过程

declare num_C integer;
begin
   --调用存储过程---
   CLEAR_TABLE();
end;

创建job定时任务 实现自动调用存储过程

declare
  job number;
BEGIN
  DBMS_JOB.SUBMIT(  
        JOB => job,  /*自动生成JOB_ID*/  
        WHAT => 'proc_add_test;',  /*需要执行的存储过程名称或SQL语句*/  
        NEXT_DATE => sysdate+3/(24*60),  /*初次执行时间-下一个3分钟*/  
        INTERVAL => 'trunc(sysdate,''mi'')+1/(24*60)' /*每隔1分钟执行一次*/
      );  
  commit;
end;

查询存储过程

SELECT * FROM USER_SOURCE WHERE NAME = UPPER('proc_add_TESTW1')

查询系统表查看该job信息

select * from user_jobs;

手动调用job

begin
   DBMS_JOB.RUN(40); /*40 job的id*/
 end;

begin
  /*删除自动执行的job*/
  dbms_job.remove(40);
end;

sqlplus 远程连接

sqlplus username/password@//host:port/sid

定时任务不跑的时候查看
SQL> show  parameter  job_queue_process;
若是为0 
可
alter system set job_queue_processes = 10
就可以了

指定时间执行一次 
declare
  job number;
BEGIN
  DBMS_JOB.SUBMIT(  
        JOB => job,  /*自动生成JOB_ID*/  
        WHAT => 'proc_add_TESTW1;',  /*需要执行的存储过程名称或SQL语句*/  
        NEXT_DATE => to_date('2021-04-28 14:22:00','yyyy-mm-dd hh24:mi:ss') 

      );  
  commit;
end;

插入

方式1、 INSERT INTO t1(field1,field2) VALUE(v001,v002);            // 明确只插入一条Value

 

方式2、 INSERT INTO t1(field1,field2) VALUES(v101,v102),(v201,v202),(v301,v302),(v401,v402);

在插入批量数据时方式2优于方式1.

 

方式3.1、  INSERT INTO t2(field1,field2) SELECT col1,col2 FROM t1 WHERE ……

分组  分隔符自定义相较于wm_concat
SELECT  listagg (RYMC, ' ') WITHIN GROUP (ORDER BY RYMC) AS RYMC FROM (
SELECT DISTINCT RYMC FROM ESTATE_QL_DLR WHERE YWH IN ('${YWH}','${YWH}') AND RYFL = '1'
UNION
SELECT DISTINCT RYMC FROM ESTATE_QL_DLR WHERE YWH IN ('${YWH}','${YWH}') AND RYFL = '3'
))

GRANT SELECT ON ESTATE.ESTATE_QLXX TO ESTATEOUT; --赋予查询表权限给另一个用户

查询表字段

SELECT column_name || ',' FROM user_tab_columns where table_name = upper('ESTATE_QL_FDCQ1') 

查询表详情

SELECT
	ut.COLUMN_NAME,--字段名称
	uc.comments,--字段注释
	ut.DATA_TYPE,--字典类型
	ut.DATA_LENGTH,--字典长度
	ut.NULLABLE --是否为空
	
FROM
	user_tab_columns ut
	INNER JOIN user_col_comments uc ON ut.TABLE_NAME = uc.table_name 
	AND ut.COLUMN_NAME = uc.column_name 
WHERE
	ut.Table_Name = 'RC_METADATA' 
ORDER BY
	ut.column_name

给字段加注释

comment on column TESTW1.YWH is 'This is comment';

替换表字段顺序

select object_id from all_objects where owner = 'WF' and object_name = 'ACT_PROCESS_REVISION'; --查询表id
select obj#, col#, name from sys.col$ where obj# = 97195 order by col#;    --查询表字段id
update col$ set col#=col#+1 where obj#=97195 and col#>=2;--给要换位置的字段之后的字段（包括自己）位置+1
update col$ set col#=2 where obj#=97195 and name = 'PROCESS_DEFINITION_KEY';--替换字段为原来字段
alter system flush shared_pool;
alter system flush BUFFER_CACHE;
alter system checkpoint;

查询是否锁表

SELECT
    object_name,
    machine,
    s.sid,
    s.serial# 
FROM
    gv$locked_object l,
    dba_objects o,
    gv$session s 
WHERE
    l.object_id　 = o.object_id 
    AND l.session_id = s.sid

释放被锁的表

alter system kill session 'sid, serial#'

1.使用sysdba账号 登陆后 可以修改其他账号密码           

运行 cmd 按如下输入命令 sqlplus / as sysdba ---------以sys登陆 超级用户（sysdba）

2.解除锁定账号

alter user 用户名 account unlock; --------- 解除锁定(必须带“;”号)

以system用户名为例，即命令为alter user system account unlock;

3.为该账户设置新密码

alter user 用户名 identified by 密码; -------------修改密码（密码加不加双引号均可，必须带“;”号）

以用户名system密码dhee为例，即命令为alter user system identified by system;

然后用你改好的密码登陆。


CONN SYSTEM/system AS SYSDBA;





Oracle创建用户、角色、授权、建表  
oracle数据库的权限系统分为系统权限与对象权限。系统权限( database system privilege )可以让用户执行特定的命令集。例如，create table权限允许用户创建表，grant any privilege 权限允许用户授予任何系统权限。对象权限( database object privilege )可以让用户能够对各个对象进行某些操作。例如delete权限允许用户删除表或视图的行，select权限允许用户通过select从表、视图、序列(sequences)或快照(snapshots)中查询信息。

每个oracle用户都有一个名字和口令,并拥有一些由其创建的表、视图和其他资源。oracle角色(role)就是一组权限(privilege)(或者是每个用户根据其状态和条件所需的访问类型)。用户可以给角色授予或赋予指定的权限，然后将角色赋给相应的用户。一个用户也可以直接给其他用户授权。


一、创建用户

oracle内部有两个建好的用户：system和sys。用户可直接登录到system用户以创建其他用户，因为system具有创建别 的用户的 权限。 在安装oracle时，用户或系统管理员首先可以为自己建立一个用户。

语法[创建用户]： create user 用户名 identified by 口令[即密码]；

例子： create user test identified by test;

语法[更改用户]: alter user 用户名 identified by 口令[改变的口令];

例子： alter user test identified by 123456;


二、删除用户

语法：drop user 用户名;

例子：drop user test;

若用户拥有对象，则不能直接删除，否则将返回一个错误值。指定关键字cascade,可删除用户所有的对象，然后再删除用户。

语法： drop user 用户名 cascade;

例子： drop user test cascade;

 

三、授权角色

oracle为兼容以前版本，提供三种标准角色（role）:connect/resource和dba.

（1）讲解三种标准角色：

1》. connect role(连接角色)

--临时用户，特指不需要建表的用户，通常只赋予他们connect role. 

--connect是使用oracle简单权限，这种权限只对其他用户的表有访问权限，包括select/insert/update和delete等。

--拥有connect role 的用户还能够创建表、视图、序列（sequence）、簇（cluster）、同义词(synonym)、回话（session）和其他  数据的链（link）

 

2》. resource role(资源角色)

--更可靠和正式的数据库用户可以授予resource role。

--resource提供给用户另外的权限以创建他们自己的表、序列、过程(procedure)、触发器(trigger)、索引(index)和簇(cluster)。

 

3》. dba role(数据库管理员角色)

--dba role拥有所有的系统权限

--包括无限制的空间限额和给其他用户授予各种权限的能力。system由dba用户拥有

 

（2）授权命令

语法： grant connect, resource to 用户名;

 例子： grant connect, resource to test;

   （3）撤销权限

      语法： revoke connect, resource from 用户名;

      列子： revoke connect, resource from test;

 

四、创建/授权/删除角色

除了前面讲到的三种系统角色----connect、resource和dba，用户还可以在oracle创建自己的role。用户创建的role可以由表或系统权限或两者的组合构成。为了创建role，用户必须具有create role系统权限。

1》创建角色

语法： create role 角色名;

例子： create role testRole;

2》授权角色

语法： grant select on class to 角色名;

列子： grant select on class to testRole;

注：现在，拥有testRole角色的所有用户都具有对class表的select查询权限

3》删除角色

语法： drop role 角色名;

例子： drop role testRole;

注：与testRole角色相关的权限将从数据库全部删除

并行建立索引

create index INX_REL2_ID on WF.T_WF_BIZ_FILE_URL_REL2(ID) parallel 12;
//将索引改回非并行
alter index INX_REL2_ID noparallel;
//查询索引的线程  1是正常
select s.degree
      from dba_indexes s
     where s.index_name = upper('INX_REL2_ID');

查询各表空间使用详情

SELECT a.tablespace_name "表空间名称", 
total / (1024 * 1024) "表空间大小(M)", 
free / (1024 * 1024) "表空间剩余大小(M)", 
(total - free) / (1024 * 1024 ) "表空间使用大小(M)", 
total / (1024 * 1024 * 1024) "表空间大小(G)", 
free / (1024 * 1024 * 1024) "表空间剩余大小(G)", 
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)", 
round((total - free) / total, 4) * 100 "使用率 %" ,
b.filenamecount "表空间数量",
b.file_name "表空间路径"
FROM (SELECT tablespace_name, SUM(bytes) free FROM dba_free_space GROUP BY tablespace_name) a, 
(SELECT tablespace_name, SUM(bytes) total ,count(0) filenamecount,listagg(file_name,',')within group (order by file_name) file_name FROM dba_data_files GROUP BY tablespace_name) b 
WHERE a.tablespace_name = b.tablespace_name;

查看临时表空间

select c.tablespace_name,
to_char(c.bytes/1024/1024/1024,'99,999.999') total_gb,
to_char( (c.bytes-d.bytes_used)/1024/1024/1024,'99,999.999') free_gb,
to_char(d.bytes_used/1024/1024/1024,'99,999.999') use_gb,
to_char(d.bytes_used*100/c.bytes,'99.99') || '%'use,
c.file_name  "表空间位置",
c.autoextensible "是否自动增长"
from  (select tablespace_name,sum(bytes) bytes,listagg(file_name,',')within group (order by file_name) file_name,max(autoextensible) autoextensible
from dba_temp_files GROUP by tablespace_name) c left join 
(select tablespace_name,sum(bytes_cached) bytes_used
from v$temp_extent_pool GROUP by tablespace_name) d
on  c.tablespace_name = d.tablespace_name;

创建临时表空间

create temporary tablespace tempwf tempfile '/oracle_data/tablespace/tempwf01.dbf' size 30000M autoextend on;

添加临时表空间大小

alter tablespace tempestate add tempfile '/oracle_data/tablespace/tempestate04.dbf' size 10G autoextend on;

修改默认表空间为刚刚建立的临时表空间

alter database default temporary tablespace temp;

修改用户临时表空间

alter user wf temporary tablespace tempwf;

查看临时表空间与用户对应关系

select username,ACCOUNT_STATUS,DEFAULT_TABLESPACE,TEMPORARY_TABLESPACE from dba_users order by 3,1;

用户、表空间对应关系

select username,default_tablespace from dba_users;

用户绑定表空间

alter user FMS default tablespace FMS;

删除表空间

DROP TABLESPACE ESTATETEST2 INCLUDING CONTENTS AND DATAFILES;

修改当前表空间大小

ALTER DATABASE DATAFILE '/database/app/oracle/oradata/orcl/sysaux01.dbf' RESIZE 5G;

创建多份表空间（因为表空间最大是（大概）30G，所以要建多份）

create tablespace ESTATETEST datafile '/oracle_data/tablespace/ESTATETEST1.dbf' size 30720m autoextend on next 100M maxsize unlimited;
alter tablespace ESTATETEST add datafile '/oracle_data/tablespace/ESTATETEST2.dbf' size 30720m autoextend on next 100M maxsize unlimited;
alter tablespace wf add datafile '+DATA' size 30g autoextend off;

SELECT * FROM ALL_TABLES;--系统里有权限的表
SELECT * FROM DBA_TABLES ;-- 系统表
SELECT * FROM USER_TABLES;-- 当前用户下的表  包含系统表
SELECT * FROM ALL_TABLES WHERE OWNER = 'WF';--指定用户下的表   不包含系统表

create temporary tablespace TEMPESTATE tempfile '+DATA' size 30G autoextend on;
alter tablespace TEMPESTATE add tempfile '+DATA' size 30G autoextend on;
alter user ESTATE temporary tablespace TEMPESTATE;


create temporary tablespace TEMPDEFAULT tempfile '+DATA' size 30G autoextend on;
alter tablespace TEMPDEFAULT add tempfile '+DATA' size 30G autoextend on;
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPDEFAULT;

//查看谁使用了表空间
SELECT * FROM V$SORT_USAGE
//根据sqlid查询sql
select SQL_TEXT,SQL_FULLTEXT,SQL_ID,LOADS,FIRST_LOAD_TIME,PLSQL_EXEC_TIME,JAVA_EXEC_TIME,ROWS_PROCESSED,COMMAND_TYPE,PARSING_USER_ID,PARSING_SCHEMA_ID,PARSING_SCHEMA_NAME,KEPT_VERSIONS,ADDRESS,SERVICE,MODULE,MODULE_HASH,ACTION,CPU_TIME,ELAPSED_TIME,OUTLINE_SID,CHILD_ADDRESS,SQLTYPE,REMOTE,OBJECT_STATUS,LITERAL_HASH_VALUE,LAST_LOAD_TIME,PROGRAM_ID,PROGRAM_LINE#,LAST_ACTIVE_TIME 
    from  v$sql where sql_id='6tgpc63durqj7'
	
--查看默认临时表空间
select PROPERTY_NAME,PROPERTY_VALUE
   from DATABASE_PROPERTIES
   where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';
   
   
--查看asm模式的空间大小
select name,total_mb,free_mb from v$asm_diskgroup;


create table TABLENAME as(select )会很快


select SUBSTR( TO_CHAR((SELECT SYSDATE FROM dual),'yyyy-mm-dd hh24-mi-ss') , '8') from dual							 月份之后
select extract(year from (select  ADD_MONTHS( (SELECT SYSDATE FROM dual) , 12) from dual)) from dual     明年年份
look


select   TO_DATE(((select extract(year from (select  ADD_MONTHS( (SELECT SYSDATE FROM dual) , 12) from dual)) from dual) || '-' || '06' || (select SUBSTR( TO_CHAR((SELECT SYSDATE FROM dual),'yyyy-mm-dd hh24-mi-ss') , '8') from dual)), 'yyyy-mm-dd hh24-mi-ss') from dual

数据恢复上一次

select * from bdc_key as of timestamp to_timestamp('2019-8-28 9:25:50','yyyy-mm-dd hh24:mi:ss');

alter table bdc_key enable row movement;

flashback table bdc_key to timestamp TO_TIMESTAMP('2019-8-28 9:25:50','yyyy-mm-dd hh24:mi:ss');

ORACLE跨库查询

​

释放dblink
DROP PUBLIC DATABASE LINK WU_BDC【连接名】

查询所有的dblink
select * from dba_db_links;

创建dblink
CREATE PUBLIC database link WU_BDC【连接名称】 CONNECT TO "bdcpt"【用户名】 IDENTIFIED BY "bdcpt2hstw863k2"【密码】 using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = 192.18.8.157【IP】)(PORT = 1521【端口】)))(CONNECT_DATA =(SID = orcl【SID可通过：select instance_name from  V$instance查询】)))';

​
​ select * from bdcpt.BDC_KEY@WU_BDC;
​
​ select column_name from user_tab_columns@WU_BDC where table_name = 'BDC_KEYTEST';--------查字段，from表名后得加上@WU_BDC连接名称，又因为
​ user_tab_columns不是bdcpt用户下的表而是系统表，所以不用加bdcpt

同步两张表的字段

SELECT 'alter table LND_STORAGEDDOC1_TWO add ' ||  COLUMN_NAME || ' ' || DATA_TYPE || '(' || DATA_LENGTH || ')' FROM (
  SELECT
  COLUMN_NAME,DATA_TYPE,DATA_LENGTH

FROM
  user_tab_cols 
WHERE
  table_name = 'LND_STORAGEDDOC1'
  MINUS
    SELECT
  COLUMN_NAME,DATA_TYPE,DATA_LENGTH

FROM
  user_tab_cols 
WHERE
  table_name = 'LND_STORAGEDDOC1_TWO')

update SYS_USER set key =
        (
        <foreach collection="list" item="item" open="case" close="else key end">
            <foreach collection="item.entrySet()" index="key" item="value">
                when id = #{key} then #{value}
            </foreach>
        </foreach>
        )

dbms_lob.substr(f.LJZH,1000)

查询序列
select SEQUENCE_OWNER,SEQUENCE_NAME from dba_sequences where sequence_owner='FMS'; 
删除序列
DROP SEQUENCE '序列名'; 

MERGE使用

MERGE INTO ESTATE_TD_ZDJBXX A 
 USING 
 (
SELECT VALUE,LABEL FROM UUMS.UUMS_DICT WHERE TYPE = '土地用途'
 ) B 
 ON (A.YT=B.VALUE)
WHEN MATCHED THEN
	UPDATE SET A.YTMC = B.LABEL

字段日期缺省值

CURRENT_TIMESTAMP

ROW_NUMBER() OVER()函数

SELECT * FROM (
SELECT
  ROW_NUMBER () OVER ( PARTITION BY FLOW.BIZ_KEY ORDER BY FLOW.START_TIME DESC ) RN,
  FLOW.* 
FROM
  T_BIZ_FLOW_INFO_HISTORY FLOW)
WHERE
  RN = 1