初步了解oracle

1、 Oracle的创始人

2、 Oracle版本含义

3、 Oracle安装：用户种类及初始密码

在oracle10g11g中默认scott被锁定。

4、 Oracle数据库的启动

a) 启动两个服务***Listener/***Service

b) “开始-》运行”-》sqlplus或 sqlplusw

5、用户连接、解锁、锁定

a) conn/connect scott/tiger; system/orcl; sys/orcl as sysdba;

b) 解锁/锁定：alter user 用户名 account unlock/lock;

6、用户的查找

a) 显示当前的用户：show user;

b) 显示系统默认的用户：select * from all_users;

7、创建表空间

/*分为四步 */

/*第1步：创建数据表空间 */

create tablespace user_data

datafile 'D:oracleoradataOracle9iuser_data.dbf'

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;

/*第2步：创建临时表空间 */

create temporary tablespace user_temp

tempfile 'D:oracleoradataOracle9iuser_temp.dbf'

size 50m

autoextend on

next 50m maxsize 20480m

extent management local;

//删除表空间

drop tablespace user_data including contents and datafiles

/*第3步：创建用户并指定表空间 */

create user username identified by password

default tablespace user_data

temporary tablespace user_temp;

8、创建新用户

a) 创建用户密码：SQL>CREATE USER test IDENTIFIED BY test123;

b) 更改密码：SQL>ALTER USER test IDENTIFIED BY test321;

c) 给用户授系统权限：

授予连接数据库权限：

a) SQL> grant create session to test;

授予创建数据库表权限：

a) SQL> grant create table to test;

d) 给用户授对象权限：

授予查询表的权限

a) SQL> grant select on dept to test;

b) SQL> grant all on scott.dept to test;

e) 给用户授角色：

SQL>grant connect,resource to test;

f) 收回权限

SQL> revoke select on dept from test;
SQL> revoke all on dept from test;

g) 查看权限

查看当前用户的权限：select * from session_privs;
查看系统角色的权限：select * from dba_sys_privs where grant=’RESOURCE’;

h) 删除用户

SQL> drop user test;
SQL> drop user test cascade;

Cascade 作用是在删除用户的同时，删除其用有的所有对象。

9、创建角色

a) 创建语法：create role role1；

b) 删除语法：drop role role1；

c) 给角色分配权限和给用户分配一样：grant create table to role1

d) 撤销权限也一样：revoke create table from role1

10、表

a) 查询数据库中有哪些表：select * from tab;

b) 查询某张表的结构：desc 表名;

11、代码

 1 create tablespace user_data
 2 datafile 'c:oracleoradataorcluser_data.dbf'
 3 size 50m
 4 autoextend on next 50m
 5 maxsize 20480m
 6 extent management local;
 7 
 8 create temporary tablespace user_temp
 9 tempfile 'c:oracleoradataorcluser_temp.dbf'
10 size 50m
11 autoextend on next 50m
12 maxsize 20480m
13 extent management local;
14 
15 
16 create temporary tablespace user_temp1
17 tempfile 'c:oracleoradataorcluser_temp1.dbf'
18 size 50m
19 autoextend on next 50m
20 maxsize 20480m
21 extent management local;
22 
23 drop tablespace user_temp1;
24 
25 --------------创建用户
26 drop user philis;
27 create  user philis identified by philis
28 default tablespace user_data
29 temporary tablespace user_temp;
30 
31 create user u1 identified by u1;
32 
33 ---修改密码
34 alter user philis identified by 123;
35 alter user philis identified by philis;
36 --- 锁定和解锁账户
37 alter user system account lock;
38 alter user system account unlock;
39 
40 
41 -------授权
42 grant create session to philis;
43 grant select on scott.emp to philis;
44 revoke create session from philis;
45 revoke select on scott.emp from philis;
46 
47 -----授予角色
48 grant resource,connect to philis ;
49 ----传递权限
50 grant select on scott.emp to philis with grant option;
51 revoke select on scott.emp from philis;