重置資料庫狀態資訊
表格空間
select * from dba_tablespaces;
資料檔案
select * from dba_data_files;
更多資料檔案
select * from v$datafile;
日誌檔案
select * from v$logfile;
更多日誌檔案
select * from v$log;
控制檔案資訊
select * from v$controlfile;
有效的資料庫參數
select * from v$parameter;
取得datafile與tablespace關聯性
select * from v$backup;
有效的語言字元集
select * from v$nls_parameters;
取得三天前的日誌歷史資訊
select * from v$log_history where first_time > sysdate -3;
取得資料字典
select * from dictionary;
亦可使用同義字的VIEW進行查詢，該VIEW名稱為DICT_COLUMNS，用法如
select * from DICT_COLUMNS;
資料字典範例
USER_TABLES
SELECT table_name, tablespace_name FROM
user_tables;
ALL_SEQUENCES
SELECT sequence_name, min_value, max_value,
increment_by FROM all_sequences WHERE
sequence_owner IN ('MDSYS','XDB');
DBA_USERS
SELECT USERNAME, ACCOUNT_STATUS FROM
dba_users WHERE ACCOUNT_STATUS = 'OPEN';
DBA_INDEXES
DESCRIBE dba_indexes;
偵測BLOCK CORRUPTION:
查閱以下VIEW：
V$BACKUP_CORRUPTION
V$COPY_CORRUPTION
要讓某ID使用者可用EM，先進入SQLPLUS：
Open CMD
C:\>Set oracle_sid= SID_OF_THE_DATABASE
C:\>sqlplus
username: SYSMAN
password: ****
SQL>execute MGMT_USER.MAKE_EM_USER(‘USERNAME’);
USERNAME – the user that you use to connect to enterprise managert
don’t copy/past the command, sometimes they are some hidden characters
查詢 FLASH RECOVERY AREA使用率：
SQL> SELECT file_type,
2 percent_space_used AS used,
3 percent_space_reclaimable AS reclaimable,
4 number_of_files AS number
5 FROM v$flash_recovery_area_usage ;
查詢系統參數
SQL> desc v$system_parameter
Name Null? Type
----------------------------------------- -------- ----------------------------
NUM NUMBER
NAME VARCHAR2(80)
TYPE NUMBER
VALUE VARCHAR2(512)
DISPLAY_VALUE VARCHAR2(512)
ISDEFAULT VARCHAR2(9) --若為TRUE,表示此參數並未被明確設定參數值在parameter file內,目前所使用的參數值為預設值
ISSES_MODIFIABLE VARCHAR2(5) --若為TRUE,表示可以使用alter session set parameter=new_value,動態修改此session的此項參數值
ISSYS_MODIFIABLE VARCHAR2(9) --若為IMMEDIATE,表示可以使用alter system set parameter=new_value,動態修改所有session的此項參數值
ISINSTANCE_MODIFIABLE VARCHAR2(5)
ISMODIFIED VARCHAR2(8)
ISADJUSTED VARCHAR2(5)
ISDEPRECATED VARCHAR2(5)
DESCRIPTION VARCHAR2(255)
UPDATE_COMMENT VARCHAR2(255)
HASH NUMBER
SQL> select name,value from v$system_parameter
2 where isdefault='FALSE'; --isdefault='FALSE'表示此參數有明確設定其參數值放在parameter file中
SQL> show parameter spfile --如果有資訊回傳,表示此instance使用spfile開啟
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/dbs/spfileJENG
.ora
SQL> show parameter control_files --sql*plus本身的功能,找出所有參數名稱中有control_files這個字串的參數
SQL> select value from v$system_parameter where name like '%control_files%'; --一般的sql command
SQL> select value from v$system_parameter where name like '%control_files%';
VALUE
--------------------------------------------------------------------------------
/u01/app/oradata/JENG/control01.ctl, /u01/app/flash_recovery_area/JENG/control02
.ctl
SQL>
SQL> show parameter control_files --sql*plus本身的功能,找出所有參數名稱中有control_files這個字串的參數
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/dbs/spfileJENG
.ora
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_files string /u01/app/oradata/JENG/control0
1.ctl, /u01/app/flash_recovery
_area/JENG/control02.ctl
SQL> select value from v$system_parameter where name like '%control_files%'; --一般的sql command
SQL> select value from v$system_parameter where name like '%control_files%';
VALUE
--------------------------------------------------------------------------------
/u01/app/oradata/JENG/control01.ctl, /u01/app/flash_recovery_area/JENG/control02
.ctl
SQL>
SQL> select status from v$instance;
STATUS
------------
OPEN
開啟database的順序
1. startup listener(只需一次) --> $ lsnrctl start
--如果有asm instance,必須在database instance啟動前先開啟.但是只須開啟一次(因為每個host最多只有一個asm instance)
$ export ORACLE_SID=+ASM -> $ sqlplus / as sysdba -> SQL> startup
2. startup database(每個database一次) $ export ORACLE_SID=orcl -> $ sqlplus / as sysdba -> SQL> startup
3. startup dbconsole(每個console一次) $ export ORACLE_SID=orcl -> $ emctl start dbconsole
關閉database的順序
1. shutdown dbconsole(每個console一次) $ export ORACLE_SID=orcl -> $ emctl stop dbconsole
2. shutdown database(每個database一次) $ export ORACLE_SID=orcl -> $ sqlplus / as sysdba -> SQL> shutdown immediate
--關閉asm instance $ export ORACLE_SID=+ASM -> $ sqlplus / as sysdba -> SQL> shutdown immediate
3. stop listener(只需一次) --> $ lsnrctl stop
SQL> select role from dba_roles;
ROLE
------------------------------
CONNECT
RESOURCE
DBA
SELECT_CATALOG_ROLE
EXECUTE_CATALOG_ROLE
DELETE_CATALOG_ROLE
EXP_FULL_DATABASE
IMP_FULL_DATABASE
LOGSTDBY_ADMINISTRATOR
DBFS_ROLE
AQ_ADMINISTRATOR_ROLE
ROLE
------------------------------
AQ_USER_ROLE
DATAPUMP_EXP_FULL_DATABASE
DATAPUMP_IMP_FULL_DATABASE
ADM_PARALLEL_EXECUTE_TASK
GATHER_SYSTEM_STATISTICS
JAVA_DEPLOY
RECOVERY_CATALOG_OWNER
SCHEDULER_ADMIN
HS_ADMIN_SELECT_ROLE
HS_ADMIN_EXECUTE_ROLE
HS_ADMIN_ROLE
ROLE
------------------------------
GLOBAL_AQ_USER_ROLE
OEM_ADVISOR
OEM_MONITOR
WM_ADMIN_ROLE
JAVAUSERPRIV
JAVAIDPRIV
JAVASYSPRIV
JAVADEBUGPRIV
EJBCLIENT
JMXSERVER
JAVA_ADMIN
ROLE
------------------------------
CTXAPP
XDBADMIN
XDB_SET_INVOKER
AUTHENTICATEDUSER
XDB_WEBSERVICES
XDB_WEBSERVICES_WITH_PUBLIC
XDB_WEBSERVICES_OVER_HTTP
ORDADMIN
OLAPI_TRACE_USER
OLAP_XS_ADMIN
OWB_USER
ROLE
------------------------------
OLAP_DBA
CWM_USER
OLAP_USER
SPATIAL_WFS_ADMIN
WFS_USR_ROLE
SPATIAL_CSW_ADMIN
CSW_USR_ROLE
MGMT_USER
APEX_ADMINISTRATOR_ROLE
OWB$CLIENT
OWB_DESIGNCENTER_VIEW
55 rows selected.
顯示所有 GRANTEE
SQL> select distinct grantee from dba_sys_privs;
GRANTEE
------------------------------
EXP_FULL_DATABASE
AQ_ADMINISTRATOR_ROLE
OWBSYS_AUDIT
MDSYS
DIP
DBA
OEM_ADVISOR
SPATIAL_WFS_ADMIN_USR
RECOVERY_CATALOG_OWNER
SCHEDULER_ADMIN
OLAPSYS
GRANTEE
------------------------------
SPATIAL_CSW_ADMIN_USR
OWBSYS
HR
OLAP_USER
FLOWS_FILES
RESOURCE
CTXSYS
OUTLN
MYDBA
IMP_FULL_DATABASE
EXFSYS
GRANTEE
------------------------------
OWB$CLIENT
APEX_030200
DATAPUMP_EXP_FULL_DATABASE
SCOTT
SYSTEM
CONNECT
ORACLE_OCM
DBSNMP
OE
SH
SYSMAN
GRANTEE
------------------------------
ORDSYS
MDDATA
APEX_PUBLIC_USER
PM
XDB
OLAP_DBA
IX
BI
JAVADEBUGPRIV
ORDDATA
APPQOSSYS
GRANTEE
------------------------------
SYS
DATAPUMP_IMP_FULL_DATABASE
OEM_MONITOR
WMSYS
MGMT_USER
LOGSTDBY_ADMINISTRATOR
SI_INFORMTN_SCHEMA
ANONYMOUS
52 rows selected.
SQL> select privilege from dba_sys_privs where grantee='RESOURCE'; -->當被授與resource角色時,Oracle server自動授與unlimited tablespace權限
PRIVILEGE
----------------------------------------
CREATE TRIGGER
CREATE SEQUENCE
CREATE TYPE
CREATE PROCEDURE
CREATE CLUSTER
CREATE OPERATOR
CREATE INDEXTYPE
CREATE TABLE
已選取 8 個資料列.
顯示目前此session可使用的system privilege
SQL> select * from session_privs; --目前此session可使用的system privilege
PRIVILEGE
----------------------------------------
CREATE SESSION
UNLIMITED TABLESPACE -->當被授與resource角色時,Oracle server自動授與unlimited tablespace權限.擁有此權限將不受到tablespace quota的限制
CREATE TABLE
CREATE ANY TABLE
SELECT ANY TABLE
CREATE CLUSTER
CREATE SEQUENCE
CREATE PROCEDURE
CREATE TRIGGER
CREATE TYPE
CREATE OPERATOR
CREATE INDEXTYPE
已選取 12 個資料列.
顯示目前生效的角色
SQL> select * from session_roles; --顯示目前生效的角色
ROLE
------------------------------
RESOURCE
APP_USER1
只要生效app_user1某個角色
SQL> set role app_user1; --只要生效app_user1這個角色,所以resource角色的權限將無法使用
SQL> select * from session_roles;
ROLE
------------------------------
APP_USER1
把角色生效或不生效
SQL> set role all; --生效所有授與給frank的角色
SQL> set role app_user1,resource; --只生效app_user1與resource
SQL> set role none; --讓所有角色都不生效
不可以把 unlimited tablespace 從某個 GRANTEE拿掉，只可以把它從某USER拿掉
SQL> revoke unlimited tablespace from resource;
revoke unlimited tablespace from resource
*
ERROR at line 1:
ORA-01931: cannot grant UNLIMITED TABLESPACE to a role
SQL> revoke unlimited tablespace from frank;
Revoke succeeded.
SQL> revoke unlimited tablespace from resource;
revoke unlimited tablespace from resource
*
ERROR at line 1:
ORA-01931: cannot grant UNLIMITED TABLESPACE to a role
SQL> revoke unlimited tablespace from frank;
Revoke succeeded.
