- 基础操作
Su - oracle
-
- 登录
若系统上有多个oracle实例,则需要指定实例名,输入命令:export ORACLE_SID=orcl。
登录数据库命令:sqlplus / as sysdba(以dba用户登录);
sqlplus scott/tiger(以普通用户登录);
若没有指定实例名可以在登录时指定,命令为:sqlplus / as sysdba@orcl(以dba用户登录);
sqlplus scott/tiger@orcl(以普通用户登录);
登录之后查看实例名:
select instance_name from v$instance;
1.2. 启动与关闭数据库
oracle数据库实例启动分为3个步骤,分别是启动实例、加载数据库、打开数据库。
启动命令如下:
startup(该命令直接包含3个步骤的启动)
关闭命令如下:
shutdown immediate;(等事务完成后关闭)
shutdown abort;(强制关闭,谨慎使用)
1.3. 创建用户
创建用户只有dba有权限,首先用以dba用户登录数据库,然后执行创建用户的命令:
create user test identified by test;(该命令创建用户test,密码为test)。
1.4 用户授权与回收权限
1)然后授予test用户权限,主要授予以下几个权限,代码如下:
grant connect,resource to test;(连接和开发系统权限,用户就可连接数据库以及创建表、视图、序列等)
grant create session to test;(创建会话权限)
2)回收test用户resource权限,代码如下:
revoke resource from test;
3)赋予用户权限
Grant any user to test
1.5 查看用户默认表空间
一般这两个权限就够了,此时用户默认使用表空间为users表空间,查看当前用户所属表空间,代码如下:
select username,default_tablespace from user_users;
1.6 修改用户密码
将test用户密码修改为’123456’,代码如下:
alter user test identified by "123456";
1.7 锁定和解锁用户账号
1)锁定test用户账号,代码如下:
alter user test account lock;
2)解锁test用户账号,代码如下:
alter user test account unlock;
1.8 查看用户所有表
查询test用户下所拥有的表及表大小,代码如下:
select table_name,blocks*8/1024/1024 size_m from user_tables;
1.9 删除用户
删除test用户,代码如下:
drop user test cascade;(将用户所拥有的对象一起删除)
- 表空间使用
2.1. 查看表空间
查看表空间名、数据文件位置、大小、是否自动扩展,扩展最大值,代码如下:
select tablespace_name,file_name,bytes/1024/1024 size_m,
autoextensible,maxbytes/1024/1024 maxsize_m from dba_data_files;
2.2. 查看表空间大小
查看各个表空间大小,及使用情况,代码如下:
select a.tablespace_name,a.bytes/1024/1024 tbs_size,
(a.bytes-b.bytes)/1024/1024 use,
b.bytes/1024/1024 free,round(((a.bytes-b.bytes)/a.bytes)*100,2)||'%' use_l
from (select tablespace_name,sum(bytes) bytes from dba_data_files
group by tablespace_name)a,(select tablespace_name,sum(bytes) bytes from dba_free_space group by tablespace_name)b
where a.tablespace_name=b.tablespace_name;
2.3. 创建表空间
创建表空间test,代码如下:
create tablespace test datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 1024m;
2.4. 更改表空间大小
更改表空间大小,以test表空间为例,代码如下:
alter database datafile '/u01/app/oracle/oradata/orcl/test01.dbf' resize 10240m;(将表空间设置大小为10g)
2.5. 表空间自动扩展开启和关闭
1)设置表空间test自动扩展,每次扩展100m,最大扩展到30g,代码如下:
alter database datafile '/u01/app/oracle/oradata/orcl/test01.dbf' autoextend on next 100m maxsize 30720m;
2)关闭数据文件自动扩展,代码如下:
alter database datafile '/u01/app/oracle/oradata/orcl/test01.dbf' autoextend off;
2.6. 表空间无限扩展
也可以设为在空间足够的情况下无限扩展(一般数据文件扩展极限为32G,若还是不够需要添加数据文件),代码如下:
alter database datafile '/u01/app/oracle/oradata/orcl/test01.dbf' autoextend on next 100m maxsize unlimited;
2.7. 表空间中添加数据文件
可以向表空间中添加数据文件,代码如下:
alter tablespace test add datafile '/u01/app/oracle/oradata/orcl/test02.dbf' size 10240m;
2.8. 指定用户默认表空间
给test用户指定默认表空间test,代码如下:
alter user test default tablespace test;
2.9. 查看表空间下的表
查看表空间test下的表及表大小,代码如下:
select owner,segment_name,sum(bytes/1024/1024) mb
from dba_segments
where tablespace_name=’TEST’ group by owner, segment_name order by 3 asc;
2.10. 删除表空间
删除表空间test及其数据文件,代码如下:
drop tablespace test including contents and datafiles;
- 维护数据表
3.1. 创建表
创建表代码如下:
create table test(id int,name varchar(10));
3.2. 增加和删除字段
1)增加字段代码如下:
alter table test add sex char(1);
2)删除字段代码如下:
alter table test drop column sex;
3.3. 修改字段
1)将test表中的sex字段长度由1更改为2,代码如下:
alter table test modify sex char(2);
2)将test表中的sex字段类型由char更改为varchar2,代码如下:
alter table test modify sex varchar2(2);
3.4. 重命名表
将test表修改为test1,代码如下:
alter table test rename to test1;
3.5 查看一个表所在表空间
查看表所在的表空间,代码如下:
select tablespace_name from all_tables where table_name='TEST';
select tablespace_name from user_tables where table_name=TEST;
3.6. 改变表存储的表空间
将表test从test表空间移到users表空间,代码如下:
alter table test move tablespace users;
3.6. 删除表
1)删除test表,语句如下:
drop table test;
2)若test表不放入回收站,直接删除,代码如下:
drop table test purge;
3)删除test表以及所有引用这个表的视图、约束和触发器等,代码如下:
drop table test cascade constraints;
- 索引创建和维护
索引是为了提高查询速度,本章主要介绍B树索引和位图索引。
4.1. B树索引的创建
表test结构为id(身份证号),name(名字),sex(性别),数据量有14亿,此时我们要查身份证号为513023199101231922人的名字,为了提高查询效率此时就适合建立B数索引,代码如下:
create index idx_id on test(id);
4.2. 位图索引的创建
1)若要统计test表中男、女、人妖各多少人,此时就适合在sex上建立位图索引,代码如下:
create bitmap index idx_sex on test(sex);
2)若test表为分区表,创建位图索引代码如下:
create bitmap index idx_sex on test(sex) local;
4.3. 索引维护
1)查询失效索引,代码如下:
select a.owner, a.index_name, a.status from dba_indexes a where a.status = 'UNUSABLE'
union all
select a.index_owner, a.index_name, a.status from dba_ind_partitions a
where a.status = 'UNUSABLE';
2)当查询到有失效索引后,可以对索引进行重建,若test表上的idx_id索引失效需要重建,代码如下:
alter index idx_id rebuild online;
4.4. 删除索引
删除test表上的idx_id索引,代码如下:
drop index idx_id;
4.5. 不走索引的情况
在很多情况下,查询表中数据时不走索引,包括但不限于以下情况:
- 建立组合索引,但查询谓词并未使用索引的第一列。
- 在包含有null值的table列上建立索引,当使用select count(*) from table时不会使用索引。
- 在索引列上使用函数,如果一定要使用索引只能建立函数索引。
4)当被索引的列进行隐式转换时不会使用索引:如select * from test where id=5,id列建立索引但类型为字符型。
- 当全表扫描更快时,不会走索引。
- 用了<>
- 使用like ‘%’,百分号在前
8)有一段时间没有分析表,导致统计信息过期,分析表语句为:
analyze table test compute statistics;
- 视图和序列
5.1. 创建视图
视图是一个虚拟表,不再数据库中存储数据值,其数据值由来自定义视图的查询语句所引用的表。可以通过修改视图修改基本表的数据。与之对应,改变基本表中数据也会反映到由该表组成的视图中,在scott模式下创建视图代码如下:
create or replace view test_view as select b.dname,b.loc,a.empno,a.ename from emp a,dept b
where a.deptno=b. deptno and b.deptno=20;
5.2. 管理视图
5.2.1. 查看视图定义
查看视图定义代码如下:
select view_name,text from user_views where view_name=upper(’ test_view’);
5.2.2. 修改视图定义
建立视图后,如果要改变视图定义,代码如下:
create or replace view test_view as select b.dname,b.loc,a.empno,a.ename from emp a,dept b
where a.deptno=b. deptno and b.deptno=30;(起到关键作用的关键字是replace,它表示使用新的视图定义替换掉旧的视图定义)
5.2.3. 重新编译视图
视图被创建后,如果用户修改了视图所依赖基本表的定义,则该试图会被标记为无效状态。当用户访问视图时,oracle会自动重新编译视图,除此之外还可以手动重新编译,代码如下:
alter view test_view compile;
5.2.4. 删除视图
删除视图后,只是视图的定义被删除,对视图内所有表的数据没有任何影响。代码如下:
drop view test_view;
5.3. 创建序列
序列是oracle提供的用于生存一系列唯一数字的数据库对象。序列会自动生存顺序递增的序列号,以实现自动提供唯一的主键值。序列可以在多用户并发环境中使用,并且可以为所有用户生成不重复的顺序数字,而不需要额外的I/0。
序列与视图一样,并不占用实际的存储空间,创建序列代码如下:
create sequence test_seq increment by 1 start with 1 nomaxvalue nocycle cache 50;
increment by 1:每次加1
start with 1:从1开始计数
nomaxvalue:不设置最大值
nocycle:一直累计不循环
cache50:设置缓存序列个数,如果系统down掉了或者其他情况会导致序列不连续,也可以设置为nocache
5.4. 使用序列
1)使用序列时需要用到序列的两个伪列NEXTVAL与CURRVAL。其中NEXTVAL将返回序列生存的下一个序列号;CURRVAL会返回序列当前序列号。
查看序列当前序列号,代码如下:
select test_seq.nextval from dual;
2)查看序列下一个序列号,代码如下:
select test_seq.currval from dual;
3)在scott模式下,使用序列为emp表的新记录提供员工编号,代码如下:
insert into emp(empno,ename,deptno) values(test_seq.nextval,’东方’,20);
5.5. 管理序列
5.5.1 修改序列
修改序列test_seq的最大值为100000,序列增量为2,缓存值为100,代码如下:
alter sequence test_seq maxvalue 100000 increment by 2 cache 100;
5.5.2 删除序列
删除序列代码如下:
drop sequence test_seq;
- SQL优化
6.1. SQL语句开发注意事项
1)SQL语句使用大写,因为Oracle总是先解析sql语句,把小写的字母转换成大写的再执行。无论是关键字还是表名、字段等,都建议大写;
2)使用表的别名,当在SQL语句中连接多个表时, 请使用表的别名并把别名前缀于每个Column上。这样一来,就可以减少解析的时间并减少那些由Column歧义引起的语法错误;
3)SQL语句中避免使用“*”, SQL语句在解析的过程中,会将“*”依次转化为所有的列名,这个工作是通过查询数据字典来完成的,这将会耗费很长的时间。
4)使用EXISTS 代替IN,使用IN时,首先返回子查询的结果集,然后再去匹配总的结果集的字段,需要进行比较确切的比较。而使用EXISTS时,只是验证数据是否存在,返回TRUE或FALSE。因此,使用IN 会花费更多的资源。同理,尽量使用NOT EXISTS代替 NOT IN。
5)FROM子句中表的顺序,当多表连接进行查询时,Oracle解析器在处理FROM子句中的表时,是按照从右至左的顺序,最后放置的表被首先处理,该表被作为基础表。处理完倒数第一张表并将数据排序后,才进行倒数第二张表的插叙,然后将两表中的数据合并。因此应该将数量较小的表放在最后作为基础表,来提高执行效率。
6)WHERE子句的条件顺序,在SQL语句中,可以指定多个检索条件,Oracle采取自右至左的顺序解析WHERE,根据这个顺序,表之间的连接条件应该写在其他过滤条件之前,可以过滤掉最大数据量的条件应该放在WHERE子句条件的末尾。
7)避免对索引列使用NOT关键字,在Oracle中,如果在索引列上使用了NOT关键字,则会停止使用索引,而进行全表扫描。
8)总是使用联合索引的第一个列,如果索引是建立在多个列上, 只有在它的第一个列被where子句引用时,优化器才会选择使用该索引。 这也是一条简单而重要的规则,当仅引用索引的第二个列时,优化器使用了全表扫描而忽略了索引。
9)避免在索引列上计算,WHERE子句中,如果索引列是函数的一部分。优化器将不使用索引而使用全表扫描。
10)用>=代替>,用<=代替<,两者的区别在于,前者DBMS(数据库管理系统)将直接跳到第一个DEPTNO等于4的记录而后者将首先定位到DEPTNO=3的记录并且向前扫描到第一个DEPTNO大于3的记录。
11)若删除表的所有数据,用TRUNCATE代替DELETE。DELETE会把数据放入UNDO表空间,TRUNCATE不会放入,直接将表的数据全部删除,所以速度会快很多。
12)避免在索引列上使用IS NULL和IS NOT NULL以及’!=’,否则将会全表扫描。
6.2. 表连接规范
当A、B两张表关联时:
- 只返回A表的字段:使用exists或not exists 的方式关联;
- 需要返回A、B两张表的数据时:使用 left join 的方式关联,严格审查关联条件,非需求情况下严禁出现笛卡尔积。
6.3. 查询低效执行的SQL语句
有些SQL效率低下,占用大量数据库资源和时间,可用如下代码查出:
SELECT EXECUTIONS,DISK_READS,BUFFER_GETS,
ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) HIT_RADIO,
ROUND(DISK_READS/EXECUTIONS,2) READS_PER_RUN,SQL_TEXT
FROM V$SQLAREA WHERE EXECUTIONS>0 AND BUFFER_GETS>0
AND ((BUFFER_GETS-DISK_READS)/BUFFER_GETS)<0.8
ORDER BY 4 DESC;
- 对象管理
7.1 显示一个表的结构
显示表结构比较简单,可以在工具中查看,也可在sql*plus页面中查看,查看TEST表代码如下:
SQL> DESC TEST;
7.2. 查看对象及状态
查看当前用户的函数与存储过程及状态,代码如下:
select object_name,status from user_objects where object_type='FUNCTION';
select object_name,status from user_objects where object_type='PROCEDURE';
object_type可以是:SEQUENCE、PROCEDURE、LOB、PACKAGE、PACKAGE BODY、TRIGGER、INDEX、TABLE、VIEW、FUNCTION、Java CLASS、Java SOURCE、TYPE ...
7.3. 编译对象
若发现有存储过程失效或者函数失效(status为invalid),则重新编译对象,代码如下:
alter function FUNCTION_NAME compile;
alter procedure PROCEDURE_NAME compile;
若重新编译后还是失效状态,则是函数或者存储过程本身存在问题。
7.4. 锁对象
1)查询当前所有被锁对象的信息,代码如下:
select * from v$locked_object;
2)查询所属用户,被锁对象,会话ID,锁的模式,代码如下:
select b.owner, b.object_name, a.session_id,a.locked_mode
from v$locked_object a, dba_objects b
where b.object_id = a.object_id;
3)查询发生锁对应的语句
select distinct sql_text from v$sql where hash_value in
(select sql_hash_value from v$session where sid in (select session_id from v$locked_object));
4)查询锁的sid和serial#,当强制关闭锁的时候会用到,代码如下:
select b.username, b.sid, b.serial#,logon_time
from v$locked_object a, v$session b
where a.session_id = b.sid
order by b.logon_time;
5)有些锁可能等待很久都没释放,此时可能需要强行关闭,代码如下:
SQL>alter system kill session 'sid,serial#';
8. 查看参数
1)查看所有初始化参数
SQL> show parameters;
2)如果只想查询一个具体的参数值,则使用命令:show parameters [参数名称]。
例如查询控制文件信息:
SQL> show parameters control_files;
3)查询当前数据库字符集,代码如下:
SQL> select userenv('LANGUAGE') from dual;
4)查看服务器端字符集配置
SQL> select * from v$nls_parameters;
SQL> select * from nls_database_parameters;
第二个语句的查询结果比第一个语句多了一个数据库版本的参数NLS_RDBMS_VERSION。
5)客户端字符集
SQL> select * from nls_instance_parameters;
9. 用户连接管理
1)用系统管理员登录,查看当前数据库有几个用户连接
SQL> select username,sid,serial# from v$session;
SQL> select username,sid,serial# from v$session where username=upper(‘test’);
2)关闭某个连接
语法:alter system kill session 'sid,serial#';
SQL> alter system kill session '104,1894';
3)查询Oracle支持的最大连接数
SQL> show parameter processes;
4)修改最大连接数
SQL> alter system set processes=300 scope = spfile;
5)创建pfile
SQL> create pfile from spfile;
6)查询当前连接数
SQL> select count(*) from v$session;
7)查询当前并发连接数
SQL> select count(*) from v$session where status='ACTIVE';
8)查询不同用户的连接数
SQL> select count(*) from v$session where username is not null;
SQL> select username, count(username) from v$session
where username is not null
group by username;
9)查看当前数据库建立的会话情况
SQL> select sid, serial#, username, program, machine,status from v$session;
10. 数据导入和导出
10.1 EXPDP导出数据
首先要用dba用户创建一个directory对象,并为scott用户授予使用目录的权限,代码如下:
create directory my_dir as ‘/u01/mydir’;
grant read,write on directory my_dir to scott;
导出指定数据表,代码如下:
expdp scott/tiger directory=my_dir dumpfile=test.dmp tables=emp,dept(表可以多个)
导出该用户下所有表,代码如下:
expdp scott/tiger directory=my_dir dumpfile=table.dmp include=table
导出该用户下所有存储过程或者函数,代码如下:
expdp scott/tiger directory=my_dir dumpfile=procedure.dmp include=procedure
expdp scott/tiger directory=my_dir dumpfile=procedure.dmp include=function
10.2 IMPDP导入数据
将scott中导出的数据文件导入到test用户,首先赋予目录的权限给test,代码如下:
grant read,write on directory my_dir to test;
导入指定数据表,代码如下:
impdp test/test directory=my_dir dumpfile=test.dmp remap_schema=scott:test(表可以多个)
导入所有表,代码如下:
impdp test/test directory=my_dir dumpfile=table.dmp remap_schema=scott:test
导入所有存储过程,代码如下:
impdp test/test directory=my_dir dumpfile=procedure.dmp remap_schema=scott:test
注:如果要将dmp文件导入其他用户,则必须指定remap_schema参数