Oracle的一些语句

最新推荐文章于 2024-11-02 12:29:24 发布

云平_Stephen

最新推荐文章于 2024-11-02 12:29:24 发布

阅读量245

点赞数 3

文章标签： oracle 数据库

本文链接：https://blog.csdn.net/qq_43303221/article/details/136857345

版权

用户相关

创建用户
create user test08 identified by test08;
 
--赋予DBA权限
grant connect,resource,dba to test08;

创建用户
create user test01 identified by 123456;

/赋予连接权限/
grant connect to test01;

/创建同义词权限/
grant create synonym to test01;

赋予某个表的权限
grant select on stephen.test02 to test01;

存储过程访问系统表需要赋权限
#sqlplus / as sysdba
grant select on  v_$process to test01;
grant select on  v_$session to test01;
grant select on  v_$sqlarea to test01;

查看表占用空间大小

select segment_name,sum(bytes)/1024/1024 Mbytese from user_segments where segment_type = 'TABLE'  group by segment_name  order by Mbytese DESC;
select segment_name,sum(bytes)/1024/1024 Mbytese from user_segments where segment_type = 'TABLE PARTITION'  group by segment_name  order by Mbytese DESC;

select segment_name,bytes/1024/1024  from user_segments where segment_type='TABLE PARTITION' and segment_name =upper('表名')order by bytes desc;

select max(bytes)/1024/1024 Mbytese from user_segments where segment_type = 'TABLE'

设置端口

alter system set local_listener='(address=(protocol=tcp)(host=10.45.186.155)(port=1521))';

创建dblink

drop  database link gwtest_155;

create database link gwtest_155
connect to stephen identified by stephen
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.45.186.155)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = stephen)
)
)';

sqluldr2导出工具

$sqluldr2(bin文件名) $username(数据库用户)/$password(密码)@$ipaddress:$port/$sid (ip端口及sid) query="${hkgc}"  (sql语句) field=0x2c head=no file=$expdir/datafile/$filename.txt  （文件绝对路径）charset=utf8 batch=yes rows=2000000

blob类型转换函数

CREATE OR REPLACE FUNCTION base64encode(p_blob IN BLOB)
RETURN CLOB
IS
l_clob CLOB;
l_step PLS_INTEGER := 12000;
BEGIN
FOR i IN 0 .. TRUNC((DBMS_LOB.getlength(p_blob) - 1 )/l_step) LOOP
l_clob := l_clob || UTL_RAW.cast_to_varchar2(UTL_ENCODE.base64_encode(DBMS_LOB.substr(p_blob, l_step, i * l_step + 1)));
END LOOP;
RETURN l_clob;
END;
/

查看会话及PID

查看正在执行的sql

SELECT b.sid oracleID,
	b.username,
	b.serial#,
	spid,
	paddr,
	sql_text,
	b.machine
FROM v$session b
LEFT JOIN v$process a ON a.addr = b.paddr
LEFT JOIN v$sqlarea c ON b.sql_hash_value = c.hash_value
WHERE sql_text IS NOT NULL;

根据上面查出来的sid去查出PID

SELECT spid, osuser, s.program FROM v$session s, v$process p WHERE s.paddr=p.addr AND s.sid=31750;

oracle的时间计算

select to_char(trunc(sysdate),'yyyyMMddhhmmss') from dual

select to_char(trunc(sysdate -2),'yyyyMMddhhmmss') from dual

select to_char(trunc(sysdate - 2/1440),'yyyyMMddhhmmss') from dual;

select to_date('2021-08-19 19:33:00','yyyy/MM/dd hh24:mi:ss') from dual;

select to_date('2021-08-19 19:33:00','yyyy/MM/dd hh24:mi:ss') from dual;

select to_date('2021-08-19 19:33:00') from dual;

select sysdate + 5/24/60 from dual;

创建定时任务job

create or replace procedure proc_gwtest 
is 
begin  
  UPDATE test set a = 1;
commit;
dbms_output.put_line('插入新纪录成功!');
end pro_update_imsi;

begin
 proc_gwtest;
 end;