不想錯過任何事？

通過認證考試的技巧

最新考試新聞和折扣資訊

由我們的專家策劃和更新

是的，請向我發送時事通訊

查看其他面試題
1
參考答案
LEFT, RIGHT, FULL
2
參考答案
Unified Auditing consolidates all audit types into a single infrastructure, improving performance and manageability. To enable: • For new installations of Oracle 12c and later, Unified Auditing is enabled by default. • For earlier versions or upgrades, you might need to enable the unified audit trail by running the enable script as SYSDBA. Configuration: • Use CREATE AUDIT POLICY to define what actions to audit (e.g., logins, object access). • Enable policies with AUDIT POLICY policy_name;. • View audit records in UNIFIED_AUDIT_TRAIL view. Unified Auditing supports fine-grained, user, and system auditing in one place.
職涯加速

考取認證，讓履歷脫穎而出。

數據分析顯示，持有 IT 認證的從業者年薪平均比求職者高出 26%。在 SPOTO，您可以同時備考認證與準備面試，加速職涯成長。

1 100% 通過率
2 2 週題庫練習
3 通過認證考試
全球認可 100 萬+ 已認證 邊工作邊學習
制定學習計劃
3
參考答案
WITH ADMIN OPTION
4
參考答案
Steps to recover a dropped table: • Check if Flashback Table is enabled; if yes, use FLASHBACK TABLE to restore it. • If not, use RMAN to perform point-in-time recovery or restore from backup. • You may also use Export/Import if recent exports exist. • Inform users and coordinate downtime as needed. • Verify table integrity and dependent objects after recovery.
5
參考答案
There is a way, But only if flashback is enabled. If flashback is enabled , then we can get the current scn from the new primary database and flashback the old primary(new standby) upto that scn.
6
參考答案
I would perform regular audits and tests, at least quarterly, to ensure that all security measures are effective and up-to-date.
7
參考答案
SQL Server supports three types of data replication: snapshot replication, transactional replication, and merge replication. Snapshot replication distributes data exactly as it appears at a specific moment in time. Transactional replication continuously distributes changes from a publisher to subscribers. Merge replication allows multiple sites to make changes independently and then merge them into a single consistent dataset.
8
參考答案
I start by understanding the Recovery Point Objective (RPO) and Recovery Time Objective (RTO) for the system. Based on that, I plan full backups, transaction log backups, and implement high-availability features like AlwaysOn or failover clusters. Regular testing of recovery steps is critical.
9
參考答案
You can start up a database with the modes below:
10
參考答案
Look for: Specific optimization strategies and measurable outcomes.
11
參考答案
To handle data loss, I first assess the scope and impact of the loss by reviewing logs and backups. I then initiate a recovery process using the most recent full backup, followed by differential and transaction log backups to restore data to the point of failure. I also troubleshoot the root cause to prevent future occurrences and communicate the recovery status to stakeholders under pressure.
12
參考答案
Look for: Experience with migrations, problem-solving skills, and ability to handle data integrity.
13
參考答案
The interviewer will ask you this question to assess how you would handle a database migration and what you would do if any problems occurred. Since data migrations present challenges and there's always a risk data will become lost or corrupted, this is an important topic to cover. You should talk through the precautions you take before the migration to help prevent problems, specifically putting proper failover and recovery processes in place. Also, you can mention how migrating data in smaller increments rather than on large migration is the best way to minimize data loss. You also need a system for validating the migrated data. This allows errors to be detected and managed faster before continuing the migration process. While it won't eliminate loss, it will significantly lower the risk.
14
參考答案
Users can connect to SQL Server using Excel by right-clicking Excel, selecting Data from the menu, clicking connections, and adding their connection, they can browse the options available before selecting New Source, as described herein.
15
參考答案
Obsolutely no becoz once the datafiles is formatted into 8k we cannot change the database block sizes , if you need, you have to create fresh database with new block size and restore from backup or import.
16
參考答案
The Data Dictionary is a collection of tables and views maintained by Oracle to store metadata about the database. It includes: • Information about tables, indexes, columns • User accounts and privileges • Storage structures • Constraints, triggers, and sequences It is stored in the SYSTEM tablespace and updated automatically by Oracle when you create, modify, or drop objects. Common views: • USER_ views: Info for current user (e.g., USER_TABLES). • ALL_ views: Info about objects accessible by user (e.g., ALL_OBJECTS). • DBA_ views: Full metadata for DBAs (e.g., DBA_USERS). The data dictionary is essential for Oracle to function correctly and is used heavily in scripting, automation, audits, and troubleshooting.
17
參考答案
Central Inventory: A global repository that tracks all Oracle installations on a server. Local Inventory: Specific to a single Oracle home and contains patching details for that installation.
18
參考答案
AIR conducts enterprise-scale AI voice interviews and delivers structured scorecards to your ATS automatically.
19
參考答案
AIR screens candidates 24/7, conducts initial interviews in 16 languages, and time-to-fill dropped from 45 to 12 days for hiring 500+ nurses per quarter.
20
參考答案
Startup modes: NOMOUNT, MOUNT, OPEN. Shutdown modes: CLOSE, DISMOUNT, SHUTDOWN.
21
參考答案
SELECT COUNT(*) AS pending_claims FROM claims WHERE FORMAT(submitted_date, 'yyyy-MM') = '2021-12' AND acceptance_date IS NULL AND rejection_date IS NULL;
22
參考答案
A private database 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.
23
參考答案
Oracle Manged Files enables us to create tablespaces without providing file names and locations. But the problem is the naming convention of the files. No, my environment does not use OMF feature.
24
參考答案
We can use the below data dictionary tables to monitor the space allocations:; 1.DBA_FREE_SPACE 2.DBA_SEGMENTS 3.DBA_DATA_FILES
25
參考答案
A SQL agent performs the job scheduling function. You can set jobs to run at a specific time or when a particular event occurs. In most cases, SQL agents are used to schedule repetitive administrative jobs. One example of this is a daily database backup.
26
參考答案
- An INNER JOIN returns only the rows with a match between the two tables based on the join condition. - A LEFT JOIN returns all the rows from the left table and the matched rows from the right table; if there is no match, NULL values are returned for the columns from the right table. - A RIGHT JOIN is similar to a LEFT JOIN, but it returns all the rows from the right table and the matched rows from the left table, filling in NULLs where there is no match. These joins are used to combine data across multiple tables, and choosing the right join depends on the specific use case. For example, a LEFT JOIN might be used to get a list of all customers, even those without orders, while an INNER JOIN would only return customers who have placed orders. Here's a practical example: Table: Customers | CustomerID | Name | Country | | 1 | Alice | USA | | 2 | Bob | UK | | 3 | Charlie | Canada | Table: Orders | OrderID | CustomerID | OrderAmount | | 101 | 1 | $200 | | 102 | 2 | $150 | | 103 | 4 | $300 | Result of INNER JOIN: Only returns rows where there is a match between the Customers and Orders tables. | CustomerID | Name | OrderID | OrderAmount | | 1 | Alice | 101 | $200 | | 2 | Bob | 102 | $150 | Result of LEFT JOIN: Returns all customers, including those with no orders, with NULLs for unmatched rows. | CustomerID | Name | OrderID | OrderAmount | | 1 | Alice | 101 | $200 | | 2 | Bob | 102 | $150 | | 3 | Charlie | NULL | NULL | Result of RIGHT JOIN: Returns all orders, including those with no matching customer, with NULLs for unmatched rows. | CustomerID | Name | OrderID | OrderAmount | | 1 | Alice | 101 | $200 | | 2 | Bob | 102 | $150 | | NULL | NULL | 103 | $300 |
27
參考答案
Character sets define how characters are stored and sorted, while collations determine the order of characters within a character set.
28
參考答案
SQL Server 2022 introduces several new features, including Azure Synapse Link integration, Ledger for SQL Server, containing Availability Groups, enhanced Query Store, and improvements in security, performance, and availability.
29
參考答案
1433.
30
參考答案
The physical files that hold the data and information that compose up a database are referred to as database files. The control files, data files, and redo log files are among them. Whereas, The storage that is distributed and accessible by all threads and background processes is referred to as an instance.
31
參考答案
ps -ef | grep pmon gives output of running database instances on a server. /etc/oratab file lists all the databases created on a server, weather they are running or shutdown is a different case.
32
參考答案
MRP: Managed Recovery Process (applies redo on standby). RFS: Remote File Server (receives redo on standby). LNS/NSS/NSA: Log/Network Service (sends redo; NSS for SYNC, NSA for ASYNC in 12c+). DMON: Data Guard Broker monitor. FSFP: Fast-Start Failover Process.
33
參考答案
ACID stands for Atomicity, Consistency, Isolation, and Durability. Atomicity ensures all operations in a transaction complete successfully or none do—no partial updates. Consistency guarantees that transactions only leave the database in a valid state according to defined rules. Isolation prevents concurrent transactions from interfering with each other, and Durability ensures committed changes survive system failures. I configure isolation levels based on business requirements—using READ_COMMITTED for most operations but READ_UNCOMMITTED for certain reporting queries where dirty reads are acceptable for performance gains.
34
參考答案
ALTER TABLESPACE tablespace_name ADD DATAFILE 'datafile_name' SIZE 5M;
35
參考答案
First, I would analyze the query execution plan to identify any performance bottlenecks. Indexing is a primary method for improving query performance, so I would ensure that the necessary indexes are in place for columns used in the WHERE clause, JOIN conditions, and ORDER BY clauses. Another approach is to avoid using SELECT * and instead specify only the columns needed, which reduces the amount of data retrieved. Additionally, I would look at rewriting complex queries into simpler subqueries or using temporary tables to break down the query into manageable parts. For instance, instead of using correlated subqueries, I might use JOINs to enhance performance. A table can help you remember the various techniques for optimizing SQL queries: | Optimization technique | Description | Example or application | | Indexing | Add indexes on columns used in WHERE, JOIN, and ORDER BY. | Create an index on customer_id for faster lookups. | | Avoiding SELECT * | Retrieve only the necessary columns to reduce the data being processed. | Use SELECT name, email instead of SELECT *. | | Query execution plan analysis | Use execution plans to find bottlenecks and identify missing indexes. | Analyze query performance using EXPLAIN. | | Avoiding correlated subqueries | Replace correlated subqueries with JOINs for better performance. | Replace WHERE EXISTS with INNER JOIN. | | Limiting rows with LIMIT | Use LIMIT to restrict the number of rows returned, especially during testing. | Use SELECT * FROM orders LIMIT 100. |
36
參考答案
When taking an incremental backup, RMAN typically scans the entire database to identify changed blocks, which can be time-consuming for large databases. By enabling Block Change Tracking (BCT), RMAN maintains a record of changed blocks and backs up only those blocks, avoiding a full database scan, which significantly improves backup performance.
37
參考答案
Yes.
38
參考答案
If somebody's been doing database administration long enough to claim the title Senior DBA, they've built up a little wish list of database management tools they've seen along the way. Tool types might include: - Data modeling - Change management - Backup compression - Performance monitoring - Alerting If they had a $X tool budget for their workstation, how would they spend it? Forget corporate standards – I want to know what tools they'd use if they could pick on their own.
39
參考答案
Data loss is one of the situations where database administrators experience the most pressure, especially if a migration project falls behind schedule. This question will help you learn more about how a candidate behaves under pressure and stress, and more importantly, what strategies they use to overcome this situation — understanding how to troubleshoot and recover data is a crucial function all DBA roles should know how to handle.
40
參考答案
Backup Set: A logical structure where the backup is stored, containing one or multiple database files, SPFILEs, control files, etc. Backup Piece: A physical file within a backup set, stored in binary format.
41
參考答案
The HAVING clause was introduced in SQL to allow the filtering of query results based on aggregate functions and groupings, which cannot be achieved using the WHERE clause that is used to filter individual rows.
42
參考答案
The binary log records all changes to the database, allowing for replication, backup, and point-in-time recovery.
43
參考答案
Clustering is an integral part of grid infrastructure and focuses on a specific objective. While grid, which may or may not consist of multiple clusters, possesses a wider framework that enables sharing of storage systems, data resources and remaining others across different geographical locations. A cluster will have single ownership but the grid can have multiple ownership based on the number of the cluster it holds.
44
參考答案
It's a necessary step to check the different ways you can collect data. However, you firstly need to audit your data flows properly and verify you're satisfying all the legal obligations, such as obtaining proper consents for processing, providing the data subject with the information specified by GDPR, etc.
45
參考答案
A transaction in SQL Server is a single unit of work. If a transaction is successful, all of the data modifications made during the transaction are committed and become a permanent part of the database. If a transaction encounters errors and must be canceled or rolled back, then all of the data modifications are erased.
46
參考答案
Start by identifying which queries or processes are consuming most CPU: Run: SELECT * FROM v$sql ORDER BY cpu_time DESC; to find top SQLs by CPU. • Use top and ps -ef from OS to see which Oracle processes are heavy. • Look into plans of SQLs causing high CPU—bad plans, missing indexes, or high parse rate can be reasons. • Also check: o Statistics are stale? o Bind peeking or plan instability? o Too many context switches? Use SQL tuning, plan baselines, or rewriting inefficient queries to reduce load.
47
參考答案
In the context of database administration, taking a database offline means temporarily removing it from an active state, rendering it unreachable to users and applications. When a database is taken offline, the database engine disallows new connections and terminates current ones. This action can be used for a variety of administrative and maintenance duties. Here's an overview of what putting a database offline entails: - No User Access: When a database is taken offline, users and programs are no longer able to connect or interact with it. Attempts to access the database while in this offline state will result in an error. - Administrative tasks: Taking a database offline is frequently used to undertake administrative procedures that require exclusive access to the database. This might include things like running a backup, installing updates or fixes, or migrating database files. - Maintenance Operations: Maintenance procedures, such as rebuilding indexes, updating statistics, or making substantial changes to the database structure, may necessitate taking the database offline to guarantee data consistency and prevent any conflicts.
48
參考答案
select current_scn from v$database.
49
參考答案
Oracle Multitenant was introduced in version 12c. It allows a single Container Database (CDB) to host multiple Pluggable Databases (PDBs). CDB contains: • Shared components like background processes, memory (SGA), and control/redo/datafiles. • At least one Root Container and Seed PDB (PDB$SEED). PDBs contain: • User objects like tables, views, indexes. • Application-specific data. Benefits: • Easier consolidation of databases. • Simplified patching and upgrades. • Resource isolation between PDBs. • Fast cloning and unplug/plug options for migrations. CDB/PDB model helps organizations manage hundreds of databases more efficiently. It's now the default architecture in Oracle 21c and is mandatory in Oracle 23c.
50
參考答案
I want to know that they've got at least a couple of people they can call when the going gets rough and the servers catch fire. These other references could be mentors, or could be people they've mentored or just worked with along the way. I expect to get terrified looks, and I'd answer those by saying, “I don't want to raise any red flags by calling people at your current employer, and I'm sure you know people who've left your company and moved on. I just want to talk to people who've worked with you on projects or on problems. I won't ask for your level of technical competency, because these other guys can't judge that. I just want to know you've interacted with them.”
51
參考答案
SQL Server supports Windows Authentication and mixed-mode. Mixed-mode allows you to use both Windows Authentication and SQL Server Authentication to log into your SQL Server. It is important to note that if you use Windows Authentication, you will not be able to log in.
52
參考答案
Performing operating system restore of the database files
53
參考答案
SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, which are called jobs in SQL Server. It can manage job scheduling, alert system, and operators for notifying event responses.
54
參考答案
DGMGRL (Data Guard Manager command-line) is used to manage Data Guard environments—ensuring disaster recovery and data availability. Common commands: • SHOW CONFIGURATION; — view overall Data Guard setup and status. • SHOW DATABASE ‘dbname'; — check individual database status in the DG setup. • ENABLE CONFIGURATION; and DISABLE CONFIGURATION; — activate or deactivate Data Guard. • SWITCHOVER TO ‘dbname'; — switch primary and standby roles gracefully. • FAILOVER TO ‘dbname'; — emergency failover if primary fails. • VALIDATE DATABASE ‘dbname'; — check health and sync status. • EDIT DATABASE ‘dbname' SET PROPERTY; — modify database properties like log transport.
55
參考答案
Monitor key metrics: CPU usage, memory consumption, disk I/O, connection counts, query response times, and database-specific metrics like buffer cache hit ratios or lock waits. Set up meaningful alerts: Configure thresholds based on baseline performance—alert when response times exceed 2x normal levels or when disk space drops below 20%. Avoid alert fatigue by tuning thresholds carefully. Use monitoring tools: Implement database-specific monitoring (like SQL Server Management Studio, MySQL Enterprise Monitor) and integrate with broader infrastructure monitoring (Nagios, Zabbix, or cloud-native solutions).
56
參考答案
The transaction log is a critical component of SQL Server that records all transactions and database modifications. It ensures data integrity by supporting rollback and recovery operations, and it is used for point-in-time restoration. Managing its size and growth is essential to prevent performance issues.
57
參考答案
No
58
參考答案
Physical Standby: An exact block-for-block replica of the primary, synchronized via redo apply. Supports read-only mode (Active Data Guard) for reporting. Ideal for high availability and disaster recovery. Logical Standby: Maintains logical equivalence using SQL Apply, allowing read-write operations and different physical structures. Suitable for reporting and database upgrades. Snapshot Standby: A temporary read-write physical standby for testing, reverting to physical standby after use, discarding changes.
59
參考答案
ORA-01555: snapshot too old error occurs when a query attempts to access a snapshot of data that is no longer available in the undo tablespace. This happens when:1. A long-running query requires a read-consistent view of data. 2. The undo retention period is too short, causing the required undo data to be overwritten.To avoid ORA-01555: 1. Increase undo retention period: Set the UNDO_RETENTION parameter to a higher value to retain undo data for a longer period. 2. Increase undo tablespace size: Expand the undo tablespace to store more undo data. 3. Tune queries: Optimize long-running queries to reduce their execution time and minimize the need for old snapshots. 4. Use GUARANTEE option: Use the GUARANTEE option when creating the undo tablespace to ensure that enough space is allocated for undo data. 5. Monitor undo tablespace usage: Regularly monitor undo tablespace usage and adjust parameters as needed.
60
參考答案
General steps (high-level): - Read patch README carefully and prerequisites. - Backup: full DB backup, backup ORACLE_HOME, and take GI/ASM backups if needed. - Put cluster in maintenance: stop services using srvctl that could interfere. - Apply patch to Oracle Grid Infrastructure first (if required by patch): - On GI home run OPatch or runInstaller for GI; run root scripts as needed. - Use patchmgr oropatch in GI home. - - Apply patch to database ORACLE_HOME on one node: - Stop DB instances on that node or follow rolling upgrade approach. - - Rolling patching: For RAC, perform a rolling patch to minimize downtime: - Patch one node at a time, srvctl stop instance -d -n , apply patch, rundatapatch (if SQL changes), start instance on that node, then move to next node. - - Run datapatch to apply SQL changes to the database (often required after binary patch). - Validate services and opatch lsinventory . - Check cluster status: crsctl stat res -t andsrvctl status database -d . - Complete root scripts on each node as required. - Test application connectivity, do smoke tests. Always follow specific patch README for switching off/on services and running opatch with -local or-applyRolling .
61
參考答案
On SQL Server 2005, installing the SQL Server failover cluster is a single-step process where the installation installs on all nodes. On SQL Server 2008 or above, it is a multi-step process where you need to install separately on all nodes (e.g., 2 times for a 2-node cluster, 3 times for a 3-node cluster).
62
參考答案
The credentials of the database users are saved in the database's data dictionary. When the user tries to request access to the database, it compares the user's account and passcode to the data contained in the database. Once The user is only allowed database access if the account and passcode match. We can access the data dictionary as long as the database is open. This dictionary also contains the credentials for admins. You can't access the dictionary once the database is shut down. Because starting up a closed database is one of the executive's jobs, there has to be a means for admins to login into the database even when the server is down. A password file is a distinct OS file that is kept on a disc and is not connected to the server. It stores the login details for SYSDBA and SYSOPER clients. While the database is offline, the password file is used to verify admins with those rights.
63
參考答案
CHAR is a fixed-length string data type, while VARCHAR is a variable-length string data type.
64
參考答案
In the world of database design, entity relationship diagrams serve as valuable tools for designing complex systems and their relationships. In this article will go through the step-by-step process of designing an ER diagram, and defining how entities, attributes, and relationships are defined. Entity relationship diagrams are extremely important in database design and require a clear structure of all data. Entity Relationship Diagram for BANK We are following the below steps to design an ER diagram: - Defining Entities - Adding Attributes - Establishing Relationships - Specify Cardinality - Identify Primary Keys - Draw the ER Diagram - Benefits of ER Diagram
65
參考答案
ACID properties are pivotal to maintaining database reliability, especially in transaction processing. You should understand and explain the concept behind Atomicity, Consistency, Isolation, and Durability, their importance in database transactions, and the role they play in ensuring data integrity and consistency. ACID properties are Atomicity, Consistency, Isolation, and Durability. Atomicity ensures that a transaction is completely processed or not at all to prevent partial updates. Consistency maintains the rules of the database, ensuring that each transaction brings the database from one consistent state to another. Isolation keeps transactions separate until completed to avoid conflicts. Durability guarantees that once a transaction is confirmed, it will remain committed even in the event of system failure.
66
參考答案
Oracle's volume manager and file system optimized for DB files.
67
參考答案
Oracle DBA is the database administrator who performs all administrative tasks. Administrative tasks include: 1 .User-level administration i.e. creates users, remove existing users or modifies user permissions. 2 .Maintains database security. 3 .Manages database storage & objects. 4 .Tunes performance of a database. 5. Performs backups & recovery tasks.
68
參考答案
To revoke an object privilege: REVOKE SELECT ON T FROM AISHU; To revoke a role: REVOKE SURESH FROM AISHU; -- SURESH is a role here
69
參考答案
It may lead to compatibility and synchronization issues between the two databases, potentially causing replication failures.
70
參考答案
The Oracle server process
71
參考答案
Ways to speed patching: - Pre-check and stage patches to each node before execution. - Use local storage rather than slow NFS for ORACLE_HOME. - Ensure OPatch and patch tools are present and up-to-date. - Parallelize non-dependent steps: prepare homes on multiple nodes in parallel. - Disable antivirus or system checks temporarily if they slow file operations (with approvals). - Use snapshots and clone ORACLE_HOME to avoid lengthy extraction each time. - Ensure sufficient CPU, disk I/O before patch; have spare resources. - Run prechecks in advance (opatch prereq) to avoid mid-patch failures. - Apply patches during low activity windows to avoid waiting for services to quiesce. - Keep ORACLE_HOME backups ready so you can quickly rollback without lengthy repair. - Use automation scripts that run root scripts in parallel across nodes (careful with cluster resources). - Use faster patch bundles (choose cumulative RU vs full patching strategy based on guidance).
72
參考答案
ORA-03113 means the connection between client and server was unexpectedly terminated. This could be caused by server crash, network issues, or bugs. To resolve: • Check the database alert log and trace files to identify server-side errors or crashes. • Verify network connectivity and firewall settings. • Confirm that the listener is running and accepting connections. • Check for server resource issues like memory, CPU, or disk space. • Apply patches or updates if the problem is caused by bugs. • Restart the database and listener services carefully. • Use SQL*Net tracing for detailed communication logs if needed.
73
參考答案
In '=', the value being returned must match the value it is being compared to. However, you can use LIKE with several different parameters, allowing you to be more flexible in your rules.
74
參考答案
I regularly follow industry blogs and forums to stay updated with the latest trends and technologies. Additionally, I attend webinars and conferences, and participate in online courses to continuously enhance my skills.
75
參考答案
There are many different types of database infrastructures, so with this question, you'll be confirming if a candidate has the background you're looking for. For example, if your database is built with MySQL or Oracle, then it's important to see how familiar candidates are with these database servers and their level of expertise.
76
參考答案
And really pretend that you're a developer. If you're a DBA manager, bring in one of your toughest developers to play bad cop. Challenge them – does it really improve performance or manageability? How do you know? Is it just your opinion, or where's the proof? If they can explain it in clear, easy-to-comprehend terms, that bodes well for their ability to communicate with other teams.
77
參考答案
The three main components are Publisher, Distributor, and Subscriber. The publisher is the data source of a publication. The distributor is responsible for distributing the database objects to one or more destinations. The subscriber is the destination where the publisher's data is copied or replicated.
78
參考答案
During the restoration process, backup files are copied from the hard disk, media or tapes to the restoration location and later make the database operational. Recovery has an additional step of updating these data files by applying redo logs so as to recover the changes which are not backed up. Let us understand this with the help of a scenario. 1.Database full backup is taken on Friday 11 PM 2.Database crash happened on Saturday 7 AM We can restore the lost files using the 11 PM full backup which is Restoration. However, the data will be restored up till Friday at 11 PM and not till Saturday at 7 AM. In order to do the same, redo logs can be applied which will bring the database to the point of failure.
79
參考答案
Common wait events: - db file sequential read — single-block reads (index lookups). Cause: physical I/O. Resolution: tune queries, add index, improve I/O. - db file scattered read — multi-block reads (table scans). Cause: full table scans. Resolution: add indexes, tune queries, increase buffer_cache. - log file sync — wait for LGWR to write redo — caused by commit waits. Resolution: reduce commit frequency, tune application, faster redo logs, increase LOG_BUFFER. - log file parallel write — LGWR writing multiple redo members. Resolution: check I/O subsystem for redo logs; ensure redo logs on fast disks. - enqueue waits (enq: TX) — locks for transactions. Cause: uncommitted transactions, row locks. Resolution: find blocking session and resolve. - latch free — contention for internal structures. Resolution: application tuning, parameter tuning, reducing parallelism. - buffer busy waits — contention for blocks. Resolution: reduce hot block access, partition tables, increase freelist. - CPU — CPU bound. Resolution: tune SQL, add CPUs, offload work. - log file switch (checkpoint incomplete) — waiting for checkpoint. Resolution: increase DBWR performance, tune checkpoints, increase redo logs, add more redo groups. For each event: analyze with AWR / ASH andv$session_wait /v$system_event to find root cause and fix.
80
參考答案
A subquery, as the name itself suggests, is a query within another query. It acts like a mini-query that helps to solve a specific part of the main query, and its results are used by the outer query to filter or manipulate the final data. Subqueries are nested queries that filter data based on the results of another query, while joins combine data from multiple tables based on a shared relationship. Subqueries are generally easier to understand but less efficient for large datasets, while joins are more efficient but can be a little complex. For ex: Find all customers who placed orders exceeding the average order amount. Subquery for the above question: SELECT * FROM customers WHERE customer_id IN ( SELECT customer_id FROM orders WHERE amount > (SELECT AVG(amount) FROM orders) );
81
參考答案
Corresponding tablespace itself
82
參考答案
This is a non-technical question to evaluate your dedication, positive attitude, flexibility, and readiness to adopt new changes. You should describe your incident management process, including detection, escalation, resolution, and post-incident analysis.
83
參考答案
SQL Server 2022 supports cloud integration primarily through Azure SQL Edge, enabling seamless data streaming, management, and analysis across cloud environments. It also offers enhanced Azure Hybrid Benefit options, allowing customers to use their on-premises SQL Server licenses to manage Azure SQL Database instances at reduced costs. Features like Managed Instance in Azure SQL provide near-complete compatibility for migrating on-premises SQL Server databases to the cloud, facilitating easier and more secure cloud adoption.
84
參考答案
Situation: Our company acquired another firm that used PostgreSQL while we were primarily a SQL Server shop. Task: I needed to become proficient in PostgreSQL to manage the integrated systems within two months. Action: I created a structured learning plan including online courses, hands-on labs, and connecting with PostgreSQL community forums. I also set up a test environment to practice common administrative tasks and compare them to SQL Server equivalents. Result: I successfully migrated three critical databases and became the go-to person for PostgreSQL questions, eventually leading to a 15% salary increase for my expanded skill set.
85
參考答案
For schema export/import, I use Data Pump with schemas= in expdp and impdp. For a full database, I use full=y. I store dump files in a directory object with proper permissions, and use parallelism for speed. During import, I can remap schemas or tablespaces and verify results via logs.
86
參考答案
You can use Open Database Connectivity to make different frontends talk to different data sources (DSNs). You should know that the specifics available depend on certain factors, such as the type of application being used, the backend to which it is being applied, and the driver being used.
87
參考答案
From attention to detail to problem-solving, soft skills are critical for database administrators. Gauge their skill level by asking them this DBA interview question.
88
參考答案
A transaction is a sequence of operations performed as a single logical unit of work. It ensures that a series of operations are completed successfully or not at all. Transactions are essential for maintaining data consistency and integrity. They follow the ACID properties: Atomicity, Consistency, Isolation, and Durability.
89
參考答案
A comprehensive backup strategy includes: 1) Performing at least two types of backups: a logical backup (e.g., export using tools like mysqldump or Oracle expdp) and a physical backup (e.g., cold or hot backup). 2) Scheduling nightly backups, either cold (database offline) or hot (online) depending on availability requirements. 3) Storing backups off-site to ensure disaster recovery, not on the same server as the database. 4) Testing backups regularly by restoring them to a test environment. 5) Implementing incremental or differential backups to reduce backup time and storage. 6) Documenting the backup and recovery procedures.
90
參考答案
Error handling in SQL Server is implemented using TRY...CATCH blocks in T-SQL. The TRY block contains the code that might cause an error, and the CATCH block handles the error by capturing details like error number, severity, state, and message using functions such as ERROR_NUMBER(), ERROR_MESSAGE(), and ERROR_SEVERITY().
91
參考答案
Database technologies are constantly evolving. It's crucial to stay updated to enhance your skills and bring new ideas to your projects. Discuss the resources you use to keep up with the latest trends and how you apply your learnings. I regularly follow reputed tech blogs, attend webinars, and participate in online forums. I also take online courses to learn new technologies. By staying updated, I've been able to bring new ideas to my projects, such as implementing NoSQL databases for certain use cases and using automation tools for routine tasks.
92
參考答案
PGA memory allocations
93
參考答案
I have worked extensively with AWS RDS and Azure SQL Database, managing large-scale deployments and ensuring high availability. One challenge I faced was optimizing cost while maintaining performance, which I resolved by implementing automated scaling and performance monitoring tools.
94
參考答案
In Oracle, there are several types of partitions:1. Range Partitioning: Divides data into partitions based on a range of values for a specific column. 2. List Partitioning: Divides data into partitions based on a list of discrete values for a specific column. 3. Hash Partitioning: Divides data into partitions based on a hash function applied to a specific column. 4. Composite Partitioning: Combines range, list, or hash partitioning to create a multi-level partitioning scheme. 5. Interval Partitioning: An extension of range partitioning, where new partitions are created automatically as new data is inserted. 6. Reference Partitioning: Partitions a table based on a referential constraint to another table. 7. System Partitioning: Allows the database administrator to define partitions manually. 8. Hybrid Partitioning: Combines different partitioning methods, such as range and hash partitioning.
95
參考答案
Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.
96
參考答案
SELECT * FROM v$version;
97
參考答案
Query the DBA_FREE_SPACE view: sql SELECT * FROM DBA_FREE_SPACE;
98
參考答案
Yes, I do work in shifts. As a DBA, I am responsible for ensuring the availability and performance of the database 24/7. To achieve this, our team works in shifts to provide around-the-clock coverage. This includes night shifts, weekend shifts, and holiday shifts
99
參考答案
Datapatch applies the SQL part of an Oracle patch to the database after binary patching. It ensures the dictionary and components are upgraded to match the new binaries, and it's always run after applying PSUs, RUs, or one-off patches.
100
參考答案
SELECT SUM(bytes)/1024/1024/1024 AS size_in_GB FROM dba_data_files;
101
參考答案
Database performance optimization is a crucial part of a DBA's role. Discuss the steps you'd take to diagnose and resolve performance issues, and any tools or techniques you might use. When a database is running slow, I first look at the server's CPU, memory, and disk I/O utilization. If these are okay, I then examine the database logs and run an EXPLAIN PLAN to identify slow queries. Once identified, I optimize these queries, look for missing indices and unnecessary full table scans. Regular monitoring and proactive optimization can help avoid such situations.
102
參考答案
Best practices include: • Regularly review alert logs and system logs. • Monitor key performance metrics (CPU, memory, I/O). • Check space usage for tablespaces, datafiles, and undo. • Validate backups and test recovery procedures. • Apply patches and upgrades during maintenance windows. • Run health check scripts or OEM automated checks. • Analyze wait events and SQL performance. • Review security settings and audit logs. • Document and automate routine checks. Proactive checks help prevent outages and improve reliability.
103
參考答案
The SQL Server Management Studio Graphic User Interface and T-SQL script serve to guide users step-by-step in creating operators in SQL Server.
104
參考答案
Database scalability is critical for applications that need to grow over time. Discuss your understanding and experience of horizontal and vertical scaling strategies and how you've implemented them. To ensure database scalability, I've used both vertical and horizontal scaling. Vertical scaling involves adding more resources to the existing server. While it's easier to implement, it has limitations. Horizontal scaling, or sharding, involves distributing the database across multiple servers. While more complex, it allows virtually unlimited scalability.
105
參考答案
If archive log files exceed 300-400, to sync the primary and standby databases:Take Incremental Backup on Primary and Apply to DR 1. Take an incremental backup on the primary database. 2. Transfer the backup to the DR site. 3. Apply the incremental backup to the standby database.This approach helps to: – Reduce the archive log gap between primary and standby. – Re-sync the standby database with the primary. – Avoid manual deletion of archive logs.By taking an incremental backup and applying it to the DR site, you can efficiently re-sync the databases and resolve the sync issue.
106
參考答案
In a recent project, we needed to integrate a third-party application with our existing database system. I collaborated closely with the development team, the vendor's technical team, and the business stakeholders to ensure a smooth implementation. I organized regular meetings to align requirements, set expectations, and establish clear communication channels. I facilitated discussions to address technical challenges and ensured everyone was on the same page throughout the project. By fostering effective collaboration and maintaining open lines of communication, we successfully implemented the integration, meeting the project goals and delivering value to the business.
107
參考答案
Execute catalog.sql and catproc.sql. Update /etc/oratab file.
108
參考答案
A view in SQL Server is a virtual table based on the result-set of an SQL statement. It contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
109
參考答案
I implement a comprehensive backup strategy that includes full, differential, and transaction log backups based on recovery point objectives (RPO) and recovery time objectives (RTO). Backups are stored in multiple locations, including off-site or cloud storage, and are regularly tested for restore integrity. For disaster recovery, I design and maintain failover solutions such as Always On Availability Groups, database mirroring, or replication to ensure minimal downtime.
110
參考答案
Add a new redo log group using: sql Copy Edit ALTER DATABASE ADD LOGFILE GROUP ‘location' SIZE 50M;
111
參考答案
A correlated sub-query is a nested query that is linked to the outer query. For example, to find all employees who have not entered their time for the week, you could query the Employee table and use a correlated sub-query with NOT EXISTS, where the inner query relates to the outer query on the employee ID. The inner query is evaluated once per the outer query row.
112
參考答案
The model database serves as the template for all databases created on the same instance. If the model database is modified, all subsequent databases created on that instance will pick up those changes, but earlier created databases will not. Note that TEMPDB is also created from the model every time SQL Server starts up.
113
參考答案
Yes, Flashback Database must be enabled before you can create a Guaranteed Restore Point.
114
參考答案
The Process Monitor (PMON) in Oracle is responsible for cleaning up after failed user processes by releasing locked resources, recovering transactions, and ensuring that any session-specific memory is freed up.
115
參考答案
900
116
參考答案
SELECT * FROM dba_tablespaces; To view tablespace information in an Oracle database, you can query theDBA_TABLESPACES orUSER_TABLESPACES view, depending on the access level: 1. DBA_TABLESPACES: This view shows tablespaces for the entire database (requires DBA privileges).SELECT tablespace_name, status, contents, extent_management, allocation_type FROM dba_tablespaces; 2. USER_TABLESPACES: This view shows tablespaces for the current user (no DBA privileges required).
117
參考答案
Yes, but you must ensure the tablespace is empty or use INCLUDING CONTENTS or INCLUDING CONTENTS AND DATAFILES clauses to drop it and the associated objects.
118
參考答案
The Right of Access request (Art.15 – GDPR) provides individuals with the ability to know if a data controller is processing their personal data and, if that's the case, what that information is and why it is being processed. Individuals are entitled to a copy of the personal data in question, which doesn't necessarily depend if someone is an employer, worker, or self-employed. The DDPR contains exceptions that mostly focus on questions of public interest (like the investigation of crime). Moreover, the GDPR recital 47 states that fraud prevention is a legitimate interest to process personal data: ‘'The processing of personal data is strictly necessary for the purpose of preventing fraud also constitutes a legitimate interest of the data controller concerned''. Even for the purpose of fraud, the controller still has to prove that legitimate interest applies and that the processing of personal data is necessary.
119
參考答案
Go to SQL Server Configuration Manager. In the left pane, select SQL Server Services. The right side pane displays all SQL Server services/components installed on that machine. If the service is displayed as (MSSQLSERVER), it indicates a default instance; otherwise, the instance name is displayed.
120
參考答案
Oracle Database uses a combination of memory areas and background processes to perform its tasks efficiently. The architecture is broadly split into two parts: memory structures and processes. SGA (System Global Area) is shared memory accessible by all sessions. It's used to cache data and reduce disk I/O: • Shared Pool: Caches SQL statements, PL/SQL code, and data dictionary information. • Database Buffer Cache: Holds copies of data blocks read from disk. Speeds up reads and writes. • Redo Log Buffer: Stores change data (redo entries) temporarily before it's written to redo log files. • Large Pool, Java Pool, Streams Pool: Used for RMAN, Java, or stream operations. PGA (Program Global Area) is private memory allocated to each server or background process. It holds session-specific data like sort areas, hash joins, and connection state. Background Processes perform essential tasks: • DBWn: Writes modified (dirty) buffers to disk. • LGWR: Writes redo data to redo logs to ensure recoverability. • CKPT: Signals checkpoints to keep the database consistent. • SMON (System Monitor): Performs recovery after crashes. • PMON (Process Monitor): Cleans up failed user sessions. • Others include ARCn, RECO, MMON, depending on features used. These work together to manage data access, query processing, and ensure high performance and recoverability.
121
參考答案
Physical Standby: Exact copy of the primary database.Logical Standby: Uses SQL apply to keep standby updated. Snapshot Standby: Read-write mode for testing. Active Data Guard: Allows queries while logs are applied.
122
參考答案
User-defined variables allow you to store temporary values within a session for use in queries and calculations.
123
參考答案
Webservice calls from SQL Server can be made using the sp_OACreate and sp_OAMethod stored procedures to create and invoke COM objects, or by using SQLCLR to create custom functions that call web services. More modern approaches involve using external scripts or integration with tools like Azure Logic Apps.
124
參考答案
Indexing is a procedure that returns your requested data faster from the defined table. Without indexing, the SQL server has to scan the whole table for your data. By indexing, the SQL server will do the exact same thing you do when searching for content in a book by checking the index page. In the same way, a table's index allows us to locate the exact data without scanning the whole table. There are two types of indexing in SQL. - Clustered index - Non-clustered index Clustered Index A clustered index is the type of indexing that establishes a physical sorting order of rows. Suppose you have a table Student_info which contains ROLL_NO as a primary key, then the clustered index which is self-created on that primary key will sort the Student_info table as per ROLL_NO. A clustered index is like a Dictionary; in the dictionary, the sorting order is alphabetical and there is no separate index page. Examples: CREATE TABLE Student_info ( ROLL_NO int(10) primary key, NAME varchar(20), DEPARTMENT varchar(20), ); INSERT INTO Student_info values(1410110405, 'H Agarwal', 'CSE'); INSERT INTO Student_info values(1410110404, 'S Samadder', 'CSE'); INSERT INTO Student_info values(1410110403, 'MD Irfan', 'CSE'); SELECT * FROM Student_info; Output: ROLL_NO | NAME | DEPARTMENT | |---|---|---| | 1410110403 | MD Irfan | CSE | | 1410110404 | S Samadder | CSE | | 1410110405 | H Agarwal | CSE |
125
參考答案
Database normalization is crucial in eliminating redundant data and ensuring data integrity. Demonstrate your understanding of normalization, its purpose, the different normal forms, and how it helps achieve a desirable database structure. Database normalization is a process to minimize redundancy and dependency by organizing fields and table relationships. It involves several stages, called normal forms, each with a certain set of rules. The benefits include avoiding duplication, reducing the database size, and ensuring consistency, hence making data manipulation processes more efficient.
126
參考答案
AlwaysOn Availability Groups provide high availability and disaster recovery by replicating databases across multiple servers. It allows automatic failover, readable secondary replicas, and backup operations on secondary nodes to reduce primary server load.
127
參考答案
Managing on-premises databases requires handling hardware procurement, software installation, and regular maintenance like backups, patching, and monitoring. In contrast, cloud-based databases leverage the cloud provider's infrastructure, offering scalability, built-in high availability, and automated backups. Cloud databases also provide options for scaling resources up or down as needed, without the need to invest in physical hardware. For example, in AWS RDS, you can easily scale compute power and storage with just a few clicks, and the system manages the hardware side of things for you.
128
參考答案
Monitor the difference between the master's binary log position and the slave's relay log position to calculate replication lag.
129
參考答案
Difference Between CPU and PSU Patch in Oracle Feature CPU (Critical Patch Update) PSU (Patch Set Update) Full Form Critical Patch Update Patch Set Update Purpose Security fixes only Security fixes + Bug fixes Release Cycle Quarterly (Jan, Apr, Jul, Oct) Quarterly (Jan, Apr, Jul, Oct) Contents Includes only security vulnerability fixes Includes CPU patches + additional bug fixes for stability Impact Minimal impact on functionality May have minor functional changes due to bug fixes Recommended For Systems needing only security fixes Systems needing security + stability improvements Superset Of Independent security patch Includes CPU patches and additional fixes
130
參考答案
Our Oracle DBA team consists of 10 members, each handling different aspects of database management to ensure smooth operations. The team is divided as follows: - Production Support (4 members): These team members monitor and maintain production databases, troubleshoot performance issues, and ensure 24/7 availability. - Backup & Recovery (2 members) - Patching & Upgrades (2 members) - Performance Tuning & Optimization (1 member) - Automation & Scripting (1 member):
131
參考答案
Oracle Snapshots, now known as Materialized Views, are database objects that store the results of a query physically. They allow you to replicate and cache remote data locally, improving query performance and enabling offline reporting. Snapshots can be refreshed periodically or on-demand to keep the data current. They are useful for: • Replicating data across databases for distributed applications. • Offloading reporting workloads from production. • Improving query performance by avoiding repeated complex joins. • Enabling data warehousing and ETL processes. Materialized views can be simple or complex, support fast refresh using logs, and are tightly integrated into Oracle's optimizer for efficient query rewriting.
132
參考答案
To optimize a slow-running query, the first step is examining the query execution plan to identify bottlenecks such as table scans, key lookups, and costly operations. Evaluating indexing strategies, including adding, modifying, or removing indexes, is crucial to improving performance. Analyzing I/O statistics and CPU usage helps pinpoint inefficiencies. Keeping statistics up-to-date is essential. Refactoring the query to simplify joins or eliminate subqueries, and using query hints selectively, can also enhance query performance.
133
參考答案
The 'Trustworthy' setting on databases is used to indicate whether SQL Server trusts the database and its contents. It provides the ability for database objects to access resources outside the database, such as executing assemblies with external access or unsafe permissions. It should be used only when necessary, such as for databases that require cross-database ownership chaining or CLR integration, and should not be used on databases that do not need these capabilities due to security risks.
134
參考答案
In SQL Server, there are two choices for registering Windows authentication: SQL server authentication or Windows authentication, when selecting this latest method, leave the default database alone to avoid confusion and hassles.
135
參考答案
Look for: Familiarity with monitoring tools and troubleshooting methodologies.
136
參考答案
Troubleshooting a hanging database involves: 1) Checking the database alert log and system logs for errors. 2) Identifying blocking sessions using tools like SQL Server Activity Monitor or Oracle's V$SESSION. 3) Analyzing wait events to determine the root cause (e.g., I/O bottlenecks, lock contention, or resource starvation). 4) Killing problematic sessions if necessary. 5) Checking disk space, memory, and CPU usage. 6) Reviewing recent changes to the database or application. 7) Restarting the database service if the issue persists, after ensuring a proper backup is available.
137
參考答案
I would start by estimating the volume of data, considering growth over time. Then, I'd look into the types of queries and transactions to determine the required performance. Based on these factors, I'd define the storage requirements.
138
參考答案
If a transaction log file is full, a DBA can: Create a backup of the transaction log to clear space and prevent the log file from becoming full. Truncate the log using the BACKUP LOG command. Increase the size of the transaction log file or add additional log files. Check for long-running transactions and resolve them.
139
參考答案
No, running tests on a live database is risky and not recommended. I would use a staging environment that mimics the live database for all testing purposes.
140
參考答案
Oracle database is comprised of three types of files. One or more datafiles, two or more redo log files, and one or more control files. Password file and parameter file also come under physical components.
141
參考答案
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.
142
參考答案
Oracle stores data in a structured format to manage space efficiently. The smallest unit is a data block. • Data Block: Smallest unit of I/O; usually 8KB or 16KB in size. It stores rows of table data. • Extent: A set of contiguous data blocks. Oracle allocates extents to store new data as needed. • Segment: A set of extents allocated to a database object, such as a table, index, or LOB. When a table is created, Oracle allocates an initial extent. As the table grows, more extents are added. All these extents together form a segment. Segments reside in tablespaces, and tablespaces use datafiles. This layered storage model helps Oracle efficiently manage growth, fragmentation, and access speed. Oracle's storage model allows for dynamic space allocation, meaning objects can grow without manual intervention. DBAs can monitor and tune object growth using views like DBA_EXTENTS and DBA_SEGMENTS.
143
參考答案
The three recovery models are Full, Bulk-Logged, and Simple. The recovery model is chosen based on the amount of data loss one can afford. If minimal or no data loss is expected, the Full recovery model is a good choice. Depending on the recovery model, the behavior of the database log file changes.
144
參考答案
Replication involves copying data from one MySQL database to another, allowing for high availability, data redundancy, and read scalability.
145
參考答案
Start Database 1. CRS start: Start the Oracle Clusterware (CRS) service using crsctl start crs 2. Database start: Start the database instance using srvctl start database -d 3. ASM start: Start the Automatic Storage Management (ASM) instance using srvctl start asm -n Stop Database 1. Database stop: Stop the database instance using srvctl stop database -d 2. ASM stop: Stop the ASM instance using srvctl stop asm -n 3. CRS stop: Stop the CRS service using crsctl stop crs
146
參考答案
If Flashback Database is enabled, or if standby is in delay mode, or if we have RMAN backups, we can recover the truncated table by using Flashback, standby database, or tablespace point-in-time recovery.
147
參考答案
Given: Full backup Mon, incremental Tue (level 1), and hourly archivelog backups. If a single datafile corrupted at time X (after Tue), steps: - Identify SCN or time of corruption and the sequence of archivelogs needed. - Restore the corrupted datafile using RMAN from the most recent backup that contains that file (could be full backup from Monday or incremental that included that tablespace): - If incremental included the datafile and made it current to Tue, you can restore incremental + apply archivelogs since Tue. - - Steps: - RMAN will look for backups: full backup (Mon) + incremental backup (Tue) + archived logs from Tue->now. Since hourly archivelog backups exist, they'll be applied to bring file to current state. - If using block change tracking and incremental backups: consider RECOVER ... with incremental backups. - If datafile was part of tablespace that could be restored online, you may use RMAN> SWITCH andRECOVER . - After apply, open the tablespace or database: - If corruption is irrecoverable (missing required archived logs), you must restore full DB from last valid backup and roll forward using available archived logs, or use point-in-time recovery. In summary: restore the datafile from the most recent backup that contains it (full or incremental), then apply archived logs (hourly backups) from the time of that backup up to the required SCN/time.
148
參考答案
Data transfer between databases can be accomplished using several methods, including export/import utilities (such as Oracle Data Pump or SQL Server Import/Export Wizard), database links for direct queries, replication technologies, ETL (Extract, Transform, Load) processes, and bulk copy tools like BCP. The choice depends on factors such as data volume, latency requirements, and whether the transfer is one-time or ongoing.
149
參考答案
An index is a database structure that speeds up data retrieval from a table. It works like a book's index, providing quick lookup access paths to data rows based on key values.
150
參考答案
In Oracle Data Guard, MRP (Managed Recovery Process) is responsible for applying archived and redo logs to a physical standby database to keep it synchronized with the primary database.
151
參考答案
SQL Server offers Full backups (entire database), Differential backups (changes since last full), and Transaction Log backups (transactions since last log backup), crucial for point-in-time recovery.
152
參考答案
Synonym types are private and public.
153
參考答案
Users can save their password and explain their intent when accessing an SQL Server instance by setting customer SQL as their user-friendly incense; from here, users will have access to customer data.
154
參考答案
This question tests your problem-solving skills and experience. Discuss significant challenges you've encountered in your career as a DBA, how you approached them, and the solutions you implemented. One of the most challenging problems I faced was dealing with a large database that was running extremely slow. I diagnosed the problem, identified inefficient queries and missing indexes as the root cause, and fixed them. I also implemented a performance monitoring system to prevent such issues in the future.
155
參考答案
ARCH: Archive logs are sent by the ARCH process after log switch; no real-time apply (standby redo logs not required). SYNC: Synchronous redo transport for Maximum Protection/Availability; commits wait for standby acknowledgment. ASYNC: Asynchronous redo transport for Maximum Performance; commits don't wait for standby acknowledgment.
156
參考答案
Use ps -ef | grep pmon to check PMON process or lsnrctl status for listener status.
157
參考答案
To handle database backups, I follow a regular schedule and leverage automated backup tools to ensure data is protected. I perform full backups periodically and supplement them with incremental or differential backups to minimize data loss. As for disaster recovery planning, I develop comprehensive strategies that involve offsite backups, standby servers, and replication. I conduct periodic tests and simulations to validate the effectiveness of the recovery plan.
158
參考答案
The SQL Agent is SQL Server's built-in job scheduling service. I use it extensively to automate routine administrative tasks. This includes scheduling database backups and restores, performing regular integrity checks like DBCC CHECKDB, and managing index rebuilds or reorganizations. It ensures that critical maintenance tasks run reliably and on schedule, reducing manual effort and improving overall database health.
159
參考答案
SQLPlus is a command-line tool for Oracle Database that allows users to interact with the database using SQL and PL/SQL commands. When passing SQL commands as arguments to SQLPlus in a Linux environment, there are limits on the maximum length of the command line. In this article, we will learn about the Maximum length of command line arguments that can be passed to SQL*Plus. Command Line Arguments in SQL*Plus Command line arguments in SQL*Plus serve as parameters that control the behavior of the tool. These arguments can include script names, connection details, and other options. Users frequently utilize these arguments to streamline their workflows and automate tasks. Maximum Length of Command Line Argument The maximum length of a command line argument is determined by the operating system. In Linux, the maximum length is typically 131,072 bytes (or 128 KB). This includes the length of the SQL*Plus command, SQL query, and any additional parameters. Syntax: sqlplus [username]/[password]@[database] @script.sql - sqlplus: Command to start SQL*Plus. - [username]/[password]@[database]: Connection details to log in to the Oracle Database. - @script.sql: The SQL script file to be executed.
160
參考答案
ALTER USER AISHU DEFAULT TABLESPACE
161
參考答案
Normalization is the process of organizing database tables to reduce data redundancy and improve data integrity. It involves dividing large tables into smaller, related tables and defining relationships between them, typically following normal forms such as 1NF, 2NF, and 3NF.
162
參考答案
Start the database in NOMOUNT mode.Restore the SPFILE and Control Files if required. Mount the database and restore the datafiles using RMAN. Recover the database using archived logs. Open the database using RESETLOGS if necessary.
163
參考答案
Enlisted are a few of the best practices for writing SQL queries. 1.Column names should be provided instead of * in SELECT statements. 2.Joins should be used in the place of sub-queries. 3.EXISTS should be used instead of IN to verify the existence of data. 4.UNION ALL should be used in the place of UNION. 5.HAVING should be used only for filtering the resulted rows from the SQL query.
164
參考答案
Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.
165
參考答案
This is an important question because hiring managers expect you to be able to work with a specific number of databases. The number of databases will be relative to the size of the database you manage. When answering the question, mention the number of databases you're capable of working with at one time, along with their size. This is crucial because, for example, if you have worked with five at a time, but you fail to mention they were 1 TB, you'll give the interviewer an inaccurate idea of your skills and experience.
166
參考答案
Yes, RMAN backups can be taken in the MOUNT state. This mode is commonly used for consistent backups, especially for whole database backups.
167
參考答案
A DPO must be appointed in three specific cases: 1) public authorities, 2) organizations that engage in large-scale systematic monitoring, or 3) organizations that engage in large-scale processing of sensitive personal data. If your organization doesn't fall into these categories, it's not necessary to appoint a DPO.
168
參考答案
Changing the physical database file name is multistep activity. We must not perform such activities on regular basis and only do them on a need basis. Below are the high-level steps to change a physical file of a database. - Set database in OFFLINE state - Go to the location where your database files are saved and rename each file as per your desired naming convention - Update system catalog with new files names by running ALTER DATABASE statement for each file separately USE master GO ALTER DATABASE SQLSHACK MODIFY FILE (Name='SQLSHACK_Data1', FILENAME='F:\MSSQL12.MSSQLSERVER\MSSQL\DATA\SQLSHACK_Renamed.mdf') GO - Bring database ONLINE
169
參考答案
RMAN (Recovery Manager) is an Oracle tool for backup and recovery automation. To check the configuration: SHOW ALL;
170
參考答案
Unlimited, as much as CPU and memory is supported.
171
參考答案
Reduce time-to-screen by 80% while improving candidate experience and consistency.
172
參考答案
1. CPU (Critical Patch Update) patching: Apply quarterly CPU patches to fix security vulnerabilities. 2. PSU (Patch Set Update) patching: Apply quarterly PSU patches to fix bug fixes, security patches, and stability improvements. 3. BP (Bundle Patch) patching: Apply bundle patches that include a collection of fixes for specific Oracle Database versions. 4. RU (Release Update) patching: Apply release updates that include a collection of fixes, security patches, and new features for specific Oracle Database versions. 5. OPatch patching: Apply individual patches using OPatch, Oracle's patching tool. 6. Grid Infrastructure patching: Patch Oracle Grid Infrastructure (GI) components, such as Oracle Clusterware and Oracle Automatic Storage Management (ASM). 7. Database patching: Patch Oracle Database software, including the database kernel and other components.These patching activities help ensure the security, stability, and performance of Oracle databases.
173
參考答案
I monitor Data Guard sync using views like V$DATAGUARD_STATS for transport/apply lag, V$ARCHIVE_DEST_STATUS to check log shipping, and V$STANDBY_LOG for redo log usage. I also use OEM for visual monitoring and Data Guard Broker for automated health checks and alerts.
174
參考答案
Control File: Stores database metadata such as data file locations and log sequences.Parameter File (PFILE/SPFILE): Contains configuration parameters for the database instance.
175
參考答案
Use the DB_NK_CACHE_SIZE dynamic parameter in PFILE or SPFILE. (NK can be 2K, 4K, 8K, 16K, or 32K.)Example: ALTER SYSTEM SET DB_4K_CACHE_SIZE=512M SCOPE=BOTH; If DB_BLOCK_SIZE=8K, then DB_8K_CACHE_SIZE is not allowed.
176
參考答案
No, the database does not need to be stopped. Flashback Database can be enabled while the database is running.
177
參考答案
There are five types of SQL statements
178
參考答案
Tools and processes include implementing checksums, leveraging database-integrated consistency checks, scheduling regular backups with verification routines, deploying data replication across multiple nodes, and using monitoring solutions that alert on anomalies.
179
參考答案
Yes.
180
參考答案
Using Cluster Administrator, connect to the cluster and select the SQL Server cluster. Once the SQL Server group is selected, on the right-hand side of the console, the column 'Owner' gives the information of the node on which the SQL Server group is currently active.
181
參考答案
Event scheduling allows you to schedule and automate recurring tasks within the database, similar to cron jobs.
182
參考答案
Every Oracle database has one or more physical datafiles. Datafiles contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the datafiles allocated for a database.
183
參考答案
ORA-01555 occurs when a query tries to access an older version of data that has been overwritten in the undo tablespace before the query completed. This usually happens during long-running queries or when undo retention is too low. To handle it: • Increase undo tablespace size and undo retention period to keep undo data longer. • Optimize queries to run faster, reducing undo data age. • Avoid unnecessary large transactions that hold undo for a long time. • Use Oracle's Automatic Undo Management to better handle undo space. • Monitor undo tablespace usage and tune accordingly. • If it occurs in batch jobs, break the job into smaller transactions. • Check for high undo segment contention or frequent commits.
184
參考答案
My capacity planning process starts with analyzing historical performance trends for CPU, memory, I/O, and storage usage. I then factor in anticipated business growth, like new users or applications, and project future resource requirements. This involves forecasting peak workloads and identifying potential bottlenecks. I'd then propose scalable solutions, whether that's recommending hardware upgrades, optimizing queries, or leveraging cloud scalability. The goal is to ensure the database can always meet demand efficiently without performance degradation.
185
參考答案
Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.
186
參考答案
Server processes execute SQL received from user processes.
187
參考答案
The SHOW ENGINE statement displays information about MySQL storage engines, status, and variables.
188
參考答案
FGA policies audit access to specific rows or columns based on user-defined conditions. Features: • Enables detailed monitoring of sensitive data access. • Can audit SELECT, INSERT, UPDATE, DELETE operations selectively. • Useful for compliance requirements like GDPR or HIPAA. • Policies are created using DBMS_FGA.ADD_POLICY specifying the object, columns, and conditions. • Generates audit records only when conditions are met, reducing noise. FGA helps organizations detect unauthorized or suspicious access to critical data.
189
參考答案
To optimize database queries, I typically begin by identifying slow-running queries using Profiler or Extended Events. Then, I analyze their execution plans to pinpoint bottlenecks, such as missing indexes or inefficient joins. I'd then propose creating new indexes, modifying existing ones, or suggesting query rewrites to developers. Additionally, I regularly ensure statistics are up-to-date and address any fragmentation issues, all aimed at improving the query optimizer's efficiency and overall database performance.
190
參考答案
Important OS-level metrics include: • CPU usage: High CPU can indicate inefficient queries or processes. • Memory usage: Insufficient memory causes swapping and slows DB. • Disk I/O: Monitor throughput and latency to detect bottlenecks. • Network traffic: Ensure bandwidth is sufficient for DB operations. • Process count: Too many processes can exhaust system resources. • Load average: High load suggests CPU saturation. • Swap usage: Excessive swapping degrades performance. • Filesystem free space: Running out of disk space affects database files. Monitoring these helps identify infrastructure issues affecting database performance.
191
參考答案
Profiles are assigned to users, roles, and other profiles.
192
參考答案
Answers will vary, but you need to be on the lookout for applicants who name skills that align with your requirements.
193
參考答案
Say any value between 700 DB to 1.5 TB.
194
參考答案
SQL Plan Baseline is a feature to control execution plans for SQL queries. When a good plan is captured, Oracle stores it in the baseline. If a future change (e.g., stats, patch, bind value) causes a new bad plan, Oracle can reject it and keep using the trusted plan from the baseline. Benefits: • Avoids plan regressions. • Stabilizes performance for critical SQLs. • Ensures consistent performance after upgrades or re-gathering stats. Use DBMS_SPM package to manage baselines.
195
參考答案
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.
196
參考答案
Bulk insert involves inserting a large number of rows into a table in a single statement, improving performance compared to individual inserts.
197
參考答案
Look for: Time management and resource allocation skills.
198
參考答案
Look for: Independence and self-motivation.
199
參考答案
Yes, but the problem is if the listener is down, all new connections to multiple DB will not be accepted. It is suggested to have different listeners for each database. If we bring down one listener for one database, other listeners will not be affected.
200
參考答案
AIR replaced resume screening, skills assessments, phone screening, and scheduling tools. ROI was positive in the first month.


Copyright © 2024. Designer by SPOTO Official Website. All Rights Reserved.