oracle数据库管理 DBA必会知识点

原创

paopao5541 2013-07-13 11:43:47 博主文章分类：Oracle ©著作权

文章标签 oracle 数据库管理 insert create dictionary 文章分类 数据库

©著作权归作者所有：来自51CTO博客作者paopao5541的原创作品，请联系作者获取转载授权，否则将追究法律责任

grant select any dictionary to scott;

create table t1 as select * from emp;

insert into t1 select * from t1;

--查用户看scott用户下的段名为T1的存储分区记录

select segment_name,extent_id,file_id,block_id,blocks

from dba_extents where owner='SCOTT' and segment_name='T1';

--给段T1分配大小为100k的存储区间

alter table t1 allocate

extent(datafile '/u01/app/oracle/oradata/orcl/users01.dbf' size 100k);

--回收高水位线之后的空闲空间

alter table t1 deallocate unused;

--回收高水位线20k之后的空闲空间

alter table a deallocate unused keep 20k;

SQL> truncate table T1;

截断表之后，段的第一个分区依然存在，但是数据都已经清空

oracle重命名数据文件的名字

SQL> alter tablespace aaa offline;

Tablespace altered.

SQL> select ts#,name from v$tablespace;

TS# NAME

---------- ------------------------------

0 SYSTEM

1 SYSAUX

2 UNDOTBS1

4 USERS

3 TEMP

6 EXAMPLE

7 YUANLEI

8 AAA

SQL> select ts#,file#,name,status from v$datafile;

TS# FILE# NAME STATUS

---------- ---------- --------------------------------------------- -------

0 1 /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM

1 2 /u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE

2 3 /u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE

4 4 /u01/app/oracle/oradata/orcl/users01.dbf ONLINE

6 5 /u01/app/oracle/oradata/orcl/example01.dbf ONLINE

8 6 /u01/app/oracle/oradata/orcl/bbb01.dbf OFFLINE

SQL> host rename /u01/app/oracle/oradata/orcl/bbb01.dbf aaa01.dbf;

[oracle@oracle11gR2 orcl]$ pwd

/u01/app/oracle/oradata/orcl

[oracle@oracle11gR2 orcl]$ cp bbb01.dbf aaa01.dbf

[oracle@oracle11gR2 orcl]$ ls

aaa01.dbf example01.dbf redo03.log temp01.dbf yuanlei01.dbf

bbb01.dbf redo01.log sysaux01.dbf undotbs01.dbf

control01.ctl redo02.log system01.dbf users01.dbf

SQL> alter database rename file '/u01/app/oracle/oradata/orcl/bbb01.dbf' to '/u01/app/oracle/oradata/orcl/aaa01.dbf';

Database altered.

SQL> alter tablespace aaa online;

Tablespace altered.

SQL> select ts#,file#,name,status from v$datafile;

TS# FILE# NAME STATUS

---------- ---------- --------------------------------------------- -------

0 1 /u01/app/oracle/oradata/orcl/system01.dbf SYSTEM

1 2 /u01/app/oracle/oradata/orcl/sysaux01.dbf ONLINE

2 3 /u01/app/oracle/oradata/orcl/undotbs01.dbf ONLINE

4 4 /u01/app/oracle/oradata/orcl/users01.dbf ONLINE

6 5 /u01/app/oracle/oradata/orcl/example01.dbf ONLINE

8 6 /u01/app/oracle/oradata/orcl/aaa01.dbf ONLINE

6 rows selected.

重命名成功

-----创建临时表空间

SQL> create temporary tablespace test_temp

tempfile '/u01/app/oracle/oradata/orcl/test_temp.dbf' size 10M

autoextend on next 10M maxsize 100M extent management local;

------创建用户表空间并制定用户表空间

SQL> create temporary tablespace test_temp tempfile '/u01/app/oracle/oradata/orcl/test_temp.dbf' size 10M autoextend on next 10M maxsize 100M extent management local;

Tablespace created.

SQL> create tablespace test_data logging datafile '/u01/app/oracle/oradata/orcl/test_data.dbf'

2 size 10M autoextend on next 20M maxsize 100M extent management local;

Tablespace created.

SQL> create user yuanlei identified by leiyuan default tablespace test_data temporary tablespace test_temp;

User created.

------查看所有用户

SELECT * FROM DBA_USERS;

-----查看用户所在的默认和临时表空间，后面可跟where 条件

SQL> select username,default_tablespace,temporary_tablespace from dba_users;

-----修改用户的默认和临时表空间

SQL> alter user yuanlei default tablespace users;

User altered.

SQL> alter user yuanlei temporary tablespace temp;

User altered.

----限定用户在表空间上的使用配额

SQL> alter user yuanlei quota 10M on users;

User altered.

SQL> alter user yuanlei quota unlimited on users quota 1M on example;

User altered.

----查看用户的表空间配额限制可加条件

SQL> select * from dba_ts_quotas;

TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO

------------------------------ ------------ ---------- ---------- ---------- ---------- ---

SYSAUX OLAPSYS 7667712 -1 936 -1 NO

SYSAUX SYSMAN 76939264 -1 9392 -1 NO

SYSAUX FLOWS_FILES 0 -1 0 -1 NO

USERS YUANLEI 0 -1 0 -1 NO

EXAMPLE YUANLEI 0 1048576 0 128 NO

SYSAUX APPQOSSYS 0 -1 0 -1 NO

6 rows selected.

select * from user_ts_quotas;

TABLESPACE_NAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO

------------------------------ ---------- ---------- ---------- ---------- ---

USERS 0 -1 0 -1 NO

EXAMPLE 0 1048576 0 128 NO

-----取消用户的表空间配额限制

SQL> alter user yuanlei quota unlimited on users;

User altered.

SQL> alter user yuanlei quota unlimited on example;

User altered.

SQL> select * from dba_ts_quotas;

TABLESPACE_NAME USERNAME BYTES MAX_BYTES BLOCKS MAX_BLOCKS DRO

------------------------------ ------------ ---------- ---------- ---------- ---------- ---

SYSAUX OLAPSYS 7667712 -1 936 -1 NO

SYSAUX SYSMAN 76939264 -1 9392 -1 NO

SYSAUX FLOWS_FILES 0 -1 0 -1 NO

EXAMPLE YUANLEI 0 -1 0 -1 NO

USERS YUANLEI 0 -1 0 -1 NO

SYSAUX APPQOSSYS 0 -1 0 -1 NO

6 rows selected.

----锁定用户

SQL> alter user yuanlei account lock;

----解锁用户

SQL> alter user yuanlei account unlock;

----强制用户修改密码

SQL> alter user yuanlei password expire;

User altered.

----删除用户

SQL> drop user yuanlei;

User dropped.

如果用户模式非空

drop user yuanlei cascade;

查看所有系统权限

select * from system_privilege_map;

查看所有对象权限

select * from table_privilege_map;

查看用户的系统权限

SELECT * FROM DBA_SYS_PRIVS

SELECT * FROM USER_SYS_PRIVS;

查看用户对象权限

SELECT * FROM DBA_TAB_PRIVS;

SELECT * FROM ALL_TAB_PRIVS;

SELECT * FROM USER_TAB_PRIVS;

----sys用户下查看所有用户和角色的系统权限授予情况

select grantee,count(*) from dba_sys_privs

group by grantee order by grantee;

----当前用户下查看当前用户拥有的系统权限

SQL> select * from user_sys_privs;

USERNAME PRIVILEGE ADM

------------------------------ --------------------- ---

SCOTT SELECT ANY DICTIONARY NO

SCOTT UNLIMITED TABLESPACE NO

----查看当前用户授给其他用户的对象权限

SQL> select * from user_tab_privs;

GRANTEE OWNER TABLE_NAME GRANTOR PRIVILEGE GRA HIE

---------- ---------- ---------- ---------- ---------- --- ---

ORACLE SCOTT EMP SCOTT SELECT NO NO

-----连带管理系统权限，权限转移后权限不会被级联回收

grant select any table to u1 with admin option;

-----对象权限会级联回收

grant delete on emp to u1 with grant option;

-----查看所有角色

SQL>select * from dba_roles;

-----查看莫一个角色拥有的系统权限

SQL> select * from dba_sys_privs where grantee='DBA';

SQL> select * from role_sys_privs where role='RESOURCE';

SQL> select * from role_sys_privs where role in('CONNECT','RESOURCE');

SQL> select * from dba_sys_privs where grantee in('CONNECT','RESOURCE');

-----查看角色之间的嵌套关系和所授予的用户

SQL> select * from dba_role_privs order by 2;

-----修改用户所最大拥有的角色个数

SQL> alter system set max_enabled_roles=148 scope=spfile;

System altered.

----查看角色属性

SQL> select * from role_tab_privs where role='R1';

ROLE OWNER TABLE_NAME COLUMN_NAME PRIVILEGE GRA

---------- ---------- --------------- --------------- ------------ ---

R1 SCOTT EMP SELECT NO

----当前用户会话拥有的角色

SQL> show user

USER is "SCOTT"

SQL> select * from session_roles;

ROLE

------------------------------

CONNECT

RESOURCE

----查看某个用户的角色

select GRANTEE,GRANTED_ROLE from dba_role_privs

where grantee='SCOTT';

----指定某个角色为默认角色。

SQL> conn system/a

SQL> alter user u1 default role none;没有任何角色

SQL> alter user u1 default role r1;

SQL> conn u1/u1

SQL> select * from session_roles;

----角色的切换

SQL> conn u1/u1

SQL> set role all;此刻拥有全部所授予的角色

SQL> set role r1;只有r1

SQL> set role r2;

SQL> select * from session_roles;

----角色的密码验证

在角色切换的时候，需要指定密码。

SQL> conn system/a

SQL> alter role r2 identified by r2;

SQL> alter user u1 default role r1;

----取消角色的密码：

alter role r1 not identified;

----我们要把非默认的角色保护起来。

SQL> conn u1/u1

SQL> set role r2 identified by r2;

上一篇：redhat5.5下安装oracle 10g 首界面出语言乱码

下一篇：linux 下crontab管理RMAN一周自动增量备份

提问和评论都可以，用心的回复会被更多人看到评论

发布评论

相关文章

官方博客	全部文章	热门标签	班级博客
了解我们	网站地图	意见反馈

鸿蒙开发者社区	51CTO学堂
51CTO	软考资讯