(十八) 数据库管理
用户: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:1234567
Connected.
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/1234567
Connected.
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 10
16 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 denied
Warning: You are no longer connected to ORACLE.
查看某个用户的角色
SQL> select granted_role,default_role from dba_role_privs where grantee='SCOTT';