oracle导入导出常用命令及问题处理

--防丢空表
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;