Most commonly used SQL in Oracle Database by DBA’s

Posted on by Manpreet Singh

In the dynamic world of database management, SQL is the essential toolkit for Oracle DBAs. It’s the language they use daily to keep the systems running smoothly, secure the data, and ensure peak performance. But which SQL commands do they turn to most often? This article takes you inside the world of an Oracle DBA, highlighting the key SQL commands that are indispensable in their day-to-day work. Whether it’s maintaining the database, troubleshooting issues, or optimizing performance, these are the go-to SQL statements that keep everything on track.

  1. Identify Your Current Database in a Snap: Need to know which Oracle database you’re in? Just fire off SELECT name FROM v$database; and instantly get the name of your current database. It’s a quick and reliable way to confirm where you are, giving you that crucial bit of context with a single, swift command.
  2. Unveil All Your Pluggable Databases at Once: Curious about the pluggable databases in your Oracle environment? The commands SELECT name FROM v$pdbs; and SELECT pdb_name FROM dba_pdbs; have you covered. Both deliver the same result—instantly listing all pluggable databases in your system. Whether you’re managing multiple PDBs or just keeping tabs on what’s available, these commands give you a clear view with a single execution.
  3. Master the Connection with SYSDBA Privileges: When you need full control over your Oracle database, connecting as SYSDBA is your ultimate power move. Simply use connect sys/admin@localhost:1521/XEPDB1 as sysdba, where “sys” is your username, “admin” is your password, and “localhost:1521/XEPDB1” pinpoints the host, port, and database name. The sysdba keyword grants you top-level privileges, unlocking the ability to perform nearly any administrative task within the database. This command is your gateway to the highest authority in Oracle database management.
  4. Create a User: Need to add a new user to your Oracle database? The CREATE USER demouser IDENTIFIED BY demouser DEFAULT TABLESPACE users QUOTA UNLIMITED ON users; command is your tool of choice. It swiftly creates a user named “demouser,” secured with a password, and sets “users” as the default tablespace. With unlimited storage granted on that tablespace, this new user is ready to go, fully equipped for whatever tasks lie ahead.
  5. Empower Your User with Essential Privileges: Get your new user up and running with the right permissions by executing GRANT CREATE SESSION, CREATE TABLE TO demouser;. This command instantly equips “demouser” with the ability to start sessions and create tables, laying the groundwork for productive database interactions. With just one line, you’re granting the power to connect and build within your Oracle environment.
  6. Seamlessly Connect with Your New User: Simply run CONNECT demouser/demouser@localhost:1521/XEPDB1; to log in with “demouser” and their password. This command effortlessly connects you to the database, allowing you to start exploring and managing with your freshly created user credentials.
  7. Spin Up a New Pluggable Database with Ease: Kick off a new pluggable database by executing CREATE PLUGGABLE DATABASE mockpit ADMIN USER mockpitadmin IDENTIFIED BY mockpitadmin ROLE=(dba) CREATE_FILE_DEST='C:\app\manna\product\21c\oradata\mockpit';. This command sets up “mockpit” with an admin user, “mockpitadmin,” granting DBA privileges and specifying the storage location. It’s a straightforward way to expand your database environment and get a new pluggable database up and running in no time.
  8. Determine Your Database’s CDB Status in a Flash: Want to know if your database is a Container Database (CDB)? Just run SELECT name, open_mode, cdb FROM v$database;. This quick command reveals the database name, its open mode, and whether it’s a CDB. It’s your instant ticket to understanding the structure of your Oracle environment and ensuring you’re working with the right type of database.
  9. Reveal Container ID and Name Instantly: Need to identify your current container? Use SHOW CON_ID; and SHOW CON_NAME; to swiftly display the container ID and name. These commands provide a quick snapshot of your current container’s details, helping you stay oriented and informed as you navigate through your Oracle environment.
  10. Uncover All Containers with One Command: To get a complete view of all your containers, simply run SELECT name, open_mode FROM v$containers;. This command quickly lists every container in your Oracle environment along with their open modes. It’s your fast track to understanding the full scope of your database’s container setup.
  11. Open Your Pluggable Database and Activate Read-Write Access: To get your pluggable database up and running, execute ALTER PLUGGABLE DATABASE mockpit OPEN;. By default, new pluggable databases start in ‘Mounted’ status. This command transitions them to ‘Read Write’, making them fully accessible and ready for use. It’s your key to unlocking the database for full interaction and management.
  12. Reveal the Current Instance Name Instantly: To find out the name of the current Oracle instance, use SELECT sys_context('USERENV', 'instance_name') AS sid FROM DUAL;. This command quickly fetches the instance name, giving you a clear view of the database environment you’re working in. It’s a straightforward way to ensure you’re connected to the right instance.
  13. Connect to Your New PDB: To access your newly created Pluggable Database (PDB), start by connecting as SYSDBA with CONNECT sys/admin@localhost:1521/mockpit AS SYSDBA;. Then, switch to the PDB using CONNECT mockpitadmin/mockpitadmin@localhost:1521/mockpit;. This sequence ensures you’re fully equipped to manage and interact with your new PDB, leveraging the full range of administrative and user capabilities.
  14. Discover All Active Services in Your Database: To get a comprehensive list of all services for your current Oracle database, run SELECT name, pdb FROM v$services;. This command reveals each service and its associated pluggable database, giving you a clear overview of the active services in your environment. It’s the perfect tool for managing and optimizing your database’s service setup.
  15. Ensure Smooth Connectivity with Updated TNS Configuration: To prevent TNS service shutdowns following Oracle server installation, update your tnsnames.ora file by setting the HOST to localhost. After making this change, restart your Oracle instance and service to apply the update. This adjustment helps maintain a stable connection and ensures your Oracle environment runs seamlessly.
  16. Get All Users in Your Database: Quickly retrieve a list of all users visible to your current session by running SELECT username FROM ALL_USERS;. This command taps into the ALL_USERS data dictionary view, providing an instant snapshot of every user in the database. It’s your go-to for a comprehensive overview of user accounts and their visibility within your Oracle environment.
  17. Discover Your Current User Details: To get precise information about the user account you’re currently logged in with, execute SELECT username FROM USER_USERS;. This command zeroes in on the specifics of your active user, giving you a clear view of who’s connected and actively working in your Oracle database. It’s a quick and direct way to confirm your current user credentials.
  18. Different Ways to Connect to Your Database: Choose from two simple ways to connect to your Oracle database: use sqlplus <username>/<password>@<SID> for a direct connection with your System Identifier, or sqlplus <username>/<password>@<tnsnames.ora content> to connect via the TNS entry defined in your tnsnames.ora file. Both methods provide quick access to your database, letting you dive into your tasks with ease.
  19. Session vs. Connection: Understanding the Basics: Grasp the key difference between session and connection: a session represents the link between a user and the database, encapsulating the user’s interactions and activities. In contrast, a connection refers to the underlying technical setup between the user process and the database server process. Essentially, while a session is your user’s active presence in the database, a connection is the framework facilitating that presence.
  20. Decoding the Connection String Format: The connection string format for Oracle databases is crafted as host[:port][/[service_name][:server][/instance_name]]. Here’s the breakdown: host is the Oracle database’s hostname or IP address (with IPv6 addresses in brackets). The optional port specifies the TCP port (default is 1521 if omitted). service_name is the database service name, which can be derived from DB_NAME and DB_DOMAIN. The server type (dedicated, shared, or pooled) determines the service handler, with default settings applied if omitted. Finally, instance_name targets a specific instance within the database. This structured format ensures precise connections to your Oracle environment.
  21. Quickly List All Active Database Connections: To view all active Oracle database connections, use the command ps -ef | grep ora. This command scans for Oracle-related processes running on your system, giving you an immediate snapshot of all database connections. It’s a powerful way to monitor and manage the connections currently in play within your Oracle environment.
  22. Count Active Database Connections : To quickly tally the number of active Oracle database connections, use the command ps -ef | grep ora | wc -l. This command first identifies all Oracle-related processes and then counts them, giving you an instant total of current database connections. It’s a straightforward method to monitor connection activity in your Oracle environment.
  23. Explore the Full Spectrum of Database Metadata: To delve into every record in Oracle’s data dictionary, execute SELECT * FROM dictionary;. This command opens up a comprehensive view of all the metadata stored in your database, offering insights into every aspect of its structure and organization. It’s a powerful tool for DBAs to explore and understand the intricacies of their Oracle environment.
  24. Uncover All Objects Owned by Your User: To see everything your current user owns in the database, run SELECT * FROM user_objects;. This command lists all the objects—like tables, views, indexes, and more—associated with your user account, providing a complete inventory of your database assets. It’s an essential query for managing and auditing your user-specific resources in Oracle.
  25. Get a Complete List of User Tables: To view all tables owned by your current user, simply execute SELECT * FROM user_tables;. This command is equivalent to running SELECT * FROM user_objects WHERE object_type = 'TABLE';, providing a focused list of all tables in your schema. It’s a quick and efficient way to audit and manage the tables you’ve created in your Oracle environment.
  26. Get a Snapshot of All Tablespaces: To see all tablespaces in your Oracle database, use SELECT tablespace_name, status, logging FROM dba_tablespaces;. This command not only lists the tablespace names but also provides their status (online or offline) and logging mode. It’s an essential query for monitoring and managing your database’s storage architecture, giving you clear insights into how your tablespaces are configured and operating.
  27. Identify Your Current Schema Instantly: To quickly determine which schema you’re currently working in, run SELECT SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') FROM DUAL;. This command fetches the name of the active schema, ensuring you know exactly where you are within the database. It’s a handy tool for keeping track of your working environment and avoiding any schema-related confusion.
  28. Monitor All Active Sessions in Real-Time: To keep an eye on active sessions within your Oracle database, use SELECT sid, serial#, username, status FROM v$session WHERE status = 'ACTIVE';. This command provides a real-time list of all currently active sessions, displaying session IDs, serial numbers, usernames, and their status. It’s a vital query for DBAs to track ongoing user activity and manage session performance.
  29. Kill a User Session: To immediately end a problematic or inactive user session, use ALTER SYSTEM KILL SESSION '<sid, serial#>';. By specifying the session ID (SID) and serial number, you can precisely target and terminate the session in question. This command is crucial for maintaining database performance and resolving issues that may arise from unresponsive or resource-heavy sessions.
  30. Identify Long-Running Queries: To spot queries that have been running for an extended period, execute SELECT sid, serial#, username, elapsed_seconds, sql_id FROM v$session_longops ORDER BY elapsed_seconds DESC;. This command lists all long-running operations, showing session IDs, serial numbers, usernames, elapsed time, and SQL IDs, sorted by duration. It’s an essential query for DBAs to monitor and optimize performance by identifying and addressing slow-running queries in real-time.
  31. Spot Blocking Sessions: To identify sessions causing blockages in your Oracle database, run SELECT s.sid, s.serial#, s.username, s.status, l.block FROM v$session s INNER JOIN v$lock l ON s.sid = l.sid WHERE s.status = 'WAITING' AND l.block IS NOT NULL;. This command reveals sessions that are waiting and involved in blocking, providing session IDs, serial numbers, usernames, statuses, and blocking details. It’s a key query for diagnosing and resolving issues related to session contention and improving database performance.
  32. Track Active SQL Statements in Real-Time: To get a detailed look at currently running SQL statements, use SELECT S.USERNAME, s.sid, s.osuser, t.sql_id, sql_text FROM v$sqltext_with_newlines t JOIN V$SESSION s ON s.sql_address = t.address AND t.hash_value = s.sql_hash_value WHERE s.status = 'ACTIVE' AND s.username NOT IN ('SYSTEM') ORDER BY s.sid, t.piece;. This command provides a comprehensive view of active SQL queries, including usernames, session IDs, OS users, SQL IDs, and the SQL text itself. It’s an invaluable tool for monitoring and analyzing active SQL statements to ensure efficient database operation.
  33. Grant Schema Object Access to Another User: To grant access to schema objects owned by one user to another user, use the following PL/SQL block: 
DECLARE
  l_table_name dba_tables.table_name%TYPE;
  CURSOR c_tables IS
    SELECT table_name
    FROM dba_tables
    WHERE owner = 'CO';
BEGIN
  OPEN c_tables;
  LOOP
    FETCH c_tables INTO l_table_name;
    EXIT WHEN c_tables%NOTFOUND;
    -- Grant desired privileges (e.g., SELECT) 
    EXECUTE IMMEDIATE 'GRANT SELECT ON CO.' || l_table_name || ' TO sampleuser';
  END LOOP;
  CLOSE c_tables;
END;
/

This script dynamically grants SELECT privileges on all tables owned by the user ‘CO’ to ‘sampleuser’. Modify the privileges and user names as needed to tailor the access control to your specific requirements.

34. Generate and Review Your SQL Execution Plan: To analyze how Oracle will execute your SQL query, use the EXPLAIN PLAN command:

EXPLAIN PLAN FOR SELECT * FROM customers;
SELECT plan_table_output FROM TABLE(dbms_xplan.display('PLAN_TABLE', NULL, 'BASIC'));

This process generates an execution plan without running the actual SQL. The dbms_xplan.display() function then provides a detailed breakdown of the plan. Key arguments include the plan table name (default ‘PLAN_TABLE’), the statement ID (default NULL), and the format (‘BASIC’ for a concise view). This approach helps you understand and optimize your query performance efficiently.

35. Generate and Analyze SQL Execution Plans: To review the execution plan of a SQL query, use the following methods:

  • For Last Executed SQL: 
SELECT plan_table_output FROM TABLE(dbms_xplan.display_cursor(NULL, NULL, 'BASIC'));

This retrieves the plan for the most recently executed SQL statement.

  • From SQL Cache:
SELECT plan_table_output FROM TABLE(dbms_xplan.display_cursor(<sql_id>, NULL, 'BASIC'));

Replace <sql_id> with the specific SQL ID to fetch the plan from the cache.

  • View from SQL Plan Cache:
SELECT * FROM v$sql_plan;

This command provides detailed information on execution plans stored in the cache.

  • From Historical Views:
SELECT plan_table_output FROM TABLE(dbms_xplan.display_awr(<sql_id>, NULL, 'BASIC'));

Use this to get execution plans from historical AWR data by specifying the SQL ID.

Note: Execution plans returned are specific to the SQL ID you provide. The dbms_xplan.display_cursor() function can take arguments like SQL ID (defaulting to the last executed statement), child number (default 0), and format (options: ‘BASIC’, ‘TYPICAL’, ‘ALL’) to control the level of detail displayed.

36. Decode Your SQL Execution Plan: When analyzing an execution plan, pay close attention to the indentation in the Operation column. The most indented operations are executed first, while operations at the same indentation level are executed sequentially as they appear in the plan. This hierarchical view helps you understand the order of operations and the execution flow, making it easier to optimize query performance and troubleshoot issues.

Summary: Mastered Oracle Database SQL and Execution Plans

This article provided a dynamic guide to essential Oracle SQL commands and techniques for database administrators. It covered key operations such as listing current database names, creating and managing users and pluggable databases, and configuring database connections. It explained how to handle long-running queries, blocking sessions, and active user sessions, along with generating and interpreting execution plans.

Key highlights included:

  • Listing Databases and Sessions: Commands were detailed to identify the current database, view active sessions, and list all containers and user objects.
  • Managing Connections and Users: Techniques were outlined for connecting to databases, creating users, and granting privileges.
  • Execution Plan Insights: Methods were described for generating and reading execution plans, essential for optimizing SQL performance and understanding query execution.

By mastering these commands and concepts, DBAs could efficiently manage, monitor, and optimize their Oracle databases, ensuring smooth and effective database operations.

Implement these SQL commands in your Oracle environment to enhance your database management skills. Explore each command, apply the techniques, and optimize your database operations for better performance and reliability.