When Oracle starts, it reads the initialization parameter file to determine the values of initialization parameters. After this, it allocates an SGA and creates background processes.
Posted Date:- 2021-10-05 05:46:25
Oracle Real Application Clusters 10g (RAC) enables a single database to run across multiple clustered nodes in a grid, pooling the processing resources of several standard machines.
Posted Date:- 2021-10-05 05:45:31
A segment is a group of extents, each of which has been allocated for a specific data structure and all of which are stored in the same table-space
Posted Date:- 2021-10-05 05:44:46
It copies redo log files to a designated storage device after a log switch has occurred. Archiver processes are there only when the database is in ARCHIVELOG mode, and automatic archiving is enabled. An Oracle instance can have up to 10 Archi ver processes (ARC0 to ARC9). The LGWR process starts a new ARCn process whenever the current number of Archiver processes is insufficient to handle the workload.
Posted Date:- 2021-10-05 05:44:13
Job queue processes are used for processing batch. They run user jobs. They can be viewed as a scheduler service that can be used to schedule jobs like PL/SQL statements or procedures on an Oracle instance.
Posted Date:- 2021-10-05 05:43:34
The re-coverer process (RECO) is a background process used with the distributed database configuration that automatically resolves failures. The RECO process of a node automatically connects to other databases involved in an in- doubt distributed transaction.
Posted Date:- 2021-10-05 05:42:59
* The computing componenets of oracle grid are
* Oracle Enterprise Manager and Grid Control
* Oracle 10g Database and Real Application Clusters.
* ASM Storage Grid
Posted Date:- 2021-10-05 05:42:23
Large dedicated server
It has expensive costly components.
High incremental costs.
It has single point of failure.
Enterprise service at higher cost.
Oracle Grid
It has low cost modular components.
Low incremental costs.
It has no single point of failure.
Enterprise service at low cost.
Posted Date:- 2021-10-05 05:41:39
Grid computing is a information technology architecture that provides lower cost enterprise information systems. Using grid computing, independent hardware, and software components can be connected and rejoined on demand to meet the changing needs of businesses. It also enables the use of smaller individual hardware components
Posted Date:- 2021-10-05 05:41:01
You can use direct path load to improve the performance. Indexes and constraints make inserts slow. Removing indexes and constraints improve performance of inserts; and therefore, of SQL*Loader.
Posted Date:- 2021-10-05 05:40:28
* Controlling access to data(authorization).
* Restricting access to legitimate users (authentication).
* Ensuring accountability on part of the users(auditing).
* Safeguarding key data in the database(encryption).
* Managing the security of the entire organizational information structure (enterprise security).
Posted Date:- 2021-10-05 05:39:18
Into a row-level trigger based on a table trigger body cannot read data from the same table and also we cannot perform DML operation on the same table.
<> If we are trying this oracle server returns mutating error oracle-4091: table is mutating.
<> This error is called mutating error, and this trigger is called a mutating trigger, and the table is called a mutating table.
<> Mutating errors are not occurred in statement-level trigger because through this statement-level trigger when we are performing DML operations automatically data committed into the database, whereas in the row-level trigger when we are performing transaction data is not committed and also again we are reading this data from the same table then only mutating errors occur.
Posted Date:- 2021-10-05 05:37:29
ON DELETE CASCADE Indicates that when the row in the parent table is deleted, the dependent rows in the child table will also be deleted. ON DELETE SET NULL Coverts foreign key values to null when the parent value is removed. Without the ON DELETE CASCADE or the ON DELETE SET NULL options, the row in the parent table cannot be deleted if it is referenced in the child table.
Posted Date:- 2021-10-05 05:36:03
SET UNUSED option marks one or more columns as unused so that they can be dropped when the demand on system resources is lower. Unused columns are treated as if they were dropped, even though their column data remains in the table’s rows. After a column has been marked as unused, you have no access to that column. A select query will not retrieve data from unused columns. In addition, the names and types of columns marked unused will not be displayed during a DESCRIBE, and you can add to the table a new column with the same name as an unused column. The SET UNUSED information is stored in the USER_UNUSED_COL_TABS dictionary view.
Posted Date:- 2021-10-05 05:35:10
You can start up a database with three tools.
SQL*Plus: This is the most widely used option. You first connect to an idle instance with SQL*Plus and then startup the instance with the “startup†command.
Oracle Enterprise Manager: This is another way of starting up a database. You can logon to Oracle Enterprise Manager even if the database is stopped. OEM will detect the status of the down database and will present you with a “Startup†button. You can startup the database by clicking this button.
RMAN: This is rather a less used tool for starting up a database but it is possible to startup a database from the Recovery Manager command line.
Posted Date:- 2021-10-05 05:34:35
* Database Instances and Automatic Storage Management (ASM): Database instances and ASM instances will be restarted if they crash somehow.
* Oracle NET Listener: Oracle NET Listener will be started if it crashes and stops listening for an incoming connection.
* ASM Disk Groups: Oracle Restart will mount ASM Disk groups if they are dismounted.
* Database Services: Non-default database services will be started by Oracle Restart feature.
* Oracle Notification Services (ONS): This is another Oracle component that can be protected by Oracle Restart.
Posted Date:- 2021-10-05 05:31:42
USER TABLES: Is a collection of tables created and maintained by the user. Contain USER information. DATA DICTIONARY: Is a collection of tables created and maintained by the Oracle Server. It contains database information. All data dictionary tables are owned by the SYS user.
Posted Date:- 2021-10-05 05:30:30
A nested table is a database collection object, which can be stored as a column in a table. While creating a normal table, an entire nested table can be referenced in a single column. Nested tables have only one column with no restriction of rows.
For Example:
CREATE TABLE EMP (
EMP_ID NUMBER,
EMP_NAME TYPE_NAME)
Here, we are creating a normal table as EMP and referring a nested table TYPE_NAME as a column.
Posted Date:- 2021-10-05 05:29:58
Locking prevents destructive interaction between concurrent transactions. Locks held until Commit or Rollback. Types of locking are:
* Implicit Locking: Occurs for all SQL statements except SELECT.
* Explicit Locking: Can be done by the user manually.
Further, there are two locking methods:
Exclusive: Locks out other users
Share: Allows other users to access
Posted Date:- 2021-10-05 05:28:28
Trace files are files used to store details of exceptions thrown by Oracle background processes i.e. bar, low, pmon, smon, etc. They are usually created for diagnostic dumps as well and help in debugging and solving exceptions in Oracle.
To create a Trace file in oracle:
Set sql_trace=true with alter session command. This will generate a trace file for all sql commands issued by your user session. This is known as a level-1 trace file. One can also create super detailed level-4 trace files with additional details if the need be. These files are stored in the form $ORACLE_SID_ora_xxx.trc in the trace directory, where xxx is a sequential number. To create a level-4 detailed trace file, we need to know the SID and SERIAL# for the session to trace. Eg: to trace for a session for SID 5: Connect system as sysdba; oradebug SETOSPID 5; oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL4
Posted Date:- 2021-10-05 05:27:14
The Oracle grid architecture pools large numbers of servers, storage, and networks into a flexible, on-demand computing resource for enterprise computing needs. The grid computing infrastructure continually analyzes the demand for resources and adjusts supply accordingly.
Posted Date:- 2021-10-05 05:25:22
A Package is a group of related database objects like stored procs, functions, types, triggers, cursors, etc. that are stored in the Oracle database. It is a kind of library of related objects which can be accessed by multiple applications if permitted.
Posted Date:- 2021-10-05 05:23:25
<> You can increase the width or precision of a numeric column.
<> You can increase the width of numeric or character columns.
<> You can decrease the width of a column only if the column contains null values or if the table has no rows.
<> You can change the data type only if the column contains null values
You can convert a CHAR column to the VARCHAR2 data type or convert a VARCHAR2 column to the CHAR data type only if the column contains null values or if you do not change the size.
Posted Date:- 2021-10-05 05:22:35
Following is the procedure of restoring RMAN for the recovery:
1. Bring all the nodes down.
2. Start a node.
3. Restore all the data files and archive logs.
4. Recover the node.
5. Open the database.
6. Bring other nodes up.
7. Confirm that all nodes are operational.
Posted Date:- 2021-10-05 05:20:28
A column can be given a default value by using the DEFAULT option. This option prevents null values from entering the column if a row is inserted without a value for that column. The DEFAULT value can be a literal, an expression, or a SQL function such as SYSDATE and USER but the value cannot be the name of another column or a pseudo column such as NEXTVAL or CURRVAL.
Posted Date:- 2021-10-05 05:19:18
It is a group of disks that ASM manages as a unit. Within the disk groups, a file system interface maintains the Oracle database files. ASM simplifies database storage by consolidating disks into disk groups. This reduces I/O overhead.
Posted Date:- 2021-10-05 05:18:47
It is an instance that manages ASM disk groups. It comprises the System Global Area(SGA) and background processes. ASM instance mounts a disk group that is made available to the database instance. An ASM instance manages the metadata of a disk group and provides file layout information to the database instances.
Posted Date:- 2021-10-05 05:18:23
* Dynamic performance views are also called V$ views. These views provide information about the sessions.
* Any Oracle user can get information from dynamic performance views if the user has to select any table privilege. This privilege is generally granted through the SELECT_CATALOG_ROLE role.
Posted Date:- 2021-10-05 05:17:51
The performance tuning field can't be automated like other features of exp/imp, backup recovery.This is an area that requires more detective work to be carried on part of application programmers and DBA's to check how some process is running slower than expected, why can't we scale applications to a larger number of users without problems like performance degradation etc. This is an area where the technical knowledge must be used along with constant experimentation and observation.
Posted Date:- 2021-10-05 05:17:10
Cross Join: Cross Join clause produces the cross-product of two tables. This is same as a Cartesian product between the two tables.
Natural Joins: Is used to join two tables automatically based on the columns which have matching data types and names, using the keyword NATURAL JOIN. It is equal to the Equi-Join. If the columns have the same names but different data types, then the Natural Join syntax causes an error.
Join with the USING clause: If several columns have the same names but the data types do not match, then the NATURAL JOIN clause can be modified with the USING clause to specify the columns that should be used for an equi Join. Use the USING clause to match only one column when more than one column matches. Do not use a table name or alias in the referenced columns. The NATURAL JOIN clause and USING clause are mutually exclusive.
Posted Date:- 2021-10-05 05:16:24
Cartesian Join: When a Join condition is invalid or omitted completely, the result is a Cartesian product, in which all combinations of rows are displayed. To avoid a Cartesian product, always include a valid join condition in a “where†clause. To Join ‘N’ tables together, you need a minimum of N-1 Join conditions. Forex: to join four tables, a minimum of three joins is required. This rule may not apply if the table has a concatenated primary key, in which case more than one column is required to uniquely identify each row.
Equi Join: This type of Join involves primary and foreign key relation. Equi Join is also called Simple or Inner Joins.
Non-Equi Joins A Non-Equi Join condition containing something other than an equality operator. The relationship is obtained using an operator other than equal operator (=). The conditions such as <= and >= can be used, but BETWEEN is the simplest to represent Non-Equi Joins.
Outer Joins: Outer Join is used to fetch rows that do not meet the join condition. The outer join operator is the plus sign (+), and it is placed on the side of the join that is deficient in information (Top 50 Oracle Interview Questions and Answers pdf). The Outer Join operator can appear on only one side of the expression, the side that has information missing. It returns those rows from one table that has no direct match in the other table. A condition involving an Outer Join cannot use IN and OR operator
Posted Date:- 2021-10-05 05:14:36
You can use the following database views to get the information on database size:
* Dba_segments: It provides information about the used space. You can take a total of all the bytes in the dba_segments view to get the used space.
* Dba_data__files: It provides information on space allocated to datafiles for permanent tablespaces.
* v$log: It provides information on redo log files.
Posted Date:- 2021-10-05 05:13:27
The objects as such can be stored in the database. The language of the DBMS can be integrated with an object-oriented programming language. The language may even be exactly the same as that used in the application, which does not force the programmer to have two representations of his objects.
Posted Date:- 2021-10-05 05:12:36
Data from different tables can be combined using MERGE command. It works by selecting and updating data in the source table and the second table respectively based on the query provided.
Posted Date:- 2021-10-05 05:11:53
BLOB stands for Binary Large Object, which is a data type that is generally used to hold images, audio & video files or some binary executables.
Posted Date:- 2021-10-05 05:09:14
Constraints are rules that you set up for your data.
They can be used either during table creation or later, to alter the table.
Posted Date:- 2021-10-05 05:06:25
For single statements, you can use two dashes (--).
For a block statement you can use (*--*).
Posted Date:- 2021-10-05 05:03:19
Transaction occurs when a set of SQL statements are executed in one go. To control the execution of these statements, Oracle has introduced TCL i.e. Transaction Control Statements that use a set of statements
Posted Date:- 2021-10-05 05:00:07
A primary key is used to identify each table row uniquely. A primary key can be only one on the table and it cannot hold the null value in the table and we cannot insert any duplicate or null value in a table for that specific column on which the primary key constraint has applied. It is referred to as cluster index. Unique key mainly used to refrain the duplication of values in the table. In the table, unique keys can be multiple and it can hold one null value per column. It referred to as a non-cluster index.
Posted Date:- 2021-10-05 04:58:50
Following is the logical structure of the disk resources:
* Data block: The data block refers to the smallest logical storage unit. Size of a data block is a multiple of the operating system block size.
* Extent: It refers to the contiguous set of data blocks, which is allocated as a unit to a segment.
* Tablespace: The tablespace refers to the final logical storage unit. It is mapped to the physical datafile.
* Segment: The segment allocates a logical structure, such as a table. It is a set of extents, which are stored in the same tablespace.
Posted Date:- 2021-10-05 04:58:28
Integrity constraints are required to enforce business rules so as to maintain the integrity of the database and prevent the entry of invalid data into the tables. With the help of the below-mentioned constraints, relationships can be maintained between the tables .
Posted Date:- 2021-10-05 04:57:21
* Enterprise Backup Utility (EBU) is a functionally rich, high-performance interface for backing up Oracle7 databases. It is sometimes referred to as OEBU for Oracle Enterprise Backup Utility.
* The Oracle Recovery Manager (RMAN) utility that ships with Oracle8 and above is similar to Oracle7's EBU utility. However, there is no direct upgrade path from EBU to RMAN.
Posted Date:- 2021-10-05 04:56:54
The various Oracle database objects are:
* Sequences.
* Views.
* Tables.
* Tablespaces.
* Indexes.
Posted Date:- 2021-10-05 04:56:19
Yes, there is.
Rename is an invariable permanent name assigned to a record, while Alias is a temporary name.
Posted Date:- 2021-10-05 04:55:39
Privileges are granted access to another user's data or the ability to execute a command.
They are used in creating roles and can be enabled and disabled for any user.
Posted Date:- 2021-10-05 04:55:21
They are exceptions that occur during programming and can be usually handled by using EXCEPTION block command.
Posted Date:- 2021-10-05 04:55:05
SELECT student_id s_id, student_name name, birthdate date, student_number s_no FROM students;
Here, a reserved keyword ‘date’ has been used as an alias for the column birthdate. This action is illegal in Oracle SQL. In order to set a reserved keyword as an alias, we can use quotation marks. SELECT student_id s_id, student_name name, birthdate “dateâ€, student_number s_no FROM students;
Posted Date:- 2021-10-05 04:54:13
SQL Functions are a very powerful feature of SQL. SQL functions can take arguments but always return some value. There are two distinct types of SQL functions:
1) Single-Row functions: These functions operate on a single row to give one result per row.
Types of Single-Row functions:
* Character
* Number
* Date
* Conversion
* General
2) Multiple-Row functions: These functions operate on groups of rows to give one result per group of rows.
Types of Multiple-Row functions:
AVG
COUNT
MAX
MIN
SUM
STDDEV
VARIANCE
Posted Date:- 2021-10-05 04:52:39
The process of having a copy of redo log files is called mirroring. It is done by creating a group of log files together. This ensures that LGWR automatically writes them to all the members of the current on-line redo log group. In case a group fails, the database automatically switches over to the next group. It diminishes the performance. Hot backup vs. cold backup A database backup, while it is still up and running, is a Hot backup and it must be in archive log mode. A cold backup is a backup while it is shut down. The database does not require being in archive log mode in this mode. The benefit of a hot backup is that the database is still available for use while the backup is occurring. A cold backup is easier to administer the backup and recovery process. Cold backups do not require being in archive log mode and thus slight performance gain as the database is not writing archive logs to disk.
Posted Date:- 2021-10-05 04:51:50
An index is a schema object, which is created to search the data efficiently within the table. Indexes are usually created on certain columns of the table, which are accessed the most. Indexes can be clustered or non-clustered.
Posted Date:- 2021-10-05 04:24:19