学习笔记--Oracle
基本语句
登陆创建用户并设置权限
#sqlplus /nolog
SQL> conn / as sysdba;
SQL>create user username identified by password
SQL> grant dba to username;
SQL> conn username/password
SQL> select * from user_sys_privs;//查看当前用户所有权限
SQL> select * from user_tab_privs;//查看所用用户对表的权限
用户级别：
sys;//系统管理员，拥有最高权限
system;//本地管理员，次高权限
scott;//普通用户，密码默认为tiger,默认未解锁
登陆：
sqlplus / as sysdba;//登陆sys帐户
sqlplus sys as sysdba;//同上
sqlplus scott/tiger;//登陆普通用户scott
管理用户：
create user zhangsan;//在管理员帐户下，创建用户zhangsan
alert user scott identified by tiger;//修改密码
授予权限：
grant create session to zhangsan;//授予zhangsan用户创建session的权限，即登陆权限
grant unlimited tablespace to zhangsan;//授予zhangsan用户使用表空间的权限
grant create table to zhangsan;//授予创建表的权限
grante drop table to zhangsan;//授予删除表的权限
grant insert table to zhangsan;//插入表的权限
grant update table to zhangsan;//修改表的权限
grant all to public;//这条比较重要，授予所有权限(all)给所有用户(public)
grant dba, resource, connect to zhangsan;
权限：
create session
create table
unlimited tablespace
connect
resource
dba
创建角色：
create role myrole;//创建角色
grant create session to myrole;//将创建session的权限授予myrole
grant myrole to zhangsan;//授予zhangsan用户myrole的角色
drop role myrole;删除角色
删除创建数据库
drop user jingyan cascade;
drop tablespace jingyan including contents;
create tablespace jingyan datafile 'D:\oradata\jingyan.DBF' size 200m reuse autoextend on next 50m maxsize 1000m;
create user jingyan identified by 1 default tablespace jingyan;
grant dba, resource, connect to jingyan;
查询当前数据库中表空间及文件路径，数据库用户的默认表空间，表所在的表空间
select tablespace_name, file_name from dba_data_files order by file_name;
select user_id, username, default_tablespace from dba_users order by user_id;
select table_name, tablespace_name from user_tables;
select table_name from all_tables where owner = 'USER'; //查看某用户的所有表名，需要有sysdba权限
查看表空间大小使用情况
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
修改数据库默认表空间
alter database default tablespace test;
创建数据表
create table table_name(column1 type1, column2 type2, ...) tablespace tablespace_name;
或者
create table table_name as select ... from ...;
创建索引
CREATE INDEX 索引名 ON 表名 (列名) TABLESPACE 表空间名;
建表示例：
create temporary tablespace DRGS_TEMP0 TEMPFILE '/data/oradata/oracle/oradata/orcl/DRGS_TEMP0.bdf'size 100m reuse autoextend on next 20m maxsize unlimited;
CREATE TABLESPACE "DRGS2017"
LOGGING
DATAFILE
'/data/oradata/oracle/oradata/orcl/drgs201701.dbf' SIZE 32000M,
'/data/oradata/oracle/oradata/orcl/drgs201702.dbf' SIZE 32000M,
'/data/oradata/oracle/oradata/orcl/drgs201703.dbf' SIZE 32000M,
'/data/oradata/oracle/oradata/orcl/drgs201704.dbf' SIZE 32000M,
'/data/oradata/oracle/oradata/orcl/drgs201705.dbf' SIZE 32000M,
'/data/oradata/oracle/oradata/orcl/drgs201706.dbf' SIZE 32000M
REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;
create user drgsuser2017 identified by drgsuser2017 default tablespace DRGS2017 temporary tablespace DRGS_TEMP0;
grant connect,resource to drgsuser2017;
grant exp_full_database,imp_full_database to drgsuser2017;
grant dba to drgsuser2017;
alter操作
alter table student add(class_id number);
alter table student modify(class_id varchar2(20));
alter table student rename column student_id to id;
alter table student drop column class_id;
alter table student move tablespace users;
导出数据
一、Spool常用的设置
set arraysize 5000; //此参数可提高SPOOL卸载的速度，最大可以设置为5000
set autotrace on; //设置允许对执行的sql进行分析
set colsep ','; //域输出分隔符
set echo off; //显示start启动的脚本中的每个sql命令，缺省为on
set feedback off; //回显本次sql命令处理的记录条数，缺省为on，设置显示“已选择XX行”
set heading off; //输出域标题，字段的名称，缺省为on
SET LINESIZE 2500; //每行允许的最大字符数，设置大些，免得数据被截断，但不宜过大，太大会大大降低导出的速度(注意必须与trimspool结合使用防止导出的文本有太多的尾部空格)
set newpage 1; //设置页与页之间的分隔{1|n|NONE};当值为0时在每页开头有一个小的黑方框;当值为n时在页和页之间隔着n个空行;当为none时，会在页和页之间没有任何间隔；
set newp none; //设置查询出来的数据分多少页显示，如果需要连续的数据，中间不要出现空行就把newp设置为none，这样输出的数据行都是连续的，中间没有空行之类的
set num 18; //设置数字的长度，如果不够大，则用科学记数法显示
set numwidth 12; //输出number类型域长度，缺省为10
SET NULL text; //显示时,用text值代替NULL值
set pagesize 2000; //输出每页行数，页面大小，缺省为24,为了避免分页，可设定为0
set serveroutput on; //设置允许显示输出类似dbms_output;--编写存储过程时,大多会将必要的信息输出；
SET SPACE 0;
set term off; //不在屏幕上输出执行结果
set termout off; //显示脚本中的命令的执行结果，缺省为on
set timing on; //显示每个sql语句花费的执行时间，设置显示“已用时间：XXXX”
set trimout on; //去除标准输出每行的拖尾空格，缺省为off
set trimspool on; //去除重定向（spool）输出每行的拖尾空格，缺省为off
set verify off //是否显示替代变量被替代前后的语句
SET wrap on; //输出行长度大于设置行长度时(用set linesize n命令设置);值为on时,多余的字符另起一行显示,否则多余的字符将被切除，不予显示；
二、Spool 使用方法
说明：spool不但可以把数据导出到txt，也可以导出到csv等；不但可以导数据，也可以导出字符，拼装成sql文件等
1.采用设置分隔符的方法
说明：设定分隔符后，由sqlplus自己使用设定的分隔符对字段进行分割，这种方法不怎么用，在此不详述。
set colsep '|' --设置|为列分隔符
set trimspool on
set linesize 120
set pagesize 2000
set newpage 1
set heading off
set term off
set num 18
set feedback off
spool e:\temp.txt
select * from tablename;
spool off
查看oracle正在运行的sql进程
set linesize 400;
set pagesize 400;
set long 4000;
col SQL_FULLTEXT format a100;
col machine format a25;
col username format a15;
SELECT a.username,a.machine, b.sql_id, b.SQL_FULLTEXT
FROM v$session a, v$sqlarea b
WHERE a.sql_address = b.address
AND a.SQL_HASH_VALUE = b.HASH_VALUE;
特殊的数据表dual
dual表提供了一行一列的数据格式，从而使各种表达式、函数运算在以其为数据源时，能够输出单行单列的形式。
select sysdate from dual;
数据类型
| 类型 | 解释 |
|---|---|
| CHAR | 固定长度字符串，长度1-2000字节，未指定为1字节 |
| VARCHAR2 | 可变长度字符串，长度1-4000个字节，必须指定大小 |
| LONG | 可变长度字符串，最多2GB，长文本信息，一个表只能一列，不能建立索引 |
| NUMBER | 数值型 |
| DATE | 存储表中日期和时间数据 |
| TIMESTAMP | 存储日期、时间和时区信息 |
| RAW | 二进制数据或字节串，长度1-2000字节，应指定大小，可建索引 |
| LONG RAW | 可变长度的二进制数据，最大2GB，限制类似LONG类型 |
| CLOB | 大对象数据类型，存储大量字符数据 |
| BLOB | 存储大量二进制对象 |
| BFILE | 能够将二进制文件存储在数据库外部的操作系统文件中 |
SQL优化
可能引起全表扫描（索引失效）的操作，应尽量避免：
- 在索引列上使用NOT或者“<>”;
- 对索引列使用函数或者计算；
- NOT IN操作；
- 通配符位于查询字符串的第一个字符；
- IS NULL或者IS NOT NULL；
- 多列索引，但它的第一个列并没有被Where子句引用；
SQL分析
EXPLAIN用法
EXPLAIN PLAN FOR SELECT * FROM T_USER;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Oracle和Mysql的一些异同
1，oracle没有offset,limit，在mysql中我们用它们来控制显示的行数，最多的是分页了。oracle要分页的话，要换成rownum。
2，oracle有一个dual表，当select后没有表时，加上的。不加会报错的。select 1 这个在mysql不会报错的，oracle下会。select 1 from dual这样的话，oracle就不会报错了。
3，对空值的判断，name != “”这样在mysql下不会报错的，但是oracle下会报错。在oracle下的要换成name is not null,(innull(**))
4，oracle下对单引号，双引号要求的很死，一般不准用双引号
5，oracle有to_number,to_date这样的转换函数，oracle表字段是number型的，如果你$_POST得到的参数是123456，入库的时候，你还要to_number来强制转换一下，不然后会被当成字符串来处理。而mysql却不会。
6，group_concat这个函数，oracle是wm_concat。
7，group by,在下oracle下用group by的话，group by后面的字段必须在select后面出现，不然会报错的，而mysql却不会。
8，oracle的表字段类型也没有mysql多，并且有很多不同，例如：mysql的int,float合成了oracle的number型等。
9，oracle查询时from 表名后面 不能加上as 不然会报错的，select t.username from test as t而在mysql下是可以的。
10，Oracle截取字符串的函数为：substr(字段名，起始位置，字符串长度) 起始位置可从0开始，截取结果和从1开始一样。MySql截取字符串的函数为：substring(字段名，起始位置，字符串长度) 起始位置必须从1开始，0开始不能获取到数据。
11，oracle默认不自动提交，需手动提交。mysql默认是自动提交
常见问题：
中文乱码或者显示为？？
1.切换至oracle数据库的安装用户
$ su oracle
$ cd ~ //进入oracle用户的家目录
$ vim .bash_profile //编辑oracle用户下的.bash_profile文件
.bash_profile文件内容:(每个人的这个文件具体的设置可能都不太一样，不用担心,注意红色那一行就行了)
PATH=$PATH:$HOME/bin:/usr/bin:/usr/sbin:/sbin
export PATH
export ORACLE_SID=orcl
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_TERM=xterm
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/JRE:ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export NLS_LANG=AMERICAN_AMERICA.UTF8 //设置这个参数就代表设置用户客户端的字符集，在创建数据库的时候不设置这个参数也没问题，建议是最好规划好自己的库需要用到什么时候类型的字体，然后再设置这个值，最好保持与数据库一致，当然这个参数可以随时改变.(我自己的数据库是用UTF8字符集)
$ source .bash_profile //执行source命令使修改完的.bash_profile文件生效
至此，设置完毕，再次登录Oracle数据库，查询显示正常，但有一点要注意，就是如果你插入数据时的字符集不是UTF8下执行的，当你客户端和服务器都变成UTF8字符集后，以后插入的中文可能显示就为乱码了。所以说只有三个点(客户端、操作系统、数据库)的字符集保持一致的时候才能让数据库里面的中文正常显示。
日期格式问题
日期格式冲突问题
输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, date格式的类型就是: '01-Jan-01'
alter system set NLS_DATE_LANGUAGE = American scope=spfile
alter session set NLS_DATE_LANGUAGE = American scope=spfile
或者在to_date中写
select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = American') from dual;
注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，
可查看
select * from nls_session_parameters
select * from V$NLS_PARAMETERS
显示时间需要将日期型列转换成字符型，使用to_char(日期, 'yyyy-mm-dd hh24:mi:ss')或者修改oracle缺省日期格式。
重启监听器
lsnrctl status //查看状态
lsnrctl start //开启监听器
lsnrctl stop //停止监听器
lsnrctl reload //重启监听器
通过ip登陆oracle
sqlplus username/password@127.0.0.1/ORCL
not null和default顺序问题
default必须放在null或not null前面
CREATE TABLE datp0 (
GLOBALID number(11,0) DEFAULT '0' NOT NULL,
CYCLE number(11,0) DEFAULT '0' NOT NULL,
P1 varchar2(5) DEFAULT NULL,
...
);
后台执行sql
nohup sqlplus oracle/oracle@orcl @sql.sql &
查看被锁进程
//查看被锁的表
SELECT p.spid, a.serial#, c.object_name, b.session_id, b.oracle_username,b.os_user_name
FROM v$process p, v$session a, v$locked_object b, all_objects c
WHERE p.addr = a.paddr
AND a.process = b.process
AND c.object_id = b.object_id;
// 查看是哪个进程锁的
SELECT sid, serial#, username, status, osuser FROM v$session where serial# = '3789'
//查看指定进程的sql内容
select sql_text from v$sqlarea s,v$session ses where s.address=ses.sql_address and ses.serial# ='12345';
// 杀掉这个进程
alter system kill session 'sid,serial#';
//查看执行时间太长的sql语句
select sid,serial#,sql_text,executions from v$sql join v$session on v$sql.sql_id=v$session.sql_id where cpu_time>20000;
ORA-00031: session marked for kill 处理Oracle中杀不掉的锁
alter system kill session 'sid,serial#'; //若执行此语句提示ORA-00031: session marked for kill，则进行如下处理
select spid, osuser, s.program from v$session s,v$process p where s.paddr=p.addr and s.sid=33;
#kill -9 12345 //在OS界面终端执行
