Realtime Activities DBA

Examples of real-time activities that a DBA might perform:

• Monitoring database performance: DBAs use a variety of tools to monitor database performance, such as CPU usage, memory usage, disk I/O, and network traffic. They use this information to identify any potential problems and take corrective action.
• Tuning database queries: DBAs use a variety of techniques to tune database queries, such as optimizing the query plan, using indexes, and partitioning tables. This can help to improve the performance of queries and reduce the load on the database.
• Backing up and restoring databases: DBAs are responsible for backing up databases on a regular basis. This ensures that the data can be restored in the event of a disaster.
• Monitoring database security: DBAs use a variety of tools to monitor database security, such as auditing database activity and configuring permissions. This helps to protect the data from unauthorized access.
• Managing database users: DBAs are responsible for creating and managing database users. This includes assigning permissions to users and managing their passwords.
• Monitoring database logs: DBAs use database logs to track activity in the database. This information can be used to troubleshoot problems and identify any potential security threats.

RESTORE: The RESTORE statement is used to restore a database from a backup. The following is an example of a RESTORE statement:

SQL

RESTORE DATABASE my_database

FROM DISK = '/path/to/my_database.bak';

 

This statement will restore the database my_database from the backup file /path/to/my_database.bak.

BACKUP: The BACKUP statement is used to create a backup of a database. The following is an example of a BACKUP statement:

SQL

BACKUP DATABASE my_database

TO DISK = '/path/to/my_database.bak';

 

 

This statement will create a backup of the database my_database and save it to the file /path/to/my_database.bak.

ROLLBACK: The ROLLBACK statement is used to roll back a transaction. The following is an example of a ROLLBACK statement:

SQL

ROLLBACK;

 

This statement will roll back the current transaction. If there are no uncommitted changes, the database will be restored to its state before the transaction started.

The following is an example of a ROLLBACK statement that rolls back a transaction to a specific point in time:

SQL

ROLLBACK TO '2023-08-07 12:00:00';

FLASHBACK a table to a specific point in time

FLASHBACK TABLE my_table TO TIMESTAMP '2023-08-07 12:00:00';

 

This statement will flashback the table my_table to the state it was in at 12:00:00 on August 7, 2023.

 

FLASHBACK TABLE my_table TO SCN 1234567890;

This statement will flashback the table my_table to the state it was in at the checkpoint with ID 1234567890.

 =========

• To check the status of the database:

SQL

SELECT status FROM v$database;

 

 

• To check the number of users connected to the database:

SQL

SELECT COUNT(*) FROM v$session;

 

 

• To check the top 10 queries that are consuming the most CPU time:

SQL

SELECT sql_id,

       executions,

       total_elapsed_time,

       avg_elapsed_time

FROM v$sql

ORDER BY total_elapsed_time DESC

LIMIT 10;

 

 

• To check the top 10 tables that are consuming the most disk space:

SQL

SELECT table_name,

       sum(bytes) AS total_bytes

FROM dba_data_files

GROUP BY table_name

ORDER BY total_bytes DESC

LIMIT 10;

 

 

• To check the top 10 users that are consuming the most memory:

SQL

SELECT username,

       sum(bytes) AS total_bytes

FROM v$process

GROUP BY username

ORDER BY total_bytes DESC

LIMIT 10;

 

 

• To check the number of errors that have occurred in the database in the last 24 hours:

SQL

SELECT COUNT(*) FROM dba_errors

WHERE timestamp > SYSDATE - INTERVAL '1 DAY';

 

 

• To check the current wait events for all users:

SQL

SELECT username,

       event,

       max_wait_time,

       time_waited

FROM v$session_waits;

 

 

• To check the current wait events for a specific user:

SQL

SELECT username,

       event,

       max_wait_time,

       time_waited

FROM v$session_waits

WHERE username = 'username';

 

 ====================

1. SELECT status FROM v$database;
2. SELECT COUNT(*) FROM v$session;
3. SELECT sql_id, executions, total_elapsed_time, avg_elapsed_time FROM v$sql ORDER BY total_elapsed_time DESC LIMIT 10;
4. SELECT table_name, sum(bytes) AS total_bytes FROM dba_data_files GROUP BY table_name ORDER BY total_bytes DESC LIMIT 10;
5. SELECT username, sum(bytes) AS total_bytes FROM v$process GROUP BY username ORDER BY total_bytes DESC LIMIT 10;
6. SELECT COUNT(*) FROM dba_errors WHERE timestamp > SYSDATE - INTERVAL '1 DAY';
7. SELECT username, event, max_wait_time, time_waited FROM v$session_waits;
8. SELECT username, event, max_wait_time, time_waited FROM v$session_waits WHERE username = 'username';
9. SELECT sql_id, event, total_elapsed_time, avg_elapsed_time FROM v$sql WHERE sql_id = 'sql_id';
10. SELECT count(*) FROM dba_tables WHERE tablespace_name = 'tablespace_name';
11. SELECT sum(bytes) FROM dba_data_files WHERE tablespace_name = 'tablespace_name';
12. SELECT username, count(*) FROM v$session WHERE username = 'username';
13. SELECT sql_id, count(*) FROM v$sql WHERE sql_id = 'sql_id';
14. SELECT event, count(*) FROM v$session_waits GROUP BY event;
15. SELECT event, max_wait_time, time_waited FROM v$session_waits WHERE event = 'event';
16. SELECT sql_id, count(*) FROM v$sql WHERE sql_text LIKE '%keyword%';
17. SELECT username, count(*) FROM v$session WHERE username LIKE '%keyword%';
18. SELECT event, count(*) FROM v$session_waits WHERE event LIKE '%keyword%';
19. SELECT sql_id, event, total_elapsed_time, avg_elapsed_time FROM v$sql WHERE sql_text LIKE '%keyword%';
20. SELECT username, event, total_elapsed_time, avg_elapsed_time FROM v$session_waits WHERE username LIKE '%keyword%';