Typical DBA Interview Questions and Expert Answers | SPOTO

Table-level locks lock the entire table during operations, preventing other queries from accessing the table.

When the database is created

The different types of replication include: - Master-slave replication: In this setup, one database (the master) handles all write operations, while one or more replicas (slaves) handle read operations. This type of replication is commonly used to distribute read traffic and reduce the load on the master database. It's suitable for applications where reads significantly outnumber writes, and eventual consistency is acceptable. - Master-master replication: In a master-master setup, two or more databases can handle both read and write operations. Changes made to any database are replicated to the others. This type of replication is useful in distributed environments where data needs to be writable from multiple locations. However, it introduces complexities such as conflict resolution and is best suited for applications where write conflicts are rare or can be managed effectively. - Snapshot replication: This involves taking a snapshot of the database at a specific point in time and copying it to another location. It's generally used for situations where data changes infrequently or where a periodic full copy of the data is sufficient. Snapshot replication is often used for reporting or data warehousing purposes where up-to-the-minute accuracy is not critical. - Transactional replication: This method replicates data incrementally as transactions occur. It's more sophisticated than snapshot replication because it continuously applies changes to the replica. It's ideal for scenarios requiring high availability and real-time data consistency, such as load balancing and failover setups. A table comparing the differences between master-slave and master-master replication can help explain the replication types visually: | Feature | Master-slave replication | Master-master replication | | Write operations | Writes occur only on the master node. | Writes can occur on both masters. | | Read operations | Reads can be offloaded to slave nodes. | Reads can occur on any master node. | | Use case | Used when reads outnumber writes, and eventual consistency is acceptable. | Used in distributed systems with multiple write locations. | | Conflict handling | No conflicts (since only one node writes). | Requires conflict resolution mechanisms. | | Example | MySQL Master-Slave Replication | MongoDB or Cassandra Master-Master | Ultimately, the choice of replication method depends on factors like the need for data consistency, the frequency of data changes, and the specific requirements of the application.

opatch apply: Applies patches and tracks them in the Oracle Inventory. A patch applied once won't be reapplied. opatch napply: Used to apply multiple patches in a batch, avoiding reapplying already installed patches. It is useful when applying multiple patches at once.

System databases are the default databases installed when SQL Server is installed, including Master, MSDB, TempDB, and Model. It is highly recommended that these databases are not modified or altered for smooth functioning. A user database is a database created to store data and start working with the data.

The following actions occur: DBWn (Database Writer): Writes all dirty blocks to disk as of a specific SCN (System Change Number). CKPT (Checkpoint Process): Stops updating the regular checkpoint SCN in datafile headers and instead updates the hot backup checkpoint SCN field. LGWR (Log Writer Process): Instead of logging only changes, it begins logging entire changed blocks when they are modified for the first time after the backup begins. This generates a large amount of redo, as complete block images are logged instead of just changes, ensuring data consistency during recovery.

1. Network Issues: Connectivity problems between primary and standby databases. 2. Redo Log Transmission: Delays or failures in transmitting redo logs from primary to standby. 3. Apply Lag: Delay in applying redo logs on the standby database. 4. Switchover/Failover Issues: Problems during switchover or failover operations. 5. Archive Log Management: Issues with archive log management, such as running out of space. 6. Standby Database Corruption: Corruption on the standby database, requiring repair or rebuild. 7. Configuration Issues: Misconfiguration of Data Guard parameters or settings. 8. Performance Issues: Performance problems on the primary or standby database.

SYS: 1. Owner of the database: SYS is the owner of the Oracle database and its underlying structures. 2. Highest privileges: SYS has all privileges and can perform any action in the database. 3. Core database objects: SYS owns core database objects, such as system tables, views, and packages.SYSTEM: 1. Default administrative user: SYSTEM is the default administrative user for the database. 2. High-level privileges: SYSTEM has high-level privileges, but not as extensive as SYS. 3. Database administration: SYSTEM is used for database administration tasks, such as creating users, granting privileges, and managing database objects.Key differences: – SYS is the owner of the database, while SYSTEM is an administrative user. – SYS has more extensive privileges than SYSTEM. – SYS is used for core database operations, while SYSTEM is used for administrative tasks.

Various tools include: 1.AWR(Automatic Workload Repository) 2.ADDM(Automated Database Diagnostics Monitor) 3.TKPROF 4.STATSPACK 5.OEM(Oracle Enterprise Manager)

A Database Management System (DBMS) is a software system that uses a standard method of cataloging, retrieving, and running queries on data. It ensures that data is consistently organized and remains easily accessible. Examples include MySQL, Oracle, and Microsoft SQL Server.

The steps involved in upgrading SQL Server to a new version include: planning and assessing the current environment, backing up all databases, running the upgrade advisor, performing a test upgrade, upgrading the SQL Server instance, and then validating the upgrade by checking database consistency and application compatibility.

Look for: Detail orientation and thoroughness.

When the data is big, database needs more space. It uses temp tablespace in such cases to perform sorting.

Database security is paramount in protecting sensitive data and maintaining user trust. Discuss measures like encryption, managing user privileges, implementing firewalls, and regularly updating the system. Highlight your understanding of the importance of each measure and how you have practically used them. To secure a database, I'd start with user access management, ensuring each user has appropriate permissions. Regular audits can spot any irregularities. I'd also use encryption for data both at rest and in transit for added security. Implementing a firewall can help prevent unauthorized access, while regular updates and patches can help counter known vulnerabilities.

Look for: Balancing security and accessibility.

A bind variable is a placeholder in SQL used to pass values at runtime. Example: SELECT * FROM employees WHERE emp_id = :1; Instead of writing WHERE emp_id = 101, you use :1 and pass the value later. Benefits: • Reduces hard parsing. • Improves SQL reuse in library cache. • Prevents SQL Injection. • Saves CPU and memory. Without bind variables, Oracle treats each literal as a new query, leading to more parsing and CPU usage.

During a routine security audit, I discovered a vulnerability in our database system that could potentially allow unauthorized access to sensitive data. I immediately reported the issue to the relevant stakeholders and collaborated with the security team to address it. We implemented additional access controls, tightened user permissions, and enhanced encryption mechanisms. Furthermore, I conducted training sessions to educate users about best practices for data security. By taking these proactive measures, we mitigated the vulnerability and ensured stronger security measures were in place.

Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.

A stored procedure is a set of pre-compiled SQL statements stored in the database. It can be executed multiple times, improving performance, reusability, and security by abstracting logic.

Check lsitener and tnsentries. Perform tnsping from client machine. Based on above results we troubleshoot accordingly

I use tools like Oracle Enterprise Manager, SQL Server Management Studio, or open-source options like Prometheus and Grafana. Methods include monitoring query execution times, disk I/O, CPU usage, and setting up alerts for anomalies.

Yes.

Upgrading Oracle Database from 19c to 23c generally involves these steps: 1. Pre-checks – Review Oracle 23c release notes, run preupgrade.jar, and take a full backup. 2. Install 23c Home – Download and install the 23c software in a separate ORACLE_HOME. 3. Migrate Config – Update listener/network configs for the new home. 4. Upgrade Database – Use DBUA or run catctl.pl for manual upgrade. 5. Post-upgrade tasks – Run utlrp.sql to recompile invalid objects, gather stats, and validate. 6. Testing – Confirm functionality and performance before go-live.

Checkpoints cause the transaction log to be truncated up to the beginning of the oldest open transaction (the active portion of the log). Dirty pages from the buffer cache are written to disk. Storing committed transactions in the cache provides a performance gain. SQL Server can only truncate up to the oldest open transaction, so if someone forgets to commit or rollback their transaction, the expected relief from a checkpoint may not occur.

Step 1: Check the error message Review the error message to understand the cause of the issue.Step 2: Run opatch lsinventory with verbose option Run opatch lsinventory -verbose to get detailed output and identify potential issues.Step 3: Run opatch lsinventory with clean option Run opatch lsinventory -clean to remove any corrupted or inconsistent entries from the inventory.Step 4: Run opatch lsinventory with force option Run opatch lsinventory -force to recreate the inventory from scratch.Step 5: Manually remove and recreate the inventory If the above steps fail:1. Stop all Oracle services. 2. Manually remove the inventory directory (usually $ORACLE_HOME/inventory). 3. Recreate the inventory by running opatch lsinventory.Step 6: Verify the inventory After recreating the inventory, run opatch lsinventory to verify that it is correct and up-to-date.

PURGE RECYCLEBIN.

I prevent deadlocks through careful transaction design—keeping transactions short, accessing objects in consistent order, and using appropriate isolation levels. When deadlocks occur, I use deadlock graphs to identify the conflicting processes and resource chains. In one case, I resolved recurring deadlocks in our order processing system by breaking a long-running transaction into smaller chunks and adding strategic indexes to reduce lock escalation. I also implemented retry logic in the application layer for deadlock victims, reducing user-visible errors by 95%.

When a user process fails:Failure Actions: 1. PMON (Process Monitor) is notified: PMON is notified of the failed process. 2. PMON rolls back the transaction: PMON rolls back the transaction to ensure data consistency. 3. PMON releases locks: PMON releases any locks held by the failed process. 4. PMON terminates the failed process: PMON terminates the failed process. 5. The session is terminated: The session associated with the failed process is terminated.Automatic Recovery: 1. SMON (System Monitor) performs instance recovery: If the instance crashes, SMON performs instance recovery to restore the database to a consistent state. 2. Undo records are applied: Undo records are applied to roll back any uncommitted transactions.

Fast Sync provides an easy way of improving performance in synchronous zero data loss configurations. Fast Sync allows a standby to acknowledge the primary database as soon as it receives redo in memory, without waiting for disk I/O to a standby redo log file (SYNC NOAFFIRM). This reduces the impact of synchronous transport on primary database performance by shortening the total round-trip time between primary and standby.

Database is a software which allows applications to store and retrieve data faster. It allows companies to create a three tier system where first tier is users, second is application and third tier is database.

In a project, I implemented data compression and partitioning to reduce storage costs. Challenges included balancing compression ratios with query performance and managing partition maintenance without downtime.

The legislation applies to two different types of data-handles: processors and controllers. According to the definitions provided by the General Data Protection Regulation (GDPR), a controller is the entity that determines the purpose, conditions, and means of the processing of personal data. On the other hand, the processor is an entity that processes personal data on behalf of the controller.

Data masking hides sensitive data to protect privacy while allowing realistic data in non-production environments. Methods: • Static Data Masking: Replace sensitive data in backups or copies with scrambled but realistic data using Oracle Data Masking Pack. • Dynamic Data Masking: Use Oracle Database Vault or SQL policies to mask data in real-time based on user roles. • Use built-in masking formats like random numbers, nulls, or custom functions. • Apply masking during data export or refresh for development/testing environments. • Masking ensures compliance with regulations and protects against data leaks.

Database normalization is the process of organizing the fields and tables of a database to minimize redundancy and dependency. Normalization involves dividing large tables into smaller tables and defining relationships between them to increase the coherence of data.

As a controller or processor, you're entitled to process the data. However, this doesn't mean that all employees can access it – data should be available only for those in the company whose position requires them to have those rights. There are different levels of access; while some people might have full access with the rights of modifying or erasing data, others will only be able to view data.

User Process: Initiated by user for connection. Server Process: Executes user SQL requests.

--- First check on standby which blocks got corrupted. SQL> Select * from v$database_block_corruption ; ----Cancel the Recovery: alter database Recover managed standby database Cancel; --Restore the datafile, SQL> Restore datafile 9 FORCE from service 'PRIM_DB' ; -- Start MRP: SQL> alter database Recover managed standby database using current logfile disconnect from session;

Using multiple channels. Enabling Block Change Tracking (BCT). Applying compression techniques. Using optimized storage solutions. Maintaining RMAN metadata properly.

To find the data files associated with a particular tablespace, such as SYSTEM , you can query theDBA_DATA_FILES view and filter by the tablespace name. Here's the query:This will return the data files associated with the SYSTEM tablespace, along with their file paths and sizes in MB.

REMAP_SCHEMA: Used in Data Pump to import objects from one schema to another. REMAP_TABLE: Used to import table with a new name.

SQL Server Authentication should be utilised when working with Excel on SQL Server.

To back up a SQL Server database, you can use the BACKUP DATABASE command, specifying the name of the database to back up and the location to store the backup file.

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;

When new versions of database servers come out, how do you prefer to learn how to use 'em? Ideally, I want to hear a DBA say they build their own server under the desk when the beta comes out, and start hammering it and getting their arms around it long before it gets released. Not everybody has that much time, though – they might be stretched to the breaking point at their current job, with barely enough time to get their work done, let alone train on new versions. In that case, I like to level-set them by saying, “You're coming to work here because we're not that kind of shop. I want you to keep your knowledge current. How much time per month do you need to keep current, and how would you do it?”

SQL is Structured Query Language, a standard language for managing and manipulating relational databases. It's used for querying data, inserting, updating, and deleting records across different systems.

Look for: Specific examples of tuning and measurable results.

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.

“End of REDO” is the last archived redo sequence or SCN that has been confirmed as shipped and applied to the standby during a DR drill — essentially the synchronization boundary. It defines the point in redo that the standby has processed. For DR drill you often note the LATEST_APPLIED_SCN or LAST_APPLIED_SEQ to ensure no gaps. You can check: -

AIR can handle 2,000+ interviews per month without quality degradation, and the scoring consistency is remarkable.

Look for: Conflict resolution and customer service skills.

Deadlocks are detected using SQL Server Profiler or Extended Events, looking for deadlock graphs. Resolution involves analyzing the graph, optimizing queries, shortening transactions, and checking isolation levels.

1. Logical Storage Structures (Organizes Data)Tablespaces → Logical storage units containing datafiles. Segments → Storage for tables, indexes, undo, and temporary data. Extents → Groups of contiguous blocks allocated to segments. Data Blocks → Smallest storage unit in Oracle (Default: 8 KB). 2. Physical Storage Structures (OS-Level Storage)Datafiles → Store actual database data. Redo Log Files → Store transaction logs for recovery. Control Files → Store database metadata (SCN, file locations). Archive Logs → Backup of redo logs for recovery. Temporary Files → Used for sorting and temp operations. Parameter Files (SPFILE/PFILE) → Store database initialization parameters

I analyze current usage patterns and growth trends to estimate future storage and performance requirements. By implementing monitoring tools, I ensure ongoing capacity assessment and make proactive adjustments to prevent resource constraints.

SQL Server supports three main types of data replication: Snapshot, Transactional, and Merge. Snapshot replication takes a complete copy of the data at a moment in time. Transactional replication starts with a snapshot but then sends ongoing changes as they occur, which is great for near real-time synchronization. Merge replication allows changes on both sides of the publication to be synchronized when connected, which is common in client-server scenarios where clients might be offline periodically.

Yes, I have worked on Oracle Cloud Infrastructure (OCI). In fact, I have experience migrating on-premises databases to OCI, configuring OCI databases, and managing database security and networking within the OCI environment.Some specific areas I've worked on in OCI include: – Setting up and configuring Oracle Autonomous Database – Migrating databases to OCI using Oracle Cloud Infrastructure Database Migration – Configuring OCI networking and security, including VCNs, subnets, and security lists – Managing OCI database instances, including patching, upgrading, and scalingI'm excited to leverage my OCI experience to help drive cloud adoption and optimization in your organization!

Look for: Practical experience with backup and recovery planning.

Use tools like SHOW FULL PROCESSLIST, EXPLAIN, and query profiling to identify and troubleshoot long-running queries and bottlenecks.

Implementing high availability in SQL Server 2022 typically involves setting up SQL Server Always on Availability Groups, which provide failover support for multiple databases. It requires preparing the environment with Windows Server Failover Clustering on the underlying system. Configuring Availability Groups includes establishing primary and secondary replicas, setting up listener for client connections, and configuring synchronization modes—synchronous for zero data loss or asynchronous for performance optimization. Regular monitoring and testing failover scenarios are vital to ensure the system meets the required recovery objectives.

| Truncate command | Delete command | |---|---| | Truncate is a DDL command | Delete is a DML command | | The TRUNCATE command locks the whole table for the deletion of all specified entries | the DELETE command is carried out with the assistance of a row lock. When the instruction is executed, every row in the tablespace is marked for elimination. | | The TRUNCATE command locks the whole table for the deletion of all specified entries whereas, By using the truncate command we can delete all of the rows in a table | the DELETE command is in charge of eliminating only a single row at a time, rather than removing all of the rows in the table at once. When a row is deleted, its record in the transaction log is logged. | | The TRUNCATE TABLE function aids in the removal of all information by making the memory pages free that are used to store the table data. Only the page unassigning is noted in the binary log after completion. Buttons are not triggered since no changes are going to place. TRUNCATE is a Definition Data Language command, therefore it does not affect data instead, it modifies the design and description of the table | The DELETE is activated when both NSTEAD OF and AFTER are activated for the DELETE commands in the event of the DELETE command (if present). The DELETE command is a part of DML commands which helps to delete the data present in a row(row-wise). |

No, you cannot drop the SYSTEM tablespace. Oracle does not allow this since it contains critical database objects.

SELECT cust_id, COUNT(*) AS n_occurences FROM dim_customer GROUP BY cust_id HAVING COUNT(*) > 1;

PuTTY: An SSH client used to connect to a remote server. SQL*Plus: A tool used to connect to and manage Oracle databases.

Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.

ARCHIVE LOG LIST.

To retrieve all columns from a table named "employees," you can use the following SQL query: SELECT * FROM employees;

I implement a combination of full, differential, and transaction log backups to ensure comprehensive data protection. Once, I successfully restored a critical database within an hour after a server crash, minimizing downtime and data loss.

A missing or corrupt datafile can cause the database or tablespace to be unusable. To resolve: • Identify the missing or corrupt datafile from alert logs or errors. • If backup is available, restore the datafile using RMAN. • Recover the datafile using RECOVER DATAFILE command. • If the datafile belongs to a read-only or offline tablespace, bring it online or drop the tablespace if data is not needed. • If no backup is available, try to use DATAFILE OFFLINE DROP as last resort, but this results in data loss. • Check filesystem and storage devices for hardware issues. • Always verify backup and recovery plans to avoid critical data loss.

Performance can be improved by indexing, query optimization, database normalization, updating statistics, avoiding unnecessary cursors, and configuring database files and filegroups optimally.

Both primary and unique key enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where unique key creates a non-clustered index by default. Primary key doesn't allow NULLs, but unique key allows one NULL only.

NOT NULL, PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK

Physical Standby - Logical Standby - Snapshot Standby - Far Sync Standby

Flashback Logs are generated automatically as the database changes, capturing historical data needed for Flashback Database operations.

COMMIT Actions: 1. Ends the transaction: The COMMIT statement ends the current transaction. 2. Makes changes permanent: All changes made during the transaction are made permanent and visible to other users. 3. Releases locks: Any locks held by the transaction are released. 4. Generates a redo record: A redo record is generated to record the changes made during the transaction. 5. LGWR (Log Writer) writes redo records to redo logs: The LGWR process writes the redo records to the redo logs. 6. SCN (System Change Number) is incremented: The SCN is incremented to reflect the new transaction. 7. Transaction is removed from the undo tablespace: The transaction is removed from the undo tablespace.

SELECT name, state, total_mb, free_mb FROM v$asm_diskgroup;

A database view is a virtual table based on a query's result. It doesn't store data itself but displays data retrieved from one or more underlying tables. Views simplify complex queries by allowing users to select from a single view rather than writing a complicated SQL query. Views also enhance security by restricting user access to specific data fields without giving them access to the underlying tables. For example, a view might only expose certain columns of sensitive data, such as a customer's name and email, but not their financial information.

A privilege is permission to do something in the database, like: • System privilege: Create table, create user, etc. • Object privilege: Select, insert, update on a specific table.A role is a collection of privileges that can be assigned to users in one step. For example:CREATE ROLE dev_role; GRANT CREATE TABLE, SELECT ANY TABLE TO dev_role; GRANT dev_role TO alice; This way, instead of giving 10 privileges to each user, you create a role with those 10 privileges and assign the role. Easier to manage and more secure!Difference: • Privilege: Direct permission. • Role: A named group of privileges.

In a previous role, I encountered a situation where our production database experienced severe performance degradation, impacting our customer-facing application… The first step I took was to immediately notify the stakeholders and set up a bridge call to keep communication open. I then accessed the database and used tools like SQL Server Profiler to identify long-running queries and resource-intensive processes. After identifying a query that was causing a deadlock due to a missing index, I implemented a quick fix by adding the appropriate index, which immediately improved the performance. Following this, I reviewed the query execution plan and restructured the SQL queries to optimize performance further. Additionally, I scheduled a maintenance window to thoroughly analyze and optimize the database without impacting users. I documented the issue, resolution steps, and the lessons learned to improve our incident response process for future scenarios. This experience taught me the importance of having a systematic approach to troubleshooting and the need for proactive performance monitoring.

To find the total number of records in a table, the COUNT keyword is used.

A stored procedure is a prepared SQL code that you can save and reuse. Instead of writing the same code over and over again, you can create a stored procedure, and call it to execute the SQL code contained within it.

Any specified object in a database that would be used to keep or dataset is referred to as a database object. Views, clusters, tables, sequences, indexes, and synonyms have come under database objects.

Maintaing databases for the oracle based applications. Monitoring and healthcheck of all the DB servers. Backup and Recovery Management User and Security Management Applying critical patch updates (CPUs) and PSU patches. Performance Tuning of the DB servers and instances. Creating scripts for automating the DB procedure. Perform backup and DB restoration activity. Running SQL scripts given by the developers for the incident and change management. Troubleshooting the DB for production issues. Preparing audit reports for compliance requirements.

A trigger is a special kind of stored procedure that automatically executes in response to specific events on a particular table or view. Triggers can be used to enforce business rules, maintain data integrity, and perform auditing tasks.

SESSION_PRIVS

If standby has a gap (missing archived redo logs), steps: - Identify gap: - Ensure network shipping is working on primary: ARCH logs being shipped,LOG_ARCHIVE_DEST_n parameters correct. - Transfer missing archives: - If archive logs still exist on primary or storage, copy them to standby's archive location and register them: - - Or use rman to fetch from primary: - Use FAL (Fetch Archive Log) configuration:- On standby: - - Start managed recovery with RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE; - If gap too large or archives missing, consider reinstatement: Use RMAN> duplicate target database for standby or take a fresh backup from primary and restore on standby, or use incremental backups andrecover until sequence . - Check transport lag and apply lag: If long apply lag, tune apply by increasing parallel apply ( APPLY_PARALLELISM ) or resources. - Validate: v$managed_standby andv$archive_dest_status .

Configure the master and slave servers, enable binary logging, and set up replication using the CHANGE MASTER TO statement.

Before installation, kernel parameters like SHMMAX, SHMMNI, SEMMNI, and FS_FILE_MAX must be configured to ensure optimal resource allocation. The installation process involves using runInstaller to deploy the Oracle software.

In the event of database corruption, a DBA should: Identify the corrupted data by running DBCC CHECKDB. Attempt to repair the corruption using DBCC REPAIR_ALLOW_DATA_LOSS, REPAIR_FAST, or REPAIR_REBUILD. If repairs fail, restore the database from the latest clean backup. Investigate and resolve the root cause to prevent future occurrences.

Best practices include testing for data integrity, performance, security, and backup recovery. I implement them by creating test cases that simulate real-world scenarios, using automated testing tools, and conducting regular regression tests.

Central/Global Inventory: 1. Shared across multiple Oracle homes: The central inventory is shared across multiple Oracle homes on a system. 2. Stores metadata about all Oracle installations: It stores metadata about all Oracle installations, including patches, versions, and components. 3. Located outside of the Oracle home: The central inventory is typically located outside of the Oracle home directory. 4. Managed by the Oracle Universal Installer (OUI): The central inventory is managed by the OUI.Local Inventory: 1. Specific to a single Oracle home: The local inventory is specific to a single Oracle home and stores metadata about that specific installation. 2. Stores metadata about the Oracle home: It stores metadata about the Oracle home, including patches, versions, and components. 3. Located within the Oracle home: The local inventory is typically located within the Oracle home directory. 4. Managed by OPatch: The local inventory is managed by OPatch, a utility used for patching and maintaining Oracle software.

Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.

A deadlock happens when two processes block each other, waiting for resources. I identify the processes involved using SQL Server Profiler or Extended Events. Then, I tune queries, change locking order, or use deadlock priorities to prevent it.

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.

Two methods are there.Automatic gap resolution: This is done by log transport service. When there is a mismatch between currently transfered redo with that of last received log in standby, then RFS will request the missing log sequences from primary via arch-rfs hearbeat ping. No special setting is required. Fetch archive log( FAL Method): FAL_SERVER – specifies the tns service database from where the missing archive logs need to be fetched. When a archive is shipped to standby , it gets registered in the standby controlfile. When log apply service detects a gap , it sends a request to fal server to resend the missing logs.

Oracle lets you define password policies to enforce strong security. These are part of a PROFILE which controls: • Password length • Password expiration • Failed login attempts before locking • Password reuse restrictions Example:CREATE PROFILE secure_profile LIMIT FAILED_LOGIN_ATTEMPTS 5 PASSWORD_LIFE_TIME 30; ALTER USER john PROFILE secure_profile; If a user enters the wrong password 5 times, Oracle locks the account. As a DBA, you can unlock it using:ALTER USER john ACCOUNT UNLOCK; This helps protect the database from hacking or brute-force attacks

No.

In a update statement, we need to old value as user might rollback the transaction. Hence, undo tablespace comes into picture. The user data and a free undo block is copied into LRU list. These block are then copied to PGA where data swapping happens. Redo entries are generated and the dirty blocks are placed in write list. LGWR writes redo entries and then DBWR writes dirty blocks to database.

Database partitioning involves dividing a large table into smaller, more manageable pieces called partitions. Each partition is stored separately and can be queried individually, which can significantly improve performance and manageability, especially for very large datasets. Partitioning is particularly useful when dealing with large volumes of data that are frequently accessed based on specific criteria, such as date ranges or geographic regions. I would use partitioning when a table grows so large that query performance starts to degrade. For instance, in a table storing historical transaction data, I might partition the data by month or year. This allows queries that target specific time periods to access only the relevant partition instead of scanning the entire table, thus improving performance. Additionally, partitioning can make maintenance tasks, like archiving or purging old data, more efficient since these operations can be performed on individual partitions rather than the whole table. Here's a table comparing the different types of partitioning in case you're asked follow-up partitioning questions: | Partitioning type | Description | Example use case | | Range partitioning | Divides data into partitions based on a range of values in a column. | Partition a sales table by order_date (e.g., one partition per year). | | List partitioning | Partitions data based on a specific list of values. | Partition a customers table by country or region. | | Hash partitioning | Distributes data across partitions using a hash function. | Distribute rows evenly for load balancing across multiple partitions. | | Composite partitioning | Combines two or more partitioning strategies (e.g., range + list). | Partition by order_date (range) and then by region (list). |

If you're a large organization, this question is perfect for you. One thing is working for a small business that has a small data center. A whole different thing is having to deal with a data center of a large corporation. If the applicant has only worked with small data centers, then you're facing the risk of them not knowing how to handle yours or facing constant problems. It's important to learn more about the sizes of their previous companies, the number of servers, and their environments.

To create an operator in SQL Server, first connect to and expand the SQL Server Agent; configure general and notification settings using SQL Server Management Studio if available.

To ensure database security and integrity, I employ multiple layers of protection. This includes implementing robust access controls, such as role-based permissions and strong authentication mechanisms. I regularly update and patch the database systems to address any vulnerabilities. Additionally, I monitor for suspicious activities and unauthorized access attempts, perform regular security audits, and implement encryption techniques to protect sensitive data both at rest and in transit.

Redo logs are overwritten by LGWR in cyclic order. Archive logs are backup or copy of redo logs in a separate location.

You have to tell the interviewer that you will ask what type of SQL the client could support, as the SQL must suit the organization's size. You will check whether a backup has been generated or not. After that, if the old database is not compatible with the new one, you will upgrade the old version to an intermediate one and then upgrade the current server to resolve the issue.

I led a project to migrate a legacy database to a cloud-based solution, ensuring zero downtime and data integrity. By using tools like AWS Database Migration Service and thorough pre-migration testing, we successfully transitioned over 1TB of data without any issues.

Look for: Commitment to continuous learning and professional development.

In mount stage, only control file is read but its contents are not physically validated. Oracle will not check the physical existence of data files.

DMV called sys.dm_wait_stats. The answer „a DMV exists but I cannot recall it's name“ is not bad also.

Active Data Guard allows a physical standby to be open in read-only mode while applying redo in real-time. Benefits include offloading reporting, automatic block corruption repair, and RMAN backups from the standby. It requires an additional Oracle license.

A database query is an action that is closely related to some create, read, update, delete (CRUD) function. It is a request to access data from a database to manipulate it or retrieve it. There are several approaches to database query – using query strings, writing a query language, or using a QBE like REST or GraphQL.

Using pfile: Add multiple control file paths in control_files parameter in the pfile, copy the control file to new locations, and restart the database. Using spfile: Modify the control_files parameter using ALTER SYSTEM and follow the same steps to copy files before restarting.

Stored procedure

The main types are DDL (CREATE, ALTER, DROP), DML (SELECT, INSERT, UPDATE, DELETE), DCL (GRANT, REVOKE), and TCL (COMMIT, ROLLBACK).

SQL Profiler is the SQL Server utility used to trace traffic on the SQL Server instance. Traces can be filtered to narrow down the transactions that are captured and reduce the overhead incurred for the trace. The trace files can be searched, saved off, and even replayed to facilitate troubleshooting.

WITH monthly_revenue AS ( SELECT FORMAT(CAST(created_at AS date), 'yyyy-MM') AS year_month, SUM(value) AS total FROM sf_transactions GROUP BY FORMAT(CAST(created_at AS date), 'yyyy-MM') ) SELECT year_month, ROUND((total - LAG(total) OVER (ORDER BY year_month)) / LAG(total) OVER (ORDER BY year_month) * 100.0, 2) AS revenue_diff_pct FROM monthly_revenue ORDER BY year_month ASC;

Exporting a consistent image of a large number of tables

The mysql.server script is used to start, stop, and restart the MySQL server.

When an MV is refreshed atomically:1. Locks are acquired: The MV is locked exclusively to prevent concurrent modifications. 2. Refresh is executed: The refresh process is executed, which may involve deleting and re-inserting data. 3. Changes are committed: If the refresh is successful, the changes are committed, and the locks are released.Atomic refresh ensures: – Consistency: The MV remains consistent, even in the event of failures during the refresh process. – Data integrity: The MV data is not partially updated, reducing the risk of data inconsistencies.To enable atomic refresh, use the ATOMIC_REFRESH clause when creating or altering the MV: CREATE MATERIALIZED VIEW mv_name REFRESH COMPLETE ON DEMAND ATOMIC_REFRESH AS SELECT * FROM table_name;

Do your interviewees understand that a navigational database gives administrators access to data when they define a specific path? Do they know that this database gives access to objects mainly by using references from other objects?

To handle database deadlocks, I would first try to identify the root cause of the deadlock by reviewing the database logs and deadlock graphs, which provide detailed information about the involved transactions and the resources they are contending for. Once identified, there are several strategies I can employ to resolve and prevent deadlocks: - One approach is to ensure that all transactions access resources in a consistent order, which reduces the chance of circular wait conditions. Additionally, keeping transactions short and reducing the amount of time locks are held can minimize the likelihood of deadlocks. - Another strategy is to use the appropriate isolation level for transactions; for instance, using READ COMMITTED instead of SERIALIZABLE when full isolation isn't necessary can reduce the lock contention. - In cases where deadlocks are frequent, I suggest implementing a deadlock retry mechanism in the application logic. This would catch the deadlock exception and automatically retry the transaction after a short delay. The key is identifying and mitigating the underlying causes to prevent future occurrences.

The myisamchk utility is used to check, repair, and optimize MyISAM tables.

Table directory, row directory and data block header

Controls resource limits and password policies for users.

JOIN types in SQL Server include INNER JOIN (returns matching rows from both tables), LEFT JOIN (returns all rows from the left table and matching rows from the right), RIGHT JOIN (returns all rows from the right table and matching rows from the left), FULL OUTER JOIN (returns all rows when there is a match in either table), and CROSS JOIN (returns the Cartesian product of both tables).

Look for: Conflict resolution and teamwork.

Control file has MAXDATAFILES parameter. If this number is exceeded, you cannot add more data files.

I start by checking the execution plan to find bottlenecks. I also review indexes, outdated statistics, and missing indexes. Sometimes, I rewrite the query or break it into smaller parts.

Standard query language (SQL) is used in relational databases. These are vertically scalable and use predefined schema. Use NoSQL in distributed or non-relational databases. These databases are horizontally scalable and use dynamic schemas for unstructured data. Though SQL is more versatile, it's also more restrictive.

Constraints in Oracle ensure data integrity. Common constraints include:NOT NULL: Prevents null values in a column. UNIQUE: Ensures all values in a column are unique. PRIMARY KEY: Uniquely identifies a row (combines NOT NULL and UNIQUE). FOREIGN KEY: Ensures a valid reference to a primary key in another table. CHECK: Enforces a specific condition on column values. Example: Master Table (Stores primary key data) CREATE TABLE PINCODE ( AREA VARCHAR2(30), PINCODENUM NUMBER PRIMARY KEY ); INSERT INTO PINCODE VALUES ('Miyapur', 500049); INSERT INTO PINCODE VALUES ('Ameerpet', 500084); Child Table (References the primary key) CREATE TABLE EMPLOYEE ( EMPNAME VARCHAR2(30), EMPID NUMBER UNIQUE, ADDRESS1 VARCHAR2(10) CHECK (ADDRESS1 = 'Hyderabad'), ADDRESS2 VARCHAR2(20), PINCODE NUMBER CONSTRAINT PIN_FK FOREIGN KEY (PINCODE) REFERENCES PINCODE (PINCODENUM) );

Database normalization eliminates data redundancy and ensures data integrity by organizing data into related tables. I typically design to Third Normal Form (3NF) as a starting point, which eliminates transitive dependencies and reduces update anomalies. However, I've strategically denormalized in specific scenarios. For example, in a reporting database, I created a denormalized customer summary table that combined data from five normalized tables. This reduced report query time from 12 seconds to under 1 second, which was acceptable since the reports only needed to be updated nightly.

When a row is too large that it cannot fit in a block, then it will end up using consequent blocks which lead to the concept of Row Chaining. It can be avoided by updating the storage parameters to an appropriate value

The primary difference between the WHERE and HAVING clauses is when and how they filter data. The WHERE clause is used to filter rows before any grouping occurs, and it applies to individual rows in the table. It is used with SELECT, UPDATE, and DELETE statements. On the other hand, the HAVING clause is used to filter groups of rows created by the GROUP BY clause. It's used to set conditions on aggregate functions like COUNT, SUM, AVG, etc., which cannot be used directly in the WHERE clause. This practical example shows how filtering occurs with the WHERE and HAVING clauses in SQL: Table: Sales | SaleID | Product | Category | Quantity | TotalAmount | | 1 | Laptop | Electronics | 5 | $5000 | | 2 | Headphones | Electronics | 15 | $750 | | 3 | Book | Books | 10 | $150 | Using WHERE: Filters rows before grouping. SELECT Category, SUM(TotalAmount) FROM Sales WHERE TotalAmount > 1000 GROUP BY Category; | Category | TotalSales | | Electronics | $5000 | Using HAVING: Filters after grouping. SELECT Category, SUM(TotalAmount) FROM Sales GROUP BY Category HAVING SUM(TotalAmount) > 1000; | Category | TotalSales | | Electronics | $5750 |

Step 1: Create a new connection 1. Open SQL Developer and click on “New Connection” (or press Ctrl+N). 2. Select “Oracle” as the connection type.Step 2: Enter connection details 1. Enter the hostname, port, and service name of the CDB (Container Database). 2. Enter the username and password for the CDB.Step 3: Specify the PDB 1. In the “Connection” tab, click on the “Advanced” button. 2. In the “Advanced” window, select the “Oracle Multitenant” tab. 3. Enter the name of the PDB you want to connect to in the “Pluggable Database” field.Step 4: Connect to the PDB 1. Click “Connect” to establish the connection. 2. You should now be connected to the specified PDB.Alternatively, if you are already connected to CDB, you can switch to a specific PDB using: -

You can create it by CREATE PFILE FROM SPFILE query.

Clustered and Non-Clustered 1.B-Tree index 2.Bitmap index 3.Unique index 4.Function based index 5. Implicit index and explicit index Explicit indexes are again of many types like simple index, unique index, Bitmap index, Functional index, Organizational index, cluster index.

First, I follow industry blogs, publications, and forums such as SQLServerCentral, DatabaseJournal, and Stack Overflow to stay informed about new developments and best practices. I also participate in webinars, online courses, and certifications to deepen my understanding of emerging technologies like NoSQL databases, cloud database services, and automation tools. For example, I recently completed a series of courses on SQL Server for Database Administrators on DataCamp. Attending conferences and local meetups is another way I stay connected with the community, learn from experts, and exchange knowledge with peers. Additionally, I experiment with new tools and techniques in a test environment to evaluate their potential benefits for our organization. This proactive approach helps me continuously enhance my skills and stay ahead in the field.

- Stop further patching; document current state and errors (collect logs). - Check if patch tool created a rollback: Many patches have opatch rollback . - Use OPatch rollback: - If backup was taken before patch (recommended): Restore Oracle Home from backup (file-level). Steps: - Shutdown DBs and listeners that use that ORACLE_HOME. - Restore filesystem (rsync/cp or snapshot) of ORACLE_HOME from pre-patch backup. - Reconfigure environment if required. - - For GI/Grid: follow Oracle Support notes — you may need to rollback GI using opatch and re-run root scripts. - Recreate inventory entries if they were altered or corrupted. - Re-run datapatch if SQL changes partially applied —datapatch -verbose might show if SQL changes were applied to the database;datapatch -rollback is not available — to revert SQL-level changes you may need to use PSUs contrived rollback scripts or restore DB from backup if data dictionary changes are irreversible. - If database objects or SQL changes applied, restore from RMAN backup or use Flashback Database to point-in-time before patch (if flashback enabled). - Validate: start DB, run tests, opatch lsinventory should be consistent. - Open SR with Oracle Support if rollback fails. Always: maintain pre-patch backups (ORACLE_HOME, DB full backup, and inventory).

The mysqlcheck utility is used to check, repair, optimize, and analyze MySQL tables.

Truncate will release the space. Delete won't. Delete can be used to delete some records. Truncate can't. Delete can be rolled back. Delete will generate undo (Delete command will log the data changes in the log file where as the truncate will simply remove the data without it. Hence data removed by Delete command can be rolled back but not the data removed by TRUNCATE). Truncate is a DDL statement whereas DELETE is a DML statement. Truncate is faster than delete.

An Active-Passive cluster is a failover cluster configured so that only one cluster node is active at any given time. The other node, called the Passive node, is always online but in an idle condition, waiting for a failure of the Active Node. Upon failure, the Passive Node takes over SQL Server Services and becomes the Active Node.

We have tablespace utilization scripts scheduled on each server. The script triggers email whenever a tablespace utilization crosses above 80%. Depending on the alert and space on server, we add space to tablespaces.

Auditing in Oracle helps track who did what and when in the database. It's used for: • Security checks • Compliance (like GDPR, SOX) • Investigation after a breach Oracle has 3 main audit types: 1. Standard Auditing – Logs activities like logins, table access. 2. Fine-Grained Auditing (FGA) – Audits specific rows or columns. 3. Unified Auditing – A newer, more efficient auditing method.Example to audit user logins: AUDIT CREATE SESSION; To check the audit trail: SELECT * FROM DBA_AUDIT_TRAIL; You can audit: • Commands (e.g., DROP TABLE) • Object access (e.g., SELECT on sensitive tables) • User sessions

A trigger is a special type of stored procedure that automatically executes when a specific data modification event, like an INSERT, UPDATE, or DELETE, occurs on a table or view. They're often used to enforce complex business rules, maintain data integrity across related tables, or for auditing purposes. While powerful, I believe they should be used cautiously as they can impact performance and make debugging more complex if not designed and tested carefully.

No, you cannot use SYNC and AFFIRM with ARCH in the LOG_ARCHIVE_DEST_n parameter.

Normalization is structuring database tables to reduce redundancy and improve data integrity. It prevents anomalies (insert, update, delete) and makes the database more efficient and easier to maintain.

Monitoring practices include setting up alerting thresholds for critical metrics, tracking slow queries, regularly reviewing resource consumption, and employing automated performance baseline tracking to quickly detect anomalies.

INSERTED: 100 rows, DELETED: 100 rows, UPDATED: that is made up (does not exist)

I stay updated by following industry blogs and forums, attending webinars and conferences, participating in professional communities, reading official documentation and release notes, and pursuing certifications. I also experiment with new features in test environments and collaborate with peers to share knowledge and best practices.

I follow a structured migration process starting with thorough testing in a staging environment that mirrors production. I create detailed rollback plans and always take full backups before beginning. For my last major upgrade from SQL Server 2016 to 2019, I used the Database Migration Assistant to identify compatibility issues, performed the upgrade during a planned maintenance window, and validated all applications post-migration. I also coordinated with development teams to test critical business processes. The upgrade completed 30 minutes ahead of schedule with zero data loss.

Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.

You can run the query SELECT @@VERSION; in SQL Server Management Studio. It gives complete version details including service packs and editions.

Stored procedures and functions are both database objects that contain T-SQL code, but they differ in usage: stored procedures can perform DML operations and return multiple result sets, while functions must return a single value or table and cannot modify data. Functions are used in queries, whereas stored procedures are called explicitly.

SQL Server Management Studio (SSMS) is an integrated environment for managing any SQL infrastructure, from SQL Server to Azure SQL Database. SSMS provides tools to configure, monitor, and administer instances of SQL, as well as to deploy, monitor, and upgrade the data-tier components used by your applications, and build queries and scripts.

Fill Factor is a setting applicable to indexes in SQL Server. The fill factor value determines how much data is written to an index page when it is created or rebuilt. By default, the fill factor value is set to 0. You can find and change it from Management Studio by right-clicking the SQL Server, choosing properties, then Database Settings.

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.

Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing a database into two or more tables and defining relationships between the tables. The goal is to isolate data so that additions, deletions, and modifications can be made in just one table.

Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.

The UPDATE STATISTICS command forces a recalculation of query optimization statistics for tables or indexed views. While SQL Server updates statistics automatically, manually updating them ensures the query optimizer has the most current information about data distribution. This helps the optimizer choose the most efficient execution plan for queries, which can significantly improve performance. It's a key tool for tuning slow-running queries.

20

A clustered index sorts and stores the data rows in the table based on the key values, making data retrieval faster for range queries. A non-clustered index, on the other hand, creates a separate structure to store the index and includes a pointer to the data rows, which is useful for improving the performance of specific queries.

SQL Agent is the job scheduling mechanism in SQL Server. Jobs can be scheduled to run at a set time or when a specific event occurs, and can also be executed on demand. SQL Agent is most often used to schedule administrative jobs such as backups.