Whenever a new database is created, a database-specific data dictionary gets created by the system. This dictionary is owned by the SYS user and maintains all the metadata related to the database. It has a set of read-only tables and views and it is physically stored in the SYSTEM tablespace.
Posted Date:- 2021-10-05 04:21:17
There are no differences between the join. Cartesian and Cross joins are same. Cross join gives cartesian product of two tables – Rows from first table is multiplied with another table which is called cartesian product.
Cross join without where clause gives Cartesian product.
Posted Date:- 2021-10-05 04:20:16
Row can be fetched from table by using ROWID. Using ROW ID is the fastest query method to fetch data from the table.
Posted Date:- 2021-10-05 04:19:34
Oracle base is the main or root directory of an oracle whereas ORACLE_HOME is located beneath base folder in which all oracle products reside.
Posted Date:- 2021-10-05 04:18:26
LOV is property whereas list items are considered as single item. List of items is set to be a collection of list of items. A list item can have only one column, LOV can have one or more columns.
Posted Date:- 2021-10-05 04:17:35
Actual Parameters: Actual parameters are the variables or expressions referenced in the parameter list of a subprogram.
Let's see a procedure call which lists two actual parameters named empno and amt:
raise_sal(empno, amt);
Formal Parameters: Formal parameters are variables declared in a subprogram specification and referenced in the subprogram body.
Following procedure declares two formal parameters named empid and amt:
PROCEDURE raise_sal(empid INTEGER, amt REAL) IS current_salary REAL;
Posted Date:- 2021-10-05 04:16:50
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.
The set of statements include:
>> COMMIT: Used to make a transaction permanent.
>> ROLLBACK: Used to roll back the state of DB to last the commit point.
>> SAVEPOINT: Helps to specify a transaction point to which rollback can be done later.
Posted Date:- 2021-10-05 04:15:27
Yes, we can store pictures in the database by Long Raw Data type. This datatype is used to store binary data for 2 gigabytes of length. But the table can have only on Long Raw data type.
Posted Date:- 2021-10-05 04:14:18
The number of times a dictionary table is repeatedly called by various processes is known as recursive hint. Recursive hint is occurred because of the small size of data dictionary cache.
Posted Date:- 2021-10-05 04:13:39
Mirroring is a process of having a copy of Redo log files. It is done by creating group of log files together. This ensures that LGWR automatically writes them to all the members of the current on-line redo log group. If the group fails, the database automatically switches over to the next group. It diminishes the performance.
Posted Date:- 2021-10-05 04:13:01
The post-database commit trigger is executed after Oracle forms issue the commit to finalized transaction while, the post-form commit is fired during the post and commit transactions process, after the database commit occurs.
Posted Date:- 2021-10-05 04:12:06
Save Points are used to divide a transaction into smaller phases. It enables rolling back part of a transaction. There are maximum 5 save points allowed in Oracle Database. Whenever an error is encountered, it is possible to rollback from the point where the SAVEPOINT has been saved.
Posted Date:- 2021-10-05 04:10:34
There are two types of synonyms or alias:
Private: It can only accessed by the owner.
Public: It can be accessed by any database user.
Posted Date:- 2021-10-05 04:09:20
In Oracle, control file is used for database recovery. The control file is also used to identify the database and redo log files that must be opened for database operation to go ahead, whenever an instance of an ORACLE database begins.
Posted Date:- 2021-10-05 04:05:06
Aggregate functions perform summary operations on a set of values to provide a single value. There are several aggregate functions that we use in our code to perform calculations. These are:
* AVG
* MIN
* MAX
* COUNT
* SUM
* STDEV
Posted Date:- 2021-10-05 04:04:11
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.
Various integrity constraints are available which include Primary Key, Foreign Key, UNIQUE KEY, NOT NULL & CHECK.
Posted Date:- 2021-10-05 04:03:12
ANALYZE command is used to perform various functions on index, table, or cluster. The following list specifies the usage of ANALYZE command in Oracle:
* It is used to identify migrated and chained rows of the table or cluster.
* It is used to validate the structure of the object.
* It helps in collecting the statistics about object used by the optimizer. They are then stored in the data dictionary.
* It helps in deleting statistics used by object from the data dictionary.
Posted Date:- 2021-10-05 04:02:11
Following are the different modules in Oracle forms:
* Form module
* Menu module
* Pl/SQL Library module
* Object Library module
Posted Date:- 2021-10-05 04:01:16
Describe <Table_Name> is used to get the field details of a specified table.
Posted Date:- 2021-10-05 04:00:29
VArray is an oracle data type used to have columns containing multivalued attributes and it can hold bounded array of values.
Posted Date:- 2021-10-05 03:59:26
Oracle provides following temporal data types:
* Date Data Type – Different formats of Dates
* TimeStamp Data Type – Different formats of Time Stamp
* Interval Data Type – Interval between dates and time
Posted Date:- 2021-10-05 03:58:00
A pre-query trigger fire before the query executes and fire once while you try to query. With the help of this trigger you can modify the where clause part dynamically.
Pre-select query fires during the execute query and count query processing after Oracle forms construct the select statement to be issued, but before the statement is actually issued.
Pre-query trigger fires before Pre-select trigger.
Posted Date:- 2021-10-05 03:57:15
Tables: This is a set of elements organized in vertical and horizontal fashion.
Tablespaces: This is a logical storage unit in Oracle.
Views: It is virtual table derived from one or more tables.
Indexes: This is a performance tuning method to process the records.
Synonyms: This is a name for tables.
Posted Date:- 2021-10-05 03:56:26
Hash cluster is a technique to store a data in hash table and improve the performance of data retrieval. Hash function is applied on table row's cluster key value and store in hash cluster.
Posted Date:- 2021-10-05 03:55:56
Oracle shared pools contains two layers:
1. library cache
2. data dictionary cache
Posted Date:- 2021-10-05 03:55:26
Hot backup (Online Backup): A hot backup is also known as online backup because it is done while the database is active. Some sites can not shut down their database while making a backup copy, they are used for 24 hour a day, 7 days a week.
Cold backup (Offline Backup): A cold backup is also known as offline backup because it is done while the database has been shutdown using the SHUTDOWN normal command. If the database is suddenly shutdown with a uncertain condition it should be restarted with RESTRICT mode and then shutdown with NORMAL option.
For a complete cold backup the following files must be backed up.
All datafiles, All control files, All online redo log files(optional) and the init.ora file (you can recreate it manually).
Posted Date:- 2021-10-05 03:54:34
A snapshot is a replica of a target master table from a single point-in-time. In simple words you can say, snapshot is a copy of a table on a remote database.
Posted Date:- 2021-10-05 03:54:00
An Oracle database contains one or more logical storage units called tablespaces. These tablespaces collectively store whole data of databases and each tablespace in Oracle database consists of one or more files called datafiles. These datafiles are physical structure that confirm with the operating system in which Oracle is running.
Posted Date:- 2021-10-05 03:53:32
Bulk copy or BCP in Oracle, is used to import or export data from tables and views but it does not copy structure of same data.
The main advantage of BCP is fast mechanism for coping data and you can also take the backup of data easily.
Posted Date:- 2021-10-05 03:52:23
Merge statement is used to merge the data from two tables subsequently. It selects the data from the source table and then inserts/updates it in the other table based on the condition provided in the query. It is also useful in data warehousing applications.
Posted Date:- 2021-10-05 03:50:46
Sub Query is also called as Nested Query or Inner Query which is used to get data from multiple tables. A sub query is added in the where clause of the main query.
There are two different types of subqueries:
* Correlated sub query
A Correlated sub query cannot be as independent query but can reference column in a table listed in the from list of the outer query.
* Non-Correlated subquery
This can be evaluated as if it were an independent query. Results of the sub query are submitted to the main query or parent query.
Posted Date:- 2021-10-05 03:49:59
Primary Key is used to identify each table row uniquely, while a Unique Key prevents duplicate values in a table column.
Given below are a few differences:
<> The primary key can be only one on the table while unique keys can be multiple.
<> The primary key cannot hold null value at all while the unique key allows multiple null values.
<> The primary key is a clustered index while a unique key is a non-clustered index.
Posted Date:- 2021-10-05 03:49:11
NVL is a function that helps the user to substitute value if null is encountered for an expression.
It can be used as the below syntax.
NVL (Value_In, Replace_With)
Posted Date:- 2021-10-05 03:48:34
Using ON DELETE CASCADE will automatically delete a record in the child table when the same is deleted from the parent table. This statement can be used with Foreign Keys.
We can add ON DELETE CASCADE option on an existing table using the below set of commands.
Syntax:
ALTER TABLE CHILD_T1 ADD CONSTRAINT CHILD_PARENT_FK REFERENCES
PARENT_T1 (COLUMN1) ON DELETE CASCADE;
Posted Date:- 2021-10-05 03:48:04
The WITH CHECK option clause specifies check level to be done in DML statements. It is used to prevent changes to a view that would produce results that are not included in the sub query.
Posted Date:- 2021-10-05 03:47:28
A table is set to be key preserved table if every key of the table can also be the key of the result of the join. It guarantees to return only one copy of each row from the base table.
Posted Date:- 2021-10-05 03:47:05
The USING clause is used to specify with the column to test for equality when two tables are joined.
[sql]Select * from employee join salary using employee ID[/sql]
Employee tables join with the Salary tables with the Employee ID.
Posted Date:- 2021-10-05 03:46:15
It is possible to store pictures on to the database by using Long Raw Data type. This data type is used to store binary data of length 2GB. Although, the table can have only on Long Raw data type.
Posted Date:- 2021-10-05 03:45:02
A BLOB data type is a varying length binary string which is used to store two gigabytes memory. Length should be specified in Bytes for BLOB.
Posted Date:- 2021-10-04 14:39:08
Both the commands are used to remove data from the database.
Posted Date:- 2021-10-04 13:51:00
Oracle mainly provides these following temporal data types:
* Date Data Type: Different formats of Dates.
* TimeStamp Data Type: Has different formats of Time Stamp.
* Interval Data Type: Interval between dates and time.
Posted Date:- 2021-10-04 13:50:25
An aggregate function in Oracle is a function where values of multiple rows or records are joined together to get a single value output. It performs the summary operations on a set of values in order to provide a single value. There are several aggregate functions that you can use in your code to perform calculations.
Some common Aggregate functions are:
<> Average
<> Count
<> Sum
Posted Date:- 2021-10-04 13:45:26
The RAW datatype in Oracle is used to store variable-length binary data or byte string values. The maximum size for a raw in a given table in 32767 bytes.
You might get confused as to when to use RAW, varchar, and varchar2. Let me point out the major differences between them. PL/SQL does not recognize the data type and hence, it cannot have any conversions when RAW data is transferred to different systems. This data type can only be queried or can be inserted in a table.
Posted Date:- 2021-10-04 13:44:47
A Join is used to compare and combine, this means literally join and return specific rows of data from two or more tables in a database.
There are three types of joins in SQL that are used to write the subqueries.
1. Self Join: This is a join in which a table is joined with itself, especially when the table has a foreign key which references its own primary key.
2. Outer Join: An outer join helps to find and returns matching data and some dissimilar data from tables.
3. Equi-join: An equijoin is a join with a join condition containing an equality operator. An equijoin returns only the rows that have equivalent values for the specified columns.
Posted Date:- 2021-10-04 13:43:09
This “Analyze†command is used to perform various functions on index, table, or cluster. The following list specifies the usage of ANALYZE command in Oracle:
* Analyze command is used to identify migrated and chained rows of the table or a cluster.
* It is used to validate the structure of an object.
* This helps in collecting the statistics about the object used by the user and are then stored on to the data dictionary.
* It also helps in deleting statistics that are used by an object from the data dictionary.
Posted Date:- 2021-10-04 13:42:13
These are the Oracle Database Objects:
Tables: This is a set of elements organized in a vertical and horizontal manner.
Tablespaces: It is a logical storage unit in Oracle.
Views: Views are a virtual table derived from one or more tables.
Indexes: This is a performance tuning method to process the records.
Synonyms: It is a name for tables.
Posted Date:- 2021-10-04 13:38:59
The components of the logical database structure in Oracle database are:
* Tablespaces: A database mainly contains the Logical Storage Unit called tablespaces. This tablespace is a set of related logical structures. To be precise, tablespace groups are related to logical structures together.
* Database schema objects: A schema is a collection of database objects owned by a specific user. The objects include tables, indexes, views, stored procedures, etc. And in Oracle, the user is the account and the schema is the object. It is also possible in the database platforms to have a schema without a user specified.
Posted Date:- 2021-10-04 13:37:54
Oracle is one of the popular databases provided by Oracle Corporation, which works on relational management concepts and hence it is referred to as Oracle RDBMS as well. It is widely used for online transaction processing, data warehousing, and enterprise grid computing.
Posted Date:- 2021-10-04 10:09:11
RAW datatype is used to store values in binary data format. The maximum size for a raw in a table in 32767 bytes.
Posted Date:- 2021-10-04 10:07:20
Oracle has been developed using C Language.
Posted Date:- 2021-10-04 09:58:09