Oracle Interview Questions

What is oracle?

Oracle is a Company name and Database Server which uses to manage data. It has different types of objects to manager data like tables, views, synonyms, sequences, indexes, procedures, functions, trigger, packages etc.

What is difference between Synonyms and View?

Synonym is alias of a table and can be created only for one table and its physically present in the database but view can be created on multiple tables and its logical.

What is trigger and how many types of triggers available?

Trigger is database object which fired on any DML transaction occurs in the database. A single SQL statement can fire 4 types of triggers before row trigger, before statement trigger, after row trigger and after statement trigger.

How many types of backups, explain them and how we can have backup?

There are two types of backups in oracle Physical Backup and Logical Backup.

• Physical backup consists of physical database files like datafiles, control files, parameter files, archived redo log files which are required which are required to recover database. It can be manually or by RMAN.
• Logical Backup used to migrate data fully or partially. It can be taken by company (exp) or (expdp).

What are the roles and responsibilities of a DBA?

A Database Administer main role is to ensure that data should be available as it required and it should be secure from loss or corruption. There are so many other roles and responsibilities of DBA

• installation, configuration and maintenance new installation of database, configure all its requirement and maintain it properly.

• Backup and recovery plans, schedule backup and keep it safe and any if failure of corruption of data, backup can be restoring properly.
• Administration of users and Scheme Objects. DBA should manage users like create users, privileges users and other object in the database like tables space, datafiles, control file, redo log file etc.

• Security of Data. DBA is responsible for security of data from inside the environment or outside. Protect from any cyber attach, immune from attach, best practice to minimize risk. User authentication and roles for data which data user can see or change.

• Data Management. If company has different types of databases environments, then using ETL for data warehousing or central repository for data management. And in case of large database manager all types of physical files lie document, images, videos, audios.
• Performance and Tuning. DBA should properly check performance of database. So, it can overcome of slowness or any hangout. Maybe it requires any system change or hardware. Database should be tune property so that it should have good impact on database performance. Dba should continuously monitor growing size of database, in case of storge capacity issue slowness or many other manage can be happen.
• Troubleshooting. Dba should response quickly according to the situation. Quickly restore of data, minimize lose of data and quickly database availability.

How many types of blocks in PL/SQL?

There are two types of blocks, Anonymous block, Named block. Block with out any name is called anonymous block. These are created and used at the same time and can not be stored and cannot be reuse. Named block have specific name and can be stored in the database as database object and can be use later. There are two types of named block procedure and function.

How many types of shutdown methods in oracle and explain them?

There are four types of shutdown method in the oracle, Normal Mode, Transaction Mode, Immediate Mode, Abort Mode.

• Normal Mode - Command use “Shutdown Normal”. In the mode database shutdown normally, after passing this command no new connections are allowed and database waits to end all connection.
• Transaction Mode - Command use “Shutdown Transactional”. In this mode database shutdown transactionally, after passing this command no new connections are allowed and database waits to end all traction.
• Immediate Mode - Command use “Shutdown Immediate”. In this mode database shutdown immediately, after passing this command no new connections are allowed and database does not wait to end all traction or user to disconnect. But no instance recovery need at next startup.
• Abort Mode - Command use “Shutdown Abort”. In this mode database shutdown Abort, after passing this command no new connections are allowed and database does not wait to end all traction or user to disconnect. But it needs instance recovery at next startup.

What is difference between PL/SQL and SQL *Plus?

PL/SQL is an oracle program that runs and perform different types of task in the oracle database and it can be different types but SQL *Plus is editor or interface where write different types of SQL commands or PL/SQL programs.

How many types of constraints in the oracle, explain them?

There are several types so constraints in the oracle, Primary key, foreign key, unique key, not null, check Key.

• Not Null - It used to check null values. After applying not null key you cannot have null value in that column.
• Unique Key - It required to check duplicate values. After applying unique key, you cannot have duplicate vale in the column.
• Primary Key - Its combination of “Not Null” and “Unique key”. After applying primary key, you can insert null or duplicate in that column. And it also used as composite primary key to check combination column values, composite primary key can be more than one column it can be on two, three, four etc.
• Foreign key - It used to create relation with any parent value. After applying foreign key, you can have same value as in parent column or null. It will not accept any other values instead of parent column.
• Check Key - It used to validate given criteria in the check key. After applying this key, system will accept only given criteria.

How many types of default tablespaces in the oracle and explain them?

There are several types of default tablespaces in the oracle, SYSTEM, SYSAUX, USERS, UNDOTBS1 AND TEMP.

• SYSTEM AND SYSAUX - Store data dictionary tables.
• USERS - It stores permanent user’ object data.
• TEMP - It used to store temporary object data.
• UNDOTBS1 - It holds undo data for rollback.