Oracle知识点笔记(三)

来源：互联网发布：深圳软件产业基地英文编辑：程序博客网时间：2024/05/05 04:08

(十八) 数据库管理

用户：sys和system

sys角色：
dba：数据库管理员角色
sysdba：系统管理员
sysoper：系统操作员

SQL> conn sys/1234567;ERROR:ORA-28009: connection as SYS should be as SYSDBA or SYSOPER.SQL> conn sys/as sysdba;Enter password:1234567Connected.SQL>SQL> conn sys/1234567 as sysoper;Connected.

system角色：

dba：数据库管理员角色
sysdba：系统管理员

SQL> conn system/1234567;//默认以普通的dba登陆Connected.SQL>SQL> conn system/1234567 as sysdba;//实际上它是以sys登陆的Connected.SQL>show parameter;

(十九) 备份

分为逻辑备份和物理备份，以下介绍逻辑备份
导出：
导出表，导出方案，导出数据库

导出表：

exp userid=scott/m123@wilson tables=(emp) file =/emp.dmp
note：在导入和导出的时候，要到oracle目录的bin目录下
exp userid=system/1234567/@wilson tables(scott.emp) file=/emp.dmp

导出方案：

导出数据库：

exp userid=system/1234567@wilson full=y inctype=complete file=/database.dmp;//一定是system
exp userid=scott/m123@wilson owner=scott file=/emp.2

导入：

导入表：imp userid=scott/m123@wilson talbes=(emp) file=/emp.dmp

导入方案：
自身方案，imp userid=scott/m123 file=/fangan.dmp

导入数据库：
imp userid=system/1234567 full=y file=d:\xxx.dmp

(二十) 数据字典和动态性能视图

user_tables;

用于显示当前用户的表

select table_name from user_tables;

用于显示当前用户可以访问的所有表

select table_name from all_tables;

用于显示数据库所有方案所有表

select table_name from dba_tables;

desc dba_users;
要查看scott具有的权限

desc dba_role_privs;SQL> select * from dba_role_privs where grantee='SCOTT';

1、如何查看一个角色包括的权限？
a、一个角色包含的系统权限

select * from  dba_sys_privs where grantee='DBA';select * from  dba_sys_privs where grantee='CONNECT';

b、一个角色包含的对象权限

select * from dba_tab_privs where grantee='CONNECT';select * from dba_roles;

2、查询oracle中的所有的系统权限，一般是dba

select * from system_privilege_map order by name;

3、查询oracle中的所有的对象权限，一般是dba

select distinct privilege from dba_tab_privs;

4、查询某个用户具有怎样的角色

select * from dba_role_privs where grantee='username';

(二十一 ) 表空间

建立表空间

create tablespace data01 datafile '/datafile/data01.dbf' size 20m uniform size 128k;

选择表空间建立表

create table mypart(deptno number(9),dname varchar(10),loc varchar(23)) tablespace data01;

使表空间脱机
alter tablespace 表空间名 offline;
使表空间联机
alter tablespace 表空间名 online;

知道表名，查看该表在哪个表空间

select tablespace_name,table_name from user_tables where

删除表空间

drop tablespace '表空间' including contents and datafiles;

扩展表空间

a、alter tablespace data01 add datafile '/datafile/data02.dbf' size 20m ; //增加文件b、alter tablespace 表空间名 '/datafile/data01.dbf' resize 200m ; //增加原来文件的大小c、alter tablespace 表空间名 '/datafile/data01.dbf' autoextend on next 10m maxsize 500m;

(二十二) 数据完整性--约束

not null
unique
primary

SQL> create table goods(goodsId char(8) primary key,  2  goodsName varchar2(30),  3  unitprice number(10,2) check(unitprice >0),  4  category varchar2(8),  5  provider varchar2(30));Table created.

SQL> create table customer(customerId char(8) primary key,  2  name varchar2(30) not null ,  3  address varchar2(50) ,  4  email varchar2(50) unique,  5  sex char(5) default 'man' check(sex in('man','woman')),   6  cardId char(18));Table created.

SQL> create table purchase(customerId char(8) references customer(customerId),  2  goodsId char(8) references goods(goodsId),  3  nums number(5) check(nums between 1 and 30));Table created.

SQL> alter table goods modify goodsName not null;SQL> alter table customer add constraint cardunique unique(cardId);SQL> alter table customer add constraint addressCheck check (address in ('dongcheng','xicheng'));

1、删除约束

alter table customer drop constraint 约束名称;

管理索引

单列索引SQL> create index nameIndex on customer(name);

(二十三) 系统权限

create user ken identified by 1234567;create user tom identified by 1234567;SQL> grant create session ,create table to ken with admin option;SQL> grant create view to ken;SQL> grant create session,create table to tom with admin option;SQL> grant create view to tom;grant create view to tom*ERROR at line 1:ORA-01031: insufficient privileges

1、收回权限

SQL> revoke create session from ken;Revoke succeeded.SQL> conn tom/1234567Connected.SQL>

系统权限不是级联回收

(二十四) 对象权限

SQL> create user monkey identified by 1234567;User created.SQL> grant create session to monkey;Grant succeeded.SQL> conn scott/m123 ;Connected.SQL> grant select on emp to monkey;Grant succeeded.SQL>SQL> conn monkey/1234567;Connected.SQL> select * from scott.emp;

希望monkey可以修改scott.emp表

SQL> grant update on emp to monkey;SQL> grant delete on emp to monkey;

一句话搞定：

SQL> grant all on emp to monkey;

希望monkey只可以修改scott.emp表的sal字段

SQL> grant update on emp(sal) to monkey;

同理：

SQL> grant select on emp(ename,sal) to monkey;

授予index权限

grant index on scott.emp to monkey with grant option;

SQL> create user blake2 identified by 1234567;User created.SQL> grant create session to blake2 with admin option;Grant succeeded.SQL> grant select on scott.emp to blake2 with grant option;Grant succeeded.SQL>SQL> create user blake2 identified by 1234567;User created.SQL> grant create session to blake2 with admin option;Grant succeeded.SQL> grant select on scott.emp to blake2 with grant option;Grant succeeded.SQL> SQL> create user blake2 identified by 1234567;User created.SQL> grant create session to blake2 with admin option;Grant succeeded.SQL> grant select on scott.emp to blake2 with grant option;Grant succeeded.

SQL> create user jones2 identified by 1234567;User created.SQL> grant create session to jones2 with admin option;Grant succeeded.SQL> conn blake2/1234567;Connected.SQL> grant select on scott.emp to jones2;Grant succeeded.SQL> conn jones2/1234567;Connected.SQL> set linesize 120;SQL> select * from scott.emp;     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------      7369 SMITH      CLERK           7902 17-DEC-80        800                    20      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30      7566 JONES      MANAGER         7839 02-APR-81       2975                    20      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30      7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30      7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10      7788 SCOTT      ANALYST         7566 19-APR-87       3000                    20      7839 KING       PRESIDENT            17-NOV-81       5000                    10      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30      7876 ADAMS      CLERK           7788 23-MAY-87       1100                    20     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO---------- ---------- --------- ---------- --------- ---------- ---------- ----------      7900 JAMES      CLERK           7698 03-DEC-81        950                    30      7902 FORD       ANALYST         7566 03-DEC-81       3000                    20      7934 MILLER     CLERK           7782 23-JAN-82       1300                    10      9998 ..         MANAGER         7782 12-DEC-88       98.5      55.23         10      9999 ..         MANAGER         7782 12-DEC-88       98.5      55.23         1016 rows selected.

SQL> revoke select on scott.emp from blake2;Revoke succeeded.SQL> conn jones2/1234567;Connected.SQL> select * from scott.emp;select * from scott.emp                    *ERROR at line 1:ORA-00942: table or view does not exist说明对象权限是级联回收的SQL>

(二十五) 管理权限和角色

批量对用户授权
角色就可以简化
角色：把一系列的权限打包赋给某个用户

预定义角色：

connect：alter  resource：dba：

自定义角色，一般是dba来创建：

SQL> create role myrole1 not identified;Role created.SQL> grant create session to myrole1 with admin option ;Grant succeeded.SQL>SQL> conn scott/m123;Connected.SQL> grant update on emp to myrole1;Grant succeeded.SQL> grant select on emp to myrole1;Grant succeeded.SQL> grant delete on emp to myrole1;Grant succeeded.SQL>SQL> create user along identified by 1234567;User created.SQL> grant myrole1 to along with admin option;Grant succeeded.SQL> conn along/1234567;Connected.SQL> select * from scott.emp;

(二十六) 删除角色

如果角色被删除，along是否还可以登录
1、删除

drop role myrole1;

SQL> drop role myrole1;Role dropped.

结果：

SQL> conn along/1234567;ERROR:ORA-01045: user ALONG lacks CREATE SESSION privilege; logon deniedWarning: You are no longer connected to ORACLE.

查看某个用户的角色

SQL> select granted_role,default_role from dba_role_privs where grantee='SCOTT';