6.1.DATA DICTIONARY VÀ VIEWS
6.1.1. Data Dictionary
Data
dictionary hay từ điển dữ liệu hệ thống là phần rất quan trọng trong Oracle
database. Đó là một tập hợp các table và các view sử dụng cho việc tham chiếu đến
các thông tin liên quan tới database. Data dictionary được tạo bởi file script sql.bsq
trong quá trình tạo database.
Data
dictionary bao gồm các thông tin trung tâm của
Oracle server.
Data
dictionary được Oracle server tự động cập nhật mỗi khi thực hiện lệnh định
nghĩa dữ liệu (Data Definition Language – DDL).
§ Base
tabes
Base tables
Thông
tin trong data dictionary được xác định từ các thông tin có trong các base
tables (bảng cơ sở). Nội dung của các bảng này do Oracle server cập nhật. User
thuộc database hầu như không thể cập nhật các thông tin này do chúng là các
thông tin đã được chuẩn hoá và được mã hoá. Ví dụ: ta chỉ có thể truy xuất tới
các thông tin có trong bảng IND $
để biết được các thông tin về các indexes đã được định nghĩa
trong database, hoặc lấy các thông tin trong bảng OBJ$
để biết được các objects đã được định nghĩa trong database.
Ta
không thể sử dụng các câu lệnh thao tác dữ liệu như INSERT,
UPDATE, hay DELETE để
thay đổi nội dung thông tin trong các bảng cơ sở một cách trực tiếp ngoại trừ bảng
AUD$ (Xem thêm phần kiểm tra -
Auditing).
Data Dictionary Views
Data
dictionary views được tạo ra bởi các câu lệnh có trong file script catalog.sql.
Các views này giải mã và tổng hợp các thông tin có trong các base tables. Để dễ
dàng truy xuất các thông tin này, các data dictionary thường được tạo các
synonyms tương ứng.
Phần
lớn các thông tin hệ thống được User lấy về từ các data dictionary views hơn là
lấy trực tiếp từ các base tables.
6.1.2. Data
Dictionary views
Hình vẽ 2.
Dictionary views
Data
dictionary views được phân ra làm ba loại chứa các thông tin tương tự nhau
nhưng ở các mức độ khác nhau. Các loại data dictionary views này được phân biệt
bởi các tiếp đầu ngữ khác nhau.
Tiếp đầu ngữ USER
Các
views có tiếp đầu ngữ USER chứa thông tin về
các objects do User hiện thời sở hữu. Ví dụ: USER_TABLES
sẽ chứa thông tin về các bảng dữ liệu của User hiện thời.
Tiếp đầu ngữ ALL
Các
views có tiếp đầu ngữ ALL chứa thông tin về
các objects có thể truy cập bởi User hiện thời, bao gồm cả các đối tượng do
User đó sở hữu và cả các đối tượng khác mà User được gán quyền truy nhập. Ví dụ:
ALL_TABLES sẽ chứa thông tin về các bảng
dữ liệu mà User hiện thời có thể truy nhập.
Tiếp đầu ngữ DBA
Các
views có tiếp đầu ngữ DBA chứa thông tin về
các objects có trong database. Các views này là cần thiết cho quản trị viên
database. Một User bất kỳ cũng có thể xem được thông tin trong các views DBA
nếu user đó được cấp quyền SELECT ANY TABLE.
Phân loại một số
loại views
Tên View
|
Diễn giải
|
DICTIONARY
DICT_COLUMNS
|
Thông tin chung
|
DBA_TABLES
DBA_OBJECTS
DBA_LOBS
DBA_TAB_COLUMNS
DBA_CONSTRAINTS
|
Thông tin liên quan tới các đối tượng của User như:
table, Column, Constraint,...
|
DBA_USERS
DBA_SYS_PRIVS
DBA_ROLES
|
Thông tin về mức quyền của User
|
Tên View
|
Diễn giải
|
DBA_EXTENTS
DBA_FREE_SPACE
DBA_SEGMENTS
|
Tình hình cấp phát không gian cho các đối tượng trong
database.
|
DBA_ROLLBACK_SEGS
DBA_DATA_FILES
DBA_TABLESPACES
|
Thông
tin về cấu trúc database
|
DBA_AUDIT_TRAIL
DBA_AUDIT_OBJECTS
DBA_AUDIT_OBJ_OPTS
|
Các thông tin kiểm tra
|
Ví
dụ: Để lấy các thông tin chung trong từ điển dữ liệu, ta có thể truy vấn trong
Các views DICTIONARY hoặc DICT_COLUMNS.
SVRMGR>SELECT *
2> FROM dictionary
3> WHERE table_name LIKE ’%TABLE%’;
TABLE_NAME
COMMENTS
--------------------
---------------------------------------
ALL_ALL_TABLES
Description of all object and
relational tables accessible to the
user
ALL_NESTED_TABLES
Description of nested tables in
tables
accessible
to the user
ALL_OBJECT_TABLES
Description of all object tables
accessible
to the user
ALL_PART_TABLES
ALL_TABLES Description of relational tables accessible to
the user
ALL_UPDATABLE_COLUMNS
Descriptionofallupdatablecolumns
DBA_ALL_TABLES
Description of all object and
relational tables in the database
DBA_NESTED_TABLES Description of nested tables contained in all tables
DBA_OBJECT_TABLES
Description of all object tables in
the
database
...
Xây dựng
dictionary views
Sau
khi tạo database, ta truy cập vào database theo user: SYS
và chạy các scripts: catalog.sql và
catprog.sql để tạo các dictionary views. Thông thường, các scripts này nằm
trong thư mục: %ORACLE_HOME%\RDBMS80\ADMIN
Catalog.sql
CATALOG.SQL script dùng để tạo
các view dựa trên các base tables (bảng cơ sở) của database. Các view này sẽ được
tạo synonym (một tên khác với tên của objects được dùng để truy cập objects)
tương ứng để dễ dàng truy vấn các dữ liệu từ đó hơn. Scripts này còn gọi tới
các scripts khác để tạo các views và các đối tượng khác phục vụ cho các tiện
ích Server Manager, cho việc kiểm tra, cho các tiện ích Export và Import dữ liệu,...
Scripts STANDARD.SQL được gọi đến
trong đó để tạo các môi trường PL/SQL tuân theo chuẩn.
Ví
dụ: Scripts tạo mẫu giao tiếp cho 01 hàm built-in có tên BITAND:
function BITAND (LEFT binary_integer,
RIGHT binary_integer)
return binary_integer;
Catproc.sql
CATPROC.SQL script dùng để tạo
các hàm PL/SQL, các packages PL/SQL sử dụng trong RDBMS.
Ngoài ra, CATPROC.SQL script còn tạo
Các views mở rộng khác.
6.1.3. Sripts
quản trị
Các
scripts quản trị được đặt trong thư mục: %ORACLE_HOME%\RDBMS80\ADMIN
Các
scripts này được phân nhóm và đặt trong từng file riêng biệt.
Các
quy định về tên có trong Script quản trị
Quy ước
|
Diễn giải
|
Cat*.sql
|
Các thông tin Catalog và từ điển dữ liệu
|
Dbms*.sql
|
Phần
khai báo (specification) của các packages trong database
|
Prvt*.plb
|
Phần thân cua packages đã được mã hoá và đóng gói
|
Utl*.sql
|
Các views và table tiện ích trong database
|
6.2.STORED PROCEDURES VÀ CÁC PACKAGES
CHUẨN
6.2.1. Giới
thiệu chung
Stored
procedures và các packages là các đối tượng trong database, đó là tập hợp các
đoạn mã lệnh PL/SQL để thực hiện một chức năng nào đó.
Stored
procedures bao gồm cả các procedures (thủ tục), functions (hàm) và các packages
được viết gộp thành một program unit (đơn vị chương trình).
Stored
procedures có thể được tạo và huỷ bởi các lệnh CREATE
và DROP
Lợi ích của Stored
procedures
§ Các
Stored procedures được nạp vào shared pool, do đó có thể giảm bớt việc truy xuất
đĩa khi thực hiện thủ tục.
§ Đảm bảo
an toàn cho dữ liệu, ngăn không cho các users truy cập trực tiếp vào dữ liệu mà
phải thông qua các thủ tục và hàm giao tiếp đã được cung cấp.
§ Cho
phép nhiều users có thể cùng sử dụng các bản sao của Stored procedures để thực
hiện.
6.2.2. Stored
procedures
Stored
procedures là các functions hay procedures được tạo lập và lưu ngay trong dictionary
giống như một schema object. Đây là tập hợp các câu lệnh SQL và PL/SQL. Sau khi
Stored procedures được biên dịch, nó sẽ được gán tên và có thể thực hiện trực
tiếp mà không cần phải biên dịch lại thêm bất cứ một lần nào nữa.
Sử
dụng Stored procedures, ta có thể nạp trực tiếp vào ngay biểu thức thuộc câu lệnh
SQL giống như là các hàm built-in có sẵn của Oracle như UPPER
hay SUBSTR.
Các
functions và procedures cho phép sử dụng tham số dưới dạng tham số vào (IN)
và tham số ra (OUT) hoặc cũng có thể
sử dụng tham số vừa vào vừa ra (IN OUT).
Theo mặc đinh, các tham số được xác định ở chế độ vào IN.
6.2.3. Packages
chuẩn
Một
packages thông thường gồm hai phần: specification (phần đặc tả hay còn gọi là
phần khai báo) và body (phần thân). Chúng được lưu riêng biệt trong cùng một
database.
§ Phần
specification là phần giao tiếp với các ứng dụng. Phần này chứa các lời khai
báo, các kiểu, biến, hằng, exceptions, cursors, và các khai báo hàm để sử dụng.
§ Phần
body là phần cài đặt cụ thể (implementation) của các khai báo trong phần
specification.
Chức
năng của packages cũng tương tự như Stored procedures. Một khi packages được
biên dịch, packages đó có thể được sử dụng bởi nhiều ứng dụng khác nhau. Tuy
nhiên, có một lợi ích lớn nhất khi sử dụng packages là ngay lần đầu tiên gọi đến
packages, toàn bộ packages sẽ được nạp vào trong bộ nhớ.
6.2.4. Giới
thiệu một số packages chuẩn do Oracle cung cấp
Oracle
cung cấp một số packages chuẩn, ngay sau khi tạo database:
§ DBMS_LOB: cung cấp các thủ
tục cho phép làm việc trên kiểu dữ liệu BLOB
và CLOB, được định nghĩa trong
file script catprog.sql.
§ DBMS_SESION: cung cấp các câu
lệnh SQL liên quan đến session như ALTER
SESSION, SET ROLE, ... packages này được
định nghĩa trong file dbmsutil.sql và
prvtutil.sql
§ DBMS_UTILITY: chứa các thủ tục
tiện ích, được đặt trong file dbmsutil.sql
và prvtutil.sql
§ DBMS_SPACE: cung cấp các
thông tin về khoảng trống của segment.
§ DBMS_ROWID: cung cấp các
thông tin về ROWID
§ DBMS_SHARE_POOL: lưu trữ và huỷ bỏ
các thông tin có trong share pool.
Packages
|
Thủ tục trong packages
|
Diễn giải
|
DBMS_SESSION
|
SET_ROLE
|
Kích hoạt việc thực hiện Roles của user
|
SET_SQL_TRACE
|
Thiết lập chế độ dò tìm thực hiện lệnh
|
|
SET_NLS
|
Chọn chuẩn hỗ trợ ngôn ngữ
|
|
DBMS_UTILITY
|
ANALYZE_SCHEMA
|
Phân
tích các objects trong schema như: functions, procedures, packages,
triggers,..
|
COMPILE_SCHEMA
|
Biên
dịch các objects trong schema
|
|
DB_VERSION
|
Xác
định phiên bản của database
|
|
DBMS_ROWID
|
ROWID_INFO
|
Thông tin về dòng dữ liệu
|
DBMS_SPACE
|
UNUSED_SPACE
|
Vùng không gian không sử dụng
|
FREE_BLOCKS
|
Các blocks rỗi
|
|
DBMS_SHARED_POOL
|
KEEP
|
Lưu trữ các object trong shared pool
|
UNKEEP
|
Thôi lưu giữ các object
|
|
SIZES
|
Kích thước bộ nhớ trong shared pool
|
|
DBMS_SQL
|
OPEN_CURSOR
|
|
PARSE
|
||
BIND_VARIABLE
|
||
BIND_ARRAY
|
||
|
||
EXECUTE_AND_FETCH
|
||
FETCH_ROWS
|
||
COLUMN_VALUE
|
||
IS_OPEN
|
||
CLOSE_CURSOR
|
||
LAST_ERROR_POSITION
|
||
LAST_ROW_COUNT
|
||
LAST_ROW_ID
|
||
LAST_SQL_FUNCTION_CODE
|
6.2.5. Package
DBMS_METADATA
Một
PL/SQL
package mới, DBMS_METADATA,
được đưa vào Oracle 11g cho phép ta lấy
được các siêu dữ liệu (metadata) – Các thông tin tổng hợp về các schema object.
§ DBMS_METADATA là package mới bổ
sung, nó cho phép thực hiện các thao tác DDL trên objects trong database.
§ Package
này làm việc được với các tables, indexes, views, packages, functions,
procedures, triggers, synonyms, và types.
§ DBMS_METADATA có các hàm cơ bản:
§
DBMS_METADATA.GET_DDL(object_type,
name, schema)
§
DBMS_METADATA.GET_XML(object_type,
name, schema)
Ví
dụ:
SELECT
DBMS_METADATA.GET_DDL(‘TABLE’, ‘EMP’, ‘SCOTT’) from dual;
CREATE TABLE "SCOTT"."EMP"
( "EMPNO"
NUMBER(4,0),
"ENAME" VARCHAR2(10),
"JOB" VARCHAR2(9),
"MGR" NUMBER(4,0),
"HIREDATE" DATE,
"SAL" NUMBER(7,2),
"COMM" NUMBER(7,2),
"DEPTNO" NUMBER(2,0),
CONSTRAINT "PK_EMP" PRIMARY
KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS
1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT) TABLESPACE "USERS"
ENABLE,
CONSTRAINT "FK_DEPTNO"
FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT"
("DEPTNO") ENABLE NOVALIDATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS
1 MAXEXTENTS 2147483645 PCTINCREASE 0
FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
DEFAULT) TABLESPACE "USERS"
SELECT
DBMS_METADATA.GET_XML(‘TABLE’, ‘EMP’, ‘SCOTT’) from dual;
<?xml version="1.0"?>
<ROWSET>
<ROW>
<TABLE_T>
<VERS_MAJOR>1</VERS_MAJOR>
<VERS_MINOR>0</VERS_MINOR>
<OBJ_NUM>5543</OBJ_NUM>
<SCHEMA_OBJ>
<OBJ_NUM>5543</OBJ_NUM>
<DATAOBJ_NUM>5543</DATAOBJ_NUM>
<OWNER_NUM>25</OWNER_NUM>
<OWNER_NAME>SCOTT</OWNER_NAME>
<NAME>EMP</NAME>
<NAMESPACE>1</NAMESPACE>
<MINEXTS>1</MINEXTS>
<MAXEXTS>2147483645</MAXEXTS>
<EXTSIZE>128</EXTSIZE>
<EXTPCT>0</EXTPCT>
…
6.2.6. Package
dbms_redefinition
Package
này cung cấp 05 thủ tục cho phép chỉnh sửa các objects online .
§
CAN_REDEF_TABLE
§
START_REDEF_TABLE
§
FINISH_REDEF_TABLE
§
ABORT_REDEF_TABLE
§ SYNC_INTERIM_TABLE
6.3.THÔNG
TIN VỀ CÁC STORED PROCEDURES
Khi
lưu trữ các Stored procedures hay packages, Oracle sẽ tự động lưu lại trạng
thái của nó là VALID hay INVALID.
§ VALID: Stored
procedures hay packages có trạng thái là VALID
nếu nó đã được biên dịch và không có lỗi xảy ra. Khi này, nó sẵn sàng cho việc
sử dụng.
§ INVALID: là trạng thái
ngược lại với trạng thái VALID. Stored procedures
hay Packages vẫn còn lỗi khi biên dịch. Khi này, ta chưa thể sử dụng được ngay.
Cú
pháp lệnh yêu cầu biên dịch lại Stored procedures:
ALTER PROCEDURE
[schema_name].<procedure_name> COMPILE [DEBUG];
Với:
schema_name tên schema chứa procedure cần biên
dịch lại
procedure_name tên của procedure biên dịch lại.
COMPILE chỉ định yêu cầu biên dịch lại
procedure
DEBUG chỉ định chương trình biên
dịch mã lệnh PL/SQl của procedure sẽ sinh mã lệnh phù hợp để chương trình
PL/SQL debugger có thể đọc. User có thể sử dụng chương trình này để dò tìm và gỡ
lỗi cho procedure.
Ví
dụ:
Tương
tự như đối với procedure, cú pháp lệnh yêu cầu biên dịch lại Stored function có
dạng:
ALTER FUNCTION
[schema_name].<function_name> COMPILE [DEBUG];
Ví
dụ:
Đối
với package, lệnh yêu cầu biên dịch lại cũng tương tự nhưng có thêm một bổ sung
là user phải khai báo rõ từng phần của package sẽ được biên dịch lại.
Cú
pháp:
ALTER PACKAGE [schema_name].<package_name>
COMPILE [DEBUG] <PACKAGE |
SPECIFICATION | BODY>;
Các
khai báo bổ sung cho phép user yêu cầu biên dịch lại phần SPECIFICATION
hay
phần BODY
hoặc là biên dịch lại cả hai phần trên.
Ví
dụ:
ALTER
PACKAGE blair.accounting
Hoặc:
ALTER
PACKAGE blair.accounting
Để
xác định được trạng thái của các Stored procedures, ta có thể thực hiện truy vấn
dựa trên dictionary DBA_OBJECTS.
SVRMGR> SELECT
object_name, object_type, status
2>
FROM dba_objects WHERE object_name like ’DBMS_%’
OBJECT_NAME
OBJECT_TYPE STATUS
--------------------
--------------- --------
DBMS_ALERT
PACKAGES VALID
DBMS_ALERT
PACKAGES BODY VALID
DBMS_APPLICATION_INF
PACKAGES VALID
DBMS_AQ
PACKAGES VALID
DBMS_AQ
PACKAGES BODY VALID
...
Hoặc
ta cũng có thể sử dụng lệnh DESCRIBE để lấy thông tin
SVRMGR>
DESCRIBE dbms_session.set_role
procedure SET_ROLE
(ROLE_CMD VARCHAR2);
svrmgr>
describe dbms_session
packages
dbms_session is
------------
-- OVERVIEW
-- This packages
provides access to SQL "alter session"
-- statements, and
other session information from, stored
-- procedures.
----------------------------
-- PROCEDURES AND
FUNCTIONS
procedure
set_role(role_cmd varchar2);
-- Equivalent to
SQL "SET ROLE ...".
-- Input
arguments:
-- role_cmd
-- This text is
appended to "set role " and then executed as
-- SQL.
procedure
set_sql_trace(sql_trace boolean);
-- Equivalent to
SQL "ALTER SESSION SET SQL_TRACE ..."
-- Input
arguments:
-- sql_trace
-- TRUE or FALSE.
Turns tracing on or off.
procedure set_nls(param
varchar2, value varchar2);
Stored
procedures hay Packages nhận trạng thái INVALID
khi các câu lệnh trong Stored procedures hay Packages bị lỗi.
---------------------------
* Dữ liệu trong thời đại 4.0 là "TRÁI TIM" của doanh nghiệp. Quản trị cơ sở dữ liệu (Database Administration - DBA) là ngành có lương cao nhất trong ngành IT và đầy tiềm năng trong thời đại 4.0. Oracle là cơ sở dữ liệu hàng đầu trên thế giới và được rất nhiều doanh nghiệp lớn trên thế giới và Việt Nam sử dụng.
* Bạn có mong muốn từng bước trở thành chuyên gia DBA không? Nếu câu trả lời là CÓ thì hãy nhanh chóng đăng ký sở hữu trọn đời khóa học online "Quản trị cơ sở dữ liệu cơ bản" của tôi tại Unica (bạn có thể xem trên điện thoại, máy tính bảng, PC vào bất kỳ lúc nào chỉ cần có mạng Internet). Hiện tại tôi đang khuyến mại cho 100 bạn đăng ký đầu tiên giảm giá từ 2400K còn 799K, CAM KẾT HOÀN TIỀN 100% nếu học xong không có kết quả:
- Môn 1: Quản trị Oracle 12 cơ bản, giảm giá 70% HÔM NAY cho 100 bạn đầu tiên từ 2tr4 còn 799K theo link: https://bit.ly/3d2ofqZ (khoá này học ở Oracle hãng là 1.500$), chuyển khoản còn 699K
- Môn 2: Quản trị Linux trong 21h, cũng giảm giá 70% HÔM NAY cho 100 bạn đầu tiên từ 2tr4 còn 799K theo link: https://bit.ly/3e7gwJw, chuyển khoản còn 699K
*****
@ Trần Văn Bình - Founder of Oracle DBA AZ---------------------------
học oracle database
Các tìm kiếm liên quan đến học oracle database
Tự học Oracle
Tài liệu Oracle 12c tiếng Việt
Hướng dẫn sử dụng Oracle Database
Oracle SQL cơ bản
Oracle SQL là gì
Khóa học Oracle Hà Nội
Học chứng chỉ Oracle ở đầu
Khóa học Oracle online
khóa học pl/sql
học dba
học dba ở việt nam
khóa học dba
khóa học dba sql
tài liệu học dba oracle
Khóa học Oracle online
học oracle sql
học oracle ở đâu tphcm
học oracle bắt đầu từ đâu
học oracle ở hà nội
oracle database tutorial
oracle database 12c
oracle database là gì
oracle database 11g
oracle download
oracle database 19c
oracle dba tutorial
Oracle GoldenGate
Oracle DataGuard
Oracle RAC
Oracle Tunning
Oracle Backup Recovery
Oracle Partitioning