【Oracle】实用脚本归纳（2）

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

select round(to_number(dbms_random.value)*10,0) 
from dual;

​

select to_char('65647.2657696732665873677469045687295023',999990.999999999999999) 
from dual;

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

ORA-22992 cannot use LOB locators selected from remote tables

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

解决这个问题。

select b.* 
from <table_name> b 
where asciistr(b.<field_name>) like '%\%';

​

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;

​

select value 
from v$parameter 
where name = 'processes';

​

ALTER SYSTEM SET UNDO_RETENTION=<timeout_setting> SCOPE=BOTH;

​

-- 去除换行
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>);

​

select pro.spid 
from v$session ses,v$process pro 
where ses.sid='<sid_code>' and ses.paddr=pro.addr;

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>;

SELECT SID,JOB 
FROM DBA_JOBS_RUNNING;

create tablespace <tablespace_name> logging datafile '<dbf_file_path>' size 50m autoextend on ;
alter user <username> quota unlimited on <tablespace_name>; 

-- 表空间使用情况
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;

alter tablespace <tablespace_name> add datafile '<dbf_file_path>' size 2024M;

DROP USER <username> CASCADE;
DROP TABLESPACE <tablespace_name> INCLUDING CONTENTS AND DATAFILES;

analyze table <table_name> compute statistics for all indexes;

select length(replace(a.<field_name>, ',', ',,'))-length(a.<field_name>) 
from <table_name>;

alter table <table_name> enable row movement;

select * 
from user_constraints c 
where c.constraint_type = 'R' 
    and c.table_name = '<table_name>'

imp system/manager@TEST  file=d:\daochu.dmp  tables=(table1)