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).

Data dictionary đặt trong tablespace SYSTEM do User SYS quản lý. Data dictionary bao gồm hai loại sau:

§ Base tabes

§ Data dictionary Views

Hình vẽ 1. Dictionarytrong database

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

Hình vẽ 3. Stored procedures và các Packages chuẩn

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ớ.

Hình vẽ 4. Packages trong cơ sở dữ liệu

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ữ
	CLOSE_DATABASE_LINK	Đóng database link.
	UNIQUE_SESSION_ID	Trả về mã duy nhất cả các session hiên đang connect tới database.
	IS_ROLE_ENABLED	Xác đinh xem role có được kích hoạt trong session không.
	IS_SESSION_ALIVE	Xác định xem session có còn hay không.
	SET_CLOSE_CACHED_OPEN_CURSORS	Bật hoặc tắt close_cached_open_cursors
	FREE_UNUSED_USER_MEMORY	Giải phóng vùng bộ nhớ không còn sử dụ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
DBMS_SPACE	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	Trả về số hiệu cursor (ID number)
	PARSE	Phân tích câu lệnh
	BIND_VARIABLE	Binds một giá trị biến.
	BIND_ARRAY	Binds một giá trị biến mảng.
	EXECUTE Function	Executes a given cursor.
	EXECUTE_AND_FETCH	Thực hiện lệnh và lấy về các dòng dữ liệu.
	FETCH_ROWS	Lấy về các dòng dữ liệu của một cursor.
	COLUMN_VALUE	Lấy về dữ liệu của cột
	IS_OPEN	Xác định Cursor đã mở hay chưa.
	CLOSE_CURSOR	Đóng cursor và giải phóng bộ nhớ.
	LAST_ERROR_POSITION	Trả về lỗi thực hiện câu lệnh SQL
	LAST_ROW_COUNT	Trả về số lượng dòng dữ liệu lấy về
	LAST_ROW_ID	Trả về mã dòng dữ liệu xử lý ROWID
	LAST_SQL_FUNCTION_CODE	Trả về mã hàm SQL

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"?>

<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>

…

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ụ:

ALTER PROCEDURE henry.close_acct COMPILE;

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ụ:

ALTER FUNCTION merriweather.get_bal COMPILE;

Đố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

COMPILE PACKAGE;

Hoặc:

ALTER PACKAGE blair.accounting

COMPILE BODY;

Để 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_ALERT_INFO TABLE VALID

DBMS_APPLICATION_INF PACKAGES VALID

DBMS_APPLICATION_INF PACKAGES BODY 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

Thứ Sáu, 21 tháng 2, 2020

HỌC ORACLE DB TỪ A-Z - BÀI 6: DATA DICTIONARY, VIEWS VÀ PACKAGES

6.1.DATA DICTIONARY VÀ VIEWS

6.1.1. Data Dictionary

Base tables

Data Dictionary Views

6.1.2. Data Dictionary views

6.1.3. Sripts quản trị

6.2.STORED PROCEDURES VÀ CÁC PACKAGES CHUẨN

6.2.1. Giới thiệu chung

6.2.2. Stored procedures

6.2.3. Packages chuẩn

6.2.4. Giới thiệu một số packages chuẩn do Oracle cung cấp

6.2.5. Package DBMS_METADATA

6.2.6. Package dbms_redefinition

6.3.THÔNG TIN VỀ CÁC STORED PROCEDURES

ĐỌC NHIỀU