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;