If you're looking for Oracle DBA Interview Questions for Experienced or Freshers, you are at the right place. There are a lot of opportunities from many reputed companies in the world. According to research, Oracle DBA has a market share of about 0.7%.
So, You still have an opportunity to move ahead in your career in Oracle DBA Development. Mindmajix offers Advanced Oracle DBA Interview Questions 2021 that help you in cracking your interview & acquire a dream career as Oracle DBA Developer.
Types of Oracle DBA Interview Questions and Answers
- For Freshers
- For Experienced
- Advance Level
Oracle DBA Vs Oracle Developer
|Feature||Oracle DBA||Oracle Developer|
|Key Roles||Managing Databases||Development(Coding)|
|Type of Tasks||Streamlined||Wide Range|
|Work Environment||Database maintenance||Database development|
|Area of work||Backend database management||Front-end development|
|Few other tasks||Backup, recovery, server connectivity, etc||Coding, designing UI, etc|
If you would like to Enrich your career with a Database Administrator(DBA) and get Oracle certified professional, then visit Mindmajix - A Global online training platform: “Oracle DBA Online Training” Course. This course will help you to achieve excellence in this domain.
Oracle DBA Interview Questions For Freshers
Q1. List four possible ways (direct or indirect) to execute an SQL query against an Oracle Database?
- Using the SQL*Plus command-line tool. With this tool, you can directly execute SQL commands.
- Using a GUI (Graphical User Interface) tool like SQL Developer. You can directly execute SQL commands with such tools.
- Using Oracle Enterprise Manager. This is an indirect way of executing an SQL query. When you perform certain operations with Oracle Enterprise Manager, they are converted to SQL queries implicitly and these SQL queries are executed against the database.
- Writing your own program. This is not a conventional way of executing your queries but actually, it is widely used. Any web or windows program that uses an Oracle database at the backend, executes SQL queries. These programs are written using a programming language like .NET or JAVA and they use a driver to connect to the database.
Q2. What Is SQL*Plus? How can one acquire it and what kind of operations can be performed with it?
- SQL*Plus is a command-line tool developed by Oracle Corporation.
- It is freely distributed. It is shipped with Oracle client installations or Oracle database installations as a default. So, if Oracle client or Oracle database software is installed on a computer, you can find it under the “$ORACLE_HOME/bin/” directory. The name of the executable is “surplus” on Linux systems and “sqlplus.exe” on Microsoft Window Systems.
- You can connect to an Oracle database with it. Once connected, you can execute Oracle commands or SQL queries against the connected database. SQL*Plus has also its own commands for formatting the output so that you can display the results in a meat way.
Q3.Who is responsible to update the indexes?
Ans. Oracle automatically maintains and uses indexes and when any change is made in the table data Oracle automatically distributes it into relevant indexes.
Related Article:Managing the Redo Log
Q4. In our organization, we’re using an Oracle database whose version is 184.108.40.206. Explain what each digit shows?
Ans. “11”: This first digit shows the major database version. Oracle usually publishes a major release once a 4 year. This digit is usually followed by a character describing the nature of the release. For example: 9i (internet), 10g (grid), 11g (grid), 12c (cloud).
“2”: This second digit shows the maintenance release number of the software. Oracle publishes the major release as maintenance release 1 and then usually publishes a second maintenance release during the lifetime of the software. New features are added to database Software with maintenance releases.
“o”: This third digit is Fusion Middleware Number. This will be o for database software.
“4”: This fourth digit is called Component-Specific Release Number and it shows the path set update that was applied to the software. Patch set updates are published 4 times a year by Oracle and as you apply them to your database software, this fourth digit advances.
Q5. In Oracle terminology, what is a Synonym?
Ans.A synonym is an identifier that can be used to reference another database object in a SQL statement. The types of database objects for which a synonym may be created are a table, view, sequence, or another synonym.
Q6. Your client said that he forgot the password for the “SYSTEM” user of his database and he no longer could connect. How would you recover this admin password?
- If there are other users who have “DBA” privileges, you can connect with those users and change the password for the “SYSTEM” user. The users who have DBA privileges have the privilege to change any user’s password. This option is the easiest method but this may not be the case in all scenarios.
- If there are no other users with “DBA” privileges then the only way to connect to the database is to connect using operating system privileges. The Oracle software runs under a specific user at the operating system. This user is usually named “oracle”. Also, there needs to be a user group that the “oracle” user belongs to. This user group is usually named “dba”. The operating system users who belong to the “dba” group can connect to the database with “SYSDBA” privileges. So, you need to ask the system administrator to logon to a server as an “oracle” user or any user who belongs to this “dba” group. Once logged on to the operating system, you can connect to the database locally using operating system authentication with SYSDBA privileges. After connecting to the database, you can change the reset the password for this system user.
Related Article: Granting Roles And Privileges In Oracle DBA
Q7. What is a password file and why is it needed?
Ans. Passwords for database users are stored in the data dictionary of the database. When a user wants to log into the database, the username and password provided by the user are checked against the values stored in the database.
If the username and password match, the user is granted access to the database. The data dictionary is part of the database and it will be accessible as long as the database is open. The passwords for administrators are stored in the dictionary as well.
When the database is closed, the data dictionary will be inaccessible. There needs to be a mechanism for administrators to login into the database even when it is closed because it is one of the administrator’s tasks to start up a down database. A password file is a separate operating system file that is stored on a disk outside of the database.
The username and password for the users who have SYSDBA or SYSOPER privileges are stored in it. Administrators who have those privileges are authenticated using this password file even when the database is down.
Q8. You want to find out how many users are defined in the password file and what privileges those users have. How would you accomplish this?
Ans. You need to query the “v$pwfile_users” view to get information about the existing users in the password file. Execute the SQL query below:
Sql>SELECT * FROM v$pwfile_users;
The query above will return four columns for each user in the password file. The column names are USERNAME, SYSDBA, SYSOPER, and SYSASM.
- The USERNAME column shows the username of the user in the password file.
- The SYSDBA column shows whether the user has SYSDBA privileges or not.
- The SYSOPER column shows whether the user has SYSOPER privileges or not.
- The SYSASM column shows whether the user has SYSASM privileges or not.
Q7. What would be the main responsibilities of an Oracle DBA in an organization?
Ans. The main duty of an Oracle DBA is to keep the Oracle Databases of the organization up and running. This may involve installing and configuring a database from scratch.
On a running system, the DBA will be the only privileged person who can shut down and startup the database.
The DBA will create new users and manage the privileges of each user.
He will take regular backups to ensure that data is safe. In case of a disaster, he will be responsible for restoring the database from backups. He will have to do monitor the space usage and do capacity planning for the database.
He will be responsible for enforcing security policies. He will have to monitor database activities. He will have to tune the database so that it works at an acceptable speed.
He is expected to follow the latest patches and apply them when applicable.
Related Blog: Managing The Undo Tablespace - Oracle DBA
Q8. How does an Oracle DBA role differ from an Oracle Developer role in an organization? Are there any similarities between these two?
Ans. An Oracle developer is mainly responsible for developing backend applications. They do data modeling according to business rules. The design tables, create indexes and other types of constraints. They are expected to know SQL and PL/SQL. They develop procedures using this language.
However, the Oracle developers are not expected to administer the database software itself.
On the other side, an Oracle DBA’s main duty is to administer the database which involves tasks like doing maintenance to keep the databases up and running, taking backups, enforcing security policies, etc. DBAs are not primarily assigned to develop code.
DBAs are supposed to have a good knowledge of SQL and PL/SQL like a developer as these are also required for administering the database.
According to the structure of the organization, DBAs might also be assigned development tasks or at least assist the developers where necessary.
Q9. There are 10 identical servers and you want to install Oracle Database on each of them. What would you use to automate the installation process?
Ans. If you are going to do batch installations, it is best to do it with Oracle Universal Installer in silent mode. For single installations.it is best to start the installer in “interactive mode” and set installation options at each window.
However, in batch installations, this will take longer. You need to do the installations in “silent” mode with a “response file”. In a silent installation, you start the Oracle Universal Installer from a command prompt and specify the location of the “response file”.
The installation files and the response file can be shared among the servers via NFS so that you won’t have to copy the setup files to each server.
Q10. You want to create a response file to speed up the installation of databases. How would you prepare a response file?
Ans. A response file is a plain text file, where options to create a database are stored. It is possible to create it manually from scratch but that would take longer and would be erroneous.
Installation media comes with a template response file. It is rather easier to customize it manually. This file also contains notes about the parameters.
However, the easiest and most reliable way to create a response file is by using Oracle Universal Installer. If you start the installer in “record” mode, every option you choose at each step is automatically recorded in a response file in the correct format. After the installer completes in “record” mode, you’ll have a complete response file with all the options set in it.
Q11. When creating a database with SQL script, what would you specify in the script?
Ans. It is also possible to create a database via an SQL script. In this script I would specify:
- Name of the database
- The password of the SYS user
- The password of the SYSTEM user
- At least three online redo log groups. I would also specify at least two members for each redo log group.
- Character set and the national character set of the database.
- Location and size of the SYSTEM and SYSAUXtablespace. These tablespaces will be used for holding system data.
- I would specify a normal tablespace to use as the default tablespace of the database.
- I would specify a temporary tablespace to use as the default temporary tablespace of the database.
- I would specify an undo tablespace.
Q12. What makes up an Oracle Instance?
Ans. An instance is made up of a shared memory region on RAM called System Global Area (SGA) and background processes.
The system's global area is a shared memory, which means it can be accessed by multiple processes.
This holds data that is required by the instance to operate.
The background processes are operating system processes and each process has a specific responsibility in the instance.
The System Global Area and background processes are created when the instance is “started”. When the instance is “shut down”, the processes are killed and the shared memory region is “released” back to the operating system.
Do you know: Oracle Apps DBA Interview Questions
Q13. What constitutes an Oracle Database?
Ans. An Oracle database resides on disk and this is permanent. It is composed of files that are stored on a disk. These files can be categorized into three types:
- DataFiles: These files hold “user” data or “system” data. Any data that belongs to an application is an example of “user” data. The “data dictionary” of the database is an example of “system” data.
- OnlineRedo Log Files: These files hold the “change” records. Any change, which will be made to a data file, is first written to online redo log files
- ControlFiles: These files are relatively small but they are essential for a database. They hold information about the physical structure of the database like the location of data files, online redo log files, etc.
Q14. Which tools can you use to start up an Oracle database?
Ans. 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 to you the “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.
Q15. During the startup of a database, at which order does Oracle software search a parameter file?
Ans. A parameter file holds instance parameters that govern how an instance operates. In order to start up an instance, Oracle needs to locate this file.
The search order is as below: /DBS/spfile.ora – This is a server parameter file and this is the first place that oracle will look for. SID- is the service identifier of the instance.
<$ORACLE_HOME-/dbs/spfile.ora -If Oracle cannot find the file in the first location, it will search this file. This is again a server parameter file.
/dbs/init.ora – This is a parameter file and it is plain text. If Oracle cannot find the two files listed above, it will search for this file. This is the last location to search.
Q16. At what stages does an instance pass while starting up?
Ans. You can start up a database with the modes below:
- NOMOUNT: This is the first stage. In this mode, the instance is started.
- MOUNT: This is the second stage. In this mode, the instance is started and the database is mounted. However, the database is not open so you cannot still access data. However, you can perform several maintenance tasks at this stage.
- OPEN: This is the final stage. The database is open and all the data is accessible. The default open mode is “read/write” which means you can read data or write to it. However, it is also possible to open it in a “read-only” mode where you can only read data but cannot change it.
Start learning: Oracle Performance Tuning Interview Questions
Q17. You want to do maintenance on your database but during the maintenance period, you don’t want any user to be able to connect to the database. How would you accomplish this?
Ans. When a database is open, any user with the “CREATE SESSION” privilege can make a connection. However, it is possible to open the database in “restricted” mode.
When a database is open in restricted mode, only users with the “RESTRICTED SESSION” privilege can make a connection to the database. By default, only DBAs have the “RESTRICTED SESSION” privilege and it should not be granted to regular users.
Opening a database in “restricted” mode is a good way to prevent regular users from accessing the database during maintenance.
Q18. Your database is open. You don’t want to interrupt currently connected users but you want to temporarily disable further logons. What would you do to achieve this and how would you revert the database back to its normal state after that?
Ans. I would put the database in “restricted mode”. While in restricted mode, only users with the “RESTRICTED SESSION” privilege can make a connection. I would run the below command to put the database in restricted mode:
Sql> alter system enable restricted session;
After executing this command regular users won’t be able to loggon into the database. Once I want to revert the database to normal, I execute this command:
Sql>alter system disable restricted session;
Q19. What are the types of shutdown modes of an Oracle database?
- Normal: In this mode, no new connections are allowed and the database is closed after all the sessions disconnect themselves.
- Immediate: No new connections are allowed and the existing active transactions are rolled back. Changes made by an active transaction are lost in this option.
- Transactional: No new connections are allowed and Oracle waits until all active transactions are completed.
- Abort: This happens immediately however the database is not shut down cleanly. The database will have to perform instance recovery next time it is started. This option should not be used in regular activities.
Q20. The data files of your database reside on a storage system. You want to take a snapshot of the storage so that you can use it for backup purposes. You also want to ensure that no data is written to data files while the snapshot is being taken. Is it possible to accomplish this while the database is open?
Ans. Yes, it is possible to stop all I/O activity while the database is open. Normally, when a database is open, there will be constant I/O to online redo log files or data files. Even if the database is idle, there is no guarantee that the database will not write anything to files during the snapshot.
However, if you “suspend” the database, Oracle will halt I/O operations to these datafiles until it is reverted back to normal mode. So, you should “suspend” the database, take the snapshot of the disk and then put the database back in normal mode immediately after that.
Q21. What kind of information can be given while creating a sequence?
- Sequence Name: This is the name of the sequence. It should be unique inside the schema.
- Start With: This is the number that the sequence will start.
- Increment By: This number shows how much the sequence will increment at each move.
- No cycle: This determines whether the sequence will start from the beginning once it reaches the end.
- Nocache: This determines how much the next sequence number will be cached in SGA. Nocache means no next sequence will be cached.
Related Article: Undo Retention In DBA - Oracle DBA
Q22. You want your database to start automatically, after a reboot of the server. How would you do that?
Ans. In the default configuration, the Oracle database will not automatically start after the server reboots. You’ll have to start it manually after each reboot. You’ll usually want it to start automatically. There are two methods to accomplish this:
- Using Oracle Restart: “Oracle Restart” is a feature of Oracle High Availability Service (OHAS). You need to install “Grid Infrastructure” to enable the “Oracle Restart” feature. Using “Oracle Restart” is the recommended way.
- Using Your Own Script: It is also possible for you to write your own “bash” script to start the database and place that script in the startup of the operating system.
Q23. Which components of your database environment can be protected by an “Oracle Restart” configuration?
- 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 the Oracle Restart feature.
- Oracle Notification Services (ONS): This is another Oracle component that can be protected by Oracle Restart.
Q24. Explain the difference between “shared server” architecture and “dedicated server” architecture?
Ans. When a user connects to a database, he sends SQL queries to the database to execute. These SQL queries are executed by a “server process” and the result is returned back to the user. In the “dedicated server” architecture, the instance will create one server process for each connected user.
That process will be “dedicated” to that user and will only serve that client.
However, in “shared server” architecture, a single server process will serve multiple clients. In shared server architecture, the total memory consumption will be less.
However, certain operations like DBA activities can only be performed on a dedicated server.
Oracle DBA Interview Questions For Experienced
Q25. Explain how “shared server” architecture works?
Ans. In shared server architecture, the clients connect to a “dispatcher” process. This dispatcher is responsible for delivering the SQL requests to the “request queue”.
The shared server process monitors the request queue. When they find an incoming request, they execute this SQL query and place the results in the response queue. The request queue and the response queue reside in the system global area.
The dispatcher processes also monitor the response queue. When it receives a result, they deliver the result to the relevant client.
In this architecture, there will be multiple shared server processes and dispatcher processes.
Q26. What are the instance parameters that are used for configuring shared server architecture?
- DISPATCHERS: A string value that is used to configure dispatchers.
- SHARED_SERVERS: Minimum number of shared server processes that will be present in the server. Also, this number of shared servers is created during startup.
- MAX SHARED_SERVERS: This parameter determines the maximum number of shared server processes that can run at the same time.
- SHARED_SERVER SESSIONS: This parameter specifies the maximum number of sessions that can exist at the same time using a shared server connection.
- CIRCUITS: This parameter determines the maximum number of virtual circuits that can exist in the system.
Q27. Explain how the “Database Writer” process works?
Ans. There can be multiple database background processes. They are named “DBWn” in the operating system. This process is responsible for writing “dirty” buffers to the disk. When a server process wants to update a data block, it reads the block from disk to buffer cache if the block is not already in the cache and then updates the copy in the cache. The modified database block in the buffer cache is called a “dirty” block.
Oracle Data Structures & SQL
Q28. Why is the index used?
Ans. The indexis used to increase the performance of retrieval. We can make use of one or more rows in order to make the index. The index can increase the performance of retrieval and slows down the performance of insertion.
Q29. A user is logged on to a Linux server as root where the Oracle database is running. The Oracle is installed at “/uo 1/app/oracle/product/220.127.116.11/dbhome” and the name of the SID is “ORCL”. The user wants to connect to the database locally using operating system authentication with SYSDBA privileges. Show the command that the user has to execute?
- First, he needs to switch to “oracle” user:# su – oracle
- Later he needs to set the required environment variables:
$ export ORACLE_SID=ORCL$ exportORACLE_HOME=/uo1/app/oracle/product/18.104.22.168/dbhome
- Finally, he needs to execute the following command to connect to the database:
$/uol/app/oracle/product/22.214.171.124/dbhome/bin/sqlplus/ as sysdba
Q30. What is the definition of the table in Oracle?
Ans. The table is the first level of the physical unit in the database. Oracle uses tables of a database to store data into rows and columns. The table is the first level of the physical unit in the database.
Q31. What do you mean by the view and what are its types?
Ans. The viewis a type of virtual table and there is a query attached to every view in order to identify specific rows and columns of the table. Views are read-only as well as read-write.
[Related Article:Revoking User Privileges and Roles - Oracle DBA]
Q32. In Oracle terminology, what do you mean by tablespace?
Ans. The tablespace is a Logical Storage Unit used to group related logical structures together. It is the logical structure where all the objects of the database will be grouped.
Q33. When does the SYSTEM tablespace get created?
Ans. In Oracle, every database has a tablespace called SYSTEM and it is automatically created when the database is created. It also contains the data dictionary table for the whole data.
Q34. What is the relationship between tablespace and datafiles?
Ans. Each tablespace is divided into one or more data files and one and more tablespace(s) are created for each database.
Q35. How do we use the materialized view?
Ans. Materialized views are objects that have reduced sets of information that have been summarized, grouped, or aggregated from base tables. They are typically used in data warehouses or decision support systems.
[Related Article: Data Warehouse Interview Questions]
Q36.You’re at a client’s office and you are expected to solve a problem in their database. The client is not sure about their database version and you want to find out the version of their existing database. Describe three different methods you can use to find the version of database software?
- You can find the version by connecting to the database with SQL*Plus. SQL*Plus will print the name and the version of the database software once you’re connected to the database. A sample output will look like below:
Oracle Database 11g Enterprise Edition Release 126.96.36.199.0 – Production
With the Partitioning, OLAP, Data Mining, and Real Application Testing options”
- You can find the version by querying the “vs version” view. You can execute the SQL query below to find the version of the database:
SQL: SELECT * FROM v$version;A sample output would look like below:BANNEROracle Database 11g Enterprise Edition Release 188.8.131.52.0 – ProductionPL/SQL Release 184.108.40.206.0 – ProductionCORE 11.2.o.4.o ProductionTNS for Linux: Version 220.127.116.11.0 – ProductionNLSRTLVersion 18.104.22.168.0 – Production
- You can find the version from Enterprise Manager. If you logon to Oracle Enterprise Manager, the version of the database software will be listed on the home page under the “General” web part.
Q37. Different types of synonyms are?
Ans. Synonym types are private and public.
Q38. What do you understand by public synonym?
Ans. A public synonym does not belong to any schema. In other words, when any database user can access it, it is called a public synonym.
Q39. What do you understand by a private synonym?
Ans. A private synonym is one that does belong to a specific schema. In other words, when only the owner can access it, it is called a private synonym.
Q40. What are the advantages of synonyms?
Ans. A synonymis used to mask the original name and owner of an object and provides public access to an object.
Q41. What is the sequence?
Ans. A sequence generates a serial list of unique numbers for numerical columns of database tables. We can use the sequence on columns for data where we want to insert data in a sequential manner.
Q42. What do you understand by a private database link?
Ans. A privatedatabase link is created for a specific user. It is only used when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner’s views or procedures.
Visit here to learn Oracle DBA Training in Bangalore
Q43. What do you understand about the public database link?
Ans. A database link is a schema object in one database to access objects in another database. When you create a database link with a Public clause it is available for access to all the users.
Q44. What do you mean by row chaining?
Ans. Row Chaining occurs when the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG, LONG RAW, LOB, etc. Row chaining in these cases is unavoidable.
Q45. What is the definition of extent?
Ans. An extent is a set of contiguous blocks allocated in a database. In the Oracle database program, the first set of contiguous blocks, set up automatically when a segment is created, is called the initial extent.
After the initial extent has been filled, the program allocates more extents automatically. These are known as the next extents.
The total number of extents that can be allocated in a database is limited by the amount of storage space available, or in some cases, by the program used.
Q46. Explain the advantages of using view?
Ans. The view helps provide security, presentation of data from a different perspective, and store complex queries.
Q47. What do you mean by datafile?
Ans. An Oracle datafile is a big unit of physical storage in the OS file system. One of many Oracle data files is organized together to provide physical storage to a single Oracle tablespace.
The data file is used to store tables and indexes allocated to the database. Every database consists of one or more data files.
Q48. Explain the properties of data files?
Ans. Each data file can only be associated with only one database and once it is created it can not change its size.
Q49. What do you mean by redo log?
Ans. The most crucial structure for recovery operations is the redo log, which consists of two or more pre-allocated files that store all changes made to the database as they occur. Every instance of an Oracle Database has an associated redo log to protect the database in case of an instance failure.
Oracle DBA Scenario Based Interview Questions
Q50. The main function(s) of the redo log is?
Ans. Redo log’s main function is to store all changes made to the database as they occur.
Q51. What are the contents of the control file?
Every Oracle database has a control file. A control file is a small binary file that records the physical structure of the database and includes:
- The database name
- Names and locations of associated datafiles and online redo log files
- The timestamp of the database creation
- The current log sequence number
- Checkpoint information
Q52. What are the advantages of the control file?
Ans. The control file must be available for writing by the Oracle database server whenever the database is open. Without the control file, the database cannot be mounted and recovery is difficult. You might also need to create control files if you want to change particular settings in the control files.
Q53. What is the definition of SQL?
Ans. SQL is a database computer language designed for managing data in relational database management systems (RDBMS) and originally based upon relational algebra. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.
Q54. What is the use of the SELECT statement?
Ans. The SELECT statement is used to select the set of specific values from a table in a database depending on the various conditions specified in a SQL query.
Q55. How can you compare a part of the name rather than the entire name?
Ans. In order to compare parts, we use the LIKE operator which acts like a regex engine for a database.
Q56. What is the keyword to get distinct records from a table?
Ans. SELECT DISTINCT allows the user to select the distinct values from a table in a database.
Q57. In order to get sorted records from a table, what is the keyword?
Ans. ORDER BY keyword is used for sorting the results. It returns the sorted results to your program.
Q58. In order to get total records from a table, what is the keyword?
Ans. To find the total number of records in a table, the COUNT keyword is used.
Q59. What is the definition of GROUP BY?
Ans. GROUP BY keyword is an aggregate function such as SUM, MULTIPLE, etc and without this function sum for each individual group value can not be calculated.
Q60. Explain the difference between “dropping a table”, “truncating a table” and “deleting all records” from a table?
- Dropping a table means dropping the invalid indexes. It deletes table definition and records both,
- Truncating means deleting invalid data values automatically. Internally the database marks the table as empty and the deleted records are not recorded in the transaction log.
- Deleting all records, deletes all records but records all details in a Transaction log file.
Q61. Different types of SQL statements are?
There are five types of SQL statements
- Data Definition Language
- Data Manipulation Language,
- Transactional Control,
- Session Control and
- System Control.
Q61. What is the definition of the transaction?
Ans. Oracle supports transactions as defined by the SQL standard. A transaction is a sequence of SQL statements that Oracle treats as a single unit of work. As soon as you connect to the database, a transaction begins. Once the transaction begins, every SQL DML (Data Manipulation Language) statement you issue subsequently becomes a part of this transaction. A transaction ends when you disconnect from the database, or when you issue a COMMIT or ROLLBACK command.
How can I be a good DBA in Oracle? ›
- Step 1 Do some research about the role. ...
- Step 2 Study the basics of database administration. ...
- Step 3 Take training courses. ...
- Step 4 Learn about related technologies. ...
- Step 5 Look for database developer positions. ...
- Step 6 Take advanced training. ...
- Step 7 Get certified.
Database administrator, specifically in Oracle database, always is a very high priority and key area on the job position. As per hype in the current market, the administrative job in the database, specifically in Oracle is already increased 11%, which is very higher than other available jobs.Which cloud technology is best for Oracle DBA? ›
If you trust and believe me, Oracle Cloud (Database Cloud Service – DBCS) is a much better option for DBAs & Apps DBAs (Once you learn and become an Expert in Oracle Cloud for DBA's then, by all means, pick AWS IaaS).How many interview rounds are there in Oracle? ›
How many rounds are conducted in an Oracle interview for a Software Engineer? 3-4 Rounds: Online Coding Round(One Round), Technical Round (2 Rounds), HR Round Find out More.What is password file in Oracle? ›
Password files are located in the directory ORACLE_HOME \database and are named PWD sid . ora , where SID identifies the Oracle Database instance. Password files can be used for local or remote connections to Oracle Database.Is Oracle DBA easy to learn? ›
Oracle is fundamentally just like SQL Server and every other relational database system. Its database architectural principles are the same and it operates with SQL (Structured Query Language), plus Oracle's own PL/SQL extensions. It's relatively easy to learn — as long as you have a good handle on Linux and SQL.Is Oracle DBA and SQL DBA same? ›
The Oracle DBAs present were hard-core DBAs while the SQL Server DBAs were mostly developer DBAs or originally Windows system administrators. This was reflective of the complexities of Oracle versus Microsoft.Is SQL DBA a good career? ›
Yes, SQL DBA is a relatively good career.
Structured query language database administrators (SQL DBAs) are in high demand and yield high salaries. Their work is dynamic and challenging, though it can be stressful. SQL DBAs manage an organization's data using the database language SQL.
The average DBA salary in India is around ₹ 5 Lakh Per Annum. Their salary starts from nearly ₹ 3 Lakh Per Annum and goes up to ₹ 15 Lakh Per Annum. It's one of the most sought-after roles, and there's a massive demand for database administrators in the country as well.Is DBA job stressful? ›
“A Database Administrator has a special responsibility for a component that is critical to the success or failure of a business: its DATA. This makes being a DBA very challenging and stressful given its many responsibilities.
Which DBA is best? ›
|1||Alliance Manchester Business School|
|2||Richard J. Fox School of Business and Management at Temple University|
|3||SDA Bocconi School of Management|
|4||Pepperdine Graziadio Business School|
To future proof their careers, DBAs can expand their skills intelligently on other technologies like Blockchain or AI; become a data architect, data engineer, master data analytics, be a data scientist, database migration specialist or database security specialist.Does Amazon use Oracle? ›
Amazon Web Services supports Oracle databases and offer enterprises a number of solutions for migrating and deploying their enterprise applications on the AWS cloud.Does DBA have future? ›
According to the BLS numbers, DBA employment at cloud service providers will grow by 26 percent in the decade ending in 2024. The shifting emphasis to cloud databases hasn't had a great impact on the skills companies look for in their new DBAs.Is it difficult to get a job in Oracle? ›
The hiring process is long and cumbersome, but once at Oracle, you have many opportunities for changing positions and groups. Your salary is however unlikely to keep up with the market. This is a large company and it is hard at times to find out who is doing what.How long is Oracle interview process? ›
The interview process takes 2-4 weeks on average. Stage 1: Initial phone screen by HR. Stage 2: A technical phone interview where interviewers ask questions about the candidate's background/working experience and technical (coding) questions.What is the salary for freshers in Oracle? ›
Fresher Software Engineer salaries at Oracle can range from ₹4,16,793-₹14,15,193. This estimate is based upon 2 Oracle Fresher Software Engineer salary report(s) provided by employees or estimated based upon statistical methods. See all Fresher Software Engineer salaries to learn how this stacks up in the market.What is the max size of password file in Oracle? ›
The values for the minimum (no less than six characters) and maximum (up to 16 characters) length of a password for a user who has administrator privileges.What is init ora file in Oracle? ›
ORA file which contains suggested parameter settings for data warehouses and data marts. See your Oracle operating system-specific documentation for the default locations and filenames for these parameter files. The INIT. ORA file is what the Oracle Server reads for its parameter information upon startup.Can we change SYS password in Oracle? ›
To change the password for user SYS or SYSTEM : Using the SQL Command Line, connect to the database as SYSDBA . See "Logging In and Connecting to the Database as SYSDBA" for instructions.
Does Oracle DBA require programming? ›
As a DBA, particularly in the development space, having a working knowledge of C# or Java might not hurt, but you probably won't spend a lot of time actually coding in them. You will probably get more mileage from whatever scripting tools are used on your platform, although a lot of systems expose .What are Oracle skills? ›
- Sql Server.
In short, both Oracle and SQL Server are powerful RDBMS options. Although there are a number of differences in how they work “under the hood,” they can both be used in roughly equivalent ways. Neither is objectively better than the other, but some situations may be more favorable to a particular choice.Which is better Oracle DBA or SQL DBA? ›
In terms of DB size they deal with, oracle is way more ahead of SQL. Most companies with higher db size opt for Oracle DBA. And oracle has more configuration as well as it is more sophisticated.What language does Oracle Database use? ›
Structured Query Language (SQL)
SQL is the ANSI standard language for relational databases. All operations on the data in an Oracle database are performed using SQL statements.
Most, including MS SQL Server and Oracle Database, use SQL, although Microsoft uses Transact SQL (T-SQL) and Oracle uses Procedural Language SQL (PL/SQL). According to Segue Technologies: "Both are different 'flavors' or dialects of SQL, and both languages have different syntax and capabilities.What is Oracle DBA role? ›
A database administrator, or DBA, is responsible for maintaining, securing, and operating databases and also ensures that data is correctly stored and retrieved. In addition, DBAs often work with developers to design and implement new features and troubleshoot any issues.What does a DBA do all day? ›
It's been said that the database administrator (DBA) has three basic tasks. In decreasing order of importance, they are: protect the data, protect the data, and protect the data.What is the scope of DBA? ›
Scope of the career
Database administrators (DBAs) use specialized software to store and organize data. The role may include Capacity planning, installation, configuration, design of database, migration, performance monitoring, security, troubleshooting, as well as backup and data recovery.
Overall employment of database administrators and architects is projected to grow 9 percent from 2021 to 2031, faster than the average for all occupations. About 11,500 openings for database administrators and architects are projected each year, on average, over the decade.
What is a DBA in the IT world? ›
The database administrator (DBA) is the person who manages, backs up and ensures the availability of the data produced and consumed by today's organizations via their IT systems. The DBA is a critically important role in many of today's IT departments, and by extension, their organizations overall.Which is better database administrator or data analyst? ›
Data Administrator is less of a technical role and more of a business role with some technical knowledge. This role is also known as Data Analyst. So, it is mostly a high level function which is responsible for the overall management of data resources in an organization.Are DBAs happy? ›
As it turns out, database administrators rate their career happiness 2.8 out of 5 stars which puts them in the bottom 19% of careers.Is DBA easy job? ›
The responsibilities of DBAs are crucial to the success or failure of an organization or company because they work with their organization or company's data. Therefore, becoming a DBA is difficult and stressful as you will have to shoulder many responsibilities.How many hours do database administrators work? ›
Database Administrators usually work in comfortable, well-lit offices. They may also visit client sites, where users interact with the computer system. Most work 40 hours a week, but some jobs may require additional hours to meet deadlines.How many types of DBA are there? ›
Within larger organizations, DBA responsibilities typically are split into separate types of roles. Beyond general-purpose, the primary roles include system DBA, database architect, database analyst, application DBA, task-oriented DBA, performance analyst, data warehouse administrator and cloud DBA. System DBA.What is the future of Oracle database? ›
Oracle DBA career will be grooming very fast in the next 10 years in India and overseas. There are lots of big telecoms, banking and finance companies are using Oracle database. Remote DBA support is also growing very fast. Oracle introduces so many new features in the latest Oracle 12c rel 2.What is the role of DBA write 10 points? ›
DBA is held responsible and accountable for logical, physical design, external model design, and integrity and security control. DBA implements DBMS and checks database loading at time of its implementation. DBA enhances query processing by improving their speed, performance and accuracy.Who is the CEO of DBA? ›
Gulzar Singh Kalsi - MD & CEO - DBA Consultants Pvt. Ltd.What skills do you need to be a DBA? ›
- Meticulous attention to detail.
- A logical approach to work.
- The ability to prioritise tasks.
- Problem-solving skills.
- Good organisational skills.
- Communication and interpersonal skills.
What can we learn after Oracle DBA? ›
- 1# Cloud DBA.
- 2# Integration/Cloud Database Migration.
- 3# DevOps / Site Reliability Engineer cum DBA.
- 4# Big Data DBA.
- 5# Data Engineer.
Amazon's consumer business says it has turned off its final Oracle database, after migrating a massive 75 petabytes of internal data stored on nearly 7,500 Oracle databases to a range of its own AWS services.Why are people moving away from Oracle? ›
Today, many companies are moving away from legacy databases such as Oracle and SQL Server. Their reasons are many. With their strong legacy in on-premise databases, some companies are choosing to leave Oracle and SQL Server in favour of cloud-based databases to support new cloud-first business models.Is Oracle DBA a good career? ›
Database administrator, specifically in Oracle database, always is a very high priority and key area on the job position. As per hype in the current market, the administrative job in the database, specifically in Oracle is already increased 11%, which is very higher than other available jobs.Which cloud technology is best for Oracle DBA? ›
If you trust and believe me, Oracle Cloud (Database Cloud Service – DBCS) is a much better option for DBAs & Apps DBAs (Once you learn and become an Expert in Oracle Cloud for DBA's then, by all means, pick AWS IaaS).Is there scope in Oracle database? ›
There is no scope for Oracle Databases anymore because the industry has researched and come up with better technologies. These technologies include database services on cloud like for example the AWS cloud computing.What are the roles and responsibilities of Oracle DBA? ›
- Installing Oracle software.
- Creating Oracle databases.
- Performing upgrades of the database and software to new release levels.
- Starting up and shutting down the database.
- Managing the database's storage structures.
- Managing users and security.
An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. A database server is the key to solving the problems of information management.How does RMAN backup work? ›
RMAN uses a media manager API to work with backup hardware. A user can log in to Oracle RMAN and command it to back up a database. RMAN will then copy the files to the directory specified by the user. By default, RMAN creates backups on disk and generates backup sets rather than image copies.
What is SCN and checkpoint in Oracle? ›
the system change number (SCN) is Oracle's clock - every time we commit, the clock increments. The SCN just marks a consistent point in time in the database. A checkpoint is the act of writing dirty (modified blocks from the buffer cache to disk. The database ALWAYS has transactions going on, ALWAYS.What is L1 L2 L3 support in SQL DBA? ›
L1=Junior/fresher dba, having 1-2 year exp. L2=Intermediate dba, having 2+ to 4 year exp. L3=Advanced/Expert dba, having 4+ to 6 year exp..What are daily activities of DBA? ›
- Creating and maintaining database standards and policies.
- Supporting database design, creation, and testing activities.
- Managing the database availability and performance, including incident and problem management.
- Administering database objects to achieve optimum utilization.
DBA is held responsible and accountable for logical, physical design, external model design, and integrity and security control. DBA implements DBMS and checks database loading at time of its implementation. DBA enhances query processing by improving their speed, performance and accuracy.What type of DB is Oracle? ›
Oracle Database is an RDBMS. An RDBMS that implements object-oriented features such as user-defined types, inheritance, and polymorphism is called an object-relational database management system (ORDBMS).Is Oracle a DB or DBMS? ›
Oracle database services and products offer customers cost-optimized and high-performance versions of Oracle Database, the world's leading converged, multi-model database management system, as well as in-memory, NoSQL and MySQL databases.How many layers does Oracle Database have? ›
How many layers does the Oracle database have? the Oracle database have 2 layers.What are the 3 types of backups? ›
There are mainly three types of backup are there: Full backup, differential backup, and incremental backup. Let's take a look at each types of backup and its respective pros and cons.What is Level 0 and Level 1 backup? ›
A level 0 incremental backup, which copies all blocks in the data file, is used as a starting point for an incremental backup strategy. A level 1 incremental backup copies only images of blocks that have changed since the previous level 0 or level 1 incremental backup.How many types of backups are there in Oracle? ›
There are two types of backups: image copies and backup sets. An image copy is an exact duplicate of a datafile, control file, or archived log.
What is Smon process in Oracle? ›
SMON (System MONitor) is an Oracle background process created when you start a database instance. The SMON process performs instance recovery, cleans up after dirty shutdowns and coalesces adjacent free extents into larger free extents. SMON wakes up every 5 minutes to perform housekeeping activities.What is Ckpt process in Oracle? ›
The checkpoint process (CKPT) is responsible for writing checkpoints to the data file headers and control file. Checkpoints occur in a variety of situations. For example, Oracle Database uses the following types of checkpoints: Thread checkpoints.What is V Archived_log? ›
V$ARCHIVED_LOG displays archived log information from the control file, including archive log names. An archive log record is inserted after the online redo log is successfully archived or cleared (name column is NULL if the log was cleared).