常用SQL

—EXP导出用户下的表不全的问题
select table_name from user_tables where NUM_ROWS=0;
—执行结果集中的SQL
select ‘alter table ‘||table_name||’ allocate extent;’ from user_tables where
num_rows=0;
— 导出dmp文件
owner 用户 full=y 全部导出 不能同时存在
exp cds/cds@127.0.0.1:1521/cds file=D:\20190927.dmp owner=sonic full=y
exp security/security@180.167.172.122:15001/prison file=D:\af.dmp owner=security
— 导入dmp文件
imp security/security998@127.0.0.1:1521/prison file=D:\af.dmp full=y ignore=y
— 查询数据库当前的连接对象
select from gvlocked_object t;
select from vlocked_object t2 where t1.sid = t2.SESSION_ID;
— 查询锁表SQL
select from gvlocked_object f);
— 解锁表
set serveroutput on
execute sys.auto_server_pkg.unlock_table(‘用户名’,’表名’);
alter system kill session ‘1155,39095’;
— 解锁包
set serveroutput on
execute sys.auto_server_pkg.unlock_package(‘用户名’,’表名’);
— 杀死session
set serveroutput on
execute sys.auto_server_pkg.kill_session(‘SERIAL#’,’SID’);
— 解锁用户alter user PRISON_JGWH account unlock;
— 查询表主键
select cu.
from user_cons_columns cu, user_constraints au
where cu.constraint_name = au.constraint_name
and au.constraint_type = ‘P’
and cu.table_name = ‘’;
— 查询表索引
select
from user_ind_columns t, user_indexes i
where t.INDEX_NAME = i.index_name
and t.TABLE_NAME = ‘’
— 查询视图
select from user_views t where t.VIEW_NAME=’’;
— 查询函数
select
from user_objects t
where t.OBJECT_TYPE = ‘FUNCTION’
and t.OBJECT_NAME = ‘’;
— 查询存储过程创建SQL
select t.TEXT from user_source t where t.name = ‘存储过程名称’;
— 查询授权用户权限
select from user_tab_privs t where t.grantee=’’ and t.table_name=’’;
— 查询表字段
select tc.TABLE_NAME,
tc.COLUMN_NAME,
cc.comments,
tc.DATA_TYPE || ‘(‘ || tc.DATA_LENGTH || ‘)’ length,
tc.NULLABLE
from user_tab_columns tc, user_col_comments cc
where tc.TABLE_NAME = cc.table_name
and tc.COLUMN_NAME = cc.column_name
and tc.TABLE_NAME = ‘COM_DVC_GRP_MASTER’
— 查询锁表线程
select s.INST_ID,o.OWNER,
s.USERNAME,
o.OBJECT_NAME,
s.SID,
s.SERIAL# SERIAL,
s.SECONDS_IN_WAIT,
s.SQL_ID,
s.EVENT,
s.SQL_ADDRESS,
s.SQL_HASH_VALUE,
o.SUBOBJECT_NAME,
s.MACHINE,
s.OSUSER
from gvsession s
where l.OBJECT_ID = o.OBJECT_ID
and l.SESSION_ID = s.SID
and l.INST_ID = s.INST_ID
and o.OWNER = ‘PRISON_WHJDS’
— 行转列
select first_type_code,
max(case level_code
when ‘T1’ then
total
else
0
end) tier1Total,
max(case level_code
when ‘T2’ then
total
else
0
end) tier2Total,
max(case level_code
when ‘T3’ then
totalelse
0
end) tier3Total
from (select first_type_code, level_code, count(1) total
from PL_PR_basic_info_t
group by first_type_code, level_code)
— 统计汇总
with t as
(select 1 a, 2 b, 5 c, 4 d
from dual
union all
select 1 a, 2 b, 3 c, 4 d
from dual
union all
select 2 a, 3 b, 3 c, 4 d
from dual
union all
select 2 a, 3 b, 3 c, 4 d
from dual)
select decode(a, null, ‘TOTAL’, a) a, sum(b), sum(c), sum(d)
from t
group by rollup(a)
— 代码块批量处理样例
declare
vi_num integer := 0; —临时变量用于事务提交
begin
for obj in (select project_code, source_system, count(1) ct
from dual
group by project_code) loop
— 业务逻辑
vi_num := vi_num + 1;
if vi_num > 200 then
commit;
vi_num := 0;end if;
end loop;
commit;
end;
/
— 根据主键循环批量删除
declare
vi_min number(15) := 100; —获取最小记录行
vi_max number(15) := 100000; —获取最大记录行
vi_count integer := 150000; —每次要删除的行数
vi_temp integer := 0; — 中间变量
begin
while vi_temp <= vi_max loop
vi_temp := vi_min + vi_count;
— 删除业务代码
delete dual
where id >= vi_min
and id < vi_temp;
commit;
vi_min := vi_temp;
end loop;
delete dual where id <= vi_max;
commit;
end;
/
— 字符串拆分
select regexp_substr(‘310201014,310201005,’, ‘[^,]+’, 1, rownum)
from dual
connect by rownum <= length(‘310201014,310201005’) -
length(replace(‘310201014,310201005’, ‘,’)) + 1
— 自定义类型
CREATE OR REPLACE TYPE TYPE_SPLIT_TABLE IS TABLE OF VARCHAR(4000);create or replace function f_split(as_clob clob, delimiter varchar2) return
type_split_table
pipelined is
ls_str1 varchar2(8000);
ln_cnt number(8);
ls_str2 varchar2(2000);
x number(8);
y number(8);
begin
— 算一下要循环几次
ln_cnt := ceil(length(as_clob) / 4000);
— 开始循环获取
for i in 1 .. ln_cnt loop
— 取出4000个字符
ls_str1 := to_char(substr(as_clob, (4000 (i - 1)) + 1, 4000));
— 定位到1
x := 1;
y := 1;
— 开始循环截取
loop
— 定位分隔符
x := instr(ls_str1, delimiter, y);
— 如果存在分隔符，那就从现在的位置截取到分隔符前一位
if x > 0 then
— 截取并去空格
ls_str2 := ls_str2 || trim(substr(ls_str1, y, x - y));
— 如果长度大于0，那就是有效的字符串
if ls_str2 is not null then
— 取出的单条字符串插入集合，并重置ls_str2为空
pipe row(ls_str2);
ls_str2 := ‘’;
end if;
— 查找开始位置往当前定位后挪1位
y := x + 1;
else— 当前位置往后面不存在分隔符的话，取出后面的字符串，退出本次循环
ls_str2 := trim(substr(ls_str1, y, 4000 - y + 1));
— dbms_output.put_line(‘后面没分隔符了，取出：’ || ls_str2);
exit;
end if;
end loop;
end loop;
— 最后的剩余部分处理
if ls_str2 is not null then
pipe row(ls_str2);
end if;
return;
end f_split;
— 创建命名空间
create tablespace PRISON_DATA_YN
datafile ‘D:\app\tablespace\prison_data_yn.dbf’ size 2048M autoextend
on next 256M maxsize unlimited;
— 创建命名空间对应的用户
create user prison_yn
default tablespace PRISON_DATA_YN
quota unlimited on PRISON_DATA_YN
identified by “prison_yn”;
— 用户授权
grant connect to prison_yn;
grant resource to prison_yn;
grant create synonym to prison_yn;
grant create view to prison_yn;
grant create database link to prison_yn;
grant debug any procedure to prison_yn;
grant debug connect session to prison_yn;
grant unlimited tablespace to prison_yn;
grant dba to prison_yn;
— 设置用户密码永不过期ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
— mvn安装jar命令
mvn install:install-file -DgroupId=com.oracle -DartifactId=ojdbc6 -
Dversion=11.2.0.1.0 -Dpackaging=jar -
Dfile=D:\WorkSoftware\lib\com\oracle\ojdbc6\11.2.0.1.0\ojdbc6-11.2.0.1.0.jar
— 查询oracle快照
SELECT * FROM AF_PERSON_BASE_T—你操作的那张表
AS OF TIMESTAMP to_timestamp(‘2020-06-10 20:30:00’,’yyyy-mm-dd hh24:mi:ss’);
— 回滚表数据
alter table AF_ALARM_RECORD_T
enable row movement;
flashback table AF_ALARM_RECORD_T
to timestamp to_timestamp(‘2020-06-11 15:00:00’,’yyyy-mm-dd HH24:MI:SS’);
—隐式游标赋权：
select ‘GRANT insert,select,update,delete ON A.’||object_name||’ to B;’ from
dba_objects where owner=’A’ and object_type=’TABLE’;
——————————————修改用户名和密码————————————————
—用sysdba账号登入数据库，然后查询到要更改的用户信息：
SELECT user#,name FROM user SET NAME=’PORTAL’ WHERE user#=88;
COMMIT;
—强制刷新：
ALTER SYSTEM CHECKPOINT;
ALTER SYSTEM FLUSH SHARED_POOL;
—更新用户的密码：
ALTER USER PORTAL IDENTIFIED BY 123;
—用户名密码不区分大小写(DBA执行)
alter system set sec_case_sensitive_logon=false;
数据中心产品密钥：
D2N9P-3P6X9-2R39C-7RTCD-MDVJX
硬盘分区
diskmgmt.msc显示桌面图标
rundll32.exe shell32.dll,Control_RunDLL desk.cpl,,0
查看锁表对象
select sess.sid,
sess.serial#,
lo.oracle_username,
lo.os_user_name,
ao.object_name,
lo.locked_mode
from vsession sess
where ao.object_id = lo.object_id and lo.session_id = sess.sid;
alter system kill session ‘24,1505’