oracle 知识点

转载自：https://www.cnblogs.com/tianmingt/articles/4259554.html

1、正在连接的用户不能删除，确实要删除的话，如下
select sid,serial#,username from v$session where user='USERNAME';
alter system kill session 'sid,serial#';
drop user username cascade;

 

2、在一台机器中想分析下执行计划，但是在sqlplus中输入set autotrace on 报cannot set autotrace 错误
解决方法如下：
首先必须采用用Oracle的sqlplus登陆sys账号
sqlplus " sys/sys@XXX as sysdba "
然后执行如下脚本：
@?\sqlplus\admin\plustrce.sql （创建plustrace角色并授权） 
@?\rdbms\admin\utlxplan.sql （创建执行计划的表） 
然后执行： grant all on plan_table to public; （也可以授权给某一个单独的用户）
grant plustrace to public ; 
然后就可以进行 set autotrace了 但是只能在sqlplus中运行相关命令，在pl/sql developer等工具中仍然报错
关于Autotrace几个常用选项的说明:
SET AUTOTRACE OFF ---------------- 不生成AUTOTRACE 报告，这是缺省模式
SET AUTOTRACE ON EXPLAIN ------ AUTOTRACE只显示优化器执行路径报告
SET AUTOTRACE ON STATISTICS -- 只显示执行统计信息
SET AUTOTRACE ON ----------------- 包含执行计划和统计信息
SET AUTOTRACE TRACEONLY ------ 同set autotrace on，但是不显示查询输出

3、赋予创建dblink的权限有三种: sys 用户下
grant create database link to user ; --只有user用户能使用的dblink
grant create public database link to user ;--所有用户都可以使用的dblink
grant drop public database link to user; --删除dblink的权限

--创建
create (public) database link DB133
connect to gzf_common identified by "gzf_common"
using ' (DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 172.30.242.133)(PORT = 1521))
)
(CONNECT_DATA =
(SID = fgora1)
)
)';

--删除dblink 
DROP (public) DATABASE LINK DB133;
--查询该用户的dblink
select * from user_db_links;
--查询表
select * from scott.emp@DB133;

 

4、查某个表信息
select a.table_name, --表名
a.COLUMN_NAME, --字段名
a.DATA_TYPE, --数据类型
a.DATA_LENGTH, --长度
b.COMMENTS --注释
from user_tab_columns a
inner join user_col_comments b
on a.COLUMN_NAME = b.COLUMN_NAME
where a.table_name = 'TPG_BMDJ'
and b.TABLE_NAME = 'TPG_BMDJ'
order by a.column_id

 

5、查某个用户下的表信息
select a.table_name 表名,
b.created 创建时间,
b.LAST_DDL_TIME 最后修改时间,
c.LAST_ANALYZED 最后分析时间,
c.tablespace_name 表空间,
c.num_rows 行数, --要经过表分析后才精确
d.BYTES / 1024 / 1024||'M' 大小,
a.COMMENTS 表注释
from user_tab_comments a
inner join user_objects b on a.TABLE_NAME = b.OBJECT_NAME
inner join user_tables c on b.OBJECT_NAME = c.TABLE_NAME
inner join user_segments d on c.TABLE_NAME = d.segment_name
--and a.table_name like 'TPG%'
and d.segment_type = 'TABLE'
--and substr(a.table_name, -1) not in ('1', '2', '3', '4', '5', '6', '7', '8', '9', '0')
order by num_rows desc

6、每天复制表，表名为每天的日期 yyyymmdd
create or replace procedure inserta
Authid Current_User is
v_tablename varchar2(100);
v_sql varchar2(1000);
begin
select upper('tablename')||replace(to_char(sysdate,'yyyy-mm-dd'),'-','') into v_tablename from dual;
v_sql:= 'create table '||v_tablename||' as select * from tpg_gzfspb';
execute immediate v_sql;
end;

7、根据日期查星期
select to_char(sysdate,'day') from dual;
select sqbbh,sqsj,to_char(sqsj,'day') from tpg_gzfspb
select to_char(to_date('2014-09-07','yyyy-mm-dd'),'day') from dual;

 

8、listagg( ) within group (order by ) （11g新增） 与 wm_concat（10g及以前）
select deptno,wm_concat(ename)name,count(*)rs from emp group by deptno
select deptno,listagg(ename,',') within group (order by ename ) aa from emp group by deptno
select deptno,listagg(ename,',') within group (order by ename ) aa,count(*)bb from emp group by deptno

9、回收站数据清空与恢复 从oracle10g开始删除数据库表的时候并不是真正删除，而是放到了recyclebin中，这个过程类似 windows里面删除的文件会被临时放到回收站中。删除的表系统会自动给他重命名就是你看到的 【BIN$】开头的名字,通过 show recyclebin 命令可以查看被删掉的表的详细信息，或者查询 select * from recyclebin;

收回表的命令：
flashback table 原表名 to before drop;
清空回收站的命令，如果数据量大，可在sqlplus 或cmd 命令中操作，速度较快
purge recyclebin;
如果不想删除的表经过回收站
drop table 表名 purge;
或者停用数据库的回收战功能
10.1版本中，修改隐藏参数 _recyclebin
alter system set "_recyclebin" = false;
10.2版本中及以后
alter system set recyclebin = off;

 

10、检查表空间使用率 dba 权限
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 
ORDER BY F.TABLESPACE_NAME;

 

11、取随机数据
select * from emp sample(50) 
--Sample值在[0.000001,99.999999]之间。定义结果集中包含记录占总记录数量的百分比。sample只对单表生效，不能用于表连接和远程表
select dbms_random.value from dual;

12、修改数据库SID
sqlplus / as sysdba
create pfile from spfile

创建文件复制到d盘,改名为pfile 
shutdown immediate 
在cmd 中
oradim -delete -sid orcl
oradim -new -sid gzf

然后注册表里的 oracle_sid 改成 gzf 
sqlplus / as sysdba
startup pfile 'd:\pfile.ora'
create spfile from 'd:\pfile.ora'

database 下执行
orapwd file=pwdgzf.ora password=m123

改系统服务名
alter system set service_names=gzf
再改 tsnname 中的配置

 

13、表被lock
有没有被lock，可以通过这2张 view来确定：
v$locked_object, V$session

可以把该 session杀掉。
select sid,serial# from v$session where username ='XXXX'
把得到的sid，serial#号替换到下面的语句中：
alter system kill session 'SID,SERIAL#'

14、创建触发器使表在插入数据时id自增长
create table ttt (id number primary key ,name varchar2(20),age number(2))
create or replace trigger gger_tt 
before insert on ttt
for each row
when (new.id is null)
begin
select ttt_sequence.nextval into :new.id from dual;
end;

--插入数据
insert into ttt(name) values('明')

15、在表中循环插入日期
--创建会话级临时表
create global temporary table rq(id number primary key ,sj varchar2(20))
on commit preserve rows
select * from rq
--循环插入日期
declare 
c_i number :=1;
v_i number ;
v_firstday varchar2(20);
v_lastday varchar(20);
begin
select to_char(sysdate,'yyyy-mm')||'-' into v_firstday from dual;
select to_char(last_day(sysdate),'yyyy-mm-dd') into v_lastday from dual;
select ceil(last_day(sysdate)-trunc(sysdate,'mm'))+1 into v_i from dual;
loop
insert into rq (id,sj) values(c_i,v_firstday||to_char(c_i));
c_i:=c_i+1;
exit when c_i=v_i;
end loop;
end;

 

16、这样查出来是本周一 、相似度函数
select trunc(sysdate,'iw') from dual; 
select UTL_MATCH.EDIT_DISTANCE_SIMILARITy('啊','啊吗') from dual;

17、使用序列作为插入值，初始值不是 1 
---创建序列
create sequence SEQ_TEST
minvalue 1
maxvalue 99999999999
start with 21
increment by 1
cache 20;

创建表时 
CREATE TABLE tbl_test(
test_id NUMBER PRIMARY KEY, 
test_name VARCHAR2(20)
)
SEGMENT CREATION IMMEDIATE; --加上这句 可使初始值为1

或执行ALTER SYSTEM SET deferred_segment_creation=FALSE; dba 权限
insert into tbl_test values(seq_test.nextval,'abc')
select * from tbl_test

18、PDM导出表结构时没有注释执行语句 执行顺序 1 工具 2 execute commands 3 复制到 run script 运行
Option Explicit 
ValidationMode = True 
InteractiveMode = im_Batch 
Dim mdl ' the current model 
' get the current active model 
Set mdl = ActiveModel 
If (mdl Is Nothing) Then 
MsgBox "There is no current Model " 
ElseIf Not mdl.IsKindOf(PdPDM.cls_Model) Then 
MsgBox "The current model is not an Physical Data model. " 
Else 
ProcessFolder mdl 
End If 

' This routine copy name into comment for each table, each column and each view 
' of the current folder 
Private sub ProcessFolder(folder) 
Dim Tab 'running table 
for each Tab in folder.tables 
if not tab.isShortcut then 
tab.comment = tab.name 
Dim col ' running column 
for each col in tab.columns 
col.comment= col.name 
next 
end if 
next 

Dim view 'running view 
for each view in folder.Views 
if not view.isShortcut then 
view.comment = view.name 
end if 
next 

' go into the sub-packages 
Dim f ' running folder 
For Each f In folder.Packages 
if not f.IsShortcut then 
ProcessFolder f 
end if 
Next 
end sub

 

19、在cmd 命令中 通过sqlplus 登录时，提示不是内部或外部命令，也不是可运行的程序或批处理文件
查看 path环境变量里有没有配置oracle，没的话 将 bin 路径添加进去 （F:\oracle\product\10.2.0\db_1\BIN）

20、oracle远程连接服务器出现 ORA-12170 TNS连接超时 解决办法
关闭防火墙即可

 

21、oralce卸载步骤：
1、停止服务
2、卸载产品
3、删除变量
4、删除注册表
HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\[Oracle*]
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application\[Oracle*]
5、重启，然后删除相关文件夹

 

22、SQL Server 2000/2005中可以快速压缩日志log文件 PropertyManagementCenterIMS 为数据库名 
DUMP TRANSACTION PropertyManagementCenterIMS WITH NO_LOG
BACKUP LOG PropertyManagementCenterIMS WITH NO_LOG
DBCC SHRINKDATABASE(PropertyManagementCenterIMS )

sql server 2008 压缩方法
--查询当前数据库的名称和日志名称 注意 这个和物理文件名可以是不同的 注意
select * from sys.database_files

USE master
ALTER DATABASE PropertyManagementCenterIMS SET RECOVERY SIMPLE WITH NO_WAIT
ALTER DATABASE PropertyManagementCenterIMS SET RECOVERY SIMPLE --简单模式
USE PropertyManagementCenterIMS
DBCC SHRINKFILE ('PropertyManagementCenterIMS_log' , 1, TRUNCATEONLY) --压缩成1M
USE master
ALTER DATABASE PropertyManagementCenterIMS SET RECOVERY FULL WITH NO_WAIT
ALTER DATABASE PropertyManagementCenterIMS SET RECOVERY FULL --还原为完全模式

 

23、oracle 中表被delete 后，空间并没有释放，释放表空间方式，表空间释放后，不能再闪回---- Oracle 10g新增功能
alter table tpr_ydhz enable row movement ;

alter table tpr_ydhz shrink space; --释放空间
查询表大小 select segment_name,bytes,BLOCKS from user_segments where segment_name='TPR_YDHZ'

 

24、闪回表 flashback table TPR_YDHZ to before drop [rename to 表]
闪回删除，即提交后闪回 
alter table tpr_ydhz enable row movement 
flashback table tpr_ydhz to timestamp to_date('2015-01-16 09:05:30','yyyy-mm-dd hh24:mi:ss')

25、创建表时，给主键创建的唯一索引指定索引表空间
create table tmp3( 
id number,
MON VARCHAR2(6) , 
IDNO VARCHAR2(10) , 
constraint pk_tmp primary key (id) 
using index tablespace index_stat
) ;

 

26、分析表 字段 索引 数据字典或视图时得到表的最新信息 比如 行数 数据块 （传统方式）
分析表 analyze table my_table compute statistics; 
等价
analyze table my_table compute statistics for table for all indexes for all columns;

也可删除分析数据
analyze table my_table delete statistics;

call dbms_stats.gather_table_stats('SCOTT','EMP')

 

27、删除重复数据
delete from emp1 e where e.rowid > (select min(x.rowid) from emp1 x where x.empno = e.empno);

DELETE FROM TEST1 A WHERE EXISTS(SELECT 1 FROM TEST1 B WHERE A.EMPNO=B.EMPNO AND A.ROWID>B.ROWID)

 

28、查询低效 SQL 
SELECT EXECUTIONS , DISK_READS, BUFFER_GETS, 
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio, 
ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run, 
SQL_TEXT 
FROM V$SQLAREA 
WHERE EXECUTIONS>0 
AND BUFFER_GETS > 0 
AND (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8 
ORDER BY 4 DESC;

 

29、建立本地分区索引
create index local_index_range_example_id onrange_example(id) local;

create index gidx_range_exampel_id onrange_example(id)
建立全局分区索引
GLOBAL partition by range(id)

(

part_01 values less than(1000),

part_02 values less than(MAXVALUE)

);

30、1、查询Oracle会话的方法
select * from v$session
2、修改Oracle最大连接数的方法 
a、以sysdba身份登陆PL/SQL 或者 Worksheet
b、查询目前连接数
show parameter processes;
c、更改系统连接数
alter system set processes=300 scope=spfile;
d、创建pfile
create pfile from spfile;
e、重启Oracle服务或重启Oracle服务器

31、多列降序排序
SELECT * FROM tpr_cwny WHERE tzid=2 ORDER BY nf DESC,yf DESC

 

32、 创建一个job

var job_num number;
begin
dbms_job.submit(:job_num,'test_proc;',SYSDATE,'sysdate+2/24/60'); --第2分钟执行一次
end;

删除一个job 
exec dbms_job.remove(81);exec dbms_job.remove(81); exec dbms_job.remove(81);
这里的数字81是对应DBA_JOBS表中当前要删除的JOB记录所在行的JOB字段的值；

SELECT TRUNC(sysdate) +1 +2/(24)FROM dual; --每天的凌晨2点执行
SELECT TRUNC(next_day(sysdate,2))+2/24 FROM dual; --每周一凌晨2点执行
SELECT TRUNC(LAST_DAY(SYSDATE))+1+2/24 FROM dual; --每月1号凌晨2点执行
SELECT TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 2/24 FROM dual; --每季度的第一天凌晨2点执行
SELECT ADD_MONTHS(trunc(sysdate,'yyyy'),6)+2/24 FROM dual; --每年7月1日和1月1日凌晨2点
SELECT ADD_MONTHS(trunc(sysdate,'yyyy'),12)+2/24 FROM dual; --每年1月1日凌晨2点
SELECT Sysdate + 5 / (24*60) FROM dual; --每5分钟执行一次

 

33、查询锁表，kill 进程
SELECT object_name as 对象名称,s.sid,s.serial#,p.spid as 系统进程号
from v$locked_object l , dba_objects o , v$session s , v$process p
where l.object_id=o.object_id and l.session_id=s.sid and s.paddr=p.addr;

alter system kill session 'sid,serial#';

34、 case when exists

SELECT CASE
WHEN EXISTS (SELECT 1 FROM tpr_dwxx WHERE ID=10000016) THEN
1 ELSE 0 END
FROM DUAL;

 

35、
insert all结构的作用是想几个表中同时插入数据。
举个例子：

insert all 
into test1 values(no,name) 
into test2 values(no,sal) 
select empno no,ename name,sal from scott.emp;

36、returning into
returning into 语句用于执行完语句后返回的值，具体返回执行之前或者之后的结果，如下所述：
delete语句的returning into语句返回的是delete之前的结果；
insert语句的returning into语句返回的是insert之后的结果。
update语句的returning into语句返回的是update之后的结果。
returning into 语句与return通用
insert into values语句支持returning语句，insert into select语句不支持。
merge语句不支持returning语句。

例：如下的语句都在存储中，v_age为声明的一个变量
insert into t_test values (1, 'zhangsan','27') returning age into v_age; 
update t_test set age = '28' returning age into v_age; 
delete t_test returning age into v_age; 

 

37、查询死锁及kill 
SELECT l.session_id sid, 
s.serial#, 
l.locked_mode 锁模式, 
l.oracle_username 登录用户, 
l.os_user_name 登录机器用户名, 
s.machine 机器名, 
s.terminal 终端用户名, 
o.object_name 被锁对象名, 
s.logon_time 登录数据库时间 
FROM v$locked_object l, all_objects o, v$session s 
WHERE l.object_id = o.object_id 
AND l.session_id = s.sid 
ORDER BY sid, s.serial#;

模板：alter system kill session 'sid,serial#' immediate;
实例：alter system kill session '145,761' immediate;

 

38、like 查询下线线开头的字符
SELECT * FROM sky.test2 WHERE NAME LIKE '\_%' ESCAPE '\';

 

39、查回滚段
select rownum, sys.dba_rollback_segs.segment_name Name, v$rollstat.extents
Extents, v$rollstat.rssize Size_in_Bytes, v$rollstat.xacts XActs,
v$rollstat.gets Gets, v$rollstat.waits Waits, v$rollstat.writes Writes,
sys.dba_rollback_segs.status status from v$rollstat, sys.dba_rollback_segs,
v$rollname where v$rollname.name(+) = sys.dba_rollback_segs.segment_name and
v$rollstat.usn (+) = v$rollname.usn order by rownum

 

40、通过top sql找出有问题的sql语句
select * from (select * from v$sqlstats order by DISK_READS desc) where rownum<=10;
还可以对ELAPSED_TIME,AVG_HARD_PARSE_TIME等排序