【自用】ORACLE运维指令

基础知识

一、SQL语言分类：
1.DDL（数据定义语言）：  create（创建）    alter（修改）  drop（删除）------------主要是对表操作。
2.DML（数据操作语言）：  insert(插入)    delete(删除)  update(更新)  select(查询) 、select......for update(查询更新)----表中数据进行操作
3.DQL数据查询语言：基本语句、Order by 子句、Group by 分组语句
4.TCL（事务控制语言）：Commit(提交) 、Savepoint(保存) 、rollback(回滚)、
5.DCL（数据控制语言）：Grant(授权)、revoke(撤销)命令。
二、Oracle数据类型：
Char:  字符型（最大长度2000，定长、不足时以空格补充）
Varchar2:字符型 最大长度 4000，变长，实际长度由存储的数据长度决定（与存储的数据长度一致）
Number(x,y):既可以存储浮点型，也可以存储整形，x表示有效位数的最大位数，y表示小数位最大位数。
Date：存储时间类型。默认格式：dd--mm--yy:  天-月-年。
Clob:存储较大的文本，比如存储非结构化XML文档，最大为 4G
Blob:存储二进制对象，如图形、视频、声音等。
Long :存储较长字符串，最大长度为2G
三、表管理：
1.创建表：
Create   table   表名（
字段名称   类型  约束
）
2.修改表结构
2.1、添加列
Alter  table  表名 add( 字段名称  数据类型 )
例子：alter table test_tb add(test_name varchar2(200))
2.2、删除表中一列
Alter   table 表名  set unused  column 列名
例子：alter table test_tb set unused column test_name
一般：不建议删除数据库中的列。
2.3、修改列名
Alter table 表名  rename  column 旧列名 to 新列名
例子：alter table test_tb rename column test_msg to msg
2.4、修改表名：
Rename  表名1 （旧名字）to 表名2（新名字）
例子：rename  test_tb to test_tb1
2.5、修改表字段
Alter   table  表名 modify( 字段名称  新的字段类型 )
例子：alter table test_tb1 modify(msg varchar(4000))
2.6、添加表约束：
Alter table 表名 add Constraint 约束名 约束内容
例子：
alter table test_tb1 add constraint  p_k primary key(test_num)-----主键约束
alter table test_tb1 add constraint c_k check(msg='男' or msg='女')---检查约束
Not null----非空约束
四、数据操作语言：DML
对oracle自带的表进行训练操作
简单查询 ：
Select *|列名|表达式 from 表名 where 条件 order by 列名
1.* 表示所有列
2.列名可以选择若干个表中列名，各个表中列名用逗号分隔。
3.表达式可以是函数，列名，常数等组成表达式。
4.Where子句是查询的条件
5.Order by  要求在查询结果中排序，默认是升序。
例子：
select * from emp order by sal desc（降序）
select * from emp order by sal （升序）
select * from emp order by sal asc(升序)
插入：
Insert into 表名  values(所有字段对应值)；
Insert into 表名（字段名1,字段名称2....） values(对应字段值)
例子：insert into test_tb1(test_num,msg) values(666,'MSG values')
更新：
Update 表名 set 字段名称 =值 where 更新条件
例子：update test_tb1 set msg='hello oracle' where test_num=123
删除：
Delete 表名 where 条件
例子：delete test_tb1 where msg='hello oracle'
Truncate--将表中数据一次性删除
语法： truncate table 表名
Truncate和detele区别：
1、truncate是DDL命令，删除数据不能回复；delete是DML命令，删除数据可以通过数据库的日志文件进行恢复。
2、如果一个表中记录很多，truncate相对delete速度快。
警告：由于truncate命令比较危险，所有在实际开发中，truncate命令慎用。
五、操作符：
算术操作符： + 、 - 、* 、/ （加、减、乘、除）
关系运算和逻辑运算符：
=  等于
> 大于
<>或 != 不等于
<= 小于或等于
<   小于
>= 大于或等于
逻辑运算符：
And  、 or 、 in  、not in
And:且
Or: 或者
Not in 不在...中
In:在.....中
 --加法
例子：SELECT ename,job,(sal+comm) FROM emp;
--减法
例子：SELECT ename,job,(sal-comm) FROM emp;
--双竖线 ||
例子：SELECT (ename||'的工资'||(sal+comm)) FROM emp;

字符串连接操作符： ||  （双竖线）

例子：select (ename||' 的工资是：'||(sal+comm)) from emp

常用操作

一、oralce重启(windows)

  二、oralce重启(linux)

（1） 以oracle身份登录数据库，命令：su -oracle

（2） 进入Sqlplus控制台，命令：sqlplus /nolog

（3） 以系统管理员登录，命令：conn / as sysdba

（4） 启动数据库，命令：startup

（5） 如果是关闭数据库，命令：shutdown immediate

（6） 退出sqlplus控制台，命令：exit

（7） 进入监听器控制台，命令：lsnrctl

（8） 启动监听/停止监听/监听状态，命令：lsnrctl start lsnrctl stop lsnrctl status

（9） 退出监听器控制台，命令：exit

-------------------------------

（10） cmd下修改管理员密码，命令：sqlplus /nolog,conn / as sysdba,alter user sysytem indetified by 新密码

 

三、扩展临时表空间：
方法一、增大临时文件大小： 
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ resize 100m; 
方法二、将临时数据文件设为自动扩展： 
SQL> alter database tempfile ‘/u01/app/oracle/oradata/orcl/temp01.dbf’ autoextend on next 5m maxsize unlimited; 
方法三、向临时表空间中添加数据文件： 
SQL> alter tablespace temp add tempfile ‘/u01/app/oracle/oradata/orcl/temp02.dbf’ size 100m; 

例：

SYSTEM添加数据文件初始2048M，增长200M每次，最大4096M

ALTER TABLESPACE SYSTEM ADD DATAFILE
'/data/app/oracle/oradata/orcl/system02.dbf' SIZE 2048M
AUTOEXTEND ON NEXT 200M MAXSIZE 4096M;

TEMP添加数据文件初始2048M，每次增长200M，无限制最大文件大小

ALTER TEMPORARY TABLESPACE TEMP ADD TEMPFILE
'/data/app/oracle/oradata/orcl/temp02.dbf' SIZE 2048M
AUTOEXTEND ON NEXT 200M MAXSIZE UNLIMITED;

四、表占用空间

select * from sys.USER_SEGMENTS t order by t.BYTES desc 

五、DBlink

create public database link TOJYJ
connect to 用户名 identified by 密码
using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(PROTOCOL = TCP)(HOST = xxx.xxx.xxx.xxx)(PORT = 1521)))(CONNECT_DATA =(SERVICE_NAME = orcl)))';

查询

select * from crm_bd_building@TOJYJ

六、提取指定字段

UPDATE XM_SLJC_KLXX T set T.CSRQ=SUBSTR(T.SFZH,7,8)
WHERE LENGTH(T.SFZH)=18

七、多表关联UPDATE更新字段使用

UPDATE XM_SLJC_BACK_YC T
SET T.XH=(select TT.ZJDM from XM_SLJC_KLXX TT
WHERE T.xsxm=TT.xm and TT.xxmc='XXX小学' and rownum=1)
where exists(select 1 from XM_SLJC_KLXX TT
WHERE T.xsxm=TT.xm and TT.xxmc='XXX小学');

八、长连接设置

 SELECT name, value FROM gv$parameter WHERE name = 'resource_limit';

SQL> ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;

\\  ALTER SYSTEM SET RESOURCE_LIMIT=FALSE;  关闭命令