基本信息
源码名称：oracle导入导出常用命令及问题处理
源码大小：4.56KB
文件格式：.zip
开发语言：SQL
更新时间：2021-05-28
友情提示：（无需注册或充值，赞助后即可获取资源下载链接）
嘿，亲！知识可是无价之宝呢，但咱这精心整理的资料也耗费了不少心血呀。小小地破费一下，绝对物超所值哦！如有下载和支付问题，请联系我们QQ(微信同号)：813200300
本次赞助数额为： 2 元×
微信扫码支付：2 元
×
请留下您的邮箱，我们将在2小时内将文件发到您的邮箱
源码介绍
--防丢空表
Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0
exp TMS/TMS@SPMIS file=D:\20141107.dmp
exp IPSS_XY/IPSS_XY@192.168.20.150/orcl file=d:\ipss_20170407.dmp
exp eanew/eanew@192.168.25.132/orcl file=E:\经评系统\eanew_20210128.dmp
exp reap/reap@192.168.25.132/orcl file=E:\经评系统\reap_20210128.dmp
exp reap2/reap2@192.168.25.132/orcl file=E:\经评系统\reap2_20210128.dmp
exp jr_flowable/jr_flowable@192.168.25.132/orcl file=E:\经评系统\jr_flowable_20210128.dmp
IMP TMS/TMS FILE=C:\Users\Administrator\Desktop\dmp\20141107.dmp FROMUSER=TMS TOUSER=TMS ignore=y full=y;
IMP IPSS/IPSS@192.168.20.150/orcl FILE=d:\ipssxy_20170407.dmp FROMUSER=IPSS_XY TOUSER=IPSS
IMP reap2/reap2@192.168.25.132/orcl FILE=C:\reap5.dmp FROMUSER=reap2 TOUSER=reap2
IMP eanew/eanew@192.168.23.20/orcl FILE=E:\经评系统\eanew_20210128.dmp FROMUSER=eanew TOUSER=eanew
IMP reap2/reap2 FILE=/usr/local/reap5.dmp FROMUSER=reap2 TOUSER=reap2 ignore=y full=y;
--部分表导入导出
exp ITSM_BETA/ITSM@192.168.25.15/devorcl file=d:\itsm_beta_20150415_tables.dmp tables=(cm_netypecategory,cm_netype,cm_netypelink,cm_netypeindex,cm_ne,cm_neindexrtvalue,cm_alarmrecord)
imp ITSM/ITSM@192.168.25.15/devorcl file=d:\itsm_beta_20150415_tables ignore=y tables=(cm_netypecategory,cm_netype,cm_netypelink,cm_netypeindex,cm_ne,cm_neindexrtvalue)
--HaiNan_ITSM/JREAP_DATA
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
export NLS_LANG=JA16EUCTILDE
--查看dmp文件字符集
cat xxx.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6
select nls_charset_name(to_number('0345','xxxx')) from dual;
--查看数据库字符集
select userenv('language') from dual;
select * from nls_database_parameters;
--修改字符集
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;
ALTER DATABASE CHARACTER SET ZHS16GBK;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT EXCLUSIVE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
--ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;
--ALTER DATABASE CHARACTER SET AL16UTF16;
ALTER DATABASE character set INTERNAL_USE ZHS16GBK;--经济评价系统开发环境编码
SHUTDOWN immediate;
startup;
--删除用户
drop user eanew cascade;
drop user reap cascade;
drop user JREAP_TEST_WFLOW cascade;
--查询表空间所需文件位置
select TABLESPACE_NAME,FILE_NAME from dba_temp_files;
--查询用户及对应表空间
x`
--创建表空间
CREATE TABLESPACE JREAP_DATA DATAFILE '/home1/oradata/JREAP_DATA.dbf' SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M;
CREATE TABLESPACE EA2 DATAFILE '/home1/oradata/EA2.dbf' SIZE 2000M AUTOEXTEND ON NEXT 200M MAXSIZE 4000M;
CREATE TABLESPACE EA DATAFILE '/data/oracle/oradata/orcl/EA_DATA.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M;
--创建零时表空间
CREATE TEMPORARY TABLESPACE EA_TEMP TEMPFILE '/home1/oradata/EA_TEMP_DATA.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 1500M;
--创建用户赋权
CREATE USER eanew IDENTIFIED BY eanew DEFAULT TABLESPACE JREAP_DATA TEMPORARY TABLESPACE TEMP;
GRANT "CONNECT" TO eanew;
GRANT "RESOURCE" TO eanew;
GRANT "DBA" TO eanew;
GRANT "EXP_FULL_DATABASE" TO eanew;
GRANT "IMP_FULL_DATABASE" TO eanew;
commit;
CREATE USER JREAP_TEST_WFLOW IDENTIFIED BY JREAP_TEST_WFLOW DEFAULT TABLESPACE JREAP_DATA TEMPORARY TABLESPACE TEMP;
GRANT "CONNECT" TO JREAP_TEST_WFLOW;
GRANT "RESOURCE" TO JREAP_TEST_WFLOW;
GRANT "DBA" TO JREAP_TEST_WFLOW;
GRANT "EXP_FULL_DATABASE" TO JREAP_TEST_WFLOW;
GRANT "IMP_FULL_DATABASE" TO JREAP_TEST_WFLOW;
commit;
CREATE USER jr_flowable IDENTIFIED BY jr_flowable DEFAULT TABLESPACE JREAP_DATA TEMPORARY TABLESPACE TEMP;
GRANT "CONNECT" TO jr_flowable;
GRANT "RESOURCE" TO jr_flowable;
GRANT "DBA" TO jr_flowable;
GRANT "EXP_FULL_DATABASE" TO jr_flowable;
GRANT "IMP_FULL_DATABASE" TO jr_flowable;
commit;
CREATE USER reap IDENTIFIED BY reap DEFAULT TABLESPACE EA TEMPORARY TABLESPACE EA_TEMP;
GRANT "CONNECT" TO reap;
GRANT "RESOURCE" TO reap;
GRANT SELECT ANY table TO reap;--查询同义词需要
GRANT "DBA" TO reap;
GRANT "EXP_FULL_DATABASE" TO reap;
GRANT "IMP_FULL_DATABASE" TO reap;
commit;
CREATE USER reap2 IDENTIFIED BY reap2 DEFAULT TABLESPACE EA2 TEMPORARY TABLESPACE EA_TEMP;
GRANT "CONNECT" TO reap2;
GRANT "RESOURCE" TO reap2;
GRANT "DBA" TO reap2;
GRANT "EXP_FULL_DATABASE" TO reap2;
GRANT "IMP_FULL_DATABASE" TO reap2;
commit;
imp jreap/jreap file=/usr/local/eanew_20200811.dmp FROMUSER=eanew TOUSER=jreap ignore=y;
IMP jreap/jreap@10.81.80.75/orcl FILE=d:\eanew_20200810.dmp FROMUSER=eanew TOUSER=jreap ignore=y
--查看表空间使用情况
SELECT a.tablespace_name,
round(a.bytes / (1024 * 1024), 0) total,
round(b.bytes / (1024 * 1024), 0) used,
round(c.bytes / (1024 * 1024), 0) free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
--xshell sql显示问题
set linesize 2000;
--删锁
select session_id from v$locked_object;
SELECT sid, serial#, username, oSUSEr FROM v$session where sid = 118;
ALTER SYSTEM KILL SESSION '118,31908';
--重置所有用户密码
update security_account set password=lower(Utl_Raw.Cast_To_Raw(sys.dbms_obfuscation_toolkit.md5(input_string => lower(login_name)||'1')));
--mysql
update security_account set password=lower(md5(concat(lower(login_name),'1')));
update security_account set password=lower(md5(concat(lower(login_name),'Dtgjyw!1234'))) where PWDMODIFY_DATE is null;
select * from security_account WHERE LAST_LOGIN_TIME >= DATE_FORMAT('2020-06-18 00:00:00','%Y-%m-%d %H:%M:%S');
--移动平台
update mob_account t set t.`password`=MD5(CONCAT(t.account,'Dtgjyw!1234')) where t.`password`=MD5(CONCAT(t.account,'abcd1234'));
--移动所有表和索引所在表空间到另一个表空间
select 'alter table '|| table_name ||' move tablespace YXGK_platform;' from user_tables;
select 'alter index '|| index_name ||' rebuild tablespace YXGK_platform;' from user_indexes;
alter table TEST_NEWS move tablespace YXGK_platform lob (REMARK) store as(tablespace YXGK_platform);--针对于大字段索引的移动，其中TEST_NEWS是表名REMARK是对应大字段列
--删除4a用户
delete from security_business_sys_account s where s.account_id in (select t.id from SECURITY_ACCOUNT t where t.name like '%范德萨范德萨发生的%');
delete from SECURITY_ROLE_ACCOUNT s where s.account_id in (select t.id from SECURITY_ACCOUNT t where t.name like '%范德萨范德萨发生的%');
delete from SECURITY_ACCOUNT t where t.name like '%范德萨范德萨发生的%';
--删除当前连接的用户
/* Formatted on 2015/3/17 9:50:08 (QP5 v5.227.12220.39754) */
SELECT s.username,
s.osuser,
s.program,
s.MACHINE,
s.sid,
s.serial#,
p.spid,
'alter system kill session '
|| ''''
|| TRIM (s.sid)
|| ','
|| TRIM (s.serial#)
|| ''';'
FROM v$session s, v$process p
WHERE s.paddr = p.addr AND s.username IS NOT NULL;
--查询包含某文字的存储过程
SELECT DISTINCT * FROM user_source
WHERE TYPE = 'PROCEDURE'
AND upper(text) LIKE '%MAX%';
---保留小数点后两位并且小数点前的0保留
rtrim(to_char(0.215,'fm99999999990.99'),'.')
---字符串转数字报错去除tab键数据
update RL_CBGL_JSD a set a.JSRZ=trim(replace(a.JSRZ,chr(9),''));
--忽略不能转为数字的
SELECT A1,A2, ……, case when regexp_replace(XXX,'[0-9,.]', '') IS NULL then to_number(XXX) else 0 end AS XXX
--oracle避免小数点前面的0丢失问题
select decode(substr(num,1,1),'.','0'||num,num) from t1_number;
select TO_NUMBER(T1.INVENTORYDIESELCNOOC) from test t1;
select to_char(0.73,'fm9999999990.00') from test;
--oracle删除表锁
select sess.sid,sess.serial#, lo.oracle_username,lo.os_user_name, ao.object_name,lo.locked_mode from v$locked_object lo,dba_objects ao,v$session sess where ao.object_id=lo.object_id and lo.session_id=sess.sid;
alter system kill session '12,661'
--oracle连接数问题
查询数据库当前进程的连接数：
select count(*) from v$process;
查看数据库当前会话的连接数：
elect count(*) from v$session;
查看数据库的并发连接数：
select count(*) from v$session where status='ACTIVE';
查看当前数据库建立的会话情况：
select sid,serial#,username,program,machine,status from v$session;
查询数据库允许的最大连接数：
select value from v$parameter where name = 'processes';
或者：show parameter processes;
修改数据库允许的最大连接数：
alter system set processes = 300 scope = spfile;
(需要重启数据库才能实现连接数的修改)
重启数据库：
shutdown immediate;
startup;
查看当前有哪些用户正在使用数据：
select osuser,a.username,cpu_time/executions/1000000||'s',sql_fulltext,machine
from v$session a,v$sqlarea b
where a.sql_address = b.address
order by cpu_time/executions desc;
备注：UNIX 1个用户session对应一个操作系统process，而Windows体现在线程。
--blob转varchar2
create or replace FUNCTION blob_to_varchar (blob_in IN BLOB)
RETURN VARCHAR2
IS
v_varchar VARCHAR2(2000);
v_start PLS_INTEGER := 1;
v_buffer PLS_INTEGER := 2000;
BEGIN
if DBMS_LOB.GETLENGTH(blob_in) is null then
return empty_clob();
end if;
DBMS_OUTPUT.put_line('TEST:' || CEIL(DBMS_LOB.GETLENGTH(blob_in)));
FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
LOOP
v_varchar := UTL_RAW.CAST_TO_VARCHAR2(utl_raw.convert(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start),'AMERICAN_THE NETHERLANDS.UTF8', 'AMERICAN_THE NETHERLANDS.UTF8'));
v_start := v_start v_buffer;
END LOOP;
RETURN v_varchar;
end blob_to_varchar;
开始修改表数据：
ALTER TABLE requestion ADD INTRODUCE_TEMP VARCHAR2(4000);
UPDATE requestion SET INTRODUCE_TEMP=blob_to_varchar(INTRODUCE);
ALTER TABLE requestion DROP COLUMN INTRODUCE;
ALTER TABLE requestion RENAME COLUMN INTRODUCE_TEMP TO INTRODUCE;
Drop FUNCTION blob_to_varchar;
--varchar2转blob
CREATE OR REPLACE FUNCTION C2B (b IN CLOB default empty_clob())
RETURN BLOB
-- typecasts BLOB to CLOB (binary conversion)
IS
res BLOB;
b_len number := dbms_lob.getlength(b) ;
dest_offset1 NUMBER := 1;
src_offset1 NUMBER := 1;
amount_c INTEGER := DBMS_LOB.lobmaxsize;
blob_csid NUMBER := DBMS_LOB.default_csid;
lang_ctx INTEGER := DBMS_LOB.default_lang_ctx;
warning INTEGER;
BEGIN
if b_len > 0 then
DBMS_LOB.createtemporary (res, TRUE);
DBMS_LOB.OPEN (res, DBMS_LOB.lob_readwrite);
DBMS_LOB.convertToBlob (res,
b,
amount_c,
dest_offset1,
src_offset1,
blob_csid,
lang_ctx,
warning
);
else
select empty_blob() into res from dual ;
end if ;
RETURN res; -- res is OPEN here
END C2B;
UPDATE ACT_HI_COMMENT SET full_msg_temp=C2B(to_clob(full_msg_));
ALTER TABLE ACT_HI_COMMENT DROP COLUMN full_msg_;
ALTER TABLE ACT_HI_COMMENT RENAME COLUMN full_msg_temp TO full_msg_;
Drop FUNCTION C2B;
--防丢空表
Select 'alter table '||table_name||' allocate extent;' from user_tables where num_rows=0
exp TMS/TMS@SPMIS file=D:\20141107.dmp
exp IPSS_XY/IPSS_XY@192.168.20.150/orcl file=d:\ipss_20170407.dmp
exp eanew/eanew@192.168.25.132/orcl file=E:\经评系统\eanew_20210128.dmp
exp reap/reap@192.168.25.132/orcl file=E:\经评系统\reap_20210128.dmp
exp reap2/reap2@192.168.25.132/orcl file=E:\经评系统\reap2_20210128.dmp
exp jr_flowable/jr_flowable@192.168.25.132/orcl file=E:\经评系统\jr_flowable_20210128.dmp
IMP TMS/TMS FILE=C:\Users\Administrator\Desktop\dmp\20141107.dmp FROMUSER=TMS TOUSER=TMS ignore=y full=y;
IMP IPSS/IPSS@192.168.20.150/orcl FILE=d:\ipssxy_20170407.dmp FROMUSER=IPSS_XY TOUSER=IPSS
IMP reap2/reap2@192.168.25.132/orcl FILE=C:\reap5.dmp FROMUSER=reap2 TOUSER=reap2
IMP eanew/eanew@192.168.23.20/orcl FILE=E:\经评系统\eanew_20210128.dmp FROMUSER=eanew TOUSER=eanew
IMP reap2/reap2 FILE=/usr/local/reap5.dmp FROMUSER=reap2 TOUSER=reap2 ignore=y full=y;
--部分表导入导出
exp ITSM_BETA/ITSM@192.168.25.15/devorcl file=d:\itsm_beta_20150415_tables.dmp tables=(cm_netypecategory,cm_netype,cm_netypelink,cm_netypeindex,cm_ne,cm_neindexrtvalue,cm_alarmrecord)
imp ITSM/ITSM@192.168.25.15/devorcl file=d:\itsm_beta_20150415_tables ignore=y tables=(cm_netypecategory,cm_netype,cm_netypelink,cm_netypeindex,cm_ne,cm_neindexrtvalue)
--HaiNan_ITSM/JREAP_DATA
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
export NLS_LANG=SIMPLIFIED CHINESE_CHINA.ZHS16GBK
export NLS_LANG=JA16EUCTILDE
--查看dmp文件字符集
cat xxx.dmp |od -x|head -1|awk '{print $2 $3}'|cut -c 3-6
select nls_charset_name(to_number('0345','xxxx')) from dual;
--查看数据库字符集
select userenv('language') from dual;
select * from nls_database_parameters;
--修改字符集
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;
ALTER DATABASE CHARACTER SET ZHS16GBK;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT EXCLUSIVE;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;
--ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE AL16UTF16;
--ALTER DATABASE CHARACTER SET AL16UTF16;
ALTER DATABASE character set INTERNAL_USE ZHS16GBK;--经济评价系统开发环境编码
SHUTDOWN immediate;
startup;
--删除用户
drop user eanew cascade;
drop user reap cascade;
drop user JREAP_TEST_WFLOW cascade;
--查询表空间所需文件位置
select TABLESPACE_NAME,FILE_NAME from dba_temp_files;
--查询用户及对应表空间
x`
--创建表空间
CREATE TABLESPACE JREAP_DATA DATAFILE '/home1/oradata/JREAP_DATA.dbf' SIZE 1000M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M;
CREATE TABLESPACE EA2 DATAFILE '/home1/oradata/EA2.dbf' SIZE 2000M AUTOEXTEND ON NEXT 200M MAXSIZE 4000M;
CREATE TABLESPACE EA DATAFILE '/data/oracle/oradata/orcl/EA_DATA.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M;
--创建零时表空间
CREATE TEMPORARY TABLESPACE EA_TEMP TEMPFILE '/home1/oradata/EA_TEMP_DATA.dbf' SIZE 500M AUTOEXTEND ON NEXT 100M MAXSIZE 1500M;
--创建用户赋权
CREATE USER eanew IDENTIFIED BY eanew DEFAULT TABLESPACE JREAP_DATA TEMPORARY TABLESPACE TEMP;
GRANT "CONNECT" TO eanew;
GRANT "RESOURCE" TO eanew;
GRANT "DBA" TO eanew;
GRANT "EXP_FULL_DATABASE" TO eanew;
GRANT "IMP_FULL_DATABASE" TO eanew;
commit;
CREATE USER JREAP_TEST_WFLOW IDENTIFIED BY JREAP_TEST_WFLOW DEFAULT TABLESPACE JREAP_DATA TEMPORARY TABLESPACE TEMP;
GRANT "CONNECT" TO JREAP_TEST_WFLOW;
GRANT "RESOURCE" TO JREAP_TEST_WFLOW;
GRANT "DBA" TO JREAP_TEST_WFLOW;
GRANT "EXP_FULL_DATABASE" TO JREAP_TEST_WFLOW;
GRANT "IMP_FULL_DATABASE" TO JREAP_TEST_WFLOW;
commit;
CREATE USER jr_flowable IDENTIFIED BY jr_flowable DEFAULT TABLESPACE JREAP_DATA TEMPORARY TABLESPACE TEMP;
GRANT "CONNECT" TO jr_flowable;
GRANT "RESOURCE" TO jr_flowable;
GRANT "DBA" TO jr_flowable;
GRANT "EXP_FULL_DATABASE" TO jr_flowable;
GRANT "IMP_FULL_DATABASE" TO jr_flowable;
commit;
CREATE USER reap IDENTIFIED BY reap DEFAULT TABLESPACE EA TEMPORARY TABLESPACE EA_TEMP;
GRANT "CONNECT" TO reap;
GRANT "RESOURCE" TO reap;
GRANT SELECT ANY table TO reap;--查询同义词需要
GRANT "DBA" TO reap;
GRANT "EXP_FULL_DATABASE" TO reap;
GRANT "IMP_FULL_DATABASE" TO reap;
commit;
CREATE USER reap2 IDENTIFIED BY reap2 DEFAULT TABLESPACE EA2 TEMPORARY TABLESPACE EA_TEMP;
GRANT "CONNECT" TO reap2;
GRANT "RESOURCE" TO reap2;
GRANT "DBA" TO reap2;
GRANT "EXP_FULL_DATABASE" TO reap2;
GRANT "IMP_FULL_DATABASE" TO reap2;
commit;
imp jreap/jreap file=/usr/local/eanew_20200811.dmp FROMUSER=eanew TOUSER=jreap ignore=y;
IMP jreap/jreap@10.81.80.75/orcl FILE=d:\eanew_20200810.dmp FROMUSER=eanew TOUSER=jreap ignore=y
--查看表空间使用情况
SELECT a.tablespace_name,
round(a.bytes / (1024 * 1024), 0) total,
round(b.bytes / (1024 * 1024), 0) used,
round(c.bytes / (1024 * 1024), 0) free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
--xshell sql显示问题
set linesize 2000;
--删锁
select session_id from v$locked_object;
SELECT sid, serial#, username, oSUSEr FROM v$session where sid = 118;
ALTER SYSTEM KILL SESSION '118,31908';
--重置所有用户密码
update security_account set password=lower(Utl_Raw.Cast_To_Raw(sys.dbms_obfuscation_toolkit.md5(input_string => lower(login_name)||'1')));
--mysql
update security_account set password=lower(md5(concat(lower(login_name),'1')));
update security_account set password=lower(md5(concat(lower(login_name),'Dtgjyw!1234'))) where PWDMODIFY_DATE is null;
select * from security_account WHERE LAST_LOGIN_TIME >= DATE_FORMAT('2020-06-18 00:00:00','%Y-%m-%d %H:%M:%S');
--移动平台
update mob_account t set t.`password`=MD5(CONCAT(t.account,'Dtgjyw!1234')) where t.`password`=MD5(CONCAT(t.account,'abcd1234'));
--移动所有表和索引所在表空间到另一个表空间
select 'alter table '|| table_name ||' move tablespace YXGK_platform;' from user_tables;
select 'alter index '|| index_name ||' rebuild tablespace YXGK_platform;' from user_indexes;
alter table TEST_NEWS move tablespace YXGK_platform lob (REMARK) store as(tablespace YXGK_platform);--针对于大字段索引的移动，其中TEST_NEWS是表名REMARK是对应大字段列
--删除4a用户
delete from security_business_sys_account s where s.account_id in (select t.id from SECURITY_ACCOUNT t where t.name like '%范德萨范德萨发生的%');
delete from SECURITY_ROLE_ACCOUNT s where s.account_id in (select t.id from SECURITY_ACCOUNT t where t.name like '%范德萨范德萨发生的%');
delete from SECURITY_ACCOUNT t where t.name like '%范德萨范德萨发生的%';
--删除当前连接的用户
/* Formatted on 2015/3/17 9:50:08 (QP5 v5.227.12220.39754) */
SELECT s.username,
s.osuser,
s.program,
s.MACHINE,
s.sid,
s.serial#,
p.spid,
'alter system kill session '
|| ''''
|| TRIM (s.sid)
|| ','
|| TRIM (s.serial#)
|| ''';'
FROM v$session s, v$process p
WHERE s.paddr = p.addr AND s.username IS NOT NULL;
--查询包含某文字的存储过程
SELECT DISTINCT * FROM user_source
WHERE TYPE = 'PROCEDURE'
AND upper(text) LIKE '%MAX%';
---保留小数点后两位并且小数点前的0保留
rtrim(to_char(0.215,'fm99999999990.99'),'.')
---字符串转数字报错去除tab键数据
update RL_CBGL_JSD a set a.JSRZ=trim(replace(a.JSRZ,chr(9),''));
--忽略不能转为数字的
SELECT A1,A2, ……, case when regexp_replace(XXX,'[0-9,.]', '') IS NULL then to_number(XXX) else 0 end AS XXX
--oracle避免小数点前面的0丢失问题
select decode(substr(num,1,1),'.','0'||num,num) from t1_number;
select TO_NUMBER(T1.INVENTORYDIESELCNOOC) from test t1;
select to_char(0.73,'fm9999999990.00') from test;
--oracle删除表锁
select sess.sid,sess.serial#, lo.oracle_username,lo.os_user_name, ao.object_name,lo.locked_mode from v$locked_object lo,dba_objects ao,v$session sess where ao.object_id=lo.object_id and lo.session_id=sess.sid;
alter system kill session '12,661'
--oracle连接数问题
查询数据库当前进程的连接数：
select count(*) from v$process;
查看数据库当前会话的连接数：
elect count(*) from v$session;
查看数据库的并发连接数：
select count(*) from v$session where status='ACTIVE';
查看当前数据库建立的会话情况：
select sid,serial#,username,program,machine,status from v$session;
查询数据库允许的最大连接数：
select value from v$parameter where name = 'processes';
或者：show parameter processes;
修改数据库允许的最大连接数：
alter system set processes = 300 scope = spfile;
(需要重启数据库才能实现连接数的修改)
重启数据库：
shutdown immediate;
startup;
查看当前有哪些用户正在使用数据：
select osuser,a.username,cpu_time/executions/1000000||'s',sql_fulltext,machine
from v$session a,v$sqlarea b
where a.sql_address = b.address
order by cpu_time/executions desc;
备注：UNIX 1个用户session对应一个操作系统process，而Windows体现在线程。
--blob转varchar2
create or replace FUNCTION blob_to_varchar (blob_in IN BLOB)
RETURN VARCHAR2
IS
v_varchar VARCHAR2(2000);
v_start PLS_INTEGER := 1;
v_buffer PLS_INTEGER := 2000;
BEGIN
if DBMS_LOB.GETLENGTH(blob_in) is null then
return empty_clob();
end if;
DBMS_OUTPUT.put_line('TEST:' || CEIL(DBMS_LOB.GETLENGTH(blob_in)));
FOR i IN 1..CEIL(DBMS_LOB.GETLENGTH(blob_in) / v_buffer)
LOOP
v_varchar := UTL_RAW.CAST_TO_VARCHAR2(utl_raw.convert(DBMS_LOB.SUBSTR(blob_in, v_buffer, v_start),'AMERICAN_THE NETHERLANDS.UTF8', 'AMERICAN_THE NETHERLANDS.UTF8'));
v_start := v_start v_buffer;
END LOOP;
RETURN v_varchar;
end blob_to_varchar;
开始修改表数据：
ALTER TABLE requestion ADD INTRODUCE_TEMP VARCHAR2(4000);
UPDATE requestion SET INTRODUCE_TEMP=blob_to_varchar(INTRODUCE);
ALTER TABLE requestion DROP COLUMN INTRODUCE;
ALTER TABLE requestion RENAME COLUMN INTRODUCE_TEMP TO INTRODUCE;
Drop FUNCTION blob_to_varchar;
--varchar2转blob
CREATE OR REPLACE FUNCTION C2B (b IN CLOB default empty_clob())
RETURN BLOB
-- typecasts BLOB to CLOB (binary conversion)
IS
res BLOB;
b_len number := dbms_lob.getlength(b) ;
dest_offset1 NUMBER := 1;
src_offset1 NUMBER := 1;
amount_c INTEGER := DBMS_LOB.lobmaxsize;
blob_csid NUMBER := DBMS_LOB.default_csid;
lang_ctx INTEGER := DBMS_LOB.default_lang_ctx;
warning INTEGER;
BEGIN
if b_len > 0 then
DBMS_LOB.createtemporary (res, TRUE);
DBMS_LOB.OPEN (res, DBMS_LOB.lob_readwrite);
DBMS_LOB.convertToBlob (res,
b,
amount_c,
dest_offset1,
src_offset1,
blob_csid,
lang_ctx,
warning
);
else
select empty_blob() into res from dual ;
end if ;
RETURN res; -- res is OPEN here
END C2B;
UPDATE ACT_HI_COMMENT SET full_msg_temp=C2B(to_clob(full_msg_));
ALTER TABLE ACT_HI_COMMENT DROP COLUMN full_msg_;
ALTER TABLE ACT_HI_COMMENT RENAME COLUMN full_msg_temp TO full_msg_;
Drop FUNCTION C2B;