ORACLE数据库DBA运维常用语句脚本

一个牛逼的程序员，都是玩linux系统的，linux系统也有比windows更好的权限机制，故大部分公司都是在linux上部署oracle，本文主要以linux系统(CentOS-6.x)为基础与众多小伙伴进行探讨。当然windows系统脚本命令也与这差不多

楼主以前在税务方面做了快一年的数据库运维，大数据平台hivesql这些也在从事着，加上大学期间所学的sql server，算是对入门学习数据库有了自己的心得，下面这些是我积攒下来的一些数据库基础用法。

-- 查询锁进程，解锁
SELECT
    L.SESSION_ID,
    S.SERIAL#,
    L.LOCKED_MODE AS 锁模式,
    L.ORACLE_USERNAME AS 所有者,
    L.OS_USER_NAME AS 登录系统用户名,
    S.MACHINE AS 系统名,
    S.TERMINAL AS 终端用户名,
    O.OBJECT_NAME AS 被锁表对象名,
    S.LOGON_TIME AS 登录数据库时间
FROM V$LOCKED_OBJECT L
    INNER JOIN ALL_OBJECTS O ON O.OBJECT_ID = L.OBJECT_ID
    INNER JOIN V$SESSION S ON S.SID = L.SESSION_ID
WHERE 1 = 1;

ALTER SYSTEM KILL SESSION 'SESSION_ID, SERIAL#';


-- 修改列类型
-- 同类型转换/字段值为空的情况下
alter table {TABLE_NAME} modify	{COLUMN_NAME} decimal(18,2)	default 0 not null;
-- 跨类型转换，如varchar转0
update {TABLE_NAME}	set {COLUMN_NAME} = '0' where {COLUMN_NAME} = ' ';	
alter table {TABLE_NAME} add {COLUMN_NAME}_BAK decimal(18,2) default 0 not null ; 
update {TABLE_NAME}	set {COLUMN_NAME}_BAK = to_number({COLUMN_NAME}); 
alter table {TABLE_NAME} drop column {COLUMN_NAME};
alter table {TABLE_NAME} rename column {COLUMN_NAME}_BAK to {COLUMN_NAME};
comment on column tinf_fundarchives_jy.{COLUMN_NAME} is 'XXXXX';

 du -h --max-depth=1       --查看linux系统当前目录大小，很实用的，可以找到是哪个文件占用了内存

 service iptables stop/start    --关闭/开启防火墙，因为pl/sql和其他的远程都需要linux防火墙的放行，所以测试时可以关闭防火墙，当然正式环境最好只是开放端口而不是全部把防火墙给关掉

--启动oracle的三个步骤
--切换到oracle用户下，启动监听
lsnrctl start   

sqlplus  / as sysdba   --DBA用户进入到oracle ，或者其他具有dba权限的用户也可以

startup;       --启动oracle     

--创建表空间，创建表用户并指定表空间，赋予表用户权限
create tablespace TABLEPLACE_NAME datafile '/oracle/TABLEPLACE_NAME' size 100m autoextend on next 50m maxsize unlimited;

create user USER_NAME identified by PASSWORD default tablespace TABLEPLACE_NAME;

grant connect,resource,dba to USER_NAME;   


--删除用户和表空间，先删除用户再删除表空间
drop user USER_NAME cascade;

drop tablespace TABLEPLACE_NAME including contents and datafiles; 

--all_tables ,all_col_comments,all_users,all_table_columns...
--这些all视图是oracle很棒的总结查询
--譬如你只记得一个表的部分表名，你就可以这样查询该表

select * from all_tables a where a.table_name like '%KG%DZ%';

--ORACLE的数据导入导出
--一般情况下 用pl/sql自带的导入导出即可比较方便和图形化操作可视化较高，具体可见我的另一篇博客
--但是对于数据量很大的操作，最好是通过dmp文件导入导出

--导出:exp

exp USER/PASSWORD@ORACLE_SID file=/home/oracle/daochu.dmp owner=(USER1,USER2)  
exp USER/PASSWORD@ORACLE_SID file=/home/oracle/daochu.dmp tables=(table_name)
exp USER/PASSWORD@ORACLE_SID file=/home/oracle/daochu.dmp tables=(table_name1,table_name2...)  query=\"where TABLE_COLUMN1 >to_date('xxxx-xx-xx')and TABLE_COLUMN2 like '%%'\"  
--注释：linux系统要加转义

--导入:imp

--一般导入时目标库即被导入库需要配置与导出库相同的表空间
--看导入日志，需要配置什么表空间就直接创建同名表空间即可，oracle会自动寻找该表空间
--此外表空间最大为32G，如果一个表空间被占满，无法扩展时，可以创造一个表空间（名字任取）关联该表空间即可

alter tablespace TABLEPLACE_NAME add datafile '/home/oracle/TABLEPLACE_NAME' SIZE 100M

AUTOEXTEND ON NEXT 50M


--开始导入

imp USER/PASSWORD@ORACLE_SID file=/home/oracle/daochu.dmp full=y  ignore=y 
--注释:ignore代表了即使导入库有该表，不报错，继续导入  
imp USER/PASSWORD@ORACLE_SID file=/home/oracle/daochu.dmp fromuser=USER1 touser=USER2 
--注释：这样可以把不同用户下的表都放在同一用户下

--查看表空间实际占用内存量，从而压缩腾出内存

select file#, name from v$datafile;
select (select max(block_id) from dba_extents where file_id=8)* 8 / 1024 from dual;
alter database datafile '/oracletest/TABLEPLACE_NAME' resize 28200m;

--查看用户下表的数量条数
select count(1) from all_tables where table_name IN ('')

--查看表下数据量，初识单位值是byte，除以/1024的数量依次对应K,M,G,T
select num_rows * avg_row_len/1024/1024 from all_tables where table_name IN('')  

--开并行，加快查询速度，一般推荐不超过20，不然可能会搞垮数据库，导致数据库宕机
select /*+ PARALLEL(10)*/ * form 。。。 

//小技巧

1.表名后面加别名可以方面记录和书写

2.pl/sql可以设置快速补全，譬如sf=select * from，ii=insert into

3.从内存优化层面来讲，语句脚本大写可以降低运行压力，因为oracle运行时要把小写转化为大写，推荐两个函数upper，lower很实用

4.能用join和union的就不要用子查询，嵌套查询

5.几个常用的子查询结构

select* from table a where a.id in (select b.id from table2 b);

select (select * from table2) from table1; --理论上可以嵌套嵌套再嵌套，但是为何要这么傻呢

6.必会的语法

case when .. and .. then .. end as..

7.拼

select '这是一个来自'|| a.name||'的博客' from table1 a ;

8. in,like,_,% ,这几个很实用。

/**很多初学者分不清甚至不会用join和union，这个多测试几张表就行了

join与union都是用来关联查询的，举个栗子，你要查的内容不只在一张表上，在两张表甚至多张表里，这个时候就需要

用join和union来关联了。

join是列关联，你想要的内容是三列，需要第一张表的两列和另外一张表的一列，这个时候就用join

join比较灵活，可以left join ，right join，inner join ，all join分别对应左关联，右关联，内关联和全关联，左关联就是我

这一列有多少行，你也要多少行，不够空格来凑，其他类似，可以多多测试掌握。

*/

--语法：

select * from table1 a left join table2 b on a.id=b.id where a.id>0;

/**union是行关联，union分为union和all union，我以前面试时就有问过这两个的区别，就是前者是返回不重复项，后者是

全部返回包括重复项，这个从数据库运行优化方面来说，union all最好的，占用数据库内存少一点，但是还是要根据实际情况

需要注意的是union 上下需要一一对应，包括数据类型和行的数量,union执行的是乘积查询，即笛卡尔积，这一点需要注意。

*/

语法：

select a.id,a.name from table1 union select b.id,b.name from table2;

用union也可以快速配置脚本，比notepad的一键替换好多了，灵活运用！

最后，最后一点很重要的事情，各位DBA大神在执行drop，delete这些敏感操作时一定要记得备份和加where条件，

不然真的就：程序员的自我修养---从删库到跑路

OGG的参考部署配置可查看楼主的另外一篇文字