创建存储过程
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
评论区