Bài tập Oracle có lời giải

khi mã phòng hợp lệ mới được phép thêm nhân viên. 
create or replace package emp_pack 
as 
 procedure new_emp 
 ( 
 MaNV employees.employee_id%type, 
 TenNV employees.first_name%type, 
 HoNV employees.last_name%type, 
 Email employees.email%type, 
 DienThoai employees.phone_number%type, 
 NgayThue employees.hire_date%type, 
 MaCV employees.job_id%type, 
 Luong employees.salary%type, 
 HoaHong employees.commission_pct%type, 
 MaQuanLy employees.manager_id%type, 
 MaPhong employees.department_id%type 
 ); 
 function valid_deptid(i_department_id in number) 
 return boolean; 
 end emp_pack; 
--phan than 
create or replace package body emp_pack 
as 
 procedure new_emp 
 ( 
 MaNV employees.employee_id%type, 
 TenNV employees.first_name%type, 
Võ Đông Giang 2012 
26 Trường cao đẳng công nghệ thông tin TP.HCM 
 HoNV employees.last_name%type, 
 Email employees.email%type, 
 DienThoai employees.phone_number%type, 
 NgayThue employees.hire_date%type, 
 MaCV employees.job_id%type, 
 Luong employees.salary%type, 
 HoaHong employees.commission_pct%type, 
 MaQuanLy employees.manager_id%type, 
 MaPhong employees.department_id%type 
 ) 
 is 
 begin 
 insert into employees values(MaNV, TenNV, HoNV, Email, 
DienThoai, 
 NgayThue, MaCV, Luong, HoaHong, MaQuanLy,MaPhong); 
 end new_emp; 
 --ket thuc proc new_emp 
 function valid_deptid(i_department_id in number) 
 return boolean 
 is 
 v_id_dept number; 
 begin 
 select count(*) into v_id_dept 
 from departments 
 where department_id=i_department_id; 
 return 1=v_id_dept; 
 exception when others then 
 return false; 
 end valid_deptid;--ket thuc proc valid_deptid 
 end emp_pack; 
 --goi thuc thi 
 set serveroutput on 
 begin 
 if(emp_pack.valid_deptid(&i_department_id)) then 
 emp_pack.new_emp(1, 'First', 
'Last','first.last@oracle.com', 
 '(123)123-1234','18-JUN-02','IT_PROG',900,00, 
100,110); 
Võ Đông Giang 2012 
27 Trường cao đẳng công nghệ thông tin TP.HCM 
 dbms_output.put_line('Them thanh cong'); 
 else 
 dbms_output.put_line('Ma phong ban nay khong ton 
tai!'); 
 end if; 
 end; 
Võ Đông Giang 2012 
28 Trường cao đẳng công nghệ thông tin TP.HCM 
QUẢN TRỊ NGƯỜI DÙNG 
Quản trị người dùng. 
64. Tạo không gian bảng (tablespace) có kích thước 100M. 
65. Tạo không gian bảng tạm (temporary tablespace) có kích thước 50M. 
66. Tạo rollback segment rolora để truy xuất đồng thời cho table space vừa tạo. 
67. Tạo user có tên là tên sinh viên, mật khẩu do sinh viên tự đặt với tablespace và 
temporary tablespace vừa tạo. 
68. Cấp quyền truy xuất tài nguyên (resource) cho user vừa tạo. 
69. Cấp quyền cho phiên làm việc (session) cho user vừa tạo. 
70. Cấp quyền tạo bảng (table) cho user vừa tạo. 
71. Cấp quyền tạo khung nhìn (view) cho user vừa tạo. 
72. Cấp quyền Select, Insert, Update, Delete trên bảng Employees cho user vừa 
tạo. 
73. Cấp quyền Select, Insert, Update, Delete trên bảng Departments cho user vừa 
tạo. 
74. Cấp quyền Select chỉ với các thuộc tính job_id, job_title trên bảng Jobs cho 
user vừa tạo. 
75. Login vào csdl HR với user vừa tạo. 
76. Truy vấn các bảng trong csdl HR và cho nhận xét. 
77. Cho biết các user hiện có từ view dba_users. 
78. Đăng nhập với quyền hệ thống và tao user có tên là mã sinh viên, mật khẩu là 
tên sinh viên 
a. Thay đổi mật khẩu của user. 
b. Cấp quyền đăng nhập csdl 
c. Truy xuất view v$session để xem phiên làm việc. 
Võ Đông Giang 2012 
29 Trường cao đẳng công nghệ thông tin TP.HCM 
d. Tao bảng phòng ban gồm 2 thuộc tính: mã phòng ban, tên phòng ban và cho 
nhận xét câu c. 
e. Cấp phát hạn ngạch (quota) 20M cho user vừa tao. 
f. Tao lại bảng Phòng ban và cho nhận xét. 
g. Cấp phát không gian giới hạn tablespace cho user vừa tao 
h. Cấp phát tài nguyên cho user 
i. Cấp phát phiên làm việc cho user 
j. Cấp quyền đăng nhập OEM 
79. Truy xuất vào view hệ thống dba_profiles. 
80. Tao profile giới hạn việc truy xuất tài nguyên và cấp cho user vừa tạo: 
a. Thời gian kết nối 120s 
b. Số lần thất bại khi cho phép kết nối là 3. 
c. Thời gian chờ kết nối là 60s 
d. Phiên làm việc cho mỗi user là 2 
e. Gán profile cho user vừa tao 
f. Thay đổi thông số của profile với phiên làm việc cho mỗi user là 4. 
g. Số lần thất bại khi cho phép kết nối là 3 
h. Thiết lập thông số để profile có hiệu lực. 
i. Xóa profile vừa tạo. 
81. Oracle Database Resource Manager(ODRM) để tạo vùng treo,tao nhóm người 
dùng, tao kế hoạch tài nguyên và định hướng kế hoạch, kiểm tra tính hợp lệ của 
vùng treo và gởi vùng treo: 
a. Tạo vùng treo. 
b. Xóa vùng treo. 
c. Kiểm tra nhóm người dùng nào đã tồn tại và cho nhận xét liên quan các nhóm 
người dùng. 
d. Kiểm tra kế hoạch của mỗi CSDL từ dba_rsrc_plans 
e. Tao 4 nhóm người dùng: cập nhật, tìm kiếm, thống kê, báo cáo 
Võ Đông Giang 2012 
30 Trường cao đẳng công nghệ thông tin TP.HCM 
f. Kiểm tra tính hợp lệ của vùng treo. 
g. Truy xuất view dba_users và cho nhận xét. 
h. Gán người dùng vừa tạo tới nhóm người dùng. 
i. Truy xuất view dba_users và cho nhận xét. 
j. Khởi tạo user cho nhóm người dùng 
k. Truy xuất view dba_users và cho nhận xét. 
l. Tạo kế hoạch tài nguyên 
m. Tạo định hướng kế hoạch và giới hạn tài nguyên cho từng người dùng (CPU_1 
nhóm cập nhật là 60, CPU_1 nhóm thống kê 40, CPU_2 nhóm báo cáo là 30, nhóm 
khác là 100). 
n. Gởi vùng treo. 
o. Truy xuất view dba_rsrc_plan_directives và cho nhận xét. 
p. Kích hoạt kế hoạch sử dụng tài nguyên 
q. Truy xuất view v$rsrc_consumer_group để xem việc sử dụng tài nguyên giữa 
các nhóm 
r. Truy xuất view v$rsrc_plan và cho nhận xét. 
--64 
create tablespace oracle 
datafile'oracle.dbf' size 100m; 
--65 
create temporary tablespace oracle_2 
tempfile'oracle2.dbf' size 50m; 
--66 
create rollback segment seg 
tablespace oracle; 
Võ Đông Giang 2012 
31 Trường cao đẳng công nghệ thông tin TP.HCM 
--67 
create user sinhvien identified by sinhvien 
default tablespace oracle 
temporary tablespace oracle_2 
password expire; 
--68 
grant resource to sinhvien; 
--69 
grant create session to sinhvien; 
--70 
grant create table to sinhvien; 
--71 
grant create view to sinhvien; 
--72 
grant insert, update, select, delete on hr.employees to 
sinhvien; 
--73 
grant insert, select, update, delete on hr.departments to 
sinhvien; 
--74 
grant update(job_id, job_title) on hr.jobs to sinhvien; 
--75 
Võ Đông Giang 2012 
32 Trường cao đẳng công nghệ thông tin TP.HCM 
conn hr/hr; 
--76 
Khong truy van duoc vi chua cap quyen truy van 
--77 
select username from dba_users; 
--78 
conn system/system; 
grant create user to hr; 
conn hr/hr; 
create user masinhvien identified by masinhvien 
password exprire; 
--78bis xoa user masinhvien 
conn system/system; 
drop user masinhvien; 
--78b 
create create connect to masinhvien; 
--78c 
select username, status from v$session; 
--78 d Khong tao duoc vi chua cap quyen 
--78e 
create user sinhvien identified by sinhvien 
default tablespace oracle 
Võ Đông Giang 2012 
33 Trường cao đẳng công nghệ thông tin TP.HCM 
temporary tablespace oracle_2 
quota 100m on oracle; 
--80 a, b, c, d 
create profile giang limit 
connect_time 120 
failed_login_attempts 3 
idle_time 60 
sessions_per_user 2; 
--80e 
alter user sinhvien identified by sinhvien 
profile giang; 
--80h 
thiet lap thong so de profile co hieu luc 
grant create profile to sinhvien; 
--80f,g 
alter profile giang limit 
sessions_per_user 4 
failed_login_attempts 3 
--80i 
drop profile giang cascade; 
--81 a.Tao vung treo 
exec dbms_resource_manager.create_pending_area; 
Võ Đông Giang 2012 
34 Trường cao đẳng công nghệ thông tin TP.HCM 
--81 b.xoa vung treo 
exec dbms_resource_manager.clear_pending_area; 
--81 e 
exec dbms_resource_manager.create_pending_area; 
exec 
dbms_resource_manager.create_consumer_group('capnhat','nhom 
cap nhat'); 
exec 
dbms_resource_manager.create_consumer_group('timkiem','nhom 
tim kiem'); 
exec 
dbms_resource_manager.create_consumer_group('thongke','nhom 
thong ke'); 
exec 
dbms_resource_manager.create_consumer_group('baocao','nhom 
bao cao'); 
--81 f 
--kiem tra tinh hop le cua vung treo 
exec dbms_resource_manager.validate_pending_area 
--81 h 
grant capnhat to sinhvien; 
grant timkiem to sinhvien; 
grant thongke to sinhvien; 
grant baocao to sinhvien; 
--81 i 
Võ Đông Giang 2012 
35 Trường cao đẳng công nghệ thông tin TP.HCM 
select username, account_status from dba_users 
--81 h. Gán người dùng vừa tạo tới nhóm người dùng 
conn system/system 
exec 
dbms_resource_manager_privs.grant_system_privilege('SYSTEM',
'ADMINISTER_RESOURCE_MANAGER',TRUE); 
exec dbms_resource_manager.create_pending_area 
exec dbms_resource_manager.create_consumer_group('sv','nhom 
sv','ROUND-ROBIN' 
--khong chay dc 
exec 
dbms_resource_manager_privs.grant_switch_consumer_group('sin
hvien','sv',true) 
--81 l 
exec dbms_resource_manager.create_pending_area 
exec 
dbms_resource_manager.create_plan('kehoachtainguyen','ke 
hoach tai nguyen cho nguoi dung') 
--81 m 
exec 
dbms_resource_manager.create_plan_directive('kehoachtainguye
n','capnhat',60,100,100,100,100,100,100,100) 
exec 
dbms_resource_manager.create_plan_directive('kehoachtainguye
n','thongke',40,100,100,100,100,100,100,100) 
Võ Đông Giang 2012 
36 Trường cao đẳng công nghệ thông tin TP.HCM 
exec 
dbms_resource_manager.create_plan_directive('kehoachtainguye
n','baocao',30,100,100,100,100,100,100,100) 
--81 n gui ving treo 
exec dbms_resource_manager.create_pending_area 
exec dbms_resource_manager.submit_pending_area 
--81 o 
select plan, cpu_p1, cpu_p2, cpu_p3 from 
dba_rsrc_plan_directives 
--Tao role 
create role capnhat; 
create role timkiem; 
create role thongke; 
create role baocao; 
--cap quyen 
grant capnhat to sinhvien; 
--Xoa role 
revoke capnhat from sinhvien; 
Võ Đông Giang 2012 
37 Trường cao đẳng công nghệ thông tin TP.HCM 
THE END