Oracle Interview Questions – Part 10
Q.What are the OOPS concepts in Oracle?
Oracle does implement the OOPS concepts. The best example is the Property Classes. We can categorize the properties by setting the visual attributes and then attach the property classes for the objects. OOPS supports the concepts of objects and classes and we can consider the property classes as classes and the items as objects
Q.How does one enforce strict password control? (for DBA)
By default Oracle’s security is not extremely good. For example, Oracle will allow users to choose single character passwords and passwords that match their names and userids. Also, passwords don’t ever expire. This means that one can hack an account for years without ever locking the user.
From Oracle8 one can manage passwords through profiles. Some of the things that one can restrict:
.FAILED_LOGIN_ATTEMPTS – failed login attempts before the account is locked
.PASSWORD_LIFE_TIME – limits the number of days the same password can be used for authentication
.PASSWORD_REUSE_TIME – number of days before a password can be reused
.PASSWORD_REUSE_MAX – number of password changes required before the current password can be reused
.PASSWORD_LOCK_TIME – number of days an account will be locked after maximum failed login attempts
.PASSWORD_GRACE_TIME – number of days after the grace period begins during which a warning is issued and login is allowed
.PASSWORD_VERIFY_FUNCTION – password complexity verification script
Look at this simple example:
CREATE PROFILE my_profile LIMIT
ALTER USER scott PROFILE my_profile;
Q.How does one switch to another user in Oracle? (for DBA)
Users normally use the “connect” statement to connect from one database user to another. However, DBAs can switch from one user to another without a password. Of course it is not advisable to bridge Oracle’s security, but look at this example: SQL> select password from dba_users where username=’SCOTT’;
SQL> alter user scott identified by lion;
SQL> connect scott/lion
REM Do whatever you like…
SQL> connect system/manager
SQL> alter user scott identified by values ‘F894844C34402B67’;
SQL> connect scott/tiger
Note: Also see the su.sql script in the Useful Scripts and Sample Programs Page.
Q.What is the difference between candidate key, unique key and primary key?
Candidate keys are the columns in the table that could be the primary keys and the primary key is the key that has been selected to identify the rows. Unique key is also useful for identifying the distinct rows in the table.)
Q.What are Privileges and Grants?
Previleges are the right to execute a particulare type of SQL statements. e.g :: Right to Connect, Right to create, Right to resource Grants are given to the objects so that the object might be accessed accordingly.The grant has to be given by the owner of the object
Q.What is concurrency?
Concurrency is allowing simultaneous access of same data by different users. Locks useful for accesing the database are
The exclusive lock is useful for locking the row when an insert,update or delete is being done.This lock should not be applied when we do only select from the row.
b) Share lock
We can do the table as Share_Lock as many share_locks can be put on the same resource.
Q.Table Space,Data Files,Parameter File, Control Files
Table Space :: The table space is useful for storing the data in the database.When a database is created two table spaces are created.
a) System Table space :: This data file stores all the tables related to the system and dba tables
b) User Table space :: This data file stores all the user related tables
We should have seperate table spaces for storing the tables and indexes so that the access is fast.
Data Files :: Every Oracle Data Base has one or more physical data files.They store the data for the database.Every datafile is associated with only one database.Once the Data file is created the size cannot change.To increase the size of the database to store more data we have to add data file.
Parameter Files :: Parameter file is needed to start an instance.A parameter file contains the list of instance configuration parameters e.g.::
db_block_buffers = 500
db_name = ORA7
db_domain = u.s.acme lang
Control Files :: Control files record the physical structure of the data files and redo log files
They contain the Db name, name and location of dbs, data files ,redo log files and time stamp.
Q.What are snap shots and views?
Snapshots are mirror or replicas of tables. Views are built using the columns from one or more tables. The Single Table View can be updated but the view with multi table cannot be updated
Q.What are mutating tables?
When a table is in state of transition it is said to be mutating. eg :: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select.
Q.What is Physical Storage of the Data?
The finest level of granularity of the data base are the data blocks.
Data Block :: One Data Block correspond to specific number of physical database space
Extent :: Extent is the number of specific number of contigious data blocks.
Segments :: Set of Extents allocated for Extents. There are three types of Segments
a) Data Segment :: Non Clustered Table has data segment data of every table is stored in cluster data segment
b) Index Segment :: Each Index has index segment that stores data
c) Roll Back Segment :: Temporarily store ‘undo’ information
Q.What are the Pct Free and Pct Used?
Pct Free is used to denote the percentage of the free space that is to be left when creating a table. Similarly Pct Used is used to denote the percentage of the used space that is to be used when creating a table
eg.:: Pctfree 20, Pctused 40
Q.What is Row Chaining?
The data of a row in a table may not be able to fit the same data block.Data for row is stored in a chain of data blocks .
Q.Can U disable database trigger? How?
Yes. With respect to table
ALTER TABLE TABLE
[[ DISABLE all_trigger ]]
Q.What is the difference between deleting and truncating of tables?
Deleting a table will not remove the rows from the table but entry is there in the database dictionary and it can be retrieved But truncating a table deletes it completely and it cannot be retrieved.
Q.What is a 2 Phase Commit?
Two Phase commit is used in distributed data base systems. This is useful to maintain the integrity of the database so that all the users see the same values. It contains DML statements or Remote Procedural calls that reference a remote object. There are basically 2 phases in a 2 phase commit.
a) Prepare Phase :: Global coordinator asks participants to prepare
b) Commit Phase :: Commit all participants to coordinator to Prepared, Read only or abort Reply
Q.What are Codd Rules?
Codd Rules describe the ideal nature of a RDBMS. No RDBMS satisfies all the 12 codd rules and Oracle Satisfies 11 of the 12 rules and is the only Rdbms to satisfy the maximum number of rules.
Q.What is the Difference between a post query and a pre query?
A post query will fire for every row that is fetched but the pre query will fire only once.
Q.Deleting the Duplicate rows in the table?
We can delete the duplicate rows in the table by using the Rowid
Q.What is pseudo columns ? Name them?
A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values. This section describes these pseudocolumns:
Q.What is Normalisation?
Normalisation is the process of organising the tables to remove the redundancy.There are mainly 5 Normalisation rules.
a) 1 Normal Form :: A table is said to be in 1st Normal Form when the attributes are atomic
b) 2 Normal Form :: A table is said to be in 2nd Normal Form when all the candidate keys are dependant on the primary key
c) 3rd Normal Form :: A table is said to be third Normal form when it is not dependant transitively
Q.How many columns can table have?
The number of columns in a table can range from 1 to 254.
Q.What is clustered index?
In an indexed cluster, rows are stored together based on their cluster key values . Can not applied for HASH.
Q.What are object group?
An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or reference them in another module.
Q.What are attributes of cursor?
%FOUND , %NOTFOUND , %ISOPEN,%ROWCOUNT
Q.Is space acquired in blocks or extents?
In extents .
Q.Can you use select in FROM clause of SQL select?
Q.What are the datatypes supported By oracle (INTERNAL)?
Varchar2, Number,Char , MLSLABEL.
Q.Which trigger are created when master -detail relay?
master delete property
* NON-ISOLATED (default)
a) on check delete master
b) on clear details
c) on populate details
a) on clear details
b) on populate details
b) on clear details
c) on populate details
Q.What are referenced objects?
Referencing allows you to create objects that inherit their functionality and appearance from other objects. Referencing an object is similar to copying an object, except that the resulting reference object maintains a link to its source object. A reference object automatically inherits any changes that have been made to the source object when you open or regenerate the module that contains the reference object.
Q.which system variables can be set by users?