orcle常用命令

orcle相关命令收集


1，系统与登录
1.1 用管理员登陆：/as sysdba
连接用户 conn name/password；

1.2 查询数据库版本
select * from v$version;

1.3 查看用户空间下所有的表
-- connect xx/xx 切换到这个用户
select * from tab;

1.4 sid相关,查询数据库的sid show parameter instance_name;
1.4.1 查看sid
select instance_name from v$instance;
1.4.2 解释
sid是创建数据库时命名的

2，用户名与密码
2.1 更改用户密码
alter user name identified by password；
alter user exptest identified by exptest;
2.2 查询用户
select username,password from dba_users;

2.3 创建用户
create user coco identified by coco;
create user exptest identified by D33gw1TYs;

3，授权
create user coco identified by coco;
alter user coco default tablespace oa_data;
alter user coco quota unlimited on oa_data;
grant create session to coco;
grant create table to coco;
grant create tablespace to coco;
grant create view to coco;
grant alter tablespace to coco;
grant select ,insert(sno,cno),update on sscc to coco--为用户对象付特权
3.1 管理员权限
grant dba to coco;
--Revoke dba from coco;

4， 表空间创建
4.1 创建用户给默认表空间：
create user username identified by password default tablespace user_data;
创建表空间：
create tablespace OA_DATA datafile 'D:\data\oracle\oa_data.dbf' size 50m autoextend on
next 50m maxsize 512m extent management local;

4.2 为用户指定表空间
alter user coco default tablespace OA_DATA;

4.3 解除锁定
alter user ems account unlock

4.4 ORA-1658 无法为表空间的段创建initial区

5， 查询所有空间
5.1 select tablespace_name from dba_tablespaces;
5.2 修改用户默认表空间 alter user coco default tablespace oatest;

6，merge
6.1 merge into 有则更新，无则插入
MERGE INTO 的用途
MERGE INTO 是Oracle 9i以后才出现的新的功能。那这个功能 是什么呢？
简单来说，就是insert or update
6.2 语法
MERGE [INTO] [schema.]table [alias]
USING {[schema.]table|views|query} [alias]
ON {condition}
WHEN MATCHED THEN UPDATE SET {clause}
WHEN NOT MATCHED THEN INSERT VALUES {clause}
6.2.1 for example
merge into vv
using(select field from xx)
on(field = xx)
when matched then
update set xx...
6.2.1.1 实例
merge into tf_imei_separate_month_new t
using(select r.regionname region, e.custid custId from cm_cu_ecustomer e, cm_cu_region r
where e.logicregionid= r.regionid) v
on(t.eccust_id = v.custId)
when matched then
update set t.ctiy_belong = v.region;

7， 查询用户自己所在的表空间select username,default_tablespace from user_users;
7.1查询用户权限
select * from dba_sys_privs;
select * from user_sys_privs;

7.2查看角色(只能查看登

陆用户拥有的角色)所包含的权限
select * from role_sys_privs;

7.3查看用户对象权限：
select * from dba_tab_privs;
select * from all_tab_privs;
select * from user_tab_privs;

7.4查看所有角色：
select * from dba_roles;

8, union
8.1 概述 UNION和UNION ALL关键字都是将结果集合并为一个，但这两者从使用和效率上来说都有所不同。
8.1.1 对重复结果的处理：
UNION 会筛选掉重复的记录
Union All 不会去除重复记录
8.1.2 对排序的处理：
Union 将会按照字段的顺序进行排序；
UNION ALL 简单的将两个结果合并后就返回。
从效率上说，UNION ALL 要比UNION快很多，所以，如果可以确认合并的两个结果集中不包含重复数据且不需要排序时的话，那么就使用UNION ALL。

8.1.3 select 2 from dual union select 1 from dual

9，column字段
9.1 添加 alter table emp4 add test varchar2(10);
9.2 修改 alter table emp4 modify test varchar2(20);
9.3 删除 alter table tf_imei_separate_month_user drop column week_range;

10，判断连接
telnet 127.0.0.1 1521
ping 10.132.21.18

11，登陆远程sqlplus
conn exptest/cc@10.132.21.18/oatest;
conn coco/coco@192.168.1.7/orcl;
//10.132.21.18:1521:oatest

12，创建数据库之间的连接dblink
create database link linkexp
connect to exptest identified by D33gw1TYs
using '(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.132.21.18)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = oatest)
)
)';

13，删除dblink
Drop public DATABASE LINK linkme;
Drop DATABASE LINK linkme;

14，tnsnames.ora配置
D:\app\Administrator\product\11.2.0\client_1\network\admin\tnsnames.ora,
# tnsnames.ora Network Configuration File: D:\app\Administrator\product\11.2.0\dbhome_2\NETWORK\ADMIN\tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))


ORCL =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
)
(CONNECT_DATA =
(SID = orcl)
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)

14.2——ORA-01034和ORA-27101
出现ORA-01034和ORA-27101的原因是多方面的：主要是oracle当前的服务不可用，shared memory realm does not exist，是因为oracle没有启动或没有正常启动，共享内存并没有分配给当前实例.所以，通过设置实例名，再用操作系统身份验证的方式，启动数据库。这样数据库就正常启动了，就不会报ORA-01034和ORA-27101两个启动异常了。还有就是可能是登录数据库后，不正常的退出，比如直接关掉窗口，而这时数据库里有未完成的动作。

15，oracle卸载
运行regedit，删除HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services，滚动

这个列表，删除所有Oracle入口。
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\Eventlog\Application， 删除所有Oracle入口。

16，特殊命收集
16.1 修改成功数
sql%rowcount 获得修改成功的行数
16.2 (+)
SELECT a.*, b.* from a(+) = b就是一个右连接
等同于select a.*, b.* from a right join b
SELECT a.*, b.* from a = b(+)就是一个左连接
等同于select a.*, b.* from a left join b
(+)"所在位置的另一侧为连接的方向,通常将全量集合与部分集合连接时，在部分集合的列后面带上（+）.没有匹配时，也要显示出一个null的效果

16.3 explain执行计划
explain plan for select * from dual
select * from table(dbms_xplan.display);
16.3.1 显示执行计划
SET AUTOTRACE TRACEONLY; -- 只显示执行计划，不显示结果集

17，迁移数据库
当本地用户名与服务器端不一致时，需要在本地建立一个相同的用户
17.1导入数据库
@d:/EE.sql
PL/SQL中直接写@



19，共享命令，安装12c时用到：net share nu=c:\ /grant:everyone,full

20，导出命令exp coco/coco@orcl file=d:\exp.dmp full=y;
exp exptest/exptest@localhost:1521/orcl file=d:\exp.dmp full=y;
exp ems/D33gw1TYs@10.132.21.18/oatest file=d:\exp.dmp;
exp oapre/Cq#1988@10.132.21.18/oapre file=d:\oapre.dmp;
exp iot_app/iot_app@192.168.202.253/iot file=d:\data\oracle\iot_app.dmp;

url="jdbc:oracle:thin:@10.132.21.18:1521:oapre"
username="oapre"
password="Cq#1988"

exp iot_appaa/iot_app@192.168.156.23/bjdb file=d:\data\iot\bjdb.dmp;
[ORA-00904]根据网上的资料和总结，分析可能是本地使用的exp导出工具的版本与数据库服务器端exp版本不一致导致

导入命令：
imp coco/coco file=uat.dmp fromuser=oapre touser=coco tablespaces=users;
imp ems/ems file=ems.dmp fromuser=ems touser=ems tablespaces=oa_data;


20.1，导出指定表：exp 用户名/密码@数据库名 file=c:\filename.dmp tables=(table1,table2).
必须在括号中列出所有表名

20.2，导出特定用户所有表：exp 用户名/密码@数据库名 file=c:\filename.dmp owner=用户名

21，查询相关：查询有clob字段的表
select distinct('TABLE "'||a.OWNER ||'"."'||a.TABLE_NAME||'"') from sys.all_tab_columns a
where a.OWNER = 'EXPTEST' and a.TABLE_NAME not in (
select t.TABLE_NAME from sys.all_tab_columns t
where t.OWNER = 'EXPTEST' and t.DATA_TYPE in ('CLOB','BLOB')
);
21.1,oracle查询字符长度：length()
21.2,sqlserver查询字符长度：len():
select * from t_department where len(departMentCostCode) > 0;

21.3,查询重复 select msisdn,count(1) from cm_subs_statuschg group by msisdn having count(1)>1
a单字段： select upper(code),count(1) from t_user group by upper(code) having count(1)>1
b全字段： select * from login where Id in (select Id from login group by Id having count

(Id) > 1)
c数字中的空值
select * from table t where t.field is null;

21.4,去重复组合
查询：
select * from t_user where upper(code) in (select upper(code)
from t_user group by upper(code) having count(upper(code)) > 1)
删除：
DELETE from t_user WHERE (upper(code)) IN (
SELECT upper(code) FROM t_user GROUP BY upper(code) HAVING COUNT(upper(code)) > 1
) AND ROWID NOT IN (SELECT MIN(ROWID) FROM t_user
GROUP BY upper(code) HAVING COUNT(*) > 1);

21.5,去密码为空
update t_user t set t.logon_password = '4a7d1ed414474e4033ac29ccb8653d9b' where t.logon_password is null;

21.6,分页查询
select * from (
select rownum as rowno, t.* from FIN_ACCOUNTING_SUBJECT t where rownum <= 2
) account where account.rowno >= 1

21.7,exist与in
select a.moni_id, a.desAddr, a.subject, a.content, a.create_time from moni_result_mail a where exists(select b.create_time from moni_result_mail_his b where a.create_time = b.create_time)
in引导的子句只能返回一个字段
exists强调的是是否返回结果集，不要求知道返回什么
EXISTS与IN,意思相同不过语法上有点点区别，IN效率差，应该是不会执行索引的原因

21.8,select mail_list, count(mail_list) from moni_mail_list group by mail_list
查询某个字段中的值重复次数

21.9,查询大于某个日期
SELECT * FROM cm_subs_statuschg t WHERE t.createdate > to_date('2016/9/7 00:00:00', 'YYYY/MM/DD HH24:MI:SS');

22.10,查询多个列
查询字段按照省份进行统计
select t.provinceid province, count(1) total,
(select count(*) from tf_imei_separate_month s where s.feedback is not null and s.provinceid = t.provinceid) feedbackData,
(select count(*) from tf_imei_separate_month s where s.feedback is null and s.provinceid = t.provinceid) notFeedbackData,
(select min(s.first_feedback_time) from tf_imei_separate_month s ) firstFeedTime,
(select max(https://www.wendangku.net/doc/477402594.html,st_feedback_time) from tf_imei_separate_month s ) lastFeedTime
from tf_imei_separate_month t group by t.provinceid


22.11,查询时间类型
select to_date('2005-01-01 13:14:20','yyyy-MM-dd HH24:mi:ss') from dual;
MM和mm被认为是相同的格式代码，所以Oracle的SQL采用了mi代替分钟


22，误操作
22.1删除之前闪回
flashback table sa_db_sysparam to before drop;
22.2指定时间之前闪回
alter table AA enable row movement;
flashback table AA to timestamp to_timestamp('2016-02-18 10:25:21','yyyy-mm-dd hh24:mi:ss');
22.3通过查询闪回
select * from AA as of timestamp TO_TIMESTAMP('2016-02-19 13:36:05', 'yyyy-mm-dd hh24:mi:ss');

23，to_方法
23.1 to_number/to_char从日期中取年份\月份
select to_number(to_char(sysdate,'yyyy')) from dual ——年份
select to_char(sysdate,'yyyy-MM') from dual ——月份

23.2 to_date字符串转化为日期
select to_date(https://www.wendangku.net/doc/477402594.html,st_call_time,

'yyyy-MM-dd hh24:mi:ss') from tf_imei_separate_month t

23.3 拼月份select t.*, to_char(to_date(https://www.wendangku.net/doc/477402594.html,st_call_time, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM') months from tf_imei_separate_month t
23.1 按照月份进行分组
select tt.months, count(tt.months) from (select t.*, to_char(to_date(https://www.wendangku.net/doc/477402594.html,st_call_time, 'yyyy-MM-dd hh24:mi:ss'), 'yyyy-MM') months from tf_imei_separate_month t)
tt group by tt.months

23.4 比较
23.4.1 视图方式
select * from (select import_time, to_char(import_time, 'yyyy-MM') date_month from tf_imei_separate_month_new) t
where t.date_month = '2017-02'
23.4.2 where to_char方式
select import_time from tf_imei_separate_month_new where to_char(import_time, 'yyyy-MM') = '2017-02'
23.5 查询周
select to_char(date'2010-3-9','iw') from dual;
select to_char(to_date('2017-03', 'yyyy-mm'), 'iw') from dual;


24，通过select插入数据
insert into table1(id, dept_id, user_id, role_id) select 1,2,3,https://www.wendangku.net/doc/477402594.html, from table2 ;
insert into t_user_role(id, dept_id, user_id, role_id, company_id) select (1000+t_user.id), t_user.dept_id, t_user.id,'user',t_https://www.wendangku.net/doc/477402594.html,pany_id from t_user where t_user.id > 10;

24.1 SELECT INTO FROM语句
语句形式为
SELECT vale1, value2 into Table2 from Table1
要求
目标表Table2不存在，因为在插入时会自动创建表Table2，并将Table1中指定字段数据复制到Table2中
24.2 update子句
update b set b.newname=（select https://www.wendangku.net/doc/477402594.html, from a where a.id=b.id）
where exist(select 1 from a where a.id=b.id)

25，物化视图：
释义：内存快照，普通视图是一组查询语句集合，调用时本质是一种select过程。物化视图则是一组内存快照，将一段时间的视图数据读取出来放在一个单独的物理空间。

26，SGA：oracle系统全局内存区域。
26.1：共享池是oracle缓存数据的地方

27，表分区关键字
partition by range (PROVINCEID)
(
partition PART100 values less than ('101')
tablespace IOTDATA01
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
),
...
)
27.1 分区时机 Tables greater than 2GB should always be considered for partitioning
27.2 分区类型
>范围分区 range
>范围－哈希复合分区 range-hash
>范围－列表复合分区 range-list
>interval 自动分区
>哈希分区 hash
>列表分区 list


27.3 按时机分区
create table pdba (id number, time date) partition by range (time)
(
partition p1 values less than (to_date('2010-10-1', 'yyyy-mm-dd')),
partition p2 values less than (to_date('2010-11-1', 'yyyy-mm-dd')),
partition p3 values less than (to_date('2010-12-1', 'yyyy-mm-dd')),
partition p4 values less than (maxvalue)
)
27.3.1 按月自动循环分区
PARTITION BY RANGE(SUB_DATE)
INTERVAL(NUMTOYMINTERVAL(1，'MONTH'))
(
PARTITION

P1 VALUES LESS THAN(TO_DATE('2014-05-01','YYYY-MM-DD'))
);
27.5 功能 分区表的优点：
>改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度
>增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用
>维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可
>均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能

27.5.1 查看select TABLE_NAME,PARTITION_NAME,TABLESPACE_NAME from user_tab_partitions where table_name='MONTH_PART';
27.5.2 分区参数
>按周分区：
CREATE TABLE TEST_PART(
TIME_ID NUMBER,
REGION_ID NUMBER,
ORDER_ID NUMBER,
ORDER_DATE DATE
)
INTERVAL ( NUMTODSINTERVAL (7, 'day') )(
PARTITION part1
VALUES LESS THAN (TO_DATE ('2013-06-01', 'YYYY-MM-DD')),
PARTITION part2
VALUES LESS THAN (TO_DATE ('2013-06-08', 'YYYY-MM-DD'))
)
> 按年分区
INTERVAL (NUMTOYMINTERVAL(1,'year')
(PARTITION part1
VALUES LESS THAN (TO_DATE ('2013-06-01', 'YYYY-MM-DD')),
PARTITION part2
VALUES LESS THAN (TO_DATE ('2014-06-01', 'YYYY-MM-DD'))
)
>按日分区
NTERVAL ( NUMTODSINTERVAL (1, 'day') )
(PARTITION part1
VALUES LESS THAN (TO_DATE ('2013-06-01', 'YYYY-MM-DD')),
PARTITION part2
VALUES LESS THAN (TO_DATE ('2013-06-02', 'YYYY-MM-DD'))
)
27.6 分区说明
Tables can be partitioned into up to 1024K-1 separate partitions.
27.6.1 eg
>Tables greater than 2GB should always be considered for partitioning
>Tables containing historical data, in which new data is added into the newest partition.
A typical example is a historical table where only the current month's data is updatable and the other 11 months are read only.

28，索引相关
28.1 create index index_me_id on me(id);
28.2 oracle 在创建主键时，会为库表自动创建索引

29，decode(条件,值1,返回值1,值2,返回值2,...值n,返回值n,缺省值)
29.1 例子
select decode(1, '1', 'one', '2', 'two') from dual
select decode(2, '1', 'one', '2', 'two') from dual
29.2 从结果中查询
select decode(nvl(r.regionname,0),r.regionname,r.regionname,'0') from cm_cu_ecustomer e, cm_cu_region r
where e.logicregionid= r.regionid and e.custid = '100016868100100000'

30，解锁
30.1 用dba权限的用户登陆数据库
查询表 select t.OBJECT_ID from v$locked_object t group by t.OBJECT_ID;
[505470, 1070710]
30.1.1 select t.OBJECT_ID, t.SESSION_ID from v$locked_object t where t.OBJECT_ID = 505470 查询被锁住的会话
30.1.2 select object_name, object_type from dba_objects where object_id 查看被锁住的表

30.1.1 查询sid，serial#
select sid, serial#, machine, program from v$session where sid in (select t.SESSION_ID from v$locked_object t where t.OBJECT_ID

= 1070849)

30.2 kill会话 alter system kill session 'sid,serial#';
alter system kill session '734,65508';
alter system kill session '1040,46925';

30.3 说明
1、select * from v$locked_object 查出被锁定的对象，其中object_id是对象的ID,session_id是被锁定对象有session ID；
2、select object_name, object_type from dba_objects where object_id = 刚才查出来的object_id;（根据v$locked_object里的object_id提出来的）
这样来查被锁定这个对象的名字，如果能确定是哪个TABLE被锁并且要解锁，则再执行
3、select sid, serial#, machine, program from v$session where sid =第1步中查出来的session_id;(是根据v$locked_object对应锁定记录的session_id找出来的) 然后
4、alter system kill session 'sid,serial#';用来杀死这个会话；
以上几个步骤即能解决对象被锁定问题。
30.4 锁定一个表的语句 LOCK TABLE tablename IN EXCLUSIVE MODE;将锁定整个表

31，case when
31.1 语法
SELECT grade, COUNT (CASE WHEN sex = 1 THEN 1
ELSE NULL
END) 男生数,
COUNT (CASE WHEN sex = 2 THEN 1
ELSE NULL
END) 女生数
FROM students GROUP BY grade;
31.1.1 eg
select count(case when PROVINCEID = '200' then 1 else null end) from tf_imei_separate_month_user
31.1.2 统计
select
t.provinceId,
count(https://www.wendangku.net/doc/477402594.html,er_count) total,
count(t.exception_type),
count(t.risklevel_ratio_three) total,
count(case when (t.exception_type is not null and to_char(t.data_time, 'yyyy-MM') = '2017-02')
then t.risklevel_ratio_three else null end)
feedbackcount,
min(t.first_feedback_time),
max(https://www.wendangku.net/doc/477402594.html,st_feedback_time),
to_date('2017-02', 'yyyy-MM')
from tf_imei_separate_month_new t where to_char(t.data_time, 'yyyy-MM') = '2017-02' group by provinceId;