oracle数据库基础知识
-
SQL*PLUS
-
SQL*PLUS简介
(或 称sqlplus)里除了我们经常使用的sql语句外，还有另外一些命令，它们通过对标题、列标题、页宽、页长以及时间等参数的格式化操作来控制sql语 句的输出的内容和格式，常见的比如"set timing on”,"set heading off”等。
-
基本的SqlPlus命令
remark 注释,set headsep 标题换行,ttitle 设置输出页的头标题,btitle 设置输出页的尾标题,column 对sql语句中的列进行格式化处理,break on 通知sqlplus在输出结果中插入空格,compute sum 通知sqlplus计算小计,set linesize 设置sqlplus输出的最大行宽,set pagesize 设置页面的最大行数,set newpage 设置页面之间的空行数,spool sqlplus屏幕的文件输入输出命令,/**/ 注释，同remark,-- 双杠，注释，同remark,set pause sqlplus屏幕输出结果时在页面之间停顿,save 保存当前session最近的sql语句至指定的文件中,host 返回到操作系统环境，类似!,start或@,执行文件中的命令,edit 使用自定义的编辑器编辑指定文件,define_editor 自定义sqlplus里的编辑器,exit或quit 退出sqlplus
- 操作系统和sqlplus命名行切换：Sqlplus usename/password
SQL>! --输入’!’号表示切换到操作系统
[oracle@webhost]exit --输入exit表示切换到sqlplus命令行
SQL>spool /home/oreacle/tem/tem.txt --表示下面操作输出到文件tem.txt
SQL>spool off --关闭输出定向
- 命令实例举证
- 1. 执行一个sql脚本（test.sql）：start g:/ks.sql或者@g:/ks.sql，重新运行上一次命令输入’/’。
- set pagesize n: 分页参数，最大值可以为50000。0表示不分页。
- set feedback off: 不显示本次sql命令处理的记录条数。
- set echo off 则只显示命令执行的结果，而不显示出命令本身。
- set termout off 显示脚本中的命令的执行结果，缺省为on
- set colsep '| '; 域输出分隔符(每个字段分隔符以’|’分隔)。
- create syno NOTICE_PUBLISH for fund.NOTICE_PUBLISH;--创建同义词
- drop synonym NOTICE_PUBLISH; --删除同义词
- desc accmsg.NOTICE_PUBLISH--查看表结构
- show user--显示当前用户
- Ø sql查询效率
- 按索引查询：例如索引name为：IDX_TB_INT_TXN_LOG
select /*+ index(a IDX_TB_INT_TXN_LOG) */ * from cpab.tb_int_txn_log_hist where 条件。
- 左连接和右连接：“（+）”链接符在哪个表的字段上就链接哪个表。例如有a和b两张表。
Setlect * from a,b where a.column1 = b. column1(+).表示链接b表
Setlect * from a,b where a.column1(+) = b. column1(+).表示链接a表
- from table where tran_dt between ‘20151209’ and ‘20151211’;(包括20151209和20151211这两天的数据)
- Ø 格式转换相关语句to
- 1. 日期格式转换：to_date(‘20151116182211’,’yyyymmddhh24mis’).to_char(sysdate,’ yyyymmddhh24mis’).
- 2. 将金额tran_at设置为15位长度和两位小数点,不足15用空格补齐
Select lpad(to_char(tran_at,’fm99999999990.00’),15,’ ’) from tb_table;
- 3. 将字符串转换为数字：前提条件字符串不能包含数字以外的其他字符：to_number(str_cloum)
- 4.
- Ø 查询排序和分组: group by、order by、having，intersect
- 升序排列：Order by tran_dt，降序排列：Order by tran_dt desc;
- select tran_dt,tran_cd,count(!),sum(tran_at) from table where tran_dt > ‘20151201’ group by tran_dt having count(1) > 1 and sum(tran_at) > 100;
- intersect: 取交集
- Ø 查询筛选:decode,case
- decode(tran_dt,’811015’,’银行缴费’,’831011’,’电力扣费’,’其他’);
- case when tran_cd = ‘811015’ then ‘银行缴费’ else ‘其他’
- Ø ORACLE使用函数对二进制、十进制、十六进制数互相转换
- 十进制转换为十六进制：select to_char(53435,'XXXXXX') from dual;
- 十六进制转换成十进制：select to_number('D0BB','xxxxxx') from dual;
- 将ASCII码（十进制的）转换成汉字：select chr(53435) from dual;
- 将汉字转换为十进制的ASCII码：select ascii('谢') from dual;
表空间信息查询
--1、查看表空间的名称及大小
SELECT t.tablespace_name, round(SUM(bytes / (1024 * 1024)), 0) ts_size
FROM dba_tablespaces t, dba_data_files d
WHERE t.tablespace_name = d.tablespace_name
GROUP BY t.tablespace_name;
--2、查看表空间物理文件的名称及大小
SELECT tablespace_name,
file_id,
file_name,
round(bytes / (1024 * 1024), 0) total_space
FROM dba_data_files
ORDER BY tablespace_name;
--3、查看回滚段名称及大小
SELECT segment_name,
tablespace_name,
r.status,
(initial_extent / 1024) initialextent,
(next_extent / 1024) nextextent,
max_extents,
v.curext curextent
FROM dba_rollback_segs r, v$rollstat v
WHERE r.segment_id = v.usn(+)
ORDER BY segment_name;
--4、查看控制文件
SELECT NAME FROM v$controlfile;
--5、查看日志文件
SELECT MEMBER FROM v$logfile;
--6、查看表空间的使用情况
SELECT SUM(bytes) / (1024 * 1024) AS free_space, tablespace_name
FROM dba_free_space
GROUP BY tablespace_name;
SELECT a.tablespace_name,
a.bytes total,
b.bytes used,
c.bytes free,
(b.bytes * 100) / a.bytes "% USED ",
(c.bytes * 100) / a.bytes "% FREE "
FROM sys.sm$ts_avail a, sys.sm$ts_used b, sys.sm$ts_free c
WHERE a.tablespace_name = b.tablespace_name
AND a.tablespace_name = c.tablespace_name;
--7、查看数据库库对象
SELECT owner, object_type, status, COUNT(*) count#
FROM all_objects
GROUP BY owner, object_type, status;
--8、查看数据库的版本
SELECT version
FROM product_component_version
WHERE substr(product, 1, 6) = 'Oracle';
--9、查看数据库的创建日期和归档方式
SELECT created, log_mode, log_mode FROM v$database;
--1G=1024MB
--1M=1024KB
--1K=1024Bytes
--1M=11048576Bytes
--1G=1024*11048576Bytes=11313741824Bytes
SELECT a.tablespace_name "表空间名",
total "表空间大小",
free "表空间剩余大小",
(total - free) "表空间使用大小",
total / (1024 * 1024 * 1024) "表空间大小(G)",
free / (1024 * 1024 * 1024) "表空间剩余大小(G)",
(total - free) / (1024 * 1024 * 1024) "表空间使用大小(G)",
round((total - free) / total, 4) * 100 "使用率 %"
FROM (SELECT tablespace_name, SUM(bytes) free
FROM dba_free_space
GROUP BY tablespace_name) a,
(SELECT tablespace_name, SUM(bytes) total
FROM dba_data_files
GROUP BY tablespace_name) b
WHERE a.tablespace_name = b.tablespace_name
Oralce扩容操作步骤
一、服务器重启
运维负责linux的启动
二、检查服务启动是否完整
1、oracle服务是否启动
2、oracle侦听服务是否启动
su - oracle(切换到oracle用户)
lsnrctl start(启动监听
linux中启动oracle主要分两步：一个是启动监听器，一个是启动实例
(1)secureCRT连接到数据库所在的linux机器，切换到oracle用户模式下
[root@nstlbeta ~]# su - oracle
(2)[oracle@nstlbeta bin]$ sqlplus /nolog //登录sqlplus
SQL> connect /as sysdba //连接oracle
SQL> startup //起动数据库
SQL> exit //退出sqlplus ，起动监听
(另外的方法）
su - oracle
就是进入到oracle的安装目录的bin下，执行dbstart
(3)[oracle@nstlbeta bin]$ cd $ORACLE_HOME/bin //进入oracle安装目录
[oracle@nstlbeta bin]$ lsnrctl start //起动监听
3、weblogic服务是否启动
4、定时启动weblogic服务是否启动
三、项目数据备份：
1、先在ssh工具上，用oracle用户登录；
2、查oracle实例:
[oracle@webhost ~]$ echo $ORACLE_SID
eg：hldb
3、执行导出语句：
exp user/psw@sid file=路径/文件名.dmp log=xxx.log
4、压缩文件
gzip 文件名.dmp
先打包，再压缩
也可以使用 [root@linux ~]# tar -zcvf /tmp/etc.tar.gz /etc <==打包后，以 gzip 压缩进行压缩
eg: [oracle@webhost ~]$ exp interest/interest@hldb file=/home/oracle/interest20170118.dmp
四、设置JVM内存配置
1、找到配置文件，在域下面，修改setDomainEnv.sh文件中关于内存的配置
2、备份该文件
cp setDomainEnv.sh setDomainEnv.sh.bak20170118
3、修改配置值
如下参数都需要修改：
-Xms512m -Xmx512m
-XX:PermSize=256m
-XX:MaxPermSize=256m
目前服务器扩容到16G，则初步设置
-Xms2048m -Xmx2048m
-XX:PermSize=512m
-XX:MaxPermSize=512m
4、停weblogic服务
5、启weblogic服务
五、修改住房维修基金广宁的用户对应的机构代码
六、公积金项目换版
七、附件、
JVM的内存限制
SUN 1.5.x 1492 1520
SUN 1.5.5(Linux) 2634 2660
SUN 1.4.2 1564 1564
SUN 1.4.2(Linux) 1900 1260
IBM 1.4.2(Linux) 2047 N/A
BEA JRockit 1.5 (U3) 1909 1902
linux 关机
shutdown -h now
使用数据库系统管理用户登录：sqlplus / as sysdba
关闭数据库：shutdown immediate(安全性退出) abort（强制性退出）
启动数据库：startup
