Oracle 大数据库考试重点

合集下载

orcl复习重点部分

第一章1.简要说明例程与数据库之间的联系和区别？ORACLE数据库是安装在磁盘上的ORACLE数据库文件和相关的数据库管理系统的集合。

磁盘上比较重要的文件包括数据文件，控制文件，重做日志文件，初始化参数文件，口令文件，归档重做日志文件。

例程是由在内从中的一组后台进程和内存结构组成。

2.说明数据库、表空间和数据文件之间的关系？ORACLE数据库的逻辑结构和物理结构的对应关系，一个ORACLE数据库可以拥有多个表空间，每个表空间有多个段组成，每个段由若干个区间组成，每个区间包含多个ORACLE 数据块，每个ORACLE数据块包含多个OS屋里磁盘快。

表空间有多个物理文件支持，具体存储表空间中的个对象。

3.比较表和视图二者间的异同？视图中没有数据，仅仅是一条SQL语句，查询语句检索出来的数据以表的形式表示；视图的定义存储在数据字典中，视图的查询基于表；视图没有直接的相关物理数据，不能像表那样被索引。

第二章1.写出以scott用户登录到数据库orcl，然后查看该用户可以访问的表，视图的相关命令？CONN scott/tiger@orclSELECT * FROM tab;2.设置查询结果的显示格式：每页24行，每行100个字符，SAL列的显示格式为￥99.990.0. SET PAGESIZE 24SET LINESIZE 100COLUMN sal FORMAT $99,990.00第三章1.写出查看某个用户说有用的角色的SQL语句。

CONN stu01/stu01pwd@orclCOLUMN USERNAME FORMAT A10SELECT username,granted_role FROM user_role_privs;2.写出查询当前用户将哪些表的访问权限授予给其他用户的SQL语句。

CONN scott/tiger@orclCOLUMN GRANTEE FORMAT A10SELECT grantee,table_name,grantor,privilege,grantableFROM user_tab_privs_made;3.以系统管理员的身份登录，在SQL*plus中查询各用户获得的系统特权和角色。

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数据库复习大纲

Oracle 数据库复习大纲一、Oracle 数据库概述（第1章、第2章）① Oracle10g 中的g 的含义二、创建数据库 ① Oracle 支持数据库的类型有哪三种② Oracle 支持的数据存储方式有哪三种③ 利用Oracle 中哪个应用程序（数据库配置助手）可以创建数据库④ 创建数据库时缺省的5个表空间⑤ 文本初始化参数文件与服务器初始化参数文件的格式差别 ⑥ Oracle 数据库的启动和关闭的3个步骤三、Oroacle 企业管理器① OEM 通过采用何种方式（通过采用何种方式（Web Web 应用）实现对Oracle 运行环境的完全管理 ② Oracle 提供了三种不同类型的OEM OEM（数据库控制（数据库控制OEM OEM、网格控制、网格控制OEM 和应用服务器控制OEM OEM））③在默认情况下，只有SYS ，SYSTEM 和SYSMAN 三个数据库用户才能登录和使用OEM 控制台。

其中SYSMAN 用户是OEM 控制台的超级用户，是在安装OEM 的过程中创建的，用于执行系统配置、全局配置等任务。

④设置”首选身份证明”的目的是用户通过OEM 控制台访问该目标时，不需要进行显式登录。

四、物理存储结构① Oracle 数据库系统结构由哪两部分组成。

② 物理存储结构是② 物理存储结构是Oracle Oracle 数据库外部数据在操作系统中如何组织和管理数据，与具体的操作系统有关；逻辑存储结构是Oracle 数据库内部数据的组织和管理方式，与操作系统无关。

③数据文件、控制文件和重做日志文件是物理存储结构中最重要的三种文件。

数据文件用于存储数据库中的所有数据；控制文件用于记录和描述数据库的物理存储结构信息；重做日志文件用于记录外部程序（用户）对数据库的改变操作。

④数据文件与表空间的关系：一个表空间可以包含多个数据文件；一个数据文件只能从属于一个表空间。

⑤Oracle 数据库能够把已经写满了的重做日志文件保存到指定的一个或多个位置，被保存的重做日志文件的集合称为归档重做日志文件，这个过程称为归档。

oracle数据库期末考试复习题

一名词解释：（5*2 共10分）1. 角色：一组相关权限的集合称之为角色。

2. PL/SQL 语言：是Oracle 数据库专⽤的⽤种⽤级程序设计语⽤，是对标准SQL 语⽤进⽤了过程化的扩展。

3. 游标：用来存储多条查询数据的一种数据结构（结果集或缓冲区），它有一个指针，用来从上往下移动，是指向该缓冲区的句柄或指针，从而达到遍历每条记录的作用。

P2324. 表空间（tablespace）：Oracle数据库在逻辑上可以划分为一系列的逻辑区域，每个逻辑区域成为一个表空间，表空间是Oracle数据库中的最大逻辑存储结构，有一系列的段组成。

P495. 段（segment）：段是由一个或多个连续或不连续的区组成的逻辑存储单元。

表空间的组成单位，代表特定数据类型的数据存储结构。

6. 区间（extent）：区是由一系列连续的数据块组成的逻辑存储单元，是存储空间分配与回收的最小单元。

7. 数据块（block）：Oracle数据块是数据库中最小的逻辑存储单元，也是数据库的执行输入/输出操作的最小单位，由一个或多个操作系统块构成。

8. 索引：是一种可选的与表相关的数据库对象，用于提高数据的查询效率。

P989. Oracle 实例：处于用户与物理数据库之间的一个中间层软件称之为实例，由一系列内存结构和后台进程组成。

通常一个实例对应一个数据库。

P65（第五章）10. 序列：是用于产生唯一序号的数据库对象，可以为多个数据库用户依次生成不重复的连续整数，通常使用它自动生成表中的主键，并且不占用实际存储空间。

P115（第七章）11. 同义词：同义词是数据库中表、索引、视图或其他模式对象的⽤个别名。

二单选（10*2 共20分）这些只是范围，没有明确的题，所以基本了解就可以答选择1.在Oracle 数据库的存储结构包括物理存储结构和逻辑存储结构。

2.SGA 中包括数据高速缓冲区、日志缓冲区、共享池、大型池、Java 池、流池等。

Oracle数据库重点

考试题型：一、单选（2*15）二、填空（1*10）三、设计（4*10）四、问答（5*2）四、问答题：1．简述Oracle物理存储结构的主要存储部分以及各部分的主要功能。

●数据文件——用于存储数据库中的所有数据；●控制文件——用于记录和描述数据库的物理存储结构信息；●重做日志文件——用于记录外部程序（用户）对数据库的修改操作；●初始化参数文件——用于设置数据库启动时的参数初始值；●跟踪文件——用于记录用户进程、数据库后台进程的运行情况；●归档文件——用于保存已经写满的重做日志文件●口令文件——用于保存具有SYSDBA,SYSOPER权限的用户名和SYS用户口令。

2．简述Oracle操作模式有哪两种，它们有什么关系？在Oracle数据库中，数据库的操作模式分为专用服务器（DELICATED SERVER）模式和多线程服务器(MULTITHREADED SERVER)模式两种。

其中，在专用服务器模式中，用户进程与服务器进程之间是一对一的关系，即一个服务器进程只为一个用户进程服务；而在多线程服务器模式中，用户进程与服务器进程之间是多对一的关系，即一个服务器进程可以为多个用户进程提供服务。

3．说明数据库模式与用户之间的区别？数据库模式与用户之间的区别在于：用户是数据库的使用者和管理者，用户具有帐户状态、访问权限和操作权限等属性。

模式是一系列逻辑数据结构或对象的集合，是数据库中对象的组织和管理单位。

4．说明数据库的启动过程。

数据库启动分为三个步骤：创建并启动数据库实例、装载数据库和打开数据库。

数据库启动时首先根据初始化参数文件创建并启动实例，然后根据控制文件装载数据库的数据文件和重做日志文件，最后打开数据文件和重做日志文件，从而启动数据库。

三、设计题：1.P111（1）为USERS表空间添加一个数据文件，文件名为userdata03.dbf,大小为50MB。

alter tablespace usersadd datafile ‘d:\Oracle\userdata03.dbf’size 50M;(2)为EXAMPLE表空间添加一个数据文件，文件名为example02.dbf,大小为20MB。

大型数据库期末复习题纲

大型数据库期末复习题纲1、教材2.2节P19-20页，主要掌握在命令行执行sqlplus命令登录数据库的语法格式，掌握以SYSDBA身份和以普通用户身份登录的方法；掌握SQLPLUS命令conn、edit、DESC、QUIT等命令的功能及用法。

2、教材3.1节P27页，掌握ORACLE数据库的存储结构由哪些部分构成，各自的作用是什么？掌握ORACLE实例的结构构成，掌握oracle数据库的内存结构、后台进程和存储结构间的关系（图3-1）并加以说明。

3、教材P3.2节P27-28页，掌握ORACLE数据库的物理结构构成，并说明物理存储结构与逻辑存储结构的基本关系。

P29-30页，掌握ORACLE数据库的逻辑结构构成，并掌握数据块、区、段表空间的作用。

4、教材P3.3节P30-32页，掌握ORACLE数据库的内存结构的具体构成，各个部分的主要功能，都包含哪些部分等。

其中SGA区由哪些部分构成，PGA区由哪些部分构成；共享池的功能和构成？数据缓冲区和日志缓冲区有什么作用?5、教材P3.4节P33-35页，掌握ORACLE的进程有哪些类型，后台进程主要有哪些？后台进程中掌握DBWR、LGWR、CKPT、ARCH的功能。

6、教材P5.2节P48-49页，掌握表空间的类型、管理方式，区的分配方式，段的管理方式；掌握创建永久表空间的语句，并会创建具有一个或多个数据文件的永久表空间。

掌握与表空间相关的数据字典dba_tablespaces和dba_data_files.（参看实验2的相关内容。

）7、教材5.4 （P56），掌握控制文件的作用和主要内容；掌握数据字典v$controlfile的用法。

掌握控制文件是在数据库启动的第几个阶段进行读取的？8、教材5.5（P59-60），掌握重做日志文件（组）的组成和工作过程。

掌握数据字典V$log和v$logfile的功能。

9、教材5.6（P63），掌握数据库运行在何模式下，数据库才会对重做日志进行归档操作。

Oracle数据库考试重点

1、Which two statements about online redo log members in a group is trueB、All members in a group are the same sizeC、The members should be on different disk drivers2、Which commanddoes a DBA user to list the current status of archivingA、ARCHIVE LOGLIST3、How many control files are required to create a databaseA、one4、Complete the following sentence: The recommended configuration fro control files isC Two control files on two disks5、When you create a control file, the database has to be:C Open6、Which data dictionary view shows that the database is in ARCHIVELOG mode C、V$DATABASE7、What is the biggest advantage of having the control files on different disks B Guards against failure8、Which file is used to record all changes made to the database and is used only when performing an instance recovery A，Archive log file9、How many ARCn processes can be associated with an instance C ten10、Whichtwo parameters cannot be used together to specify the archive destination A．LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST 第七章1、A collection of segments is a (an): C、TABLESPACE2 When will the rollback information applied in the event of a database crash C immediately after re-opening the database before the recovery 3、The data dictionary tables and views are stored inB、SYSTEM tablespace4 PCTFREE and PCTUSED together should not exceed:A 1005 Which of the following three portions of a data block are collectively called as OverheadC table directory, row directory and data block header6 When the database is open , which of the following tablespace must be online A，SYSTEM7 Sorts can be managedefficiently by assigning ___________ tablespace to sort operations B TEMPORARY8 The sort segment of temporary tablespace is created:A at the time of the first sort operation9 Which of the following segments is self administeredB ROLLBACK10 What is the default temporary tablespace, if no temporary tablespace is defined D SYSTEM11 Rollback segments are used for: D ，all of the above12 Rollback segment stores:A old values of the data changed by each transaction 第八章1 An Oracle instance is : D All of the above2 The SGA consists of the following items:D All of the above3 The area that stores the blocks recently used by SQL statements is called: B Buffer Cache4 Which of the following is not a background server processes in Oracle B LGWR5 Which of the following is valid background server processes in OracleD All of the above6 The process that writes the modified blocks to the data files is: A DBWR7 The process that records information about the changes made by all transactions that commit is : D None of the above8 Oracle does no consider a transaction committed until:B The LGWR successfully writes the changes to redo9 The process that performs internal operations like tablespace coalescing is : B SMON10 The process that managesthe connectivity of user sessions is: A PMON 第十章1 The Database must be in this mode for in instance to be started: C NOMOUNT2 When Oracle startups up , what happens if a datafile or redo file no available or corrupted due to OS ProblemsB Oracle returns a warning message and does not open the database3 The RESTRICTED SESSION system privilege should be given toB DBA, who perform structural maintenance exports and imports the data 4 When Starting up a database, If one or more of the files specified in the CONTROL_FILESparameter does not exist ,or cannot be openedA Oracle returns a warning message and does not mount the database5 Bob tried to shutdown normal, Oracle said it was unavailable, and when he tried to startup, oracle said that it was already started. What is the best mode that bob can use to force a shutdown on the server B ABORT6 Tom issued a command to startup the database. What modes does the Instance and Database pass through to finally have the database openB NOMOUNT, MOUNT, OPEN7 Diane is a new DBA and issued a shutdown command while her server is being used. After a while she figures that oracle is waiting for all the users to sign off. What shutdown mode did she use: A NORMAL8 Which script file creates commonly used data dictionary views B9 In order to perform a full media recovery, the Database must be :C Mounted and Opened using ARCHIVELOG option10 When is the parameter file read during startupC During instance startup第十二章1 The default tablespace clause in the create user command sets the location for:A Database Objects created by the user2 What does sessions_per_user in a resource limit setB No. of Sessions Per User3 What value sets the no activity time before a user is disconnected A IDLE_TIME4 Which of the following statements is incorrect when used with ALTERUSER usera A 、ADD QUOTA 5M5 What view consists information about the resource usage parameters for each profileB、DBA_PROFILES6 Which of the following is not a system privilegeA SELECT7 What keyword during the create user command,limits the space used by users objects in the database D QUOTA8 W hat operations are limited by the Quota on a tablespace D All of the above9 P rofiles cannot be used to restrict which of the following D timespent reading blocks10 Which of the following is not a roleD CREATE SESSION 第十三章1 What option of Exporting allows quicker data extractions D、Direct =y2 How are exports useful C Can be used to recover dropped tablesdue a user error3 What are the 3 levels of ExportsB FULL, USER, TABLE4 The following methods can be used to run exports:D all of the above5 Which of the following are valid parameters for an Export utility D All of the above6 What Incremental Parameters can be used with exportsD all of the above7 What can you do to reduce the burden on a rollback segment during the import of a large table C、COMMIT= Y8 What is an Incremental ExportB Export of rows that have changed since last export9 An Incremental Export is a good strategy for:C all of the aboveD none of the above10 What is a Cumulative Export A Export of the objects that have changed since last export11 What option of export utility allows for faster extraction of data B DIRECT = Y简答题：1, 为EXAMPL表空间添加一个数据文件，文件名为,大小为20MB.ALTER TABLESPACE EXAMPLE ADD DATAFILE‘D: ' SIZE 20M';2, 修改USERS表空间中的为自动扩展方式，每次扩展5MB最大为100MBALTER DATABASE DATAFILE‘D: ' AUTOEXTEND ON NEXT 5M MAXSIZE 100M;3, 将表空间USER卿的数据文件更名为,将表空间EXAMPLES的数据文件更名为. SHUTDOWN IMMEDIATE;在操作系统中重命名、分别为、STARTUP MOUNT;ALTER DATABASE RENAME FILE‘D: ', ‘D: ' TO‘D: ', ‘D: ';ALTER DATABASE OPEN;4, 为数据库添加一个重做日志文件组,组内包含两个成员文件,分别为和, 大小分别为5MBALTER DATABASE ADD LOGFILE GROUP 4( ‘D: ', 'D: ')SIZE 5M;5, 为新建的重做日志文件组添加一个成员文件,名称为。

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

1、Which two statements about online redo log members in a group is true?B、All members in a group are the same sizeC、The members should be on different disk drivers2、Which command does a DBA user to list the current status of archiving?A、ARCHIVE LOGLIST3、How many control files are required to create a database?A、one4、Complete the following sentence: The recommended configuration fro control files is?C Two control files on two disks5、When you create a control file, the database has to be:C Open6、Which data dictionary view shows that the database is in ARCHIVELOG mode? C、V$DATABASE7、What is the biggest advantage of having the control files on different disks? B Guards against failure8、Which file is used to record all changes made to the database and is used only when performing an instance recovery? A，Archive log file9、How many ARCn processes can be associated with an instance? C ten10、Whichtwo parameters cannot be used together to specify the archive destination?A．LOG_ARCHIVE_DEST and LOG_ARCHIVE_DUPLEX_DEST第七章1、A collection of segments is a (an): C、TABLESPACE2 When will the rollback information applied in the event of a database crash? C immediately after re-opening the database before the recovery3、The data dictionary tables and views are stored inB、SYSTEM tablespace4 PCTFREE and PCTUSED together should not exceed:A 1005 Which of the following three portions of a data block are collectively called as Overhead?C table directory, row directory and data block header6 When the database is open , which of the following tablespace must be online? A，SYSTEM7 Sorts can be managed efficiently by assigning _____ tablespace to sort operations B TEMPORARY8 The sort segment of temporary tablespace is created:A at the time of the first sort operation9 Which of the following segments is self administered?B ROLLBACK10 What is the default temporary tablespace, if no temporary tablespace is defined? D SYSTEM11 Rollback segments are used for: D ，all of the above12 Rollback segment stores:A old values of the data changed by each transaction第八章1 An Oracle instance is : D All of the above2 The SGA consists of the following items:D All of the above3 The area that stores the blocks recently used by SQL statements is called: B Buffer Cache4 Which of the following is not a background server processes in Oracle?B LGWR5 Which of the following is valid background server processes in Oracle?D All of the above6 The process that writes the modified blocks to the data files is: A DBWR7 The process that records information about the changes made by all transactions that commit is :D None of the above8 Oracle does no consider a transaction committed until:B The LGWR successfully writes the changes to redo9 The process that performs internal operations like tablespace coalescing is :B SMON10 The process that manages the connectivity of user sessions is: A PMON第十章1 The Database must be in this mode for in instance to be started: C NOMOUNT2 When Oracle startups up , what happens if a datafile or redo file no available or corrupted due to OS Problems?B Oracle returns a warning message and does not open the database3 The RESTRICTED SESSION system privilege should be given toB DBA, who perform structural maintenance exports and imports the data4 When Starting up a database, If one or more of the files specified in the CONTROL_FILES parameter does not exist ,or cannot be opened?A Oracle returns a warning message and does not mount the database5 Bob tried to shutdown normal, Oracle said it was unavailable, and when he tried to startup, oracle said that it was already started. What is the best mode that bob can use to force a shutdown on the server? B ABORT6 Tom issued a command to startup the database. What modes does the Instance and Database pass through to finally have the database open?B NOMOUNT, MOUNT, OPEN7 Diane is a new DBA and issued a shutdown command while her server is being used. After a while she figures that oracle is waiting for all the users to sign off. What shutdown mode did she use: A NORMAL8 Which script file creates commonly used data dictionary views? B catalog.sql9 In order to perform a full media recovery, the Database must be :C Mounted and Opened using ARCHIVELOG option10 When is the parameter file read during startup?C During instance startup第十二章1 The default tablespace clause in the create user command sets the location for:A Database Objects created by the user2 What does sessions_per_user in a resource limit set?B No. of Sessions Per User3 What value sets the no activity time before a user is disconnected?A IDLE_TIME4 Which of the following statements is incorrect when used with ALTER USER usera? A 、ADD QUOTA 5M5 What view consists information about the resource usage parameters for each profile? B、DBA_PROFILES6 Which of the following is not a system privilege?A SELECT7 What keyword during the create user command, limits the space used by users objects in the database? D QUOTA8 What operations are limited by the Quota on a tablespace? D All of the above9 Profiles cannot be used to restrict which of the following? D time spent reading blocks10 Which of the following is not a role?D CREATE SESSION第十三章1 What option of Exporting allows quicker data extractions? D、Direct = y2 How are exports useful? C Can be used to recover dropped tables due a user error3 What are the 3 levels of Exports?B FULL, USER, TABLE4 The following methods can be used to run exports:D all of the above5 Which of the following are valid parameters for an Export utility? D All of the above6 What Incremental Parameters can be used with exports?D all of the above7 What can you do to reduce the burden on a rollback segment during the import of a large table?C、COMMIT= Y8 What is an Incremental Export?B Export of rows that have changed since last export9 An Incremental Export is a good strategy for:C all of the aboveD none of the above10 What is a Cumulative Export? A Export of the objects that have changed since last export11 What option of export utility allows for faster extraction of data? B DIRECT = Y简答题：1，为EXAMPLE表空间添加一个数据文件，文件名为example02.dbf,大小为20MB.ALTER TABLESPACE EXAMPLE ADD DATAFILE‘D:\ORACLE\ORADATA\ORCL\example02.dbf’ SIZE 20M’;2，修改USERS表空间中的userdata03.dbf为自动扩展方式，每次扩展5MB，最大为100MB。