oracle常用笔记总结

1、用dba权限的用户查看数据库都有哪些锁
select ername,t2.sid,t2.serial#,t2.logon_time
from v$locked_object t1,v$session t2
where t1.session_id=t2.sid order by t2.logon_time;
2、查看有哪些对象被锁了
select b.owner,b.object_name,a.session_id,a.locked_mode
from v$locked_object a,dba_objects b
where b.object_id = a.object_id
3、根据sid查找进程id
select pro.spid from v$session ses,
v$process pro where ses.sid=129 and ses.paddr=pro.addr;
select ses.sid from v$session ses,
v$process pro where pro.spid=129 and ses.paddr=pro.addr;
4：根据这个spid对应linux下的进程pid
linux命令：
ps -ef | gre | 'ora' (找到所有ora开头的进程)
kill -9 (pid)
5、解决delete后表的高水位的问题
初步判断应该是之前有大量数据，后来delete了，选成高水位，用shrink
清除高水位：
alter table t_mobile_client_tp_prov enable row movement;
alter table t_mobile_client_tp_prov shrink space;
重新收集统计信息：
begin
dbms_stats.gather_table_stats(ownname=>'traffic',tabname=>'T_MOBILE_CLIENT_TP_PR end;
6、ORA-02064 distributed operation not supported
Cause:One of the following unsupported operations was attempted:
Array execute of a remote update with a subquery that references a database link, or
An update of a long column with bind variable and an update of a second column with a subquery that both references a database link and a bind variable, or
A commit is issued in a coordinated session from an RPC with OUT parameters.
Action:Simplify the remote update statement.
问题: 数据库A ,B 通过DBlink互相访问, 数据库A 调用数据库B的存储过程pro_b , pro_b 过程中有out 输出参数,并且里面有dml语句,之后commit ,或rollback. 这时数据库A 通过DBlink 的调用pro_b@B就会产生这个错误.
解决办法:
a. 去掉pro_b中的commit, rollback ,统一有数据库B来控制事务.(当然也可以去掉out参数,这样也破坏是事务的完整性)
b.用自制事务来控制pro_b , 在pro_b中添加.PRAGMA AUTONOMOUS_TRANSACTION; 优点:不用去修改调用的所有方法.当然这样也不能控制事务的完整.
7、建表开启并行
v_table_sql := 'CREATE TABLE T_MOBILE_CAP_DETA82_'||in_date
||' parallel (degree 4) as '
||' select /*+parallel(t1 4) */ * from
T_MOBILE_CAP_DETAIL_'||in_date||'@QAS82 t1';
EXECUTE IMMEDIATE v_table_sql;
8、创建dblink
CREATE DATABASE LINK CONNECT TO TRAFFIC
IDENTIFIED BY "pwd"
USING '(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
(HOST=192.168.228.65)(PORT=1521)))(CONNECT_DATA=(SID=qas)))'
9、创建临时表
a.create global temporary table temp_tbl(col_a varchar2(30))
on commit preserve rows（当提交后保留数据，session结束时才清除数据）
b.create global temporary table temp_tbl(col_a varchar2(30))
on commit delete rows （当提交后不保留数据，session结束时也清除数据）
10、drop后的表被放在回收站(user_recyclebin)里，而不是直接删除掉。

这样，回收站里的表信息就可以被恢复，或彻底清除。

a.通过查询回收站user_recyclebin获取被删除的表信息，然后使用语句 flashback table <user_recyclebin.object_name or
user_recyclebin.original_name> to before drop [rename to
<new_table_name>];
将回收站里的表恢复为原名称或指定新名称，表中数据不会丢失。

若要彻底删除表，则使用语句：drop table <table_name> purge;
b.清除回收站里的信息
清除指定表：purge table <table_name>;
清除当前用户的回收站：purge recyclebin;
清除所有用户的回收站：purge dba_recyclebin;
11、REGEXP_SUBSTR函数格式如下：
function REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)
__srcstr ：需要进行正则处理的字符串
__pattern ：进行匹配的正则表达式
__position ：起始位置，从第几个字符开始正则表达式匹配（默认为1）
__occurrence ：标识第几个匹配组，默认为1
__modifier ：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。

默认为'c'。

）
例：select
regexp_substr(call_logs,'\d*',instr(call_logs,'"duration":"')+12,1,'i'),call_logs from t_mobile_callogs_sdk_20130519 where upper(client_type) like
'%C8812%'
12、alter table T_MON_APP_COOP modify mark varchar(10)
13、alter table T_MOBILE_CAP_USE_20130822 drop column is_run
14、ORACLE利用TNS控制远程访问的IP地址
利用TNS可以实现IP地址的过滤。

如下：
在D:\oracle\ora92\network\admin的sqlnet.ora文件中添加你需要排除或者允许访问的地址按照规定格式
TCP.VALIDNODE_CHECKING = YES 该参数表明会对连接进行验证
TCP.EXCLUDED_NODES= (10.83.25.44) 该参数表明排除该地址的访问TCP.INVITED_NODES= (10.83.25.125) 该参数表明允许该地址的访问
如果有多个地址怎么搞？很简单，全部都搞就好了。

TCP.INVITED_NODES= (10.83.25.125, 10.83.25.1)中间用逗号隔开。

首先查看当前使用的数据库实例:
select name from V$database;
切换两个数据库实例
在sqlplus里connect username/passWord@sid
或host set ORACLE_SID=sid
或cmd里set ORACLE_SID=sid
或修改注册表里ORACLE_SID项
更改oracle 密码 ALTER USER "TRAFFIC" IDENTIFIED BY "TRAFFIC209";
密码过期解决办法
1. 查看用户的profile设置：
SELECT username,profile FROM dba_users;
2. 查看系统profiles中PASSWORD_LIFE_TIME设置：
SELECT * FROM dba_profiles s WHERE s.profile='DEFAULT' AND resource_name='PASSWORD_LIFE_TIME';
3. 修改DBA_PROFILES中PASSWORD_LIFE_TIM的设置，改为ULIMITED
ALTER PROFILE DEFAULT LIMIT PASSWORD_LIFE_TIME UNLIMITED;
4. 已经被报告了密码快要过期的账户必须再改一次密码（需要DBA权限）
以system用户为例
alter user system identified by root;
job执行、删除
exec dbms_job.run(4);
exec dbms_job.remove(4);
commit;
恢复delete的数据
select * from T_MOBILE_REPORT_LOG as of timestamp
to_timestamp('20131220 9:40:00','yyyy-mm-dd hh24:mi:ss')
where error_info like '%ORA%' order by op_date desc
其中时间点只要在删除之前就行了
ALTER SYSTEM SET db_block_buffers=1677721 SCOPE=BOTH;
生成awr报告
@/home/oracle/product/10.2.0/rdbms/admin/awrrpt.sql;
@/home/oracle/product/11.2.0/rdbms/admin/awrrpt.sql;
@/home/oracle/11g/oracle/product/11.2.0/dbhome_1/rdbms/admin/awrrpt.sql; oracle : oinstall asmdba dba oper
警告日志目录
select * from v$diag_info;
oracle 中 NLSSORT函数的用法
NLSSORT()，用来进行语言排序
拼音
SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,'NLS_SORT = SCHINESE_PINYIN_M')
笔划
SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,'NLS_SORT = SCHINESE_STROKE_M')
部首
SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,'NLS_SORT = SCHINESE_RADICAL_M')
plsq求余
if mod(to_number(substr(in_date,7,2)),2)=0 then
end if
select * from dba_objects a,v$locked_object b where a.object_id = b.object_id;
ORACLE里锁有以下几种模式:
0：none
1：null 空
2：Row-S 行共享(RS)：共享表锁，sub share
3：Row-X 行独占(RX)：用于行的修改，sub exclusive
4：Share 共享锁(S)：阻止其他DML操作，share
5：S/Row-X 共享行独占(SRX)：阻止其他事务操作，share/sub exclusive 6：exclusive 独占(X)：独立访问使用，exclusive
select object_name,machine,s.sid,s.serial# from v$locked_object
l,dba_objects o ,v$session s where l.object_id = o.object_id and
l.session_id=s.sid;
ps -ef|grep LOCAL=NO|grep -v grep|cut -c 9-15|xargs kill -9
dbms_space_admin.segment_drop_corrupt
exec dbms_space_admin.segment_corrupt('TRAFFIC',153,977835);
exec dbms_space_admin.segment_drop_corrupt('TRAFFIC',153,977835); exec dbms_space_admin.tablespace_verify ('TRAFFIC');
select 'exec
dbms_space_admin.segment_corrupt(''TRAFFIC'','||HEADER_FILE||','||HEADER FROM
dba_segments where segment_type like 'TEMP%' and tablespace_name='TRAFFIC'
让数据文件脱机
alter database datafile '/oradata/qas/traffic23.dbf' offline drop;
alter database datafile '/oradata/qas/system02.dbf' offline drop;
系统全局区SGA：
SGA包含的组件：共享池（shared pool）；数据库缓冲区高速缓存（database buffer cache）；日志缓冲区（redo buffer cache）；大池；Java池；流池等。

SGA相关视图：
v$sga：V$SGA这个视图包括了SGA的的总体情况，只包含两个字段：name（SGA内存区名字）和value（内存区的值，单位为字节）。

它的结
果和show sga的结果一致。

v$sgastat：10g之前用于查看各SGA组件大小。

V$SGAINFO的作用基本
和V$SGA一样，只不过把Variable size的部分更细化了一步
v$sgainfo：10g及10g之后才有的。

用于查看SGA组件大小更简便。

v$sga_dynamic_components：这个视图记录了SGA各个动态内存区的情况，它的统计信息是基于已经完成了的，针对SGA动态内存区大小调整
的操作。

v$sga_dynamic_free_memory：这个视图只有一个字段就是用来表示SGA当前可以用于调整各个组件的剩余大小。

v$sga_target_advice：该视图可用于建议SGA大小设置是否合理。

SELECT a.sga_size,--sga期望大小
a.sga_size_factor,--期望sga大小与实际sga大小的百分比
a.estd_db_time,--sga设置为期望的大小后，其dbtime消耗期望的变
化
a.estd_db_time_factor,--修改sga为期望大小后，dbtime消耗的变化
与修改前的变化百分比
a.estd_physical_reads--修改前后物理读的差值
FROM v$sga_target_advice a;
Oracle Sqlplus设置行数和宽度
设置页面显示总行数
show pagesize;
set pagesize 100;
设置行的宽度
show linesize;
set linesize 100;
设置默认的值
在Oracle的安装目录下找到glogin.sql，加入
set linesize 300 ;
set pagesize 100 ;
declare
in_date VARCHAR2(10);
V_TOTAL_DAYS number;
v_date VARCHAR2(10);
BEGIN
in_date:='201402';
V_TOTAL_DAYS:=last_day(to_date(in_date||'01','yyyymmdd'))-
to_date(in_date||'01','yyyymmdd')+1;
for X IN 1..V_TOTAL_DAYS LOOP--循环开始
v_date:=to_char(to_date(in_date||'01','yyyymmdd')+X-1,'yyyymmdd'); dbms_output.put_line(v_date);
end loop;
end;
设置job数量，如果为0，job就不会执行
ALTER SYSTEM SET job_queue_processes = 20;
commit;
启用/禁用sys用户远程登陆
alter system set remote_login_passwordfile=exclusive scope=spfile;
alter system set remote_login_passwordfile=none scope=spfile;
查看占用system表空的用户
select username, default_tablespace, temporary_tablespace from dba_users where username not in
('SYS','SYSTEM') and (default_tablespace='SYSTEM' or temporary_tablespace='SYSTEM');
11g中oracle的审计功能是默认开启的（10g是默认关闭的），统计信息会影响数据库性能，也会给system表空间带来巨大的压力
问题处理方法：
1.如果审计不是必须的，可以关掉审计功能；
SQL> alter system set audit_trail=none scope=spfile;
SQL>showdown immediate;
SQL>startup
2.删除已有的审计信息
可以直接truncate表aud$,
或者采取dbms_audit_mgmt来清除。

3.或者将aud$表移到另外一个表空间下，以减少system表空间的压力和被撑爆的风险
sqlplus下查看执行计划：
1、EXPLAIN PLAN FOR
select * from dual;
然后查看
2、SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
强制使用索引
select /*+ index(t i_t) */ * from t where username='EYGLE';
遇到
ORA-39006: internal error
ORA-39213: Metadata processing is notavailable
执行
sqlplus / as sysdba
execute sys.dbms_metadata_util.load_stylesheets;
ORA-00020:maximum number of processes (150) exceeded 错误解决方法1、批量杀死过多的进程
ps x|grep oracleqas|grep -v grep |awk '{print $1}'|xargs kill -9
启动OEM
emctl start dbconsole
查看地址
https://172.16.22.151:1158/em/
查询dblink的信息
select count(*) from link$;
查看某个表的索引
select distinct index_name from dba_indexes where table_name='T_MOBILE_MID_USE_P';
删除某一列
alter table T_MOBILE_APP_USE_201403301 drop column rn
更改某用户的环境变量，使之永久生效
如，在/home/oracle/.bash_profile中添加export NLS_LANG=american_america.utf8
source /home/oracle/.bash_profile 让更改的配置立即生效，不然得重新登陆此用户才有效
注意：更改/etc/profile文件对所有用户有效果
查看redo log日志的切换时间
SELECT b.recid,
TO_CHAR(b.first_time,'dd-mon-yy hh24:mi:ss') start_time, a.recid,
TO_CHAR(a.first_time,'dd-mon-yy hh24:mi:ss') end_time,
ROUND(((a.first_time-b.first_time)*25)*60,2) minutes
FROM v$log_history a, v$log_history b
WHERE a.recid = b.recid+1
ORDER BY a.first_time DESC
用create table as 创建事务级的临时表
create global temporary table t_app2 ON COMMIT PRESERVE ROWS parallel (degree 4) as select /*+parallel(t 4) */ * from t_mobile_application
设置oracle闪回
alter system set recyclebin = on | off;
flashback table tb_employees to before drop;
清理某个分区
alter table t_mobile_client_p truncate partition P14
查看分区索引
SELECT INDEX_NAME "NAME", NUM_ROWS, DISTINCT_KEYS "DISTINCT",
sample_size,LAST_ANALYZED
FROM DBA_INDEXES
WHERE OWNER = 'TRAFFIC' AND TABLE_NAME IN
('T_MOBILE_MID_USE_P')
ORDER BY TABLE_NAME,INDEX_NAME;
SELECT INDEX_NAME "NAME",
NUM_ROWS,
DISTINCT_KEYS "DISTINCT",
sample_size,
LAST_ANALYZED ,
t.partition_name
FROM dba_ind_partitions t
WHERE INDEX_OWNER = 'TRAFFIC'
AND INDEX_NAME = 'IDX_MUPN_RUN_START_TIME'
ORDER BY INDEX_NAME;
可以查看该索引的每个分区的记录情况，如果NUM_ROWS不等于sample_size，说明要重新收集
alter profile default limit FAILED_LOGIN_ATTEMPTS 30;
alter profile default limit FAILED_LOGIN_ATTEMPTS unlimited
GRANT update,select ON test TO dbuser; -----把表test的修改、查询权限赋给dbuser;
--查看被覆盖的存储过程
SELECT text
FROM DBA_source AS OF TIMESTAMP TO_TIMESTAMP('2014-06-26 12:00:00', 'YYYY-MM-DD HH24:MI:SS')
WHERE OWNER = 'TRAFFIC'
AND NAME = 'SP_CAP_RECORD_COUNT'
ORDER BY LINE;
压缩一个未压缩的表
ALTER TABLE SALES_HISTORY_TEMP MOVE COMPRESS;
你也可以将ALTER TABLE ...MOVE语句用于解压缩一个表，如下例所示：ALTER TABLE SALES_HISTORY_TEMP MOVE NOCOMPRESS;
plsql从大到小循环
declare
x int;
begin
for x in reverse 151..162
loop
dbms_output.put_line(x);
end loop;
end;
当分区关键字更改时，需要更改表的属性
alter table test_move enable row movement;
查看某个表的分区类型
select index_name,table_name,partitioning_type,locality,ALIGNMENT from user_part_indexes where table_name='T_MOBILE_CLIENT_TP_GD';
oracle的中文排序
1.SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,'NLS_SORT = SCHINESE_PINYIN_M'); --1.按中文拼音进行排序
2.SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,'NLS_SORT = SCHINESE_STROKE_M'); --2.按中文笔画进行排序
3.SELECT * FROM TEAM ORDER BY NLSSORT(排序字段名,'NLS_SORT = SCHINESE_RADICAL_M');--3.按中文部首进行排序
查询表空间各表占用空间情况：
select segment_name,sum(bytes)/1024/1024 G from dba_segments where tablespace_name='SYSTEM' group by segment_name order by G desc; oracle清理用户和对应的表空间
对于单个user和tablespace 来说，可以使用如下命令来完成。

步骤一：删除user
drop user ×× cascade
说明：删除了user，只是删除了该user下的schema objects，是不会删除相应的tablespace的。

步骤二：删除tablespace
DROP TABLESPACE tablespace_name INCLUDING CONTENTS AND DATAFILES;。