oracle常用操作方法

---oracle创建数据库，基于plsqldev工具

1、创建表空间，创建空内容的物理文件
create  tablespace db_test --表空间名
datafile 'D:\test.dbf' --物理文件 表空间数据文件存放路径
size 50m  --大小初始值
autoextend on  --自动扩展
next 50m maxsize 20480m  --每次扩展50m，最大为20480m
extent management local;

2、创建用户
create user testdev         --创建用户名 testdev
  identified by "test1234"     --创建密码 test1234
  default tablespace db_test    --表空间  db_test
  temporary tablespace TEMP     --临时表空间（默认的）
  profile DEFAULT               --默认权限（下面给分配）
  quota unlimited on db_test;      --该用户在 db_test 表空间里的配额不限

3、用创建的用户登录，登录后即可新建表等操作

---创建索引
create index index_name on table_name(col_name)

---添加字段
alert table add(col_name col_type default '' not null);\

---查询空表
select distinct table_name from user_tables where num_rows = 0

---查询某个表中的重复数据
select * from table_name where col_name in (select col_name from table_name group by col_name having (col_name） > 1)

--查询被锁的表
SELECT sn.username, m.SID,sn.SERIAL#, m.TYPE,
　　DECODE (m.lmode,
　　　　　　0, 'None',
　　　　　　1, 'Null',
　　　　　　2, 'Row Share',
　　　　　　3, 'Row Excl.',
　　　　　　4, 'Share',
　　　　　　5, 'S/Row Excl.',
　　　　　　6, 'Exclusive',
　　　　　　lmode, LTRIM (TO_CHAR (lmode, '990'))
　　) lmode,
DECODE (m.request,
　　　　0, 'None',
　　　　1, 'Null',
　　　　2, 'Row Share',
　　　　3, 'Row Excl.',
　　　　4, 'Share',
　　　　5, 'S/Row Excl.',
　　　　6, 'Exclusive',
　　　　request, LTRIM (TO_CHAR (m.request, '990'))
　　) request,
　　m.id1,

　　m.id2
　　FROM v$session sn, v$lock m
　　WHERE (sn.SID = m.SID AND m.request != 0) --存在锁请求，即被阻塞
　　　　OR ( sn.SID = m.SID --不存在锁请求，但是锁定的对象被其他会话请求锁定
　　　　　　AND m.request = 0
　　　　　　AND lmode != 4
　　　　　　AND (id1, id2) IN (
　　　　　　SELECT s.id1, s.id2
　　　　　　FROM v$lock s
　　　　　　WHERE request != 0 AND s.id1 = m.id1
　　　　　　AND s.id2 = m.id2)
　　　　)
　　ORDER BY id1, id2, m.request;

---执行解锁
alter system kill session 'sid,SERIAL#';

---delete update 执行很慢的时候，先查询锁定记录，然后删除锁定记录。再删除
SELECT s.sid, s.serial# FROM v$locked_object lo, dba_objects ao, v$session s WHERE ao.object_id = lo.object_id AND lo.session_id = s.sid;
ALTER system KILL session '101,44149';

---树结构查询按层级排序
select t.* from table_name t
start with t.id = 1
connect by t.part_id = prior t.id
order siblings by t.id;

---查看数据库字符集

select * from nls_database_parameters where parameter ='NLS_CHARACTERSET'

---创建触发器将序列中的值赋给插入employee表的行
create replace trigger insert_employee_autoinc
before insert on employee
for each row
begin
select employee_autoinc.nextval into :new.Id from dual;
end insert_employee_autoinc;