ORACLE DB 的學習者們

2013年2月6日 星期三

利用PLSQL查詢系統表格狀態資訊


重置資料庫狀態資訊

表格空間
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> 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>


查詢INSTANCE目前狀態

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

顯示所有的 ROLES

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.