Have you prepared for your interview, can you answer these basic Interview questions on Oracle.
Oracle is a database server that is used to handle data in a structured manner. It lets its users to retrieve and to store data in a way that multiple users can access similar data at the same time. Oracle achieves this with high efficiency. Many security checks are followed to limit access to authorized users only and to retrieve data in case of accidental data loss.
Each operating Oracle database is associated with an Oracle instance. As soon as a database server starts the database, it is assigned a memory area by Oracle called System Global Area (SGA) and starts one or more than one processes. The SGA and Oracle processes together are referred to as an Oracle instance. The process and memory of an instance are used to efficiently handle data used by multiple users.
Parameter file is a file having a track of initialization parameters and the matching values. The two types of Oracle supported parameter files are:
Based on individual installations users can specify their individual initialization parameters.
It is the memory area which contains shared data like SQL statements shared pool and buffer cache, between all users. As soon as an Oracle database instance starts the SGA is allocated. Value changes become effective during subsequent startup.
Every user is given particular attributes identified by a username termed as a user account. The below can be incorporated into the user attributes:
The five query types in Oracle are as follows:
A function is used to return a single value whereas a procedure doesn’t return any value. It returns multiple variables. This is achieved by passing variables by reference through OUT parameter.
The constructs for a package, function or a procedure are:
In order to prevent Grants from getting dropped we create and replace procedures rather than drop and recreate.
No. SQL*Plus doesn’t contain PL/SQL engine, unlike Oracle Forms. Because of which all PL/SQL is sent to the database engine to get executed which increases the efficiency. Each SQL statement is individually sent to the database and not stripped off.
As of now a compiled/ parsed block of PL/SQL has a maximum size limitation of 64K and max. code size being 100K. The statement for querying existing procedure or the package size is as follows:
SQL> select * from dba_object_size where name = 'procedure_name'
Oracle 7.3 has a UTL_FILE package included in it which is used to read/ write files. The directory where you want to write to has to be in the INIT.ORA file. Prior to Oracle 7.3, DBMS_OUTPUT with the SQL*Plus SPOOL command was the only way to write a file.
Source codes of PL/SQL V2.2 made available with Oracle 7.2 are protected by implementing the binary wrapper. A stand-alone function does this by transforming the source code of PL/SQL to a portable binary object code. Thus the software can be distributed without the proprietary methods and algorithms getting exposed. Such scripts can still be understood and executed by SQL*DBA and SQL*Plus. The only precaution to be taken is that “decode” command shouldn’t be available.
Changing old and new values in an insert, delete and update triggers
INSERT : new = new value, old = NULL DELETE : new = NULL, old = old value UPDATE : new = new value, old = old value
Clusters reduce the access time for joins and increases it for insert.
In a table, a check condition of a column can reference another column in the same table thus providing self referential integrity.
A table can have maximum 254 columns.
The & operator signifies that a user input is needed for the PL SQL block variable. The && operator signifies that this variable’s value must be the same as inputted earlier by the user for same variable.
Never Miss an Articles from us.