oracle教程(第二版)》部分习题参考答案

文字性习题答案参见相应章节内容

第1章

习题1

D A C C

第2章

习题1

执行edit命令打开文本编辑器；

使用column命令设置列显示宽度

习题2

CONN scott/tiger@orcl

SELECT * FROM tab; --本题严谨一些的答案是查询ALL_OBJECTS视图，带条件查询习题3

@d:\init_data\init_data.sql

习题4

SET PAGESIZE 24

SET LINESIZE 100

COLUMN sal FORMA T $99,990.00

第3章

习题1

D B

习题2

CONN stu01/stu01pwd@orcl

COLUMN USERNAME FORMA T A10

SELECT username,granted_role FROM user_role_privs;

习题5

CREA TE SESSION 系统权限

习题11

CONN scott/tiger@orcl

COLUMN GRANTEE FORMA T A10

SELECT grantee,table_name,grantor,privilege,grantable

FROM user_tab_privs_made;

习题12

CONN system/systempwd@orcl

COLUMN GRANTEE FORMA T A22

SELECT grantee,privilege,admin_option FROM dba_sys_privs

ORDER BY grantee,privilege;

SELECT grantee,granted_role FROM dba_role_privs;

使用REVOKE命令回收不必要的系统权限和角色。\

第4章

习题8

CONN /@cemerp AS SYSDBA

ALTER USER scott QUOTA 10M ON USERS;

习题9

CONN system/systempwd@orcl

CREA TE TABLESPACE data_ts1

DA TAFILE '%ORACLE_BASE%\oradata\orcl\data_ts1.dbf' SIZE 10000M REUSE; CREA TE TEMPORARY TABLESPACE temp_ts1

TEMPFILE '%ORACLE_BASE%\oradata\orcl\temp_ts1.dbf' SIZE 200M REUSE; CREA TE USER surtec IDENTIFIED BY surtecpwd

DEFAULT TABLESPACE data_ts1 TEMPORARY TABLESPACE temp_ts1; GRANT CREA TE SESSION TO surtec;

GRANT RESOURCE TO surtec;

CONN surtec/surtecpwd@orcl

--创建该用户的表emp……

--授权给SIB

GRANT SELECT ON emp TO sib;

GRANT UPDA TE(flag) ON endowment_insurance TO sib;

习题10

CONN system/systempwd@orcl

CREA TE USER test01 IDENTIFIED BY test1234;

GRANT CREA TE SESSION TO test01;

GRANT CREA TE TABLESPACE TO test01;

CONN test01/test1234@orcl

CREA TE TABLESPACE test_ts

DA TAFILE '%ORACLE_BASE%\oradata\orcl\test_ts.dbf' SIZE 100K

AUTOEXTEND ON NEXT 50K MAXSIZE 5M UNIFORM SIZE 50K;

ALTER TABLESPACE test_ts

ADD DA TAFILE '%ORACLE_BASE%\oradata\orcl\test_ts2.dbf' SIZE 10M; ALTER DA TABASE

DA TAFILE '%ORACLE_BASE%\oradata\orcl\test_ts2.dbf' RESIZE 15M ;

习题11

CONN system/systempwd@orcl

ALTER TABLESPACE pur_ts

ADD DA TAFILE '%ORACLE_BASE%\oradata\orcl\pur_data.dbf' SIZE 800M; ALTER DA TABASE

DA TAFILE '%ORACLE_BASE%\oradata\orcl\pur_data.dbf' RESIZE 2000M ; ALTER DA TABASE DA TAFILE '%ORACLE_BASE%\oradata\orcl\pur_data.dbf' AUTOEXTEND ON NEXT 30M;

第5章

习题1

B A

习题8

(1)撤销表空间中不允许建立永久方案对象；将方案对象建立在数据表空间即可。

(2)在脱机表空间上不允许执行创建方案对象操作；将该表空间联机即可。

(3)使用ALTER USER修改用户在USERS表空间上的限额即可。

(4)使用ALTER USER修改用户，扩大在USERS表空间上的限额即可。

习题9

CONN hr/hrpwd@orcl

CREA TE TABLE employees2 AS

SELECT employee_id id, first_name, last_name, salary,

department_id dept_id

FROM employees;

习题10

CONN scott/tiger@orcl

ALTER TABLE emp

ADD (dept_id NUMBER(7));

ALTER TABLE emp