Oracle数据库管理 DBA必会知识点
Oracle数据库管理 DBA必会知识点
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;
评论暂时关闭