Oracle常用命令 檢視資料庫的SQL

訪問資料庫
------誰正在訪問資料庫?
Select c.sid, c.serial#,c.username,a.object_id,b.object_name,
c.program,c.status,d.name,c.osuser
from v$Locked_object a,
All_objects b,
v$session c,
audit_actions d
where a.object_id=b.object_id
and a.session_id =c.sid(+)
and c.command=d.action;

alter system kill session '&1,&2';

Select a.sid,a.serial#,a.username,a.status,a.program,b.name,a.osuser
from v$session a,audit_actions b
where a.command=b.action
And username='&1';
------誰被鎖住?
Select a.sid,a.serial#,a.username,A.LOCKWAIT,a.status,a.program,b.name
from v$session a,audit_actions b
where a.command=b.action
AND LOCKWAIT IS NOT NULL;
------誰在鎖表?
Select a.sid,a.serial#,a.username,A.LOCKWAIT,a.status,a.program,b.name
from v$session a,audit_actions b
where a.command=b.action
AND STATUS='ACTIVE';

Select sid, serial#, object_name, row_wait_block#,
row_wait_row#, row_wait_file#
from all_objects, v$session
where row_wait_obj#=object_id and type='USER'
and lockwait is not null ;

Select sl.username, sl.sid, sl.serial#
from v_$lock l1, v$session s1
where exists (select * from v_$lock l2, v$session s2
where l2.sid=s2.sid and l2.id1=l1
and s2.lockwait=l2.kaddr
and request=0
and l1.sid=s1.sid) ;

select count(*) from v$session;
select count(*) from sys.v_$process;
select count(*) from sys.v_$transaction;

ZYP_35.98

------檢視哪些包要固定
COLUMN OWNER FORMAT A10
Select owner, name, type,
source_size+code_size+parsed_size+error_size BYPES
from dba_object_size
where type='PACKAGE BODY' ORDER BY 4 DESC ;

------檢視一個使用者擁有哪些表空間的實體資訊:
Select tablespace_name, owner, segment_name,segment_type
from dba_segments
where owner-'SyS'
and segment_type_-'ROLLBACK'
order by tablespace_name, owner, segment_name ;

break on owner on segment_name
COLUMN segment_name FORMAT A15
cOLUMN tablespace_name FORMAT A15
COLUMN file_name FORMAT A20
SELECT A.owner, a.segment_name, b.tablespace_name, b.file_name,
sum(a.bytes) bytes
from dba_extents a, dba_data_files b
where a.file_id-b.file_id group by a.owner, a.segment_name,
b.tablespace_name, b.file_name ;

------看記憶體緩衝區使用效率的指數是命中率HITS:
Hits=Logical_reads/(logical_reads+physical_reads)
其中：logical_reads=db_block_gets+consistent_reads

select cur.value db, con.value con, phy.value phy,
(cur.value+con.value)/cur.value+con.value+phy.value)*100 HITS
from v$sysstat cur, v$sysstat con, v$sysstat phy
where CUR.NAME='db block gets' AND
CON.NAME='consistent gets' AND
PHY.NAME='physical reads' ;

------如何檢測ROLLBACK SEGMENT競爭?
select class, count from v$waitstat
where class in
('system undo header', 'system undo block',
'undo header', 'undo block') ;

select sum(value) from v$sysstat where name in
('db block gets', 'consistents gets') ;

若count/sum(value)大於1%，則應考慮增加ROLLBACK SEGMENT

------檢視有事務在哪幾個回退段中：
COLUMN u FORMAT A15
COLUMN s FORMAT A15
COLUMN s FORMAT A80
select osuser o, username u, segment_name s, sa.sql_text
from v$session s, v$transaction t, dba_rollback_segs r, v$sqlarea sa
where s.taddr=t.addr and t.sidusn=r.segmant_id(+)
and s.sql_address=sa.address(+) ;
　

檢視資料庫的SQL

1、檢視錶空間的名稱及大小

select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size
from dba_tablespaces t, dba_data_files d
where t.tablespace_name = d.tablespace_name
group by t.tablespace_name;

2、檢視錶空間物理檔案的名稱及大小

select tablespace_name, file_id, file_name,
round(bytes/(1024*1024),0) total_space
from dba_data_files
order by tablespace_name;

3、檢視回滾段名稱及大小

select segment_name, tablespace_name, r.status,
(initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent,
max_extents, v.curext CurExtent
From dba_rollback_segs r, v$rollstat v
Where r.segment_id = v.usn(+)
order by segment_name ;

4、檢視控制檔案

select name from v$controlfile;

5、檢視日誌檔案

select member from v$logfile;

6、檢視錶空間的使用情況

select sum(bytes)/(1024*1024) as free_space,tablespace_name
from dba_free_space
group by tablespace_name;

SELECT A.TABLESPACE_NAME,A.BYTES TOTAL,B.BYTES USED, C.BYTES FREE,
(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE"
FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME;

7、檢視資料庫庫物件

select owner, object_type, status, count(*) count# from all_objects group by owner, object_type, status;

8、檢視資料庫的版本　

Select version FROM Product_component_version
Where SUBSTR(PRODUCT,1,6)='Oracle';

9、檢視資料庫的建立日期和歸檔方式

Select Created, Log_Mode, Log_Mode From V$Database;






1、檢視當前所有物件

SQL> select * from tab;

2、建一個和a表結構一樣的空表

SQL> create table b as select * from a where 1=2;

SQL> create table b(b1,b2,b3) as select a1,a2,a3 from a where 1=2;

3、察看資料庫的大小，和空間使用情況

SQL> col tablespace format a20
SQL> select b.file_id　　檔案ID,
　　b.tablespace_name　　表空間,
　　b.file_name　　　　　物理檔名,
　　b.bytes　　　　　　　總位元組數,
　　(b.bytes-sum(nvl(a.bytes,0)))　　　已使用,
　　sum(nvl(a.bytes,0))　　　　　　　　剩餘,
　　sum(nvl(a.bytes,0))/(b.bytes)*100　剩餘百分比
　　from dba_free_space a,dba_data_files b
　　where a.file_id=b.file_id
　　group by b.tablespace_name,b.file_name,b.file_id,b.bytes
　　order by b.tablespace_name
　　/
　　dba_free_space --表空間剩餘空間狀況
　　dba_data_files --資料檔案空間佔用情況


4、檢視現有回滾段及其狀態

SQL> col segment format a30
SQL> SELECT SEGMENT_NAME,OWNER,TABLESPACE_NAME,SEGMENT_ID,FILE_ID,STATUS FROM DBA_ROLLBACK_SEGS;

5、檢視資料檔案放置的路徑

SQL> col file_name format a50
SQL> select tablespace_name,file_id,bytes/1024/1024,file_name from dba_data_files order by file_id;

6、顯示當前連線使用者

SQL> show user

7、把SQL*Plus當計算器

SQL> select 100*20 from dual;

8、連線字串

SQL> select 列1::列2 from 表1;
SQL> select concat(列1,列2) from 表1;

9、查詢當前日期

SQL> select to_char(sysdate,'yyyy-mm-dd,hh24:mi:ss') from dual;

10、使用者間複製資料

SQL> copy from user1 to user2 create table2 using select * from table1;

11、檢視中不能使用order by，但可用group by代替來達到排序目的

SQL> create view a as select b1,b2 from b group by b1,b2;

12、透過授權的方式來建立使用者

SQL> grant connect,resource to test identified by test;

SQL> conn test/test

　



　

How to find the tablespace of a table?

SELECT tablespace_name
FROM all_tables
WHERE table_name = 'YOURTABLENAME';
How to remove duplicate rows from a table

If the unique/primary keys can be identified from the table, it is easier to remove the records from the table using the following query:
DELETE FROM tablename
WHERE rowid not in (SELECT MIN(rowid)
FROM tablename
GROUP BY column1, column2, column3...);
Here column1, column2, column3 constitute the identifying key for each record.
If the keys cannot be identified for the table, you may create a temporary table using the query
CREATE TABLE temptablename
AS SELECT DISTINCT *
FROM tablename;
Then drop the original table and rename the temp table to original tablename.
How to identify and remove bad 'child' records to enable / create a foreign key ("Parent Keys Not Found" error when you try to enable/create relation from child table to parent table!)

The records can be identified and removed using the query

DELETE FROM childtablename ct
WHERE NOT EXISTS (SELECT 'x' FROM parenttablename pt
WHERE ct.keycolumn1 = pt.keycolumn1 AND ct.keycolumn2 = pt.keycolumn2...)
Or if you need to provide the user with bad records you may change the DELETE to SELECT with column list.

Find total number of records in a table

The simple query to find the total number of records is

SELECT COUNT(*) FROM tablename;

If you want to see the record count of more than one table, you may

SELECT TABLE_NAME, NUM_ROWS
FROM ALL_TABLES
WHERE TABLE_NAME LIKE 'searchstring';
if the tables are ANALYZED.

Or you may create a script quickly by

SET PAGES 0 FEEDBACK OFF ECHO OFF VERIFY OFF TERMOUT OFF
SPOOL COUNT.SQL
SELECT 'SELECT COUNT(*) FROM ' :: TABLE_NAME :: ';'
FROM ALL_TABLES
WHERE TABLE_NAME LIKE 'searchstring';
SPOOL OFF
SET TERMOUT ON
@COUNT.SQL
SET FEEDBACK ON VERIFY ON PAGES 24

What is my current session id?

The username, program, machine, terminal, session id, serial # and more can be found from the v$session view. This view has a column audsid. When you join this coulum to your userenv('sessionid') value, you get the session information for your current session. The query could be

SELECT USERNAME, SID, SERIAL#, PROGRAM FROM V$SESSION
WHERE AUDSID = USERENV('SESSOINID');
How to terminate a session?

Using the above method you find the SID and SERIAL# for the session you wish to terminate. Then issue the command

ALTER SYSTEM KILL SESSION 'sid, serial#';

Please note that the sid and serial# should be in quotes separated by a comma.

Which database am I connected to? As which user?

The database name can be found out from different views. The view which everyone has access is GLOBAL_NAME. The query is

SELECT GLOBAL_NAME FROM GLOBAL_NAME;

To find the user, from sqlplus you can do "SHOW USER".

What is the SGA size?

There are two simple ways to find this. The first is to invoke server manager (svrmgrl) and connect ineternal (or as any user). Issue the command "SHOW SGA". Or you can run the query "SELECT * FROM V$SGA;" from svrmgrl or sqlplus.

Where are my alert log and dump files written to?

The alert file is written to your BACKGROUND_DUMP_DEST. This variable is set in the config.ora (init.ora) file. You can find the current values of the dump directories from the database. Invoke svrmgrl and connect. Issue command "SHOW PARAMETER DUMP". The SHOW PARAMETER command can be used to find the value of any database parameter value. For example if you want to find the block size and block buffers, issue command "SHOW PARAMETER BLOCK".

How to create structure (no data) of a table from another table?

If you need to duplicate a table you can do a "create table newtablename as select * from tablename;" This will create the new table will all the data. If you need to create only the structure, add a where condition "1=2" or some condition which is always false.

CREATE TABLE NEWTABLENAME AS
SELECT * FROM OLDTABLENAME WHERE 1=2;
How to increase the size of a tablespace?

The size of the tablespace is increased by changing the size of the size of the underlying physical files. You can either add more space to the existing file by

ALTER DATABASE DATAFILE 'filename' RESIZE nn M;

OR you can add more physical datafiles to the tablespace by

ALTER TABLESPACE tablespacename
ADD DATAFILE 'filename' SIZE nn M;
Make sure you specify the full path name for the filename. Use the script tsinfo.sql to find the size and related physical files of a tablespace.

Is my database running in Archivelog mode? Where are the archived files written?

This can be found by invoking server manager (svrmgrl) and issuing the command "ARCHIVE LOG LIST".[] 　

一、ORACLE的表的分類：
1、REGULAR TABLE：普通表，ORACLE推薦的表，使用很方便，人為控制少。
2、PARTITIONED TABLE：分割槽表，人為控制記錄的分佈，將表的儲存空間分為若干獨立的分割槽，記錄按一定的規則儲存在分割槽裡。適用於大型的表。

二、建表
1 CREATE TABLE 表名 （EMPNO NUMBER（2），NAME VARCHAR2（20）） PCTFREE 20 PCTUSED 50
STORAGE （INITIAL 200K NEXT 200K MAXEXTENTS 200 PCTINCREASE 0） TABLESPACE 表空間名稱
[LOGGING:NOLOGGING]所有的對錶的操作都要記入REDOLOG，ORACLE建議使用NOLOGGING；
[CACHE:NOCACHE]：是否將資料按照一定的演算法寫入記憶體。
2、關於PCTFREE 和PCTUSED
A、行遷移和行連結
B、PCTFREE：制止INSERT，為 UPDATE留FREE 空間
C、PCTUSED：為恢復INSERT操作，而設定的。

三、複製一個已經存在的表：
CREATE TABLE 新表名 STORAGE（。。） TABLESPACE 表空間
AS SELECT * FROM 老表名 ；
當老表存在約束，觸發的時候，不會拷過去。

四、修改表的引數
ALTER TABLE 名稱 PCTFREE 20 PCTUSED 50 STOAGE（MAXEXTENTS 1000）；

五、手工分配空間：

ALTER TABLE 名稱 ALLOCATE EXTENT（SIZE 500K DATAFILE '。。'）；

1、SIZE選項，按照NEXT分配
2、表所在表空間與所分配的資料檔案所在的表空間必須一樣。

六、水線
1、水線定義了表的資料在一個BLOCK中所達到的最高的位置。
2、當有新的記錄插入，水線增高
3、當刪除記錄時，水線不回落
4、減少查詢量

七、如何回收空間：
ALTER TABLE 名稱 DEALLOCATE UNUSED [KEEP 4[M:K]]
1、當空間分配過大時，可以使用本命令
2、如果沒有加KEEP，回收到水線
3、如果水線《MINEXTENTS的大小回收到MINEXTENTS所指定的大小

八、TRUNCATE 一個表
TRUNCATE TABLE 表名，表空間擷取MINEXTENT，同時水線重置。

九、DROP 一個表
DROP TABLE 表名 [CASCADE CONSTRAINTS]
當一個表含有外來鍵的時候，是不可以直接DROP的，加CASCADE CONSRIANTS將外來鍵等約束一併刪掉。

十、資訊獲取
1、dba_object
2 dba_tables:建表的引數
3 DBA_SEGMENTS:
組合查詢的連線欄位：DBA_TABLES的table_name+dba_ojbect的object_name+dba_segments的SEGMENT_NAME

第十一章：索引的管理
一、索引的分類：
1、邏輯上：
單列索引 複合索引 唯一索引 非唯一索引
2、物理上：
B-TREE OR BITMAP
B-TREE （NORMAL和反向索引）

二、CREATE INDEX
CREATE INDEX 名稱 ON 表名（列名） PCTFREE 30 STORAGE（。。。。。） TABLESPACE 名稱
沒有定義PCTUSED：索引是按照一定儲存的，如果透過PCTUSED允許恢復對BLOCK的INSERT操作，可能影響INDEX的效率。

三、建立索引的注意事項
1、索引對查詢效能有提高，但對DML語句有影響。
2、索引也應該放在一個專用的表空間
3、定義索引的EXTENT的大小時，=5*DB BLOCK
4、建立索引時，應採用 NOLOGGING
方式。
四、修改索引
ALTER INDEX 名稱 STORAGE（新值）

五、分配空間給索引
1、ALTER INDEX 名稱 ALLOCATE EXTENT（SIZE 200K DATAFILE '。。'）

六、重建索引
1、提高查詢效能
2、當一個索引重建時，老的索引會在新索引建立完成後，被刪除。
3、新索引建立過程中，老的索引仍可用於查詢。
4、硬碟的開銷大，

七、DROP一個索引
DROP INDEX 名稱

八、資訊獲取
1、DBA_INDEXES:建索引的引數
2、DBA_IND_COLUMNS:

第十二章：使用者的管理
一、ORACLE的安全域
1、TABLESPACE QUOTAS：表空間的使用定額
2、DEFAULT TABLESPACE：預設表空間
3、TEMPORARY TABLESPACE：指定臨時表空間。
4、ACCOUNT LOCKING：使用者鎖
5、RESOURCE LIMITE：資源限制
6、DIRECT PRIVILEGES：直接授權
7、ROLE PRIVILEGES：角色授權先將應用中的使用者劃為不同的角色，
二、建立使用者時的清單：
1、選擇一個使用者名稱稱和檢驗機制：A，看到使用者名稱，實際操作者是誰，業務中角色。
2、選擇合適的表空間：
3、決定定額：
4、口令的選擇：
5、臨時表空間的選擇：先建立一個臨時表空間，然後在分配。不分配，使用SYSTEM表空間
6、CREATE USER
7、授權：A，使用者的工作職能
B，使用者的級別
三、使用者的建立：
1、命令：
CREATE USER 名稱 IDENTIFIED BY 口令 DEFAULT TABLESPACE 預設表空間名 TEMPOARAY
TABLESPACE 臨時表空間名
QUOTA 15M ON 表空間名
[PASSWORD EXPIRE]：當使用者第一次登陸到ORACLE，建立時所指定的口令過期失效，強迫使用者自己定義一個新口令。
[ACCOUNT LOCK]：加使用者鎖
QUOTA UNLIMITED ON TABLESPACE：不限制，有多少有多少。
[PROFILE 名稱]：受PROFILE檔案的限制。

四、如何控制使用者口令和使用者鎖
1、強迫使用者修改口令：ALTER USER 名稱 IDENTIFIED BY 新口令 PASSWORD EXPIRE；
2、給使用者加鎖：ALTER USER 名稱 ACCOUNT [LOCK:UNLOCK]
3、注意事項：
A、所有操作對當前連線無效
B、1的操作適用於當使用者忘記口令時。

五、更改定額
1、命令：ALTER USER 名稱 QUOTA 0 ON 表空間名
ALTER USER 名字 QUOTA （數值）K:M:UNLIMITED ON 表空間名；
2、使用方法：
A、控制使用者資料增長
B、當使用者擁有一定的資料，而管理員不想讓他在增加新的資料的時候。
C、當將使用者定額設為零的時候，使用者不能建立新的資料，但原有資料仍可訪問。

六、DROP一個USER
1、DROP USER 名稱
適合於刪除一個新的使用者
2、DROP USER 名稱 CASCADE： 刪除一個使用者，將使用者的表，索引等都刪除。
3、對連線中的使用者不好用。


七、資訊獲取：
1、DBA_USERS:使用者名稱，狀態，加鎖日期，預設表空間，臨時表空間
2、DBA_TS_QUOTAS:使用者名稱，表空間名，定額。
兩個表的連線欄位：USERNAME
GRANT CREATE SESSION TO 使用者名稱


第十三章：PROFILE的管理（資源
檔案）
一、PROFILE的管理內容：
1、CPU的時間
2、I/O的使用
3、IDLE TIME（空閒時間）
4、CONNECT TIME（連線時間）
5、併發會話數量
6、口令機制：

二、DEFAULT PROFILE：
1、所有的使用者建立時都會被指定這個PROFILE
2、DEFAULT PROFILE的內容為空，無限制

三、PROFILE的劃分：
1、CALL級LIMITE：
物件是語句：
當該語句資源使用溢位時：
A、該語句終止
B、事物回退
C、SESSION連線保持
2、SESSION級LIMITE：
物件是：整個會話過程
溢位時：連線終止

四、如何管理一個PROFILE
1、CREATE PROFILE
2、分配給一個使用者
3、象開關一樣開啟限制。

五、如何建立一個PROFILE：
1、命令：CREATE PROFILE 名稱
LIMIT
SESSION_PER_USER 2
CPU_PER_SESSION 1000
IDLE_TIME 60
CONNECT_TIME 480
六、限制引數：
1、SESSION級LIMITE：
CPU_PER_SESSION:定義了每個SESSION佔用的CPU的時間： （1/100 秒）
2、SESSION_PER_USER:每個使用者的併發連線數
3、CONNECT_TIME:一個連線的最長連線時間（分鐘）
4、LOGICAL_READS_PER_SESSION: 一次讀寫的邏輯塊的數量
5、CALL級LIMITE
CPU_PER_CALL:每個語句佔用的CPU時間

LOGICAL_READS_PER_CALL:

七、分配給一個使用者：
CREATE USER 名稱。。。。。。
PROFILE 名稱
ALTER USER 名稱 PROFILE 名稱

八、開啟資源限制：
1、RESOURCE_LIMT:資原始檔中含有
2、ALTER SYSTEM SET RESOURCE_LIMIT=TRUE；
3、預設不開啟

九、修改PROFIE的內容：
1、ALTER PROFILE 名稱引數 新值
2、對於當前連線修改不生效。


十、DROP一個PROFILE
1、DROP PROFILE 名稱
刪除一個新的尚未分配給使用者的PROFILE，
2、DROP PROFILE 名稱 CASCADE
3、注意事項
A、一旦PROFILE被刪除，使用者被自動載入DEFAULT PROFILE
B、對於當前連線無影響
C、DEFAULT PROFILE不可以被刪除
十一、資訊獲取：
1、DBA_USERS:
使用者名稱，PROFILE
2、DBA_PROFILES:
PROFILE及各種限制引數的值
每個使用者的限制：PROFILE（關鍵欄位）
十二、PROFILE的口令機制限制
1、限制內容
A、限制連續多少次登入失敗，使用者被加鎖
B、限制口令的生命週期
C、限制口令的使用間隔
2、限制生效的前提：
A、RESOURCE_LIMIT:=TRUE
B ORACLERDBMSADMINUTLPWDMG.SQL
3、如何建立口令機制：
CREATE PROFILE 名稱
SESSIONS_PER_USER

.....
password_life_time 30
failed_log_attempts 3
password_reuse_time 3
4、引數的含義：
A FAILED_LOGIN_ATTEMPTS:
當連續登陸失敗次數達到該引數指定值時，使用者加鎖
B PASSWORD_LOCK_TIME:加鎖天數
C PASSWORD_LIFE_TIME:口令的有效期（天）
D PASSWORD_GRACE_TIME:口令修改的間隔期（天）
E PASSWORD_REUSE_TIME:口令被修改後原有口令隔多少天被重新使用。
F PASSWORD_REUSE_MAX:口令被修改後原有口令被修改多少次被重新使用。


第十四章:ORACLE的許可權管理
一 授權的兩種分類:
1 SYSTEM 授權:允許特定的使用者對特定的一類物件做特定的操作.可操作的物件:TABLES,INDEXES,PROCEDURES,SEGMENTS;系統授權是對某一類物件.
GRANT SELECT ANY TABLE TO ZT

2 OBJECT(物件授權):
A 允許一個特定的使用者對一個特定的物件做特定的操作.物件:TABLE,INDEX,SEGMENT,..
GRANT UPDATE ON EMP TO ZT

二 系統授權的特點
1 ORACLE中有超過80種以上的 SYSTEM授權
2 幾乎所有的SYSTEM授權包含 ANY關鍵字
3 SYSTEM授權:GRANT授權
4 SYSTEM授權:REVOKE回收
5 允許使用者做系統一級的操作,建表空間,建SESSION.
6 最常用的系統授權:
INDEX:CREATE ANY INDEX
ALTER ANY INDEX
DROP ANY INDEX

TABLE:CREATE ANY TABLE
ALTER ............
DROP .,.......
SELECT .......
UPDATE.........
SESSION:CREATE SESSION
ALTER SESSION
RESTRICTED SESSION
TABLESPACE: CREATE TABLESPACE
ALTER TABLESPACE
DROP TABLESPACE

三,如何授權:

1 命令格式
GRANT CREATE SESSION,SELECT ANY TABLE TO [USERNAME:PUBLIC:ROLE]

2帶有轉授許可權:
A 轉授:當A使用者得到系統授權後, 如果可以將這個許可權授給別人,稱轉授.

GRANT CREATE SESSION,SELECT ANY TABLE
TO [USERNAME:PUBLIC:ROLE] WITH ADMIN OPTION

舉例:
1 SYSTEM->ZT
GRANT CREATE SESSION TO ZT WITH ADMIN OPTION
ZT使用者可以擁有CREATE SESSION許可權
2 ZT->OLM
GRANT CREATE SESSION TO OLM,
ZT,OLM也有了.

四 SYSDBA和SYSOPER系統許可權
1 SYSOPER:SYSTEM OPERATOR:系統操作員
STARTUP,SHUTDOWN;
ALTER DATABASE[MOUNT:OPEN];
RECOVER TABLESPACE

BACKUP DATABASE
ARCHIVELOG OR NOARCHIVELOG;
2 SYSDBA
SYSTEM DABASE ADMIN:資料庫管理員,
SYSOPER WITH ADMIN OPTION
RECOVER DATABASE
CREATE DATABASE

五 資訊獲取
DBA_SYS_PRIVS
GRANTEE:得到許可權的人
PRIVILEGE:得到何種許可權
ADMIN OPTION:是否可以轉授
想知道SCOTT有多少許可權
SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE='SCOTT'

六 回收許可權:
REVOKE 許可權 FROM [USERNAME:PUBLIC:ROLE]
舉例:
SYSTEM->ZT(CREATE SESSION WITH ADMIN OPTION)
ZT->OLM(CREATE SESSION)

SYS ZT OLM

PRIV Y Y Y
REVOKE Y N Y


七 物件授權

TABLE: ALTER DELETE SELECT UPDATE INSERT
INDEX:DELETE INSERT SELECT UPDATE

少CREATE,物件是已經存在的事物,授權者是所有者.

一,授權:
GRANT UPDATE(ENAME) ON EMP TO ZT WITH GRANT OPTION
二資訊獲取
1 DBA_TAB_PRIVS:
GRANTEE:得到者
GRANTOR:授權者
PRIVILEGE:許可權
GRANTABLE:是否可以轉授
OWNER:所有者
三 回收物件授權
1 REVOKE 許可權 FROM 使用者



SYSTEM->ZT(UPDATE ON EMP WITH GRANT OPTION)
ZT->OLM(UPDATE ON EMP)

SYS ZT OLM

PRIV Y Y Y
REVOKE Y N N
第十五章:角色管理
一,角色及其特點:
1,角色實際上是若干許可權的集合體

2,DBA透過為應用中的不同使用者,不同職責,定義不同的角色,可以達到減少工作量的目的.
3,角色的使用同授權一樣,可以用 GRANT授權,REVOKE回收.
4,角色可以象開關一樣開啟關閉
5,角色的使用可以提高效能.
6,角色的使用可以大大減少工作量


二 建立角色
1 CREATE ROLE 名
2 CREATE ROLE 名
IDENTIFIED BY 口令

三 修改角色

1 ALTER ROLE 名稱 IDENTIFIED BY 口令
將沒有口令的加一個口令

2ALTER ROLE 名稱 NOT IDENTIFIED
將有口令的變為沒有口令

三 分配一個角色
GRANT ROLE 名 TO 使用者名稱

四 回收一個角色
REVOKE ROLE 名 FROM 使用者名稱


五 如何建立一個預設角色
只有設為預設角色,才可以在使用者登入時,使角色所含有的許可權生效
1 ALTER USER SCOTT
DEFAULT ROLE 角色1,角色2

2 ATLER USR SCOTT
DEFAULT ROLE ALL

3 ALTER USER SCOTT DEFAULT ROLE ALL EXCEPT 角色名

4 ALTER USER SCOTT DEFAULT ROLE NONE

六 開啟和關閉角色:
1 SET ROLE 名稱
2 SET ROLE IDENTIFIED BY 口令,(建立角色時,帶口令)
3 SET ROLE NONE

七 刪除角色
DROP ROLE 名稱

八 資訊獲取
DBA_ROLE:
DBA_ROLE_PRIVS:

九 如何向角色里加許可權:
GRANT 許可權 TO 角色名
REVOKE 許可權 FROM 角色名
角色:1不能跟使用者名稱重複
2 ROLE的ALTER和其他物件的ALTER 不一樣,ROLE的ALTER不能修改ROLE的內容,只可以修改角色的認證方式




1. 監控事例的等待

select event,sum(decode(wait_Time,0,0,1)) "Prev",
sum(decode(wait_Time,0,1,0)) "Curr",count(*) "Tot"
from v$session_Wait
group by event order by 4;

2. 回滾段的爭用情況

select name, waits, gets, waits/gets "Ratio"
from v$rollstat a, v$rollname b
where a.usn = b.usn;

3. 監控表空間的 I/O 比例

select df.tablespace_name name,df.file_name "file",f.phyrds pyr,
f.phyblkrd pbr,f.phywrts pyw, f.phyblkwrt pbw
from v$filestat f, dba_data_files df
where f.file# = df.file_id
order by df.tablespace_name;

4. 監控檔案系統的 I/O 比例

select substr(a.file#,1,2) "#", substr(a.name,1,30) "Name",
a.status, a.bytes, b.phyrds, b.phywrts
from v$datafile a, v$filestat b
where a.file# = b.file#;

5.在某個使用者下找所有的索引

select user_indexes.table_name, user_indexes.index_name,uniqueness, column_name
from user_ind_columns, user_indexes
where user_ind_columns.index_name = user_indexes.index_name
and user_ind_columns.table_name = user_indexes.table_name
order by user_indexes.table_type, user_indexes.table_name,
user_indexes.index_name, column_position;

6. 監控 SGA 的命中率

select a.value + b.value "logical_reads", c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) / (a.value+b.value)) "BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where a.statistic# = 38 and b.statistic# = 39
and c.statistic# = 40;

7. 監控 SGA 中字典緩衝區的命中率

select parameter, gets,Getmisses , getmisses/(gets+getmisses)*100 "miss ratio",
(1-(sum(getmisses)/ (sum(gets)+sum(getmisses))))*100 "Hit ratio"
from v$rowcache
where gets+getmisses <>0
group by parameter, gets, getmisses;

8. 監控 SGA 中共享快取區的命中率，應該小於1%

select sum(pins) "Total Pins", sum(reloads) "Total Reloads",
sum(reloads)/sum(pins) *100 libcache
from v$librarycache;

select sum(pinhits-reloads)/sum(pins) "hit radio",sum(reloads)/sum(pins) "reload percent"
from v$librarycache;

9. 顯示所有資料庫物件的類別和大小

select count(name) num_instances ,type ,sum(source_size) source_size ,
sum(parsed_size) parsed_size ,sum(code_size) code_size ,sum(error_size) error_size,
sum(source_size) +sum(parsed_size) +sum(code_size) +sum(error_size) size_required
from dba_object_size
group by type order by 2;

10. 監控 SGA 中重做日誌快取區的命中率，應該小於1%

SELECT name, gets, misses, immediate_gets, immediate_misses,
Decode(gets,0,0,misses/gets*100) ratio1,
Decode(immediate_gets+immediate_misses,0,0,
immediate_misses/(immediate_gets+immediate_misses)*100) ratio2
FROM v$latch WHERE name IN ('redo allocation', 'redo copy');

11. 監控記憶體和硬碟的排序比率，最好使它小於 .10，增加 sort_area_size

SELECT name, value FROM v$sysstat WHERE name IN ('sorts (memory)', 'sorts (disk)');


12. 監控當前資料庫誰在執行什麼SQL語句

SELECT osuser, username, sql_text from v$session a, v$sqltext b
where a.sql_address =b.address order by address, piece;

13. 監控字典緩衝區

SELECT (SUM(PINS - RELOADS)) / SUM(PINS) "LIB CACHE" FROM V$LIBRARYCACHE;
SELECT (SUM(GETS - GETMISSES - USAGE - FIXED)) / SUM(GETS) "ROW CACHE" FROM V$ROWCACHE;
SELECT SUM(PINS) "EXECUTIONS", SUM(RELOADS) "CACHE MISSES WHILE EXECUTING" FROM V$LIBRARYCACHE;

後者除以前者,此比率小於1%,接近0%為好。

SELECT SUM(GETS) "DICTIONARY GETS",SUM(GETMISSES) "DICTIONARY CACHE GET MISSES"
FROM V$ROWCACHE

14. 找ORACLE字符集

select * from sys.props$ where name='NLS_CHARACTERSET';

15. 監控 MTS

select busy/(busy+idle) "shared servers busy" from v$dispatcher;

此值大於0.5時，引數需加大

select sum(wait)/sum(totalq) "dispatcher waits" from v$queue where type='dispatcher';
select count(*) from v$dispatcher;
select servers_highwater from v$mts;

servers_highwater接近mts_max_servers時，引數需加大

16. 碎片程度

select tablespace_name,count(tablespace_name) from dba_free_space group by tablespace_name
having count(tablespace_name)>10;

alter tablespace name coalesce;
alter table name deallocate unused;

create or replace view ts_blocks_v as
select tablespace_name,block_id,bytes,blocks,'free space' segment_name from dba_free_space
union all
select tablespace_name,block_id,bytes,blocks,segment_name from dba_extents;

select * from ts_blocks_v;

select tablespace_name,sum(bytes),max(bytes),count(block_id) from dba_free_space
group by tablespace_name;

檢視碎片程度高的表

SELECT segment_name table_name , COUNT(*) extents
FROM dba_segments WHERE owner NOT IN ('SYS', 'SYSTEM') GROUP BY segment_name
HAVING COUNT(*) = (SELECT MAX( COUNT(*) ) FROM dba_segments GROUP BY segment_name);

17. 表、索引的儲存情況檢查

select segment_name,sum(bytes),count(*) ext_quan from dba_extents where
tablespace_name='&tablespace_name' and segment_type='TABLE' group by tablespace_name,segment_name;

select segment_name,count(*) from dba_extents where segment_type='INDEX' and owner='&owner'
group by segment_name;

18、找使用CPU多的使用者session

12是cpu used by this session

select a.sid,spid,status,substr(a.program,1,40) prog,a.terminal,osuser,value/60/100 value
from v$session a,v$process b,v$sesstat c
where c.statistic#=12 and c.sid=a.sid and a.paddr=b.addr order by value desc;

[@more@]