orcale日常基本操作

最新推荐文章于 2021-04-10 01:08:49 发布

a69843251

最新推荐文章于 2021-04-10 01:08:49 发布

阅读量1.1k

点赞数

分类专栏：学习

学习专栏收录该内容

10 篇文章 0 订阅

订阅专栏

基础操作

Su - oracle

1. 登录

若系统上有多个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参数

a69843251

关注

0
点赞
踩
7

收藏

觉得还不错? 一键收藏
0
评论
orcale日常基本操作

基础操作Su - oracle 登录若系统上有多个oracle实例，则需要指定实例名，输入命令：export ORACLE_SID=orcl。登录数据库命令：sqlplus / as sysdba（以dba用户登录）；sqlplus scott/tiger（以普通用户登录）；若没有指定实例名可以在登录时指定，命令为：sqlplus / as sysdba@orcl（...
复制链接

扫一扫