Bài viết này tổng hợp các lệnh SQL và thao tác dòng lệnh thường dùng để quản lý cơ sở dữ liệu Oracle, bao gồm quản lý người dùng, tablespace, bảng, cũng như các tác vụ xuất nhập dữ liệu cơ bản.
1. Quản lý Phiên làm việc và Cơ sở dữ liệu Oracle
Để thực hiện các tác vụ quản trị, bạn thường cần đăng nhập với quyền hạn cao như SYSDBA.
-
Đăng nhập với quyền SYSDBA mà không cần mật khẩu (nếu OS authentication được cấu hình):
sqlplus / as sysdba; -
Dừng cơ sở dữ liệu ngay lập tức:
SHUTDOWN IMMEDIATE; -
Khởi động cơ sở dữ liệu:
STARTUP;
2. Quản lý Tablespace
Tablespace là một đơn vị lưu trữ logic trong Oracle, nơi các đối tượng cơ sở dữ liệu như bảng và chỉ mục được lưu trữ.
-
Tạo một tablespace mới:
CREATE TABLESPACE TEN_TABLESPACE_MOI DATAFILE '/u01/app/oracle/oradata/MYDB/datafile/TEN_TABLESPACE_MOI_01.dbf' SIZE 100M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED; -
Cấu hình tự động mở rộng (autoextend) cho datafile của tablespace:
ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/MYDB/datafile/TEN_TABLESPACE_MOI_01.dbf' AUTOEXTEND ON NEXT 50M MAXSIZE 5000M; -
Kiểm tra trạng thái autoextend của các datafile:
SELECT FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE, BYTES, MAXBYTES FROM dba_data_files WHERE TABLESPACE_NAME = 'TEN_TABLESPACE_MOI'; -
Xóa một tablespace (với các tùy chọn khác nhau):
-- Xóa tablespace trống, không xóa file vật lý DROP TABLESPACE TEN_TABLESPACE_MOI; -- Xóa tablespace và tất cả nội dung của nó (bảng, index...), không xóa file vật lý DROP TABLESPACE TEN_TABLESPACE_MOI INCLUDING CONTENTS; -- Xóa tablespace trống và các file vật lý liên quan DROP TABLESPACE TEN_TABLESPACE_MOI INCLUDING DATAFILES; -- Xóa tablespace, nội dung và các file vật lý DROP TABLESPACE TEN_TABLESPACE_MOI INCLUDING CONTENTS AND DATAFILES; -- Xóa tablespace, nội dung, file vật lý và các ràng buộc (ví dụ: khóa ngoại) -- liên quan đến các đối tượng trong tablespace này từ các tablespace khác DROP TABLESPACE TEN_TABLESPACE_MOI INCLUDING CONTENTS AND DATAFILES CASCADE CONSTRAINTS;
3. Quản lý Người dùng
Các lệnh cơ bản để tạo, quản lý và cấp quyền cho người dùng trong Oracle.
-
Tạo người dùng mới và gán tablespace mặc định:
CREATE USER NGUOI_DUNG_DU_LIEU IDENTIFIED BY MatKhauAnToan! DEFAULT TABLESPACE TEN_TABLESPACE_MOI QUOTA UNLIMITED ON TEN_TABLESPACE_MOI; -- Cấp quyền sử dụng không giới hạn trên tablespace -
Xóa người dùng (tùy chọn xóa tất cả các đối tượng của người dùng đó):
-- Xóa người dùng và tất cả các đối tượng (bảng, view,...) mà họ sở hữu DROP USER NGUOI_DUNG_DU_LIEU CASCADE; -
Mở khóa tài khoản người dùng:
ALTER USER NGUOI_DUNG_DU_LIEU ACCOUNT UNLOCK; -
Thay đổi mật khẩu người dùng:
ALTER USER NGUOI_DUNG_DU_LIEU IDENTIFIED BY MatKhauMoiAnToan#; -
Cấp các quyền cơ bản cho người dùng:
GRANT CONNECT, RESOURCE TO NGUOI_DUNG_DU_LIEU; GRANT CREATE VIEW, CREATE DATABASE LINK, UNLIMITED TABLESPACE TO NGUOI_DUNG_DU_LIEU; -- Cấp quyền DBA (toàn quyền quản trị, cần thận trọng khi sử dụng) GRANT DBA TO NGUOI_DUNG_DU_LIEU; -- Cấp quyền SYSDBA (chỉ cấp cho các tài khoản quản trị hệ thống) GRANT SYSDBA TO NGUOI_DUNG_DU_LIEU; -- Cấp quyền đọc/ghi trên thư mục Data Pump (để xuất/nhập dữ liệu) GRANT READ, WRITE ON DIRECTORY DATA_PUMP_DIR TO NGUOI_DUNG_DU_LIEU;
4. Quản lý Bảng
Các thao tác cơ bản với bảng và thu thập thống kê để tối ưu hóa hiệu suất.
-
Tạo bảng:
CREATE TABLE DANH_SACH_SAN_PHAM ( MA_SP NUMBER(10) PRIMARY KEY, TEN_SP VARCHAR2(100) NOT NULL, GIA_BAN NUMBER(10, 2), NGAY_TAO DATE DEFAULT SYSDATE ); -
Xóa bảng:
DROP TABLE DANH_SACH_SAN_PHAM; -
Phân tích bảng để thu thập thống kê (cải thiện hiệu suất truy vấn):
-- Thu thập thống kê tổng thể cho bảng (bao gồm cột và index) ANALYZE TABLE DANH_SACH_SAN_PHAM COMPUTE STATISTICS; -- Chỉ thu thập thống kê cho bảng ANALYZE TABLE DANH_SACH_SAN_PHAM COMPUTE STATISTICS FOR TABLE; -- Thu thập thống kê cho tất cả các cột của bảng ANALYZE TABLE DANH_SACH_SAN_PHAM COMPUTE STATISTICS FOR ALL COLUMNS; -- Thu thập thống kê cho tất cả các chỉ mục của bảng ANALYZE TABLE DANH_SACH_SAN_PHAM COMPUTE STATISTICS FOR ALL INDEXES; -- Thu thập thống kê cho bảng, tất cả các chỉ mục và tất cả các cột (tùy chọn chi tiết nhất) ANALYZE TABLE DANH_SACH_SAN_PHAM COMPUTE STATISTICS FOR TABLE FOR ALL INDEXES FOR ALL COLUMNS;
5. Xuất và Nhập Dữ liệu với Data Pump
Oracle Data Pump (expdp và impdp) là công cụ mạnh mẽ để xuất và nhập dữ liệu giữa các cơ sở dữ liệu Oracle.
5.1. Xuất dữ liệu bằng expdp
Trước khi xuất, hãy đảm bảo bạn có một thư mục Data Pump đã được định nghĩa trong cơ sở dữ liệu.
-
Kiểm tra các thư mục Data Pump hiện có:
SELECT * FROM dba_directories; -
Ví dụ lệnh xuất schema đầy đủ:
expdp TEN_NGUOI_DUNG_XUAT/MatKhauXuat@hostname_hoac_ip/ten_service_orcl \ DIRECTORY=DATA_PUMP_DIR \ DUMPFILE=schema_du_lieu_hom_nay.dmp \ LOGFILE=export_schema_log.log \ SCHEMAS=SCHEMA_CAN_XUAT \ VERSION=12.2.0.1.0Giải thích các tham số:
TEN_NGUOI_DUNG_XUAT/MatKhauXuat@...: Thông tin đăng nhập.DIRECTORY: Tên thư mục Data Pump đã được định nghĩa trong DB.DUMPFILE: Tên file dump sẽ được tạo.LOGFILE: Tên file log ghi lại quá trình xuất.SCHEMAS: Danh sách các schema cần xuất (phân cách bằng dấu phẩy).VERSION: Phiên bản cơ sở dữ liệu đích nếu bạn xuất để nhập vào một DB phiên bản cũ hơn.
5.2. Nhập dữ liệu bằng impdp
Trước khi nhập, bạn cần:
- Tạo các tablespace cần thiết.
- Tạo người dùng đích và cấp các quyền phù hợp (như đã hướng dẫn ở trên).
- Đặt file
.dmpvào thư mục Data Pump trên máy chủ Oracle (đường dẫn vật lý củaDATA_PUMP_DIR).
-
Ví dụ lệnh nhập schema và remap (ánh xạ lại) schema/tablespace:
impdp TEN_NGUOI_DUNG_NHAP/MatKhauNhap@hostname_hoac_ip/ten_service_orcl \ DIRECTORY=DATA_PUMP_DIR \ DUMPFILE=schema_du_lieu_hom_nay.dmp \ LOGFILE=import_schema_log.log \ REMAP_SCHEMA=SCHEMA_GOC_TRONG_DUMP:SCHEMA_DICH_TRONG_DB_MOI \ REMAP_TABLESPACE=TABLESPACE_GOC_TRONG_DUMP:TABLESPACE_DICH_TRONG_DB_MOIGiải thích các tham số:
TEN_NGUOI_DUNG_NHAP/MatKhauNhap@...: Thông tin đăng nhập.DIRECTORY: Tên thư mục Data Pump nơi chứa file dump.DUMPFILE: Tên file dump cần nhập.LOGFILE: Tên file log ghi lại quá trình nhập.REMAP_SCHEMA: Ánh xạ schema từ file dump sang một schema khác trong DB đích.REMAP_TABLESPACE: Ánh xạ tablespace từ file dump sang một tablespace khác trong DB đích.