Oracle认证考试试题

合集下载

2023年Oracle认证考试真题

2023年Oracle认证考试真题1. 数据库设计与规范化（25分）在关系数据库的设计和规范化过程中，你是负责设计一个学生信息管理系统的数据库。

请按照以下要求完成相关任务：a. 根据需求分析得到的实体和关系，绘制ER图，并对ER图进行适当的优化和调整；b. 基于ER图，将实体和关系转化为关系模式，确定属性及其关联关系；c. 对关系模式进行规范化，确保数据库满足第三范式。

2. SQL查询与优化（35分）本题需要你运用SQL语言进行数据查询和性能优化。

请根据以下要求完成相关任务：a. 根据给定的表结构和数据，编写SQL查询语句，实现以下功能：- 查询学生表中的所有记录；- 查询平均成绩大于80分的学生姓名和其对应的课程名；- 查询课程表中课程名以“数据库”开头的记录。

b. 对于上述查询语句，分别给出优化方案，提高查询性能。

3. 数据库备份与恢复（20分）在数据库管理过程中，备份与恢复是非常重要的环节。

请根据以下要求完成相关任务：a. 使用Oracle提供的工具或语句进行数据库备份，要求备份包含完整的数据库结构和数据；b. 恢复数据库时，请说明你会使用的方法和步骤，确保数据能够完整恢复。

4. 数据安全与权限管理（20分）数据库安全和权限管理是企业中保护数据的重要措施。

请根据以下要求完成相关任务：a. 列举至少三种数据库安全控制措施，并简要说明其实现原理；b. 设计一个用户角色并赋予相应的权限，确保用户只能访问其所需的数据。

5. 总结与建议（重点评分项）请根据你在考试中的实际经历，结合自身专业知识和经验，对Oracle数据库认证考试进行总结与建议。

包括但不限于以下方面：a. 考试难度及相关学习准备建议；b. 考试题型及题量特点，对备考提出建议；c. 经验分享，例如学习方法、重点复习内容等。

以上是2023年Oracle认证考试真题的内容要求。

在实际考试中，请根据题目要求和时间安排合理分配答题时间。

祝你考试顺利！。

Oracle 考试试题(带答案)

a) b) c) d) 参考答案
OracleHOME_NAMETNSListener OracleServiceSID OracleHOME_NAMEAgent OracleHOME_NAMEHTTPServer
B
9) 在 Oracle 中创建用户时，若未提及 DEFAULT TABLESPACE 关键字，则 Oracle 就将（）表空间分配给用户作为默认表空间。。
DATEPART EXTRACT TO_CHAR TRUNC
bc
5) 在Oracle中，有一个教师表teacher的结构如下： ID NUMBER(5) NAME VARCHAR2(25) EMAIL VARCHAR2(50) 下面哪个语句显示没有Email地址的教师姓名（）。
a) b) c) d) 参考答案
a) b) c) d) 参考答案
OracleHOME_NAMETNSListener OracleServiceSID OracleHOME_NAMEAgent OracleHOME_NAMEHTTPServer
a
8) 在Windows操作系统中，Oracle的（）服务是使用 iSQL*Plus必须的。
A
12) 在Oracle中，下面用于限制分组函数的返回值的子句是（）。
a) WHERE b) HAVING c) ORDER BY d) 无法限定分组函数的返回值
参考答案 b
13) 在Oracle中，有一个名为seq的序列对象，以下语句能返回序列值但不会引起序列值增加的是（）。
a) b) c) d) 参考答案
%NOTFOUND %FOUND %ROWTYPE %ISOPEN %ROWCOUNT
c
15) 在Oracle中，当控制一个显式游标时，下面哪种命令包含INTO子句（）。

oracle认证考试试题

oracle认证考试试题Oracle认证考试试题作为IT行业的从业者，不管是初级还是高级，都会面临各种各样的考试。

其中，Oracle认证考试是一项非常重要的考试，它可以证明一个人在Oracle数据库方面的专业能力。

在这篇文章中，我们将探讨一些常见的Oracle认证考试试题，以帮助读者更好地准备和应对这个考试。

一、数据库基础知识1. 什么是数据库？数据库是一个有组织的数据集合，可以通过计算机系统来存储和访问。

它是一个结构化的存储系统，可以用于存储和管理大量的数据。

2. 请简要解释关系数据库的概念。

关系数据库是一种基于关系模型的数据库，它使用表格（也称为关系）来存储和组织数据。

每个表格由行和列组成，行表示记录，列表示属性。

3. 什么是SQL？SQL（Structured Query Language）是一种用于管理关系数据库的标准化语言。

它可以用于查询、插入、更新和删除数据库中的数据。

二、SQL查询1. 编写一条SQL查询，从表格"Employees"中选择所有员工的姓名和工资。

SELECT Name, SalaryFROM Employees;2. 编写一条SQL查询，计算表格"Orders"中每个客户的订单总数。

SELECT CustomerID, COUNT(OrderID) AS TotalOrdersFROM OrdersGROUP BY CustomerID;3. 编写一条SQL查询，选择表格"Products"中价格在100到200之间的所有产品。

SELECT *FROM ProductsWHERE Price BETWEEN 100 AND 200;三、数据库管理1. 什么是数据库事务？数据库事务是一系列数据库操作的逻辑单元，它要么全部执行成功，要么全部回滚。

事务具有原子性、一致性、隔离性和持久性（ACID）的特性。

2. 请简要解释什么是数据库索引。

oracle数据库管理员技能认证考试1

数据库管理员技能认证考试注意：选择题答案一律写在后面的答题表格上，在其他地方作答无效。

一、单项选择题（每题2分，共60分）1、以下（）内存区不属于SGA。

A．PGA B．日志缓冲区C．数据缓冲区D．共享池2、（）模式存储数据库中数据字典的表和视图。

A．DBA B．SCOTT C．SYSTEM D．SYS3、在Oracle中创建用户时，若未提及DEFAULT TABLESPACE关键字，则Oracle就将（）表空间分配给用户作为默认表空间。

A．HR B．SCOTT C．SYSTEM D．SYS4、（）服务监听并按受来自客户端应用程序的连接请求。

A．OracleHOME_NAMETNSListener B．OracleServiceSIDC．OracleHOME_NAMEAgent D．OracleHOME_NAMEHTTPServer5、如果一个服务器进程非正常终止，Oracle系统将使用（）进程来释放它所占用的资源。

A．DBWR B．LGWR C．SMON D．PMON6、（）SQL语句将为计算列SAL*12生成别名Annual SalaryA．SELECT ename,sal*12 …Annual Salary‟ FROM emp;B．SELECT ename,sal*12 “Annual Salary” FROM emp;C．SELECT ename,sal*12 AS Annual Salary FROM emp;D．SELECT ename,sal*12 AS INITCAP(“Annual Salary”) FROM emp;7、锁用于提供( )。

A．改进的性能B．数据的完整性和一致性C．可用性和易于维护D．用户安全8、( )锁用于锁定表，允许其他用户查询表中的行和锁定表，但不允许插入、更新和删除行。

A．行共享B．行排他C．共享D．排他9、带有( )子句的SELECT语句可以在表的一行或多行上放置排他锁。

OracleDBA认证考试存储管理试题及答案

OracleDBA认证考试存储管理试题及答案Oracle DBA认证考试存储管理试题及答案Q. 1 : Oracle immediately re-uses the space assigned to row-directory portion of the data block once you delete the corresponding rows.1. True2. False2Q. 2 : Which of the following three portions of a data block are collectively called as Overhead.1. table directory, row directory and row data2. data block header, table diretory and free space3. table directory, row directory and data blcok header4. data block header, row data and row header3Q. 3 : Which portion of the data block contains table or index data1. table directory2. row directory3. overhead4. row data4Q. 4 : What are the two space management parameters for controling the use of free space in a data block1. INITIAL and NEXT2. PCTFREE and PCTUSED3. TABLESPACE and STORAGE4. free space cannot be controlled at block level2Q. 5 : The maximum number of transactions that a block can support is1. 1002. 2553. 10004. unlimited2Q. 6 : what is PCTFREE1. A space management parameter which sets aside that much percentage of memory in the data block for future update of existing rows.2. A space management parameter which determines the minimum percentage of a block that can be used for row data plus overhead.3. A space management parameter which tells Oracle that the block is available for inserts after the percentage of used memory falls below certain percentage.4. all of them1Q. 7 : What is the default value of NEXT parameter1. 1 block2. 2 blocks3. 5 blocks4. 100 blocks3Q. 8 : Which parameter reserves the more space for future updates1. high PCTINCREASE2. high PCTFREE3. high INITIAL2Q. 9 : PCTFREE and PCTUSED together should not exceed1. 1002. 503. 254. 101Q. 10 : Row migration is a result of1. not enough space in a block for new rows.2. not enough memory for sorts3. not enough space for updates in the current block4. all of them3Q. 11 : Frequent deallocation of extents result in1. truncated segments2. block fragmentation3. truncated tablespaces4. tablespace fragmentation4Q. 12 : Which background process coalesce the fragmented space1. SMON2. PMON3. DBWR4. LGWR1Q. 13 : Row migration is a result of1. high PCTFREE2. low PCTFREE4. low PCTUSED 2。

精选新版ORCLE认证考核题库288题(含答案)

2020年ORCLE认证考试题库288题[含答案]一、选择题1．如果需要向表中插入一批已经存在的数据，可以在INSERT语句中使用___。

2．关闭数据库的几种方式：正常关闭、(4个字)、终止关闭、(4个字)。

3．使用EXPORT导出数据时，可以根据需要按3种不同的方式导出数据。

（3个字）方式就是导出一个指定的基本表，包括表的定义、表中的数据，以及在表上建立的索引、约束等。

（2个字）方式是指导出属于一个用户的所有对象，包括表、视图、存储过程、序列等。

（4个字）方式导出是指导出数据库中所有对象。

4．使用(1个字)命令可以显示表的结构信息5．使用(1个字)命令可以将缓冲区中的SQL命令保存到一个文件中，并且可以使用(1个字)命令运行该文件。

6．如果要获知索引的使用情况，可以通过查询(18个字)视图；而要获知索引的当前状态，可以查询(10个字)视图。

7．如果要获知索引的使用情况，可以通过查询（18个字）视图；而要获知索引的当前状态，可以查询（10个字）视图8．使用RMAN对整个数据库进行恢复的正确步骤为：启动实例并加载数据库，（17个字），（17个字）打开数据库。

9．如果需要在SELECT子句中包括一个表的所有列，可使用符号(1个字)。

10．使用RMAN进行目标数据库的备份时，可以使用（2个字）命令。

（1个字）命令用于数据文件备份，可以将指定的数据库文件备份到磁盘或磁带。

（1个字）命令是数据的备份，可以复制一个或多个表空间，以及整个数据库中的数据。

11．如果某个数据库的LGWR进程经常会因为检查点未完成而进入等待状态，则DBA应当采取（9个字）措施来解决该问题。

12．如果定义与组有关的索条件，可以把 (7个字) 子句添加到SELECT语句中。

13．如果表中某列的基数比较低，则应该在该列上创建（两个字）索引14．如果表中某列的基数比较低，则应该在该列上创建(2个字)索引。

15．启用复合控制文件后，由于多个控制文件互为镜像，内容总是（6个字）。

obcp笔试题库

1、在Oracle数据库中，哪个命令用于创建新的表空间？A、CREATE DATABASEB、CREATE SCHEMAC、CREATE TABLESPACED、CREATE USER（答案：C）2、关于OCP（Oracle Certified Professional）认证，以下哪项描述是错误的？A、OCP认证是Oracle公司提供的专业级认证B、OCP认证需要通过相应的考试才能获得C、OCP认证只需通过理论考试，无需实际操作经验D、OCP认证有助于提升数据库管理员的职业竞争力（答案：C）3、在SQL语句中，哪个关键字用于选择满足特定条件的记录？A、INSERTB、UPDATEC、DELETED、SELECT（答案：D）4、Oracle数据库中的redo log文件主要作用是？A、记录数据库的所有变更操作B、用于数据库的恢复和重建C、记录用户的登录信息D、存储数据库的临时数据（答案：A）5、以下哪项不是Oracle数据库管理员（DBA）的职责？A、数据库的安装与配置B、数据库的性能调优C、数据库的安全管理D、应用程序的开发与维护（答案：D）6、在Oracle数据库中，哪个工具通常用于图形化管理数据库？A、SQL*PlusB、Oracle Enterprise ManagerC、RMAND、Data Pump（答案：B）7、关于Oracle数据库的归档日志模式，以下哪项描述是正确的？A、归档日志模式仅适用于大型数据库B、归档日志模式可以提高数据库的性能C、归档日志模式可以保护数据库免受介质故障的影响D、归档日志模式会显著降低数据库的存储空间（答案：C）8、在Oracle数据库中，哪个视图可以显示当前会话的详细信息？A、DBA_USERSB、V$SESSIONC、ALL_TABLESD、USER_PRIVILEGES（答案：B）。

计算机认证考试-oca认证oracle服务器技术、体系结构

OCA认证Oracle服务器技术、体系结构一、不定项选择题1、下面的哪些语言可以与数据库一起运行(选择所有正确的答案)？A．CB．JavaC．PL/SQLD．SQLE．与OCI库链接在一起的其他任何语言2、在Web应用程序中，应用程序软件在哪一层运行(选择一个最佳答案)？A．在客户端层的Web浏览器中B．在中间层C．在启用了Java的数据库中3、必须对哪种数据存储范例的数据进行规范化(选择一个最佳答案)？A．层次数据库B．网络数据库C．面向对象的数据库D．关系数据库4、在规范化数据时，应避免哪类关系(选择一个最佳答案)？A．一对多B．多对一C．多对多D．一对一5、SQL并非是万能的。

哪些功能需要其他语言(选择所有正确的答案)？A．设计用户界面B．分支结构，如IF…THEN…ELSEC．同时影响多个行的操作D．创建和删除表6、下列哪项不是SQL命令(选择一个最佳答案)？A．MERGEB．UPSERTC．COMMENTD．SAVEPOINTE．以上都是SQL命令7、在确保数据不丢失和不危害安全方面，下面哪项通常不是DBA工作的一部分(选择一个最佳答案)？ A．设计备份例程B．设置磁盘镜像C．创建和删除数据库对象D．测试还原和恢复策略8、在下面的语句中，有关内存实例和会话内存的哪个说法是正确的(选择所有正确的答案)？A．SGA内存是专用内存段；PGA内存是共享内存段。

B．会话可以对PGA执行写操作，不能对SGA执行写操作。

C．所有会话对SGA执行写操作，只有一个会话对PGA执行写操作。

D．PGA在实例启动时分配。

E．SGA在实例启动时分配。

9、会话如何与数据库通信(选择一个最佳答案)？A．服务器进程使用Oracle Net连接到实例。

B．后台进程使用Oracle Net连接到数据库。

C．用户进程从数据库读取，对实例执行写操作。

D．服务器进程执行从用户进程收到的SQL。

10、哪些内存结构是SGA必需的部分(选择所有正确的答案)？A．数据库高速缓存区B．Java池C．大池D．日志缓冲区E．程序全局区域F．共享池G．流池11、下面的哪个或哪些SGA内存结构无法在启动实例后动态重设大小(选择所有正确的答案)？ A．数据库高速缓存区B．Java池C．大池D．日志缓冲区E．共享池F．流池G．所有SGA结构都可以在启动实例后动态重设大小12、下面的哪个或哪些SGA内存结构无法在启动实例后自动重设大小(选择所有正确的答案)？ A．数据库高速缓存区B．Java池C．大池D．日志缓冲区E．共享池F．流池G．所有SGA结构都可以在启动实例后自动重设大小13、当会话更改数据时，会在何处写入更改(选择一个最佳答案)？A．写入缓存中的数据块和重做日志缓冲区B．写入磁盘上的数据块和当前联机重做日志文件C．会话写入数据库高速缓存区，日志写入器写入当前联机重做日志文件D．在提交更改前不执行任何写入14、下面哪个后台进程是可选的(选择一个最佳答案)？A．ARCn，归档进程B．CKPT，检查点进程C．DBWn，数据库写入器D．LGWR，日志写入器E．MMON，管理监视器15、用户发出COMMIT时会发生什么事情(选择一个最佳答案)？A．CKPT进程通知检查点B．DBWn进程将事务的更改缓冲区写入数据文件C．LGWR将日志缓冲区转储到联机重做日志中D．ARCn进程将更改矢量写入归档重做日志16、在一个Oracle实例中，有些进程的数量只能是一个，而有些则可以有多个，下面的哪些进程可以多次出现(选择所有正确的答案)？A．归档进程B．检查点进程C．数据库写入器进程D．日志写入器进程E．会话服务器进程17、一个段可以分布到多个数据文件，这如何实现(选择一个最佳答案)？A．将包含块的区间分配到多个数据文件B．将段分布到多个表空间C．将多个数据文件分配到一个表空间D．使用大于操作系统块大小的oracle块大小18、有关联机重做日志的描述，哪个语句是正确的(选择一个最佳答案)？A．至少必须有一个日志文件组，此组至少有一个成员B．至少必须有一个日志文件组，此组至少有两个成员C．至少必须有两个日志文件组，每组至少有一个成员D．至少必须有两个日志文件组，此组至少有两个成员19、当前重做字节地址(也称为增量检查点位置)在何处记录(选择一个最佳答案)？A．在控制文件中B．在当前联机日志文件组中C．在每个数据文件的文件头处D．在系统全局区域答案:一、不定项选择题1、BCDSQL、PL/SQL和Java是数据库内部实现的语言。

Oracle19cOCP1Z0-082认证考试题库(第4题)

Oracle19cOCP1Z0-082认证考试题库（第4题）考试科目：1Z0-082考试题量：90通过分数：60%考试时间：150min4. You currently have an active transaction in your session and have been granted select access to V$TRANSACTIONIn which three situations will re-executing this query still return a row but with a different XIDindicating a new transaction has started?A.after successfully executing a TRUNCATE statement followed by a DML statementB.after successfully executing a CREATE TABLE AS Select statement followed by a SELECT FOR UPDATE statementC.after successfully executing a CREATE TABLE statement followed by a CREATE INDEX statementE.after successfully executing a DML statement following a failed DML statement参考答案：ABD解析:A:TRUNCATE隐式提交，然后DML接着产生了一个事务B:CREATE隐式提交，然后for update会产生一个事务C:CREATE INDEX，虽说会修改数据字典产生事务，但是存在隐式提交，事务消失D:提交或者回滚，事务结束，再进行DML，产生一个事务E:失败的DML不产生事务F:SELECT不产生事务。

oracle考试试题及答案

试题一一、填空题(每小题4分，共20分)1、数据库管理技术经历了三个阶段2、数据库三级数据结构是__________________________________________________3、Oracle数据库中，SGA由___________________________________________组成4、在Oracle数据库中，完正性约束类型有___________________________________5、PL/SQL中游标操作包括________________________________________________二、正误判断题（每小题2分，共20分）1、数据库中存储的基本对象是数据（）2、数据库系统的核心是DBMS（）3、关系操作的特点是集合操作（）4、关系代数中五种基本运算是并、差、选择、投影、连接（）5、Oracle进程就是服务器进程（）6、oraclet系统中SGA所有用户进程和服务器进程所共享（）7、oracle数据库系统中数据块的大小与操作系统有关（）8、oracle数据库系统中，启动数据库和第一步是启动一个数据库实例（）9、PL/SQL中游标的数据是可以改变的（）10、数据库概念模型主要用于数据库概念结构设计（）三、简答题（每小题7分，共35分）1、何谓数据与程序的逻辑独立性和物理独立性？2、试述关系代数中等值连接与自然连接的区别与了解？3、何谓数据库，数据库设计一般分为哪些阶段？4、简述Oracle逻辑数据库的组成？5、试任举一例说明游标的使用方法？五、设有雇员表emp(empno,ename,age,sal,tel,deptno),其中：empno-----编号，name------姓名，age -------年齡，sal-----工资，tel-----电话 deptno-----部门号。

请按下列要求分别晨SQL*PLUS下编程。

（每小题3分，共15分）1、查询家有电话的职工信息。

OracleOCP认证考试练习真题题库三

OracleOCP认证考试练习真题题库三说明：答案和解析在试卷最后第1部分：单项选择题，共20题，每题只有一个正确答案,多选或少选均不得分。

1.[单选题]Examine the description of the products tableName null? TypePROD_ID not null NUMBERPROD_NAME VARCHAR2(40)COST NUMBER(8,2)RELEASE_DATE DATEWhich query is valid?A)SELECT prod id,AVG(MAX (cost)) FROM products GROUP BY prod_idB)SELECT prod id, MAX (AVG (cost)) FROM products GROUP BY prod_idC)Select prod id, release date, SUM(cost) FROM products GROUP BY prod_idD)SELECT prod id, release date, SUM(cost) FROM products GROUP BY prod id, release_date2.[单选题]Examine the description of the SATES1 tableNAME NULL TYPESALES_ID NOT NULL NUMBERSTORE_ID NOT NULL NUMBERITEMS_ID NUMBERQUANTITY NUMBERSALES_DATE DATESATES2 is a table with the same description as SATES1，Some sales data is contained erroneously in both tables，You must display rows from SATES1 and SATES2 and wish to see the duplicates too,Which set operator generates the required output?A)MINUSB)UNION ALLC)SUBTRACTD)UNIONE)INTERSECT3.[单选题]Examine the description of the EMPLOYEES tableNIS_DATE FORMAT is set to DD-MON-YYWhich query requires explicit data type conversion?A)SELECT join date FROM employees WHERE join date >'10-02-2018';B)SELECT join date || ' '|| salary FROM employeesC)SELECT salary + '120.50' FROM employeesD)SELECT SUBSTR( join_date, 1, 2)-10 FROM employeesE)SELECT join date + '20' FROM employees4.[单选题]Your database instance is started with an SPFILEAPFILE is also availableYou execute this commandALTER SYSTEM SET DB CACHE SIZE=100KWhere is the value changed?A)in the SPFILE and PFTIEB)in the SPFILE, PFILE, and memoryC)only in the SPFILED)in the SPFILE and in memoryE)only in memory5.[单选题]View the Exhibits and examine the structure of the costs and PROMOTIONS tablesYou want to display PROD IDS whose promotion cost is less than the highest cost PROD ID in a promotion time intervalExamine this SQL statement:Select prod_id from costs where promo_id in(select promo_id from promotions where promo_costA)It executes successfully and gives the required result.B)It gives an error because the GROUP BY clause is not validC)It executes successfully but does not give the required resultD)It gives an error because the ALL keyword is not valid6.[单选题]In the spfile of a single instance database, LOCAL LISTENER is set to LISTENER 1.The TNSNAMES ORAfile in SORACLE HOME/network/admin in the database home contains:LISTENER 1 =(ADDRESS=(PROTOCOL= TCP)(HOST =host1 abc. com)(PORT=1521))Which statement is true?A)There are two listeners named LISTENER and LISTENER 1 running simultaneously using port 1521 on the same host as the database instancesB)The definition for LISTENER 1 requires a CONNECT DATAsection to enable dynamic service registrationC)LISTENER 1 must also be defined in the LISTENER. ORAfile to enable dynamic serviceRegistrationD)The LREG process registers services dynamically with the LISTENER_1 listenerE)Dynamic service registration cannot be used for this database instance7.[单选题]Which statement is true about database links?A)Adatabase link created in a database allows a connection from that database's instance to the target database's instance, but not vice versaB)Private database link creation requires the same user to exist in both the local and the remote databasesC)Apublic database link can be used by a user connected to the local database instance to connect to any schema in the remote database instanceD)Apublic database link can be created only by sysE)Adatabase link can be created only between two Oracle databases8.[单选题]The CUSTOMERS table has a CUST_LAST NAME column of data type VARCHAR2The table has two rows whose CUST_LAST NAME values are Anderson and AussonWhich query produces output for CUST_LAST NAME containing der for the first row and Aus for the second?A)SELECT REPLACE(SUBSTR(cust_last_name, -3),'AN', "O') FROM customers;B)SELECT INITCAPREPLACE(TRIM('SON FROM cust_last_namE.,'AN,'O')) FROM customers;C)SELECT REPLACE(TRIM(TRAILING "SON' FROM cust last namE.,'AN, 'O') FROM customersD)SELECT REPLACE(REPLACE(cust last name, ' son', ' '),'An', 'O') FROM customers;9.[单选题]What is true about non-equijoin statement performance?A)The join syntax used makes no difference to performance.B)The BETWEEN condition used with an non-equijoin sometimes performs better than usingThe >=and<= conditionsC)The BETWEEN condition used with an non-equijoin always performs better than when usingThe>=and <= conditionsD)The Oracle join syntax performs better than the sol: 1999 compliantANSI join syntaxE)The Oracle join syntax performs less well than the sol: 1999 compliantANSI join syntax10.[单选题]Examine the description of the PRODUCT INEORMATTON tableName Null7 Type-------------------------------- ------------- -----------------------------------PROD_ID NOT NULL NUMBER(2)PROD_NANE VARCHAR2(10)LIST_PRICE NUMBER(6,2Which query retrieves the number of products with a null list price?A)SELECT COUNT(list price)FROM product information WHERE list price NULLB)SELECT COUNT(list price) FROM product information WHERE list price Is NULL.C)SELECT COUNT(DISTINCT list price )PROM product information WHERE list price Is NULLD)SELECT COUNT(NVL(list price, 0)) FROM product information WHERE list price Is NULL11.[单选题]You execute this commandCREATE BIGFILE TABLESPACE big_tbsDATAFILE ' /u01/oracle/data/big_f1.dbf 'SIZE 20G;Sufficient storage is available in filesystem /u01Which two statements are true about the big_tbs Tablespace? (Choose twoA)AUTOEXTEND is possible for the datafileB)It must be bigger than the largest SMALLFILE tablespaceC)Additional data files may not be addedD)It will be a dictionary-managed tablespace by defaultE)It will always have a 32K block size12.[单选题]Which statement is true about the INTERSECT operator used in compoundQueries?A)Multiple INTERSECT operators are not possible in the same SQL statementB)It processes NULLS in the selected columnsC)INTERSECT is of lower precedence than UNION or UNIONALLD)It ignores NULLS13.[单选题]A database is configured to use automatic undo management with temporary undo enabled An UPDATE is executed on a temporary table. Where is the UNDO stored?A)in the undo tablespaceB)in the SYSAUX tablespaceC)in the SGAD)in the PGAE)in the temporary tablespace14.[单选题]You have been tasked to create a table for a banking application. One of the columns must meet three requirements:Be stored in a format supporting date arithmetic without using conversion functionsStore a loan period of up to10 yearsBe used for calculating interest for the number of days the loan remains unpaid WhichData type should you use?A)INTERVALYEARTOMONTHB)INTERVALDAYTOSECONDC)TIMESTAMPWITHLOCALTIMEZONED)TIMESTAMPE)TIMESTAMPWITHTIMEZONE15.[单选题]In the spfile of a single instance database, LOCALLISTENER is set to LISTENER.1 The TNSNAMES.ORA file in SORACLE HOME/network/admin in the database home containsLISTENER_ 1 =(ADDRESS=(PROTOCOL=TCP)(HOST=)(POPT=1521))Which statement is true?A)Dynamic service registration cannot be used for this database instanceB)The LREG process registers services dynamically with the LISTENER1 listenerC)LISTENER_1 must also be defined in the LISTENER.ORA file to enable dynamic service registrationD)There are two listeners named LISTENER and LISTENER1 running simultaneously using port 1521 on the same host as the database instancesE)The definition for LISTENER 1 requires a CONNECT DATA section to enable dynamic service16.[单选题]You want to write a query that prompts for two column names and the WHERE condition each time it Is executed in a session but only prompts for the table name the first time it is executed. The variables used in your query are never undefined in your sessionWhich query can be used?A)SELECT &&col1&&co2 FROM &table WHERE &&condition=&&condB)SELECT &col1&COI2 FROM &&table WHERE &conditionC)SELECT &col1&CO12 FROM &table WHERE &conditionD)ELECT &&col1&&co FROM &table WHERE &&condition=&condE)SELECT &&col1&&COL2 FROM &table WHER E&&condition17.[单选题]Examine the description of the CUSTOMERS tableName Null? Type--------------------------------- ----------------- ----------------------CUST_ID NOT NULL VARCHAR2(6)FIRST_NAME VARCHAR2(50)LAST_NAME NOT NULL VARCHAR2(50)ADDRESS VARCHAR2(50)CITY VARCHAR2(25)You want to display details of all customers who reside in cities starting with the letter D followed by at least two charactersWhich query can be used?A)SELECT * FROM customers WHERE city LIKE ‘D_%’;B)SELECT * FROM customers WHERE city = ‘%D_’;C)SELECT * FROM customers WHERE city LIKE ‘D_’;D)SELECT * FROM customers WHERE city = ‘D_%’;18.[单选题]You want to use table compression suitable for OLTP that will:1）Compress rows for all DML statements on that table2）Minimize the overheads associated with compressionWhich compression option is best suited for this?A)COLUMN STORE COMPRESS FOR QUERY LOWB)ROW STORE COMPRESS BASICC)COLUMN STORE COMPRESS FOR ARCHIVE LOWD)COLUMN STORE COMPRESS FOR ARCHIVE HIGHE)ROW STORE COMPRESS ADVANCED19.[单选题]Your data base instance is started with a PFILE.Examine these parameters:NAME TYPE VALUE----------------------------------- -------------------- -----------------------Memory_max_target big integer 0Memory_target big integer 0Sga_max_size big integer 2GSga_target big integer 2GYou want to increase the size of the buffer cache. Free memory is available to increase the Size of the buffer cache. You execute the command: SQL>ALTER SYSTEM SETDB_CACHE_SIZE=1024M;What is the outcome?A)The value is changed only in the PFILE and takes effect at the next instance startupB)The value is changed for the current instance and in the PFILEC)It fails because the SCOPE clause is missingD)Change is applied to the current instance, but does not persist after instance restart20.[单选题]Examine the description of the CUSTOMERS table:Name Null Type------------------------------------------------------------------------------------------CUST_ID NOT NULL NUMBERCUST_FIRST_NAME NOT NULL VARCHAR2(20)CUST_LAST_NAME NOT NULL VARCHAR2(30)CUST_INCOME_LEVEL VARCHAR2(30)CUST_CREDIT_LIMIT NUMBERFor customers whose income level has a value, you want to display the first name and due amount as 5% of their credit limit. Customers whose due amount is null should not be displayed.Which query should be used?A)SELECT cust_first_name, cust_credit_limit*.05ASDUE_AMOUNT FROM customers WHERE cust_incoms_level IS NOT NULL AND due_amount IS NOT NULL;B)SELECT cust_first_name,cust_cred it_limit*.05ASDUE_AMOUNT FROM customers WHERE cust_income_level!=NULL AND cust_cred it_level!=NULL;C)SELECT cust_first_name,cust_cred it_limit*.05ASDUE_AMOUN TFROM customers WHERE cust_income_level<>NULL AND due_amount<>NULL;D)SELECT cust_first_name,cust_cred it_limit*.05ASDUE_AMOUNT FROM customers WHERE cust_income_level!=NULL AND due_amount !=NULL;E)SELECT cust_first_name,cust_credit_limit*.05AS DUE_AMOUNT FROM customers WHERE cust_income_level ISNOT NULL AND cust_credit_limit IS NOT NULL;第2部分：多项选择题，共73题，每题至少两个正确答案,多选或少选均不得分。

oracle考试题

oracle考试题Oracle是一种关系数据库管理系统，广泛应用于企业和机构的数据存储和管理。

如果你想在这个领域取得职业成功，学习和掌握Oracle技术是至关重要的。

下面就是一些Oracle考试题，希望能帮助你更好地准备Oracle认证考试。

1. 什么是Oracle数据库？简要描述Oracle数据库的特点和优势。

Oracle数据库是一种关系数据库管理系统，由Oracle Corporation开发。

它具有高性能、高可靠性、可伸缩性和安全性等特点。

Oracle数据库支持SQL语言，可用于数据存储、数据管理和数据分析等领域。

2. 什么是Oracle数据库实例？它和数据库之间的关系是什么？Oracle数据库实例是Oracle数据库在内存和硬盘上的运行实例。

它包括了数据库缓冲区、PGA、SGA和后台进程等组件。

数据库是物理存储数据的地方，而数据库实例则是在内存中运行的程序。

3. 如何创建一个新的Oracle用户？要创建一个新的Oracle用户，可以使用CREATE USER语句。

例如：CREATE USER username IDENTIFIED BY password;4. 如何给Oracle用户授予SELECT权限？要给Oracle用户授予SELECT权限，可以使用GRANT SELECT命令。

例如：GRANT SELECT ON table_name TO username;5. 什么是Oracle的PL/SQL？PL/SQL是Oracle数据库的过程式编程语言。

它结合了SQL的强大和易用性以及编程语言的结构化编程特点。

PL/SQL提供了条件控制、循环、异常处理等特性，可用于编写存储过程、触发器、函数等数据库对象。

6. 如何在Oracle数据库中创建一个存储过程？要在Oracle数据库中创建一个存储过程，可以使用CREATE PROCEDURE语句。

例如：CREATE PROCEDURE procedure_nameASBEGIN-- 存储过程内容END;7. 什么是Oracle的视图？Oracle的视图是一种虚拟表，它基于一个或多个表的查询结果。

1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。

TThe safer , easier way to help you pass any IT exams.Exam : 1Z0-053Title :Version : DemoOracle Database 11g: Administration II1.You are not sure if Flashback Database is enabled. What database column and view can you query to see if the flashback logs are being created in the flash recovery area?A. Query the initialization parameter FLASHBACK_DATABASE in VJINSTANCE.B. Query the FLASHBACK_ENABLED column in VJDATABASE.C. Query the FLASHBACK column in VJDATABASE..D. Query the FLASHBACK_ON column in VJDATABASE.Answer: D2.What dynamic performance view can you query to list the supported platforms for transportable tablespaces?A. V$TRANSPORTABLE_PLATFORMB. V$TRANSPORTABLE_TABLESPACEC. V$SUPPORTED_PLATFORMSD. V$DATABASE PLATFORMAnswer: A3.You issue the following command to enable block change tracking:SQL> ALTER DATABASE ENABLE BLOCK CHANGE TRACKING;However, you receive an error message, and the tracking file is not created. What is a possible reason for the error message?A. The parameter DB_RECOVERY_FILE_DEST is not set.B. You must use ALTER SYSTEM instead. )C. Your database must be in the MOUNT state to create the block change tracking file.D. The parameter DB_CREATE_FILE_DEST is not set.Answer: D4.You are restoring and recovering a database to a new host. What data dictionary view will you use to identify the names of the datafiles on the new database?A. DBA_DATAFILEB. VJTABLESPACEC. VJDATAFILED. V$DATAFILE_COPYE. VJBACKUP DATAFILEAnswer: C5.Your flash recovery area becomes full when you are running an RMAN backup (and has not yet reached the value specified by DB_RECOVERY_FILE_DEST). It is the destination for archived redo log files and backups. What will happen to the database? (Choose the best answer.)A. If you have the flash recovery area set to autoextend, the backup will continue with no problem.B. The database will hang until you add more space to the flash recovery area.C. RMAN will delete obsolete backups from the flash recovery area to accommodate new backups.D. The RMAN backup will hang until you add more space to the flash recovery area.Answer: C6.You want to ensure the recoverability of the recovery catalog itself. Which of the following steps shouldyou implement to maximize recoverability?(Choose all that apply.)A. Configure the database for ARCHIVELOG mode.B. Use BACKUP DATABASE PLUS ARCHIVELOG when you back up the recovery catalog.C. Put the recovery catalog in the target database, and include a backup of the recovery catalog with every backup.D. Use BACKUP DATABASE PLUS ARCHIVELOG when you back up the recovery catalog.E. Back up the recovery catalog to disk and tape after each target database backup (in other words, two separate media types).Answer: A, B, D, E7.For which of the following scenarios would you use user-managed incomplete recovery? (Choose all that apply.)A. You have lost a datafile and one or more of the archived redo log files are missing.B. Several widespread logical errors have occurred in the database in the last day.C. You want to create a test database as of a point in time in the past.D. You have lost a datafile and you have all archived redo log files available.E. You have lost one of the copies of the control file.Answer: A, B, C8.You want to leverage resource consumer groups when using the Scheduler. Which Scheduler object uses resource plans directly?A. A lightweight jobB. Ajob classC. Ajob chainD. A programAnswer: B9.You need to recover the database after a disk is lost but you are missing two archived redo log files. You need to run the RMAN RECOVER DATABASE UNTIL command. In what database state can you perform this recovery?A. OPENB. MOUNTC. RESTRICTEDD. NOMOUNTAnswer: B10.Which of the following methods can you use to enable duplexed RMAN backups? (Choose two answers.)A. Use the initialization parameter RMAN_BACKUP_COPIES.B. Use the BACKUP COPIES option in the RMAN CONFIGURE command.C. Use the BACKUP COPIES option in the ALLOCATE CHANNEL command.D. Use the COPIES option in the RMAN BACKUP command.Answer: B, DThe safer , easier way to help you pass any IT exams.11.Which of the following tablespaces can be recovered while the database is online? (Choose all that apply.)A. USERSB. UNDOC. SYSAUXD. SYSTEM 3E. EXAMPLEAnswer: A, C, E12.Which of the following methods can you use to run a job chain? (Choose all that apply).A. Create and schedule a job with CREATEJOB of type CHAIN.B. Create and schedule a chain using the CREATE_CHAIN procedure.C. Create and schedule a chain using the CREATE_JOB_CHAIN procedure.D. Use the START_CHAIN procedure.E. Use the RUN_CHAIN procedure.Answer: A, E13.Which of the following commands successfully allocates a channel? (Choose all that apply.)A. ALLOCATE CHANNEL T2 TYPE TAPE;B. ALLOCATE CHANNEL T2 TYPE SBT;C. ALLOCATE CHANNEL DB2 TYPE DISK PARMS='SBT_LIBRARY=mmv.so';D. ALLOCATE CHANNEL T1 TYPE DISK;Answer: B, D14.To enable remote connections to the database using the password file, what values should REMOTE_LOGIN_PASSWORDFILE have? (Choose all that apply.)A. NONEB. SHAREDC. EXCLUSIVED. REMOTEAnswer: B, C15.You want to use Flashback Transaction Query to identify the SQL needed to reverse a transaction in the HR. EMPLOYEES table. Where can you find out the value of the transaction ID column XID to use when you query FLASHBACK_TRANSACTION_QUERY? (Choose the best answer.)A. From the VERSIONS_XID column of HR. EMPLOYEESB. From LogMinerC. From the XID column of HR.EMPLOYEESD. From the VERSIONS XID column of FLASHBACK VERSION QUERYAnswer: A16.Setting which of the following initialization parameters enables Automatic Memory Management?A. MEMORYJARGETB. MEMORY_MAX_TARGETC. SGATARGETD. PGA AGGREGATE TARGETAnswer: A17.You configure AUTOBACKUP to ON in an RMAN session. When will RMAN back up the control file? (Choose all that apply.)A. When you run an RMAN BACKUP commandB. When you run an RMAN ALLOCATE commandC. When you add or drop a table in the SYSTEM tablespaceD. When you change the physical structure of the database, such as dropping a tablespaceE. When you specify the INCLUDING CONTROLFILE clauseAnswer: A18.Which of the following two parameters must you specify to use a flash recovery area for all of your backups? (Choose two answers.)A. DB_RECOVERY_FILE_DEST_SIZEB. DB_RECYCLE_CACHE_SIZEC. DB_RECOVERY_FILE_DESTD. DB RECOVERY FILE SIZEAnswer: A, C19.Your database is not using an RMAN recovery catalog. Which of the following commands are not available to you in this scenario? (Choose all that apply.)A. CONFIGURE RETENTION . . .B. BACKUP . . . KEEP FOREVERC. DELETE . . . EXPIREDD. REPORT SCHEMA ... ATAnswer: B, D20.Which of the following dynamic performance views can you use to show the total CPU time for a consumer group? (Choose the best answer.)A. V$RSRC_CONSUMER_GROUPB. V$SESSTATC. V$SYSSTATD. V$RSRC CONSUMER GRPAnswer: A21.You want to designate one of the Linux system administrators as the recovery catalog owner. Which role must you grant to her Oracle user account?A. RECOVERY_CATALOGB. CATALOGjDWNERC. RECOVERY_CATALOG_OWNERD. SYSDBAAnswer: C22.Fill in the blanks to make the following statement true: You use the command so that the view contains the specified name associated with the session in the column.A. ALTER SESSION ENABLE RESUMABLE TIMEOUT . . . NAME 'statement description',DBA_RESUMABLE_STATEMENT, NAMEB. ALTER SESSION ENABLE RESUMABLE TIMEOUT . . . NAME 'statement description', DBA_RESUMABLE, NAMEC. ALTER SESSION ENABLE RESUMABLE TIMEOUT . . . NAME 'statement description', DBA_RESUMABLE, SQL_NAMED. ALTER USER ENABLE RESUMABLE TIMEOUT . . . NAME 'statement description', DBA_RESUMABLE, NAMEAnswer: B23.You execute this command:CONFIGURE RETENTION POLICY CLEAR;What is the retention policy set to after running the command?A. Retention is set to the default of REDUNDANCY 1.B. No retention policy will be defined.C. Retention will be set according to the value of the initialization parameter CONTROL_FILE_RECORD_KEEP_TIME.D. Retention is set to RECOVERY WINDOW OF 4 DAYS.E. Retention is set to the default of REDUNDANCY 2.Answer: A24.You lose an entire redo log group that is in the CURRENT state. What happens to the database instance and how can you recover from the lost redo log group?A. The instance will hang until you clear the redo log group.B. The instance will shut down or crash and you can perform complete recovery using the archived redo log files.C. The instance will skip to the next redo log group.D. The instance will shut down or crash and you will have to perform cancel-based recovery.Answer: D25.Identify the correct relationships between resource consumer groups, resource plan directives, and resource plans. (Choose all that apply.)A. Only one resource plan can be active at a time.B. A user can be a member of only one resource consumer group.C. One resource plan can have multiple plan directives.D. A user can be assigned to more than one resource plan.Answer: A, C26.You are using the RMAN Data Recovery Advisor functionality. When an error occurs, which of the following commands can you use to monitor and maintain failures?(Choose all that apply.)A. IDENTIFY FAILUREB. REPAIR FAILUREC. CHANGE FAILURED. ADVISE FAILUREE. RESET FAILUREAnswer: B, C, D27.You are using TISPITR to recover one or more tablespaces to a previous time in point or back to a specific SCN. What included in the recovery set? (Choose all that apply.)A. A copy of the SYSTEM tablespaceB. A copy of the SYSAUX tablespaceC. A copy of the UNDO tablespaceD. A temporary tablespace for exported database objects from the auxiliary instance.E. The datafiles from the tablespaces to be recoveredAnswer: E28.You are using a recovery catalog and you lose all copies of your control file. Which of the following sets commands will you use to restore the control file before you recover the database?A. SET DBID 188390190; RESTORE CONTROLFILE;B. SET DBID 188390190; RESTORE CONTROLFILE FROM AUTOBACKUP;C. RESTORE CONTROLFILE FROM AUTOBACKUP;D. RESTORE CONTROLFILE;Answer: D29.Identify the advantage of recovery an image copy of a datafile during RMAN backup. (Choose all that apply.)A. During media recovery, fewer incremental updates need to be applied to the last image copy.B. The overhead for the block change tracking file is minimal.C. You do not need to perform additional image copy backups.D. You no longer need to duplex your backups.E. The time required to perform the backup and incremental updates to the image copy is reduced. Answer: A, C30.After you unregister a database from a recovery catalog, what happens to the backup metadata in the recovery catalog?A. You must migrate the backup metadata from the recovery catalog to the control file before unregistering the database from the recovery catalog.B. The backup metadata is maintained in the control file for a number of days specified by the initialization parameter CONTROL_FILE_RECORD_KEEP_TIME and immediately removed from the recovery catalog.C. The backup metadata is retained in the control file, and the database structure metadata remains in the recovery catalog.D. The backup metadata is maintained in the control file and in the recovery catalog for a number of daysspecified by the initialization parameter CONTROL FILE RECORD KEEP TIME. Answer: B。