【Oracle】实用脚本归纳(2)

文中使用的Oracle版本为10g。

  1. 生成10以内随机数语句
select round(to_number(dbms_random.value)*10,0) 
from dual;

  1. 数字非科学计数法输出
select to_char('65647.2657696732665873677469045687295023',999990.999999999999999) 
from dual;
  1. 解决ORA-22992问题

在存储过程中使用游标进行数据插入,在执行了一半时抛出以下错误:

ORA-22992 cannot use LOB locators selected from remote tables

-- 可使用
create table <table_name> as select ...
-- 或
insert into <table_name> select ...

解决这个问题。

  1. 判断某字段中存在中文
select b.* 
from <table_name> b
where asciistr(b.<field_name>) like '%\%';

  1. 查询资源消耗最多的语句
select se.username,
se.sid,
su.extents,
su.blocks * to_number(rtrim(p.value)) as Space,
tablespace,
segtype,
sql_text
from v$sort_usage su, v$parameter p, v$session se, v$sql s
where p.name = 'db_block_size'
and su.session_addr = se.saddr
and s.hash_value = su.sqlhash
and s.address = su.sqladdr
order by se.username, se.sid;

  1. 查看当前数据库最大连接数
select value 
from v$parameter
where name = 'processes';

  1. 设置回滚保留时间
ALTER SYSTEM SET UNDO_RETENTION=<timeout_setting> SCOPE=BOTH;

  1. 去掉回车、换行、空格
-- 去除换行
update <table_name> t
set t.<field_name>=replace(t.<field_name>,chr(10),'');

-- 去掉回车
update <table_name> t
set t.<field_name>=replace(t.<field_name>,chr(13),'');

-- 去掉空格
update <table_name> t
set t.<field_name>=trim(t.<field_name>);

  1. 根据sid查询pid
select pro.spid 
from v$session ses,v$process pro
where ses.sid='<sid_code>' and ses.paddr=pro.addr;
  1. 分段导出数据备份
exp based/password 
file=/oracle/DataBkup/<dmp_file1>.dmp,
/oracle/DataBkup/<dmp_file2>.dmp,
/oracle/DataBkup/<dmp_file3>.dmp,
/oracle/DataBkup/<dmp_file4>.dmp
filesize=2147483648 owner=<tablespace_name>;
  1. 查看正在运行的Job(定时器)
SELECT SID,JOB 
FROM DBA_JOBS_RUNNING;
  1. 创建表空间
create tablespace <tablespace_name> logging datafile '<dbf_file_path>' size 50m autoextend on ;
alter user <username> quota unlimited on <tablespace_name>;
  1. 查看表空间
-- 表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
   D.TOT_GROOTTE_MB "表空间大小(M)",
   D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
   TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
   F.TOTAL_BYTES "空闲空间(M)",
   F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
   ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
   ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
   FROM SYS.DBA_FREE_SPACE
   GROUP BY TABLESPACE_NAME) F,
   (SELECT DD.TABLESPACE_NAME,
   ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
   FROM SYS.DBA_DATA_FILES DD
   GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
--and F.TABLESPACE_NAME = '<tablespace>'
ORDER BY 1;

-- 表空间大小以及存放位置
SELECT TABLESPACE_NAME,BYTES/1024/1024 FILE_SIZE_MB,FILE_NAME
FROM DBA_DATA_FILES;
  1. 额外增加表空间
alter tablespace <tablespace_name> add datafile '<dbf_file_path>' size 2024M;
  1. 删除表空间
DROP USER <username> CASCADE;
DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES;
  1. 重新解析表中索引增速
analyze table <table_name> compute statistics for all indexes;
  1. 统计字符出现次数
select length(replace(a.<field_name>, ',', ',,'))-length(a.<field_name>) 
from <table_name>;
  1. 变更分区信息
alter table <table_name> enable row movement;
  1. 查询表中外键
select * 
from user_constraints c
where c.constraint_type = 'R'
and c.table_name = '<table_name>'
  1. dmp导入指定表
imp system/manager@TEST  file=d:\daochu.dmp  tables=(table1)