课程目标：
1、管理框架
Oracle数据库的管理框架主要包含三个部分
也就是说Enterprise Manager分为Database Control和Grid Control，Database Control只能管理单个数据库，而Grid Control可以管理多个数据库。
2、启动和停止Database Control
确认监听已经启动。
[root@oracletest1 ~]# su - oracle
[oracle@oracletest1 ~]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 20-OCT-2015 22:27:13
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 20-OCT-2015 08:02:59
Uptime 0 days 14 hr. 24 min. 13 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/grid/product/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/grid/diag/tnslsnr/oracletest1/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracletest1)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "stone" has 1 instance(s).
Instance "stone", status READY, has 1 handler(s) for this service...
Service "stoneXDB" has 1 instance(s).
Instance "stone", status READY, has 1 handler(s) for this service...
The command completed successfully
确认数据库已经启动。
[oracle@oracletest1 ~]$ sqlplus /nolog
SQL*Plus: Release 11.2.0.4.0 Production on Tue Oct 20 22:27:18 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
SQL> conn / as sysdba
Connected.
SQL> select open_mode from v$database;
OPEN_MODE
--------------------
READ WRITE
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
设置SID，指定针对哪一个数据库进行管理
[oracle@oracletest1 ~]$ . oraenv
ORACLE_SID = [stone] ? stone
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@oracletest1 ~]$ echo $ORACLE_SID
stone
[oracle@oracletest1 ~]$ echo $ORACLE_BASE
/u01/app/oracle
直接输入emctl目录回车会显示该命令的用法。
[oracle@oracletest1 ~]$ emctl
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
Oracle Enterprise Manager 10g Database Control commands:
emctl start | stop dbconsole
emctl status | secure | setpasswd dbconsole
emctl config dbconsole -heap_size -max_perm_size
emctl status agent
emctl status agent -secure [-omsurl <http://:/em/*>]
emctl getversion
emctl reload | upload | clearstate | getversion agent
emctl reload agent dynamicproperties [:]....
emctl config agent
emctl config agent updateTZ
emctl config agent getTZ
emctl resetTZ agent
emctl config agent credentials [[:]]
emctl gensudoprops
emctl clearsudoprops
Blackout Usage :
emctl start blackout [-nodeLevel] [[:]].... [-d ]
emctl stop blackout
emctl status blackout [[:]]....
The following are valid options for blackouts
defaults to local node target if not specified.
If -nodeLevel is specified after ,the blackout will be applied to all targets and any target list that follows will be ignored.
Duration is specified in [days] hh:mm
emctl getemhome
emctl ilint
Em Key Commands Usage :
emctl config emkey -emkeyfile [-force] [-sysman_pwd ]
emctl config emkey -emkey [-emkeyfile ] [-force] [-sysman_pwd ]
emctl config emkey -repos [-emkeyfile ] [-force] [-sysman_pwd ]
emctl config emkey -remove_from_repos [-sysman_pwd ]
emctl config emkey -copy_to_repos [-sysman_pwd ]
emctl status emkey [-sysman_pwd ]
Secure DBConsole Usage :
emctl secure dbconsole -sysman_pwd [-passwd_file ]
[-host ] [-sid ] [-reset] [-secure_port ]
[-cipher_suites ] [-sign_alg ]
[-root_dc ] [-root_country ] [-root_state ] [-root_loc ]
[-root_org ] [-root_unit ] [-root_email ]
[-wallet ] [-wallet_pwd ] [-trust_certs_loc ]
emctl secure status dbconsole
Register Targettype Usage :
emctl register oms targettype [-o ] OR
emctl register oms targettype [-o ]
查看em的状态。
[oracle@oracletest1 ~]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
https://oracletest1:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/dbhome_1/oracletest1_stone/sysman/log
EM主要是DBA使用，如果不使用可以把他关掉。
[oracle@oracletest1 ~]$ emctl stop dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
https://oracletest1:1158/em/console/aboutApplication
Stopping Oracle Enterprise Manager 11g Database Control ...
... Stopped.
[oracle@oracletest1 ~]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
https://oracletest1:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is not running.
启动em。
[oracle@oracletest1 ~]$ emctl start dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
https://oracletest1:1158/em/console/aboutApplication
Starting Oracle Enterprise Manager 11g Database Control ......... started.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/dbhome_1/oracletest1_stone/sysman/log
[oracle@oracletest1 ~]$ emctl status dbconsole
Oracle Enterprise Manager 11g Database Control Release 11.2.0.4.0
Copyright (c) 1996, 2013 Oracle Corporation. All rights reserved.
https://oracletest1:1158/em/console/aboutApplication
Oracle Enterprise Manager 11g is running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/11.2.0/dbhome_1/oracletest1_stone/sysman/log
客户端浏览器直接使用https://oracletest1:1158/em/console访问不了，因为没有使用DNS解析这个主机名，可以使用IP地址访问https://192.168.230.139:1158/em/console，如果一定要使用机器名进行访问，可以修改C:\Windows\System32\drivers\etc目录下的hosts文件，增加IP地址及对应的机器名即可。
输入用户名，这里的用户名是要具有管理数据字典权限的用户，默认为sys，sysman和system。例如使用HR就不行，使用sys登录，选择连接身份为sysdba。
使用EM可以查看告警信息及性能走势图，创建和修改对象，执行备份和恢复等。下面为EM的主页。
主页上看到有数据库的一些信息，状态，什么时候启动的，实例名称，版本，主机，监听，有没有ASM，主机CPU情况，活动会话情况，响应时间，有没有报警，空间，高可用，还可以看性能，可用性，服务器，模式等等，后面会详细介绍。
3、其他Oracle工具
SQL*Plus：
SQL Developer：
管理I主要使用EM和SQL*Plus。
4、SQL*Plus
SQL*Plus是一个命令行工具，既可以运行在交换模式也可以运行在批处理模式。
可以使用命令行界面：
启动SQL*Plus步骤：
（1）打开一个终端
（2）输入 sqlplus / or /nolog
（3）如果使用/nolog选项，则需要使用 connect 进行连接
（4）输入密码
（1）可以在shell脚本或者批处理调用SQL*Plus。
例子：编写一个shell脚本，调用SQL*Plus进行查询
[oracle@oracletest1 ~]$ vi mybat.sh
#!/bin/bash
sqlplus hr/hr<<eof
select count(*) from employees;
select employee_id,last_name,salary from employees where employee_id=100;
quit
eof
[oracle@oracletest1 ~]$ sh mybat.sh
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 14 16:56:18 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL>
COUNT(*)
----------
107
SQL>
EMPLOYEE_ID LAST_NAME SALARY
----------- ------------------------- ----------
100 King 24000
SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
实际当中的运维就是这样，写一些管理脚本，获取数据库当中的关键信息，比如数据库状态，表空间等，自动运行，然后发邮件。
（2）还可以在sqlplus命令后面直接执行sql脚本。
[oracle@oracletest1 ~]$ vim mytest.sql
select * from departments where location_id=1400;
quit
[oracle@oracletest1 ~]$ sqlplus hr/hr @mytest.sql
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 14 17:01:48 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
60 IT 103 1400
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
（3）也可以进入sqlplus后运行脚本。
[oracle@oracletest1 ~]$ sqlplus hr/hr
SQL*Plus: Release 11.2.0.4.0 Production on Mon Dec 14 17:05:53 2015
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
SQL> @mytest.sql
DEPARTMENT_ID DEPARTMENT_NAME MANAGER_ID LOCATION_ID
------------- ------------------------------ ---------- -----------
60 IT 103 1400
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
（4）sqlplus里面执行操作系统的命令
SQL> ! ls -ltr
SQL> get mytest.sql
SQL> @?/rdbms/admin/awrrpt.sql
其中?表示$ORACLE_HOME，所有的操作系统通用，$ORACLE_HOME/rdbms/admin/这个目录是Oracle的创建数据库的脚本及管理脚本。
SQL> !ls -ltr $ORACLE_HOME/rdbms/admin/
5、初始化参数文件及参数
（1）初始化参数文件
当启动数据库的时候，会去读初始化参数文件。有2种类型的参数文件
Oracle推荐使用spfile维护初始化参数。
注意：在Linux下，Oracle会在$ORACLE_HOEM/dbs目录下搜索参数文件，如果使用ASM，则spfile位于ASM磁盘组上，这种情况下，init.ora必须位于$ORACLE_HOEM/dbs目录下，并在其中指定spfile的位置。
初始化参数值类型：
Derived Parameter Values
有一些参数的值是根据另外参数的值计算过来的，最好不要直接去修改这些参数的值，如果去修改，就会覆盖掉计算的值。例如sessions这个参数的值是根据processes这个参数计算过来的，如果processes变化了，则sessions也会跟着变化。
Operating System-Dependent Parameter Values
有一些参数的有效值或者取值范围依赖于操作系统。例如DB_FILE_MULTIBLOCK_READ_COUNT参数指定在一次连续扫描中单次I/O操作可以读取的最大块数量，这个参数取决于操作系统平台。
初始化参数分为基本和高级2类，在大多数情况下，只需要设置和调整大约30个基本参数就可以使数据库获得合理的性能。在极少数情况才需要调整高级参数获取优化的性能。Oracle 11gR2大约有314个高级参数。
例子：查看当前数据库的基本参数
SQL> select name,value from v$parameter where isbasic='TRUE';
NAME VALUE
------------------------------ ----------------------------------------------------------------------
processes 300
sessions 472
nls_language AMERICAN
nls_territory AMERICA
sga_target 0
control_files +DATA/stone/controlfile/current.260.893370773, +FRA/stone/controlfile/
current.256.893370773
db_block_size 8192
compatible 11.2.0.4.0
log_archive_dest_1
NAME VALUE
------------------------------ ----------------------------------------------------------------------
log_archive_dest_2
log_archive_dest_state_1 enable
log_archive_dest_state_2 enable
cluster_database FALSE
db_create_file_dest +DATA
db_create_online_log_dest_1
db_create_online_log_dest_2
db_recovery_file_dest +FRA
db_recovery_file_dest_size 4236247040
undo_tablespace UNDOTBS1
instance_number 0
NAME VALUE
------------------------------ ----------------------------------------------------------------------
ldap_directory_sysauth no
remote_login_passwordfile EXCLUSIVE
db_domain
shared_servers 1
remote_listener
db_name stone
db_unique_name stone
open_cursors 300
star_transformation_enabled FALSE
pga_aggregate_target 0
30 rows selected.
（2）初始化参数
常用的基本参数有如下：
（3）使用SQL*Plus查看参数
例子：使用v$parameter查看当前会话的当前参数值。
SQL> select name,value from v$parameter;
NAME VALUE
------------------------------ ----------------------------------------------------------------------
lock_name_space
processes 300
sessions 472
timed_statistics TRUE
timed_os_statistics 0
resource_limit FALSE
。。。
cell_offloadgroup_name
352 rows selected.
例子：使用show parameter查看包含某个字符串的参数
SQL> show parameter shared_pool_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
shared_pool_size big integer 0
SQL> show parameter para
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offload_parameters string
fast_start_parallel_rollback string LOW
parallel_adaptive_multi_user boolean TRUE
关于初始化参数的其他数据字典视图：
（4）修改初始化参数
两种初始化参数类型：
使用alter system设置或者修改初始化参数的值，scope选项指定修改的范围
如果实例没有使用spfile启动，则指定scope=spfile或者scope=both会报错。如果使用spfile启动实例，则默认选项是scope=both，如果使用文本参数文件启动实例，则默认选项是scope=memory。
对于某些动态参数，还可以指定deferred关键字，表示以后的会话才生效。deferred对以下参数有效：
字段
字段值
说明
isses_modifiable
true
表示这个参数可以使用alter session修改
false
表示不能使用alter session命令修改
issys_modifiable
immediate
表示这次对这个参数的修改会在当前所有会话中就"立即"发生作用, 即修改立即生效.
deferred
表示这次修改对当前会话不发生作用, 在以后打开的会话中起作用, 故它有"推迟"影响的效果. 修改该参数值时需要使用alter system set...deferred. 如果不加deferred关键字则报错ORA-02096: specified initialization parameter is not modifiable with this option.
false
表示不能使用alter system命令修改, 只能alter system ...... scope=spfile
例子：在会话级别修改nls_date_format参数
SQL> select sysdate from dual;
SYSDATE
------------
14-DEC-15
SQL> alter session set nls_date_format='mon dd yyyy';
Session altered.
SQL> select sysdate from dual;
SYSDATE
--------------
dec 14 2015
例子：修改系统参数sec_max_failed_login_attempts并加上注释，下次重启生效
SQL> alter system set sec_max_failed_login_attempts=8 comment='Reduce from 10 for tighter security.' scope=spfile;
System altered.
（5）使用EM查看数据库的参数
所有静态参数
基本静态参数
基本动态参数
（6）使用EM查看当前会话的参数
基本静态参数
基本动态参数
6、启动和关闭数据库
使用EM关闭数据库，需要输入操作系统的用户名和密码以及数据库的用户名和密码，使用关闭数据库的默认选项immediate。
可以使用EM，srvctl或者sql*plus启动数据库。这里使用https://192.168.230.139:1158/em登录EM，输入操作系统用户名和密码，点击“继续”，然后点击“启动”。
输入操作系统的用户名和密码以及数据库的用户名和密码
选择“启动数据库和从属资源”。
7、启动数据库实例的三种模式
（1）nomount
在数据库创建、重建控制文件或者某些备份和恢复时候，需要将实例启动到nomount模式
启动实例到nomount包括下面的内容：
（2）mount
mount数据库包括下面的内容：
可以在mount状态执行特定的维护操作，比如：
（3）open
打开数据库包括：
任何一个数据文件或者联机重做日志文件不存在将会返回错误。Oracle验证所有数据文件和联机重做日志文件，检查他们的一致性。
例子：启动数据库的选项
SQL> startup
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
Database mounted.
Database opened.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 835104768 bytes
Fixed Size 2257840 bytes
Variable Size 549456976 bytes
Database Buffers 281018368 bytes
Redo Buffers 2371584 bytes
SQL> alter database mount;
Database altered.
SQL> alter database open;
Database altered.
[oracle@oracletest1 ~]$ srvctl stop database -d stone
[oracle@oracletest1 ~]$ srvctl status database -d stone
Database is not running.
[oracle@oracletest1 ~]$ srvctl start database -d stone -o mount
[oracle@oracletest1 ~]$ srvctl status database -d stone
Database is running.
8、关闭数据库的选项
关闭数据库有四个选项：
例子：关闭数据库选项
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> shutdown abort
ORACLE instance shut down.
[oracle@oracletest1 ~]$ srvctl stop database -d stone -o abort
9、查看告警日志
每个数据库都有一个名字为alert_.ora的告警日志文件，如果设置了$ORACLE_BASE，则默认位于$ORACLE_BASE/diag/rdbms///trace目录下。
告警文件按照时间先后顺序记录如下信息：
由于告警文件的大小会不断增长，所以需要经常查看，定期备份并删除。当数据库重新去写告警文件的时候，会自动重新创建一个。
Oracle还有一个XML版本的告警文件，位于$ORACLE_BASE/diag/rdbms///alert目录下。
例子：查看告警日志文件位置
SQL> select * from v$diag_info;
INST_ID NAME VALUE
---------- ------------------------- ----------------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base /u01/app/oracle
1 ADR Home /u01/app/oracle/diag/rdbms/stone/stone
1 Diag Trace /u01/app/oracle/diag/rdbms/stone/stone/trace
1 Diag Alert /u01/app/oracle/diag/rdbms/stone/stone/alert
1 Diag Incident /u01/app/oracle/diag/rdbms/stone/stone/incident
1 Diag Cdump /u01/app/oracle/diag/rdbms/stone/stone/cdump
1 Health Monitor /u01/app/oracle/diag/rdbms/stone/stone/hm
1 Default Trace File /u01/app/oracle/diag/rdbms/stone/stone/trace/stone_ora_63954.trc
1 Active Problem Count 0
1 Active Incident Count 0
11 rows selected.
例子：使用tail查看告警日志
[oracle@oracletest1 ~]$ tail -10 /u01/app/oracle/diag/rdbms/stone/stone/trace/alert_stone.log
Warning: VKTM detected a time drift.
Time drifts can result in an unexpected behavior such as time-outs. Please check trace file for more details.
Tue Dec 15 08:14:22 2015
Starting background process SMCO
Tue Dec 15 08:14:25 2015
SMCO started with pid=21, OS id=62385
Tue Dec 15 08:14:33 2015
Closing scheduler window
Closing Resource Manager plan via scheduler window
Clearing Resource Manager plan via parameter
例子：使用EM查看告警日志
10、使用跟踪文件（trace file）
每一个服务器进程和后台进程都会写入到相关的跟踪文件中，当进程检测到一个内部错误，会将错误信息dump到跟踪文件。一般后台进程产生的跟踪文件的文件名都包含有这个进程的名字。我们可以通过跟踪文件去调整应用或者实例。可以使用tkprof命令来阅读跟踪文件。
从11g开始，Oracle提供了一个全新的系统级诊断信息架构，称之为自动诊断知识库ADR（Automatic diagnostic repository），统一管理Oracle所有的诊断信息，包括跟踪文件，告警日志，健康监控报告等，所以位于$ORACLE_HOME之外。
ADR的根目录称之为ADR base，由参数DIAGNOSTIC_DEST设置，如果没有设置该参数，则：
某种类型的诊断信息所在目录称之为ADR home，在ADR base目录下，具体路径为./diag/product_type/db_id/instance_id
例子：查看参数DIAGNOSTIC_DEST
SQL> show parameter diagnostic_dest
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
diagnostic_dest string /u01/app/oracle
11、动态性能视图
动态性能视图是基于数据库服务器内存结构创建的的虚拟表，其中一些可以在数据库mount或者open之前就可以查看。可以使用dict视图查看，一般以“v$”开头，超过590个，动态性能视图包括以下信息：
例子：通过v$sql查看sql执行时间超过2000ms的sql语句及执行次数
SQL> select sql_text,executions from v$sql where cpu_time>2000000;
SQL_TEXT EXECUTIONS
---------------------------------------------------------------------------------------------------- ----------
BEGIN EMDW_LOG.set_context(MGMT_JOB_ENGINE.MODULE_NAME, :1); MGMT_JOB_ENGINE.get_scheduled_steps(:2, 1936
:3, :4, :5); EMDW_LOG.set_context; END;
例子：通过v$session视图查看从昨天开始通过机器“WORKGROUP\SHILEI-PC”连接的数据库的会话信息
SQL> select sid,serial#,machine,logon_time from v$session where machine='WORKGROUP\SHILEI-PC' and logon_time>sysdate-1;
SID SERIAL# MACHINE LOGON_TIME
---------- ---------- ---------------------------------------------------------------- ------------
70 115 WORKGROUP\SHILEI-PC 15-DEC-15
例子：通过v$lock视图查看阻塞其他会话的会话sid及持有锁的时间
在会话1的sys用户下执行
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
46
SQL> update hr.employees set salary=10000 where employee_id=100;
1 row updated.
在会话2的sys用户下执行
SQL> select userenv('sid') from dual;
USERENV('SID')
--------------
73
SQL> update hr.employees set salary=10000 where employee_id=100;
在会话3的sys用户执行
SQL> select sid,ctime from v$lock where block>0;
SID CTIME
---------- ----------
46 20
还可以具体查看阻塞了哪一个会话
SQL> select a.sid,b.sid
2 from v$lock a,v$lock b
3 where b.id1=a.id1 and a.id2=b.id2
4 and a.sid<>b.sid
5 and b.request<>0;
SID SID
---------- ----------
46 73
动态性能视图使用指导：
12、数据字典视图
在前面SQL的章节已经讲过了。
例子：hr用户使用user_tables视图查看表名及所在表空间
SQL> select table_name,tablespace_name from user_tables;
TABLE_NAME TABLESPACE_NAME
------------------------------ ------------------------------
LOCATIONS EXAMPLE
DEPARTMENTS EXAMPLE
JOBS EXAMPLE
COUNTRIES
EMPLOYEES EXAMPLE
JOB_HISTORY EXAMPLE
REGIONS EXAMPLE
7 rows selected.
例子：sys用户使用all_sequences查看相关用户的序列
SQL> select sequence_name,min_value,max_value,increment_by
2 from all_sequences
3 where sequence_owner in ('MDSYS','XDB');
SEQUENCE_NAME MIN_VALUE MAX_VALUE INCREMENT_BY
------------------------------ ---------- ---------- ------------
FT_SQ$ 1 1.0000E+28 1
NTV2_SEQUENCE 1 1.0000E+28 1
例子：sys用户使用dba_users查看当前用户状态为open的用户
SQL> select username,account_status from dba_users where account_status='OPEN';
USERNAME ACCOUNT_STATUS
------------------------------ --------------------------------
SYSTEM OPEN
SYS OPEN
SYSMAN OPEN
HR OPEN
DBSNMP OPEN
MGMT_VIEW OPEN
6 rows selected.
13、相关习题：
（1）You want to check the details of few errors that users have reported. You search for the alert log file and execute few commands to find the location of the alert log file. View the Exhibit and check the commands executed. What is the location of the alert_orcl.log file?
Exhibit:
A. ORACLE_HOME/dbs
B. ORACEL_HOME/rdbms
C. /u01/app/oracle/admin/orcl/adump
D. /u01/app/oracle/flash_recovery_area
E. ORACLE_BASE/diag/rdbms/orcl/alert
F. ORACLE_BASE/diag/rdbms/orcl/orcl/trace
答案：F
（2）Note the functionalities of various background processes:
1. Perform recovery at instance startup.
2. Free the resources used by a user process when it fails.
3. Cleanup the database buffer cache when a process fails.
4. Dynamically register database services with listeners.
5. Monitor sessions for idle session timeout.
6. Cleanup unused temporary segments.
7. Record the checkpoint information in control file.
Which option has the correct functionalities listed for a background process?
A.Checkpoint (CKPT): 1, 2, 5
B.System Monitor (SMON): 1, 6
C.Process Monitor (PMON): 4, 6, 7
D.Database Writer (DBWR): 1, 3, 4
答案：B
（3）Identify the two situations in which the alert log file is updated with details. (Choose two.)
A. Running a query on a table returns "ORA-600: Internal Error"
B. Inserting a value in a table returns "ORA-01722: Invalid Number"
C. Creating a table returns "ORA-00955: name is already used by an existing object'
D. Inserting a value in a table returns "ORA-00001: unique constraint (SYS.PK_TECHP) violated."
E. Rebuilding an index using ALTER INDEX ... REBUILD fails with an error "ORA-01578: ORACLE data block corrupted (file # 14, block @ 50)."
答案：AE
（4）Which statements listed below describe the data dictionary views?
1. These are stored in the SYSTEM tablespace
2. These are the based on the virtual tables
3. These are owned by the SYS user
4. These can be queried by a normal user only if O7_DICTIONARY_ACCESSIBLILITY parameter is set to TRUE
5. The V$FIXED_TABLE view can be queried to list the names of these views
A. 1 and 3
B. 2,3 and 5
C. 1,2, and 5
D. 2,3,4 and 5
答案：A
（5）You have an ORDERS table with the following structure:
Name Null? Type
------------------------- -------- -------------------------
OID NUMBER(6)
ODATE DATE
CCODE NUMBER(6)
OAMT NUMBER(10,2)
The table has data in the ODATE column for all rows. Many orders are placed in a single day. You need to ensure that the ODATE column must contain data for every order in future.
Which method would serve the purpose?
A. Modify the column using the ALTER TABLE ... MODIFY command.
B. Add a UNIQUE constraint to the column using the ALTER TABLE ... ADD CONSTRAINT command.
C. Add a NOT NULL constraint to the column using the ALTER TABLE ... ADD CONSTRAINT command.
D. Add a PRIMARY KEY constraint to the column using the ALTER TABLE ... ADD CONSTRAINT command.
答案：A
（6）You have two tables with referential integrity enforced between them. You need to insert data to the child table first because it is going to be a long transaction and data for the parent table will be available in a later stage, which can be inserted as part of the same transaction.
View the Exhibit to examine the commands used to create tables.
Which action would you take to delay the referential integrity checking until the end of the transaction?
A. Set the constraint to deferred before starting the transaction
B. Alter the constraint to NOVALIDATE state before starting the transaction
C. Enable the resumable mode for the session before starting the transaction
D. Set the COMMIT_WAIT parameter to FORCE_WAIT for the session before starting the transaction
答案：A
（7）Automatic Shared Memory Management is disabled for your database instance. You realize that there are cases of SQL statements performing poorly because of repeated parsing activity, resulting in degradation of performance.
What would be your next step to improve performance?
A. Run the SQL Access Advisor
B. Run the memory Advisor for the SGA
C. Run the memory Advisor for the PGA
D. Run the memory advisor for the shared pool
E. Run the memory advisor for the buffer cache
答案：D
（8）You have issued a SHUTDOWN ABORT command to bring down your database instance. Consider the steps that will be performed later when you open the database:
1. SGA is allocated.
2. Control file is read.
3. Redo log files are read.
4. Instance recovery is started.
5. Background processes are started.
6. Data files are checked for consistency.
7. Server parameter file or the initialization parameter file is read.
Which option has the correct order in which these steps occur?
A. 7, 1, 5, 2, 3, 6, 4
B. 1, 2, 3, 7, 5, 6, 4
C. 7, 1, 4, 5, 2, 3, 6
D. 1, 7, 5, 4, 2, 3, 6
答案：Ａ
（9）You used the IMMEDIATE option to shutdown your database instance. Consider the steps that will be performed later when you open the database:
Which option has the correct order in which these steps occur?
A. 7, 1, 5, 2, 3, 6, 4
B. 1, 5, 7, 2, 3, 6; step 4 is not required
C. 7, 1, 5, 2, 3, 6 step 4 is not required
D. 1, 2, 3, 5, 6, 4; step 7 is not required
答案：C
（10）You have executed this command to change the size of the database buffer cache:
SQL> ALTER SYSTEM SET DB_CACHE_SIZE=2516582;
System altered.
To verify the change in size, you executed this command:
SQL> SHOW PARAMETER DB_CACHE_SIZE
NAME TYPE VALUE
-------------------- ---------------- ------------------
db_cache_size big integer 4194304
Why is the value set to 4194304 and not to 2516582?
A. Because 4194304 is the granule size
B. Because 4194304 is the standard block size
C. Because 4194304 is the largest nonstandard block size defined in the database
D. Because 4194304 is the total size of data already available in the database buffer cach
答案：A
（11）View the Exhibit and examine the output of the query.
What do you infer from this?
A. The SGA_TARGET is a static parameter.
B. The instance is started, but the database is not yet open.
C. The server parameter file (SPFILE) was used to start the instance.
D. The SGA_TARGET parameter does not have any effect on the database instance until the SGA_MAX_SIZE parameter is specified.
答案：C
（12）Identify the two situations in which you use the alert log file in your database to check the details. (Choose two.)
A. Running a query on a table returns "ORA-600: Internal Error."
B. Inserting a value in a table returns "ORA-01722: invalid number."
C. Creating a table returns "ORA-00955: name is already used by an existing object."
D. Inserting a value in a table returns "ORA-00001: unique constraint (SYS.PK_TECHP) violated."
E. Inserting a row in a table returns "ORA-00060: deadlock detected while waiting for resource."
答案：AE
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) | Powered by Discuz! X3.2 |
