Oracle442個應用執行個體----------概要檔案，概要設計說明書執行個體

Oracle442個應用執行個體----------概要檔案，概要設計說明書執行個體

--------------------------概要檔案講解：---------------------------------
Oracle系統為了合理分配和使用系統的資源提出了概要檔案的概念。所謂概要檔案，就是一份描述如何使用系統的資源(主要是CPU資源)的設定檔。將概要檔案賦予某個資料庫使用者，在使用者串連並訪問資料庫伺服器時，系統就按照概要檔案給他分配資源。在有的書中將其翻譯為設定檔，其作用包括：
1、管理資料庫系統資源。
利用Profile來分配資源限額，必須把初始化參數resource_limit設定為true
ALTER SYSTEM SET resource_limit=TRUE SCOPE=BOTH;
2、管理資料庫口令及驗證方式。
預設給使用者指派的是DEFAULT概要檔案，將該檔案賦予了每個建立的使用者。但該檔案對資源沒有任何限制，因此管理員常常需要根據自己資料庫系統的環境自行建立概要檔案

一、PROFILE的管理內容：
       1、CPU的時間
       2、I/O的使用
       3、IDLE TIME（空閑時間）
       4、CONNECT TIME（連線時間）
       5、並發會話數量
       6、口令機制：

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

應用情境58：在Enterprise Manager中查看概要檔案資訊

應用情境59：使用DBA_PROFILES視圖查看概要檔案資訊
SELECT * FROM DBA_PROFILES WHERE PROFILES='DEFAULT';

應用情境60：使用概要檔案生效
查看RESOURCE_LIMIT的參數值：
SHOW PARAMETER RESOURCE_LIMIT
將RESOURCE_LIMIT的參數設定成true；
ALTER SYSTEM SET RESOURCE_LIMIT=TRUE;

應用情境61：在Enterprise Manager中建立概要檔案

應用情境62：使用CREATE PROFILE 語句建立概要檔案

CREATE PROFILE<profile name>LIMIT<設定項目><取值>
ED:
CREATE PROFILE ADMINPROFILE LIMIT
SESSION_PER_USER   2
CPU_PER_SESSION   10000
CPU_PER_CALL      DEFAULT
CONNNET_TIME      500
IDLE_TIME         90
PASSWORD_LIFE_TIME 90
PASSWROD_REUSE_TIME 90
PASSWORD_REUSER_MAX 100
FILED_LOGIN_ATTEMPTS  5;

應用情境63：將概要檔案授予使用者

CREATE USER <username> PROFILE <概要檔案>
eg:CREATE USER NEWUSER PROFILE ADMINPROFILE IDENTIFIED BY PWD;

查看使用者NEWUSER的概要檔案
SELECT USERNAME,PROFILE FROM DBA_USERS WHERE USERNAME='NEWUSER';

使用ALTER USER語句修改使用者的概要檔案
ALTER USER<USERNAME> PROFILE<PROFILENAME>
EG:ALTER USER NEWUSER PROFILE DEFAULT;
   SELECT USERNAME, PROFILE FROM DBA_USERS WHERE USERNAME='NEWUSER';

應用情境64：查詢使用者的資源限制和口令設定資訊
SELECT p.PROFILE,p.RESOURCE_NAME,p.LIMIT FROM DBA_USERS DBA_PROFILES P WHERE u.PROFILE=p.PROFILE AND u.USERNAME='NEWUSER';

----------------------資料字典---------------------

　　資料字典是Oracle存放有關資料庫資訊的地方，其用途是用來描述資料的。

　　比如一個表的建立者資訊，建立時間資訊，所屬資料表空間資訊，使用者存取權限資訊等。

　　資料庫資料字典是一組表和視圖結構。它們存放在SYSTEM資料表空間中

　　當使用者在對資料庫中的資料進行操作時遇到困難就可以訪問資料字典來查看詳細的資訊。

　　使用者可以用SQL語句訪問資料庫資料字典。

　　資料字典內容包括：

　　1,資料庫中所有模式對象的資訊，如表、視圖、簇、及索引等。

　　2,分配多少空間，當前使用了多少空間等。

　　3,列的預設值。

　　4,約束資訊的完整性。

　　5,Oracle使用者的名字。

　　6,使用者及角色被授與權限。

　　7,使用者訪問或使用的審計資訊。

　　8,其它產生的資料庫資訊。

　　Oracle中的資料字典有靜態和動態之分。

　　1,待用資料字典-->主要是在使用者訪問資料字典時不會發生改變的，

　　--例如某使用者建立的表

　　2,動態資料字典-->是依賴資料庫啟動並執行效能的，反映資料庫啟動並執行一些內在資訊，所以在訪問這類資料字典時往往不是一成不變的。

　　--當前鎖住的對象

　　待用資料字典:這類資料字典主要是由表和視圖組成

　　資料字典中的表是不能直接被訪問的，但是可以訪問資料字典中的視圖。

　　待用資料字典中的視圖分為三類，它們分別由三個首碼夠成：user_*、 all_*、 dba_*。

　　user_*

　　該視圖儲存了關於目前使用者所擁有的對象的資訊。（即所有在該使用者模式下的對象）

　　all_*

　　該試圖儲存了目前使用者能夠訪問的對象的資訊。（與user_*相比，all_* 並不需要擁有該對象，只需要具有訪問該對象的許可權即可）

　　dba_*

　　該視圖儲存了資料庫中所有對象的資訊。（前提是目前使用者具有訪問這些資料庫的許可權，一般來說必須具有管理員權限）
-------------------------------------------------------------------------------------
應用情境65：在Enterprise Manager中修改概要檔案

應用情境66：使用ALTER PROFILE 語句修改概要檔案
ALTER PROFILE<PROFILENAME>LIMT<PAREMETERNAME><PAREMETERVALUES>......
eg:
ALTER PROFILE ADMINPROFILE LIMIT PASSWORD_LIFE_TIME 60;

應用情境67：刪除概要檔案
DROP PROFILE<概要檔案名稱>[CASCADE];
EG:DROP PROFILE ADMINPROFILE CASCADE;

--------------Schema-----------------
定義：
A schema is a collection of database objects (used by a user.).
Schema objects are the logical structures that directly refer to the database’s data.
A user is a name defined in the database that can connect to and access objects.
Schemas and users help database administrators manage database security.

從定義中我們可以看出schema為資料庫物件的集合，為了區分各個集合，我們需要給這個集合起個名字，這些名字就是我們在企業管理器的方案下看到的許多類似使用者名稱的節點，這些類似使用者名稱的節點其實就是一個schema，schema裡麵包含了各種對象如tables, views, sequences, stored procedures, synonyms, indexes, clusters, and database links。

一個使用者一般對應一個schema,該使用者的schema名等於使用者名稱，並作為該使用者預設schema。這也就是我們在企業管理器的方案下看到schema名都為資料庫使用者名稱的原因。Oracle資料庫中不能新建立一個schema，要想建立一個schema，只能通過建立一個使用者的方法解決(Oracle中雖然有create schema語句，但是它並不是用來建立一個schema的)，在建立一個使用者的同時為這個使用者建立一個與使用者名稱同名的schem並作為該使用者的預設shcema。即schema的個數同user的個數相同，而且schema名字同user名字一一 對應並且相同，所有我們可以稱schema為user的別名，雖然這樣說並不準確，但是更容易理解一些。

一個使用者有一個預設的schema，其schema名就等於使用者名稱，當然一個使用者還可以使用其他的schema。如果我們訪問一個表時，沒有指明該表屬於哪一個schema中的，系統就會自動給我們在表上加上預設的sheman名。比如我們在訪問資料庫時，訪問scott使用者下的emp表，通過select * from emp; 其實，這sql語句的完整寫法為select * from scott.emp。在資料庫中一個對象的完整名稱為schema.object，而不屬user.object。類似如果我們在建立對象時不指定該對象的schema，在該對象的schema為使用者的預設schema。這就像一個使用者有一個預設的資料表空間，但是該使用者還可以使用其他的資料表空間，如果我們在建立對象時不指定資料表空間，則Object Storage Service在預設資料表空間中，要想讓Object Storage Service在其他資料表空間中，我們需要在建立對象時指定該對象的資料表空間。

 

 

----------------資料表空間------------------------

資料表空間屬性：

一個資料庫可以包含多個資料表空間，一個資料表空間只能屬於一個資料庫

一個資料表空間包含多個資料檔案，一個資料檔案只能屬於一個資料表空間

表這空間可以劃分成更細的邏輯儲存單元

採用從小到大，逐步拓展的方式介紹四個結構，並且與Schema、資料檔案的區別進行對比。

資料區塊（Block）

資料區塊Block是Oracle儲存資料資訊的最小單位。注意，這裡說的是Oracle環境下的最小單位。Oracle也就是通過資料區塊來屏蔽不同作業系統儲存結構的差異。無論是Windows環境，還是Unix/Linux環境，他們的作業系統儲存結構和方式、甚至字元排列的方式都是不同的。Oracle利用資料區塊將這些差異加以屏蔽，全部資料操作採用對Oracle塊的操作，相當於是一個層次的抽象。

Oracle所有對資料的操作和空間分配，實際上都是針對資料區塊Block的操作。我們從資料表中搜尋出一行，實際中Oracle就會從記憶體緩衝區（或者硬碟）中讀取到該行所在的資料區塊，再返回這資料區塊上的指定資料行。Oracle無論是在緩衝區，還是在硬碟，進行資料操作的雖小單位也就是資料區塊。

資料區塊是有大小的，在一個資料庫建立的時候，通過參數進行設定。注意，在Oracle資料庫參數中，只有資料區塊大小的參數是建庫之後不能進行修改的。資料區塊的大小，在一個資料庫中可以支援多個，但是一般沒有太大的意義，會給管理和調試帶來一定的負擔。

資料區塊的大小是通過kb位元組個數來指定的，預設為8KB。相關參數為db_block_size，下面是查看block大小的語句。

SQL> show parameter db_block_size;

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_block_size                        integer     8192  //1024×8

 
設定資料區塊的大小是依據不同類型的系統的。如果資料區塊設定比較大，那麼一次讀取的資料行較多，相應對SGA記憶體消耗比較大，特定查詢引發的換入換出可能較多。如果設定的過小，頻繁的IO邏輯物理讀也會引起效能問題。

與資料區塊有關係的另一個參數就是db_file_multiblock_read_count，表示一次從實體儲存體中讀取的資料區塊數量。對一些資料採礦系統，可以考慮調節此參數略大一些。

區exten

區extent是比資料區塊大一級的儲存結構，表示的是一連串聯續的資料區塊集合。我們知道，實體儲存體通常是隨機的讀寫過程。即使在同一個檔案裡，我們也不能保證相同的一個資訊是儲存在絕對連續的實體儲存體空間的。Oracle資料存放區同樣如此。

在進行儲存資料資訊的時候，Oracle將分配資料區塊進行儲存，但是不能保證所有分配的資料區塊都是連續的結構。所以，出現分區extent的概念，表示一系列連續的資料區塊集合。

視圖dba_extents（或者all_extents、user_extents）是我們研究分區結構和儲存構成的重要手段。

從視圖中，我們可以清晰看出分區的幾個特點。

首先分區是帶有段特定性的。資料區段segment是分區的上層組織單位，一個資料庫物件對應一個segement，資料庫物件是歸屬在不同的schema（owner）上的。所以，通過不同的資料區段名稱、不同的owner，乃至不同的tablespace資料表空間資訊，就可以定位到資料區extent的資訊描述。

另一部分資訊是關於該區extent的分配資訊，如所在檔案編號，起始資料區塊block編號和資料區塊數量等內容。

資料區段segment

資料區段是與資料庫物件相對應，一般一個資料庫物件對應一個資料區段。多個extent是對應一個資料區段，每個資料區段實際上就是資料庫一個對象的代表。從dba_segments視圖中，可以比較清楚看清資料區段的結構。

從segment_type列的comment資訊中，可以看出資料區段的類型是多樣的。任何種類的資料庫物件，本質上都是一種資料區段。資料表、索引、復原、聚集這些都是資料區段的一種表現形式。同時，資料區段是在資料對象建立的時候就已經建立出來，隨著對象體積的增大，而不斷分配多個extents進行管理。

 

另一部分資訊可以從dba_segments中讀出的，就是該資料對象分配的空間大小和資料區塊、分區個數。使用這個視圖，可以方便的擷取到指定schema的所有對象大小。

 

SQL> select owner,sum(bytes)/1024/1024 as vol, sum(blocks) as totalblocks,sum(extents) as totalextents from dba_segments group by owner having wner='SYS';

 

OWNER             VOL TOTALBLOCKS TOTALEXTENTS

------------------------------ ---------- ----------- ------------

SYS                 585.5       74944         3248

上面查詢，說明SYS的schema，所佔用空間585.5MB，包括74944個資料區塊和3248個分區。

一個對象建立出來之後，在segment層次上是分配一個分區extent和八個資料區塊block。

有一個問題需要注意，通常我們的資料區段是與資料對象相關。一個資料對象對應一個segment。但是，分區表的時候，一個分區要對應一個segment對象。還有就是，segment對象是可以指定儲存在那個資料表空間裡，實現儲存劃分的基礎也就在於此。不同類型的segment劃分建立在不同的資料表空間裡，才有可能存放在不同的檔案中，最後分布在不同的實體儲存體。

分區實際上就是存在分開儲存的可能。一般一個對象是不會跨實體儲存體進行存放的，分區表是對應的多個segment。所以，分區表分開儲存空間是可能的。

資料表空間tablespace

TableSpace是儲存結構中的最高層結構。建立一個資料表空間的時候，是需要指定儲存的檔案。一個資料表空間可以指定多個資料檔案，多個檔案可以在不同的實體儲存體上。也就是說，資料表空間是可以跨實體儲存體的。但是有一點就是，資料表空間下一級對象資料區段的儲存，是不能指定儲存在那個檔案裡的。所以，要想讓資料對象訪問IO負載平衡，需要指定不同的資料對象在不同的資料表空間裡。這也就是為什麼將資料表和索引建立在不同的資料表空間的原因。

資料表空間通過v$tablespace進行訪問。

SQL> desc v$tablespace;
 

相對於前面的結構視圖，資料表空間視圖的結構要簡單的多，只是一些描述資訊。其中兩個參數需要注意一下。

一個是bigfile，是一個標誌位，標誌資料表空間是不是所謂的大檔案資料表空間。大檔案資料表空間是在10g中推出的一個新特性，處於效能考慮，可以設定資料表空間為大檔案資料表空間，儲存超過百T的資料，但是要求資料檔案只能有一個。另一個是flashback_on，表示資料表空間的閃回特性是否開啟。

要注意，資料表段區塊的概念集合，很容易與schema的結構相混淆。schema是一個組織概念，是來自於經典資料庫理論範疇。在oracle中，Schema就是一個組織概念，一個user對應的就是一個schema。schema是邏輯對象的集合組織，同資料表空間等概念不是一個層面的。

在一個schema裡，是可以將對象建立在任何資料資料表空間內的，只有一個預設資料表空間的概念default tablespace。指定預設資料表空間是在建立使用者的時候指定的。

分清幾個核心概念和各自的區別關係，就能更好的理解Oracle啟動並執行各種機制。
----------------------------------------------------------------------------------

應用情境68：在Enterprise Manager中查看錶空間資訊

應用情境69：使用V$TABLESPACE視圖查看錶空間資訊

SELECT * FROM V$TABLESPACE;

應用情境70：查看錶空間屬性資訊

SELECT * FROM DBA_TABLESPACES;

應用情境71：查看錶空間及其所屬資料表空間的資訊

SELECT * FROM DBA_TABLESPACE_GROUPS;

應用情境72：查看錶空間中所包含的段資訊

SELECT SEGMENT_NAME,SEGMENT_TYPE,TABLESPACE_NAME FROM DBA_SEGMENTS;

應用情境73：查看錶空間中空閑區間的資訊

SELECT TABLESPACE_NAME,BYTES,BLOCKS FROM DBA_FREE_SPACE;

應用情境74：在Enterprise Manager中建立資料表空間

應用情境75：使用CREATE TABLESPACE語句建立資料表空間

建立本地管理資料表空間
CREATE TABLESPACE OrclTBS01
    DATAFILE 'D:\app\Administrator\oradata\orcl\OrclTBS01.dbf' SIZE 50M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

大檔案資料表空間：
CREATE TABLESPACE OrclTBS02
    DATAFILE 'D:\app\Administrator\oradata\orcl\OrclTBS02.dbf' SIZE 30M
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K;

CREATE BIGFILE TABLESPACE bigtbs
    DATAFILE 'D:\app\Administrator\oradata\orcl\bigtbs.dbf' SIZE 10G;

暫存資料表空間：
CREATE TEMPORARY TABLESPACE tmptbs
    TEMPFILE 'D:\app\Administrator\oradata\orcl\tmptbs.dbf'
    SIZE 20M REUSE
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;

應用情境76：資料表空間中的段管理

建立自動段管理方式的資料表空間;
CREATE TABLESPACE OrclTBS01
    DATAFILE 'F:\app\Administrator\oradata\orcl\OrclTBS01.dbf' SIZE 30M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE
    SEGMENT SPACE MANAGEMENT AUTO;

建立手段管理的資料表空間：
CREATE TABLESPACE OrclTBS02
    DATAFILE 'F:\app\Administrator\oradata\orcl\OrclTBS02.dbf' SIZE 30M
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE
    SEGMENT SPACE MANAGEMENT MANUAL;

應用情境77：資料表空間的資料區塊管理：

建立資料表空間時，指定資料表空間使用的資料區塊大小8KB

CREATE TABLESPACE OrclTBS01
   DATAFILE 'D:\app\Administrator\oradata\orcl\OrclTBS01.dbf' SIZE 30M
   EXTENT MANAGEMENT LOCAL AUTOALLOCATE
   BLOCKSIZE 8K;

應用情境78：建立撤銷資料表空間
功能：
當執行ROOLBACK命令是，完成迴歸操作。
恢複資料庫
使用閃回查詢分析以前時間點的資料。
使用閃回技術從邏輯破壞中恢複資料。

CREATE UNDO TABLESPACE undotbs01
     DATAFILE 'D:\app\Administrator\oradata\orcl\undotbs02.dbf' SIZE 2M REUSE;

應用情境79：在Enterprise Manager中修改資料表空間屬性。

應用情境80：使用ALTER TABLESPACE語句重新命名資料表空間

ALTER TABLESPACE OrclTBS02 RENAME TO OrclTBS03;

SELECT * FROM V$TABLESPACE;

應用情境81：向本地管理資料表空間中增加資料檔案

ALTER TABLESPACE OrclTBS01
ADD DATAFILE 'D:\app\Administrator\oradata\orcl\OrclTBS11.dbf' SIZE 10M;

應用情境82：向暫存資料表空間添加臨時檔案：
ALTER TABLESPACE tmptbs
    ADD TEMPFILE 'F:\app\Administrator\oradata\orcl\tmptbs01.dbf' SIZE 20M;

應用情境83：修改大檔案資料表空間的屬性：

ALTER TABLESPACE bigtbs RESIZE 4G;

應用情境84：設定資料表空間的狀態：

ALTER TABLESPACE OrclTBS01 OFFLINE;

SELECT TABLESPACE_NAME,CONTENTS,STATUS FROM DBA_TABLESPACES;

ALTER TABLESPACE OrclTBS01 ONLINE;

應用情境85：設定唯讀資料表空間

ALTER TABLESPACE OrclTBS01 OFFLINE;

SELECT TABLESPACE_NAME,CONTENTS,STATUS FROM DBA_TABLESPACES;

ALTER TABLESPACE OrclTBS01 ONLINE;

應用情境86：刪除資料表空間

DROP TABLESPACE OrclTBS03;

DROP TABLESPACE OrclTBS01 INCLUDING CONTENTS;

DROP TABLESPACE OrclTBS01 INCLUDING CONTENTS AND DATAFILES;

應用情境87：統計資料表空間的使用方式

SELECT c.tablespace_name "資料表空間",
ROUND(a.bytes/1024/1024,2) "資料表空間大小",
ROUND((a.bytes-b.bytes)/1048576,2) "已使用空間",
ROUND(b.bytes/1048576,2) "剩餘空間",
ROUND(b.bytes/a.bytes * 100,2)||'%' "剩餘百分比"
FROM
(SELECT tablespace_name,SUM(bytes) bytes FROM DBA_DATA_FILES
GROUP BY tablespace_name) a,
(SELECT a.tablespace_name, NVL(SUM(b.bytes),0) bytes FROM DBA_DATA_FILES a, DBA_FREE_SPACE b
WHERE a.tablespace_name = b.tablespace_name (+) AND a.file_id = b.file_id (+)
GROUP BY a.tablespace_name) b,
DBA_TABLESPACES c
WHERE a.tablespace_name = b.tablespace_name(+) AND a.tablespace_name = c.tablespace_name
ORDER BY ROUND(b.bytes/1024/1024,2);

應用情境88：在CREATE TABLESPACE 語句中建立暫存資料表空間組

CREATE TEMPORARY TABLESPACE temp_spc
TEMPFILE 'D:\app\Administrator\oradata\orcl\temp03.dbf'
SIZE 20M
TABLESPACE GROUP temp_grp;

應用情境89:使用ALTER TABLESPACE 語句建立暫存資料表空間組

ALTER TABLESPACE temp_spc TABLESPACE GROUP new_temp_group

應用情境90：在Enterprise Manager中查看和添加暫存資料表空間組

應用情境91：使用視圖DBA_TABLESPACE_GROUPS查看暫存資料表空間資訊

SELECT * FROM DBA_TABLESPACE_GROUPS;

應用情境92：將資料表空間從暫存資料表空間組中移出

ALTER TABLESPACE temp_spc TABLESPACE GROUP '';

應用情境93：資料表空間組分配給指定使用者

CREATE USER LEE IDENTIFIED BY leepass
DEFAULT TABLESPACE Users
TEMPORARY TABLESPACE NEW_TEMP_GROUP;

ALTER USER LEE TEMPORARY TABLESPACE NEW_TEMP_GROUP;

SELECT USERNAME, DEFAULT_TABLESPACE, TEMPORARY_TABLESPACE
FROM DBA_USERS WHERE USERNAME='LEE';
 

ALTER DATABASE ORCL DEFAULT TEMPORARY TABLESPACE NEW_TEMP_GROUP;

應用情境94：查看復原段資訊：

SELECT SEGMENT_NAME, TABLESPACE_NAME, BYTES, NEXT_EXTENT
FROM DBA_SEGMENTS WHERE SEGMENT_TYPE='ROLLBACK';

COL NAME FOR A12
SELECT s.USN, n.NAME, s.EXTENTS, s.OPTSIZE, s.HWMSIZE, s.STATUS
FROM V$ROLLSTAT s, V$ROLLNAME n
WHERE s.USN=n.USN;

應用情境95：設定復原段的管理員模式

SHOW PARAMETER UNDO_MANAGEMENT

ALTER SYSTEM SET UNDO_MANAGEMENT=MANUAL
SCOPE=SPFILE;

應用情境96：合理規劃和建立復原段

CREATE ROLLBACK SEGMENT OrclRs01
TABLESPACE UndoTBS1
STORAGE (INITIAL 5M
          NEXT 2M
          MAXEXTENTS UNLIMITED);

應用情境97：修改復原段的屬性：

ALTER ROLLBACK SEGMENT OrclRs01 ONLINE;

SELECT NAME, STATUS FROM V$ROLLNAME, V$ROLLSTAT
WHERE V$ROLLSTAT.USN=V$ROLLNAME.USN;

ALTER ROLLBACK SEGMENT OrclRs01 SHRINK TO 1M;

應用情境98：刪除復原段

ALTER ROLLBACK SEGMENT OrclRs01 OFFLINE;
DROP ROLLBACK SEGMENT OrclRs01;