Oracle基本对象的操作

Oracle对象的操作

启动Oracle

1、启动监听(想要oracle能够远程访问到必须配置监听)       2、启动数据库

1）登录服务器，切换到Oracle用户

2）打开监听服务  lsnrctl start

3）以sys用户身份登录Oracle   sqlplus /nolog

                                                  conn /as sysdba;

4）通过startup命令启动

关闭Oracle

1）关闭数据库shutdown

2）关闭监听器lsnrctl stop

一、用户

1、查看所有用户：

select * from dba_users;

select * from all_users;

select * from user_users;

2、创建用户：

create user 用户名 identified by 密码;   

注意刚刚创建的用户是没有任何权限的，因此需要dba给该用户进行授权；

Oracle中用户建立之后是无法正常登录的，只有dba对用户分配相关的权限之后用户才可以登录。

3、给用户分配权限

grant 权限 to 用户名;

权限分为系统权限和对象权限

系统权限是数据库管理相关的权限

系统权限：create session(登录权限)、create table(创建表权限)、create index(创建索引的权限)、create view(创建视图权限)、create sequence(创建序列权限)、create trriger(创建触发器权限)

对象权限是和用户操作数据库对象相关的权限

grant select on user.tablename to 用户名

update、insert、delete、select

查看当前用户的所有权限

select * from session _privs;

查看当前用户的权限及分类

select * from user_role_privs;

4、角色

角色：是一组权限的集合，将角色赋给一个用户，代表用户有了这个角色的所有权限

角色分为预定义角色和自定义角色

常见的角色有：DBA、connect、resource

CONNECT 角色，拥有Connect权限的用户只可以登录ORACLE(仅具有创建SESSION的权限)，不可以创建实体，不可以创建数据库结构；

RESOURCE 角色，是授予开发人员的。拥有Resource权限的用户只可以创建实体，不可以创建数据库结构。同时，当把ORACLE resource角色授予一个user的时候，不但会授予ORACLE resource角色本身的权限，而且还有unlimited tablespace权限，但是，当把resource授予一个role时，就不会授予unlimited tablespace权限。

Create trigger--建立触发器；

Create sequence—建立序列；

Create type--建立类型;

Create procedure--建立过程；

Create cluster--建立聚簇；

Create operator--创建操作者;

Create indextype--创建索引类型;

Create table--建表。

DBA角色，--是授予数据库维护人员的。拥有全部特权，是系统最高权限，只有DBA才可以创建数据库结构。

5、切换用户

conn 用户名/密码;

       6、修改密码

              password 用户名;

       7、删除用户

              一般用dba的身份删除某个用户，如果用其他的用户删除则需要有drop user的权限

              drop user 用户名[cascade](全部删除)

       8、回收权限(需要sys用户或者system用户)

              revoke 权限 from 用户名;

       9、管理员修改密码

              alter user 用户名 identified by 新密码;

二、表

       1、创建表

              create table 表名 (列名 数据类型);

              约束:主键约束(primary key):规定的列值不允许为空，不允许重复，有唯一性

                      唯一约束(unique):规定的列值不允许重复

                      非空约束(not null):规定列值不允许为空

                      检查约束(check+(条件)):规定的列值必须满足制定的条件

数据类型：varchar2(length)       字符串类型：存储可变长度的字符串；最大长度不超过4000

char(length)              字符串类型：存储固定长度的字符串；最大长度不超过200

number(a,b)             数据类型：存储数据类型，可以存整数，也可以存浮点型。a代表数据的最大位数：包含小数位和小数点，b代表小数的位数

date                         时间类型：存储的是时间和日期

       2、查询表

              查看当前用户下所有表

              select table_name from user_tables

              查看表内容

              select * from 表名 where 条件 order by 列名

       3、更新表

              update 表名 set 列名=‘’ where id= ;

       4、删除表

              drop table 表名；

       5、增加字段

alter table 表名 add (字段名 字段类型 默认值 是否为空)

       6、重命名字段

              alter table rename column 列名 to 新列名

       7、删除字段

              alter table 表名 drop column 字段名

三、索引

       相当于目录；当表中的某些字段经常被查询并作为查询的条件出现的时候，就应该考虑为该列创建索引 ；当从很多行的表中查询很少的行数时，也要考虑创建索引。

       1、索引的建立

              create index 索引名 on 表名(列名)

       (1)、唯一索引：

确保在定义索引的列中没有重复的值；

Oracle自动在表的主键列上创建唯一索引；

使用create unique index语句创建唯一索引

create unique index 索引名 on 表名(列名)

(2)、组合索引:

       组合索引是在表的多个列上创建的索引

       索引中列的顺序是任意的

       如果sql语句的where子句中引用了组合索引的所有列或者大多数列，可以提高检索速度

create index 索引名 on 表名(列名1，列名2);

具体列值：该表中的元组由两列共同确定一行，列如班级号、学号唯一确定一个学生

2、查看用户下的所有索引

              select * from user_indexes;

              select * from user_ind_columns

       3、查看某个表的所有索引

              select * from user_indexes where table_name=upper(‘表名’);

       4、重置索引

              alter index 索引名 rebuild;

       5、删除索引

              drop index 索引名

四、序列

       是产生一系列唯一数字的数据库对象

       提供唯一的数值；共享对象；主要用于提供主键值；

1、创建序列

create sequence 序列名 increment by 步长 start with 初始值

maxvalue:序列可以生成的最大值

minvalue:序列可以生成的最小值

cycle:用于定义当序列产生的数值达到限制值后是否循环

       2、查看序列

              select 序列名.[currval,nextval] from dual;

              currval:表示当前序列的值

              nextval:表示序列的下一个值

              cache:设置缓存cache个序列，如果系统down掉了或者其他情况会导致序列不连续，也可以设置为nocache

              查看所有已创建的序列：

              select * from user_squences;

       3、删除序列

              drop sequence 序列名;

       4、查看所有已创建的序列

              select * from user_sequences;

       5、更改序列

              alter sequence 序列名

increment by (步长)；

maxvalue(最大值)

minvalue(最小值)
       cycle(循环)

              如果要修改序列的起始值有两种方法: (1)删除序列重建

(2)修改序列的增长值increment by,将其增长到符合要求的开始值，然后再将increment by调回1

五、同义词(synonym)

       同义词(synonym)是数据库对象的一个别名，用于简化对象访问和提高对象访问的安全性，Oracle可以为表、视图、序列、过程指定一个别名。

同义词有两种类型：私有同义词：拥有create synonym权限的用户(包括非管理员用户)可创建私有同义词，创建的私有同义词只能当前用户使用

                              公有同义词：系统管理员可以创建公有同义词，公有同义词可以被所有用户访问

1. 创建同义词

create [public] synonym 同义词名称 for 用户名.objectName

1. 查看同义词

select * from user_synonyms;

1. 删除同义词

drop synonym 同义词名

六、Oracle commit和rollback详解

       DML:Data Manipulation Language 需要提交，这部分是对数据管理操作，比如insert、update、delete

       DDL:Data Definition Language 不需要提交，这部分试对数据结构定义，比如create、alter、drop

       1、commit：

当执行DML操作的时，数据库并不会立刻修改表中的数据，这个时候需要commit，数据库中的数据就立刻修改了，如果在没有commit之前就算将整个表的数据全部删除了，如果rollback的话，数据依然能还原。如果不执行commit这个命令，那么在你这个session之外的其他session查询的数据就是你修改之前的数据。而commit之后查询的时修改的数据。

       Oracle的commit就是提交数据（这里是释放锁不是锁表），在未提交前你面前的操作更新的都是内存，没有更新到物理内存中；insert以后的commit之前是锁表的状态，其他食物无法对该表进行操作，如果不提交，这个表就被锁了。不论实物多大commit的响应时间一般都很“平”（flat，可以理解为没有高低变化）

 

隐式提交： 用SQL命令间接完成的提交为隐式提交。这些命令是：ALTER，AUDIT，COMMENT，CONNECT，CREATE，DISCONNECT，DROP，EXIT，GRANT，NOAUDIT，QUIT，REVOKE，RENAME。

 

SET AUTOCOMMIT ON；自动提交语句

       2、rollback

rollback是用来撤销当前事务或者有问题的事务

ROLLBACK [WORK] [TO [SAVEPOINT] savepoint_name | FORCE 'string'};

work - 可选。隐式关键字，可写可不写。

to savepoint savepoint_name - 可选。rollback 语句撤销当前会话的所有更改，直到由 savepoint_name 指定的保存点。如果省略该子句，则所有更改都将被撤销。

force 'string - 可选'。用于 强制回滚 可能已损坏或有问题的事务。使用此子句，可以将单引号中的 事务id 指定为 字符串。可以在 系统视图 中找到名为 dba_2pc_pengding、v$corrupt_xid_list 的事务标识（无法将有问题的事务回滚到保存点。）