SQL Developer provides a graphical connection manager, SQL worksheet, and object browser. A useful inspection follows a clear chain: connect to the intended database, confirm the session, select the intended object, then explain its rows and definition.
Oracle DBA Lesson 18 Part 1 — Inspect a Database with SQL Developer
Connect and confirm the destination
For a Basic Oracle connection, the hostname and listener port identify the connection endpoint. The configured Service name identifies the service used to reach the database. In a multitenant database, use a service configured for the intended pluggable database. A service name can differ from the container name.
Use the database account and the default role for ordinary connections. Selecting an administrative role requires the corresponding database privilege. Passwords belong in the connection dialog, not in saved SQL or shared screenshots.
The saved connection's Name is a client alias. A name such as ROOT can still refer to a connection configured for a PDB. A successful Test establishes that the entered details can make a connection. Actual session context and permission for each intended operation must still be checked.
See SQL Developer connection dialogs and multitenant administration.
Open a worksheet associated with the intended connection and run:
SELECT USER AS current_user,
SYS_CONTEXT('USERENV','CON_NAME') AS container_name
FROM dual;In an ordinary database-authenticated session, USER identifies the logged-on database user. CON_NAME reports the current container in a CDB.
| CURRENT_USER | CONTAINER_NAME |
|---|---|
| APP_USER | APP_PDB |
Compare both values with the intended target. The connection alias helps navigation. The query supplies evidence about the session where that statement ran. Repeat the check after changing a worksheet connection or reconnecting. An unshared worksheet can have a separate session, so do not assume all open windows share transactions.
See USER and SYS_CONTEXT.
Query rows and choose the execution mode
SELECT employee_id, employee_name
FROM app_owner.employees
ORDER BY employee_id;APP_OWNER qualifies the table's schema. This matters when the connected account is APP_USER and the table belongs to another schema. The account needs an appropriate table privilege, such as a direct SELECT grant. ORDER BY makes the requested presentation order explicit.
| EMPLOYEE_ID | EMPLOYEE_NAME |
|---|---|
| 101 | Support user |
| 102 | Report user |
Execute Statement runs the current statement at the cursor and presents query rows in a result grid. With an explicitly selected statement, it runs the selection. Run Script uses the Script Runner to process the worksheet as a script and presents output in Script Output. Script Runner supports a documented subset of SQL*Plus functionality. Review a saved script's client commands before reusing it. Choose the mode deliberately, especially when several statements are present.
See SQL Developer concepts and usage.
The same query can be issued in SQL*Plus. With equivalent user and container context, unchanged data, and comparable session settings, the logical query result should agree even though the clients format it differently. Separate sessions have their own transaction state, and concurrent changes can explain different results.
Inspect values and metadata
The Connections navigator lists objects accessible through its connected account. For an accessible table in another schema, look under Other Users, select its owner, and open the table. Menu arrangement and object visibility depend on client version, privileges, and filters. When navigation is unavailable, a qualified worksheet query remains a clear way to identify the intended table.
The Data tab displays row values. The Columns tab describes structure: column names, data types, and nullability. This descriptive information is metadata—information about the data.
For example, Support user is one employee-name value. The definition EMPLOYEE_NAME VARCHAR2(80) NOT NULL says that the column stores variable-length character values with a declared length limit, and requires a non-null value. The length's byte or character semantics depend on the definition and configuration. Inspect those details when multibyte characters matter. A primary key imposes its own uniqueness and non-null requirements.
| Question | Relevant evidence |
|---|---|
| What employee name is stored in this row? | Data tab or a SELECT result |
| What type and nullability does that column have? | Columns tab or dictionary metadata |
| How is this object defined? | Available SQL/definition tab. Review the complete definition. |
See SQL Developer concepts and usage.
This read-only dictionary query is an alternative for accessible column metadata:
SELECT column_name, data_type, nullable
FROM all_tab_columns
WHERE owner = 'APP_OWNER'
AND table_name = 'EMPLOYEES'
ORDER BY column_id;The unquoted identifiers appear uppercase in dictionary predicates. NULLABLE = 'N' indicates a non-null requirement. Y indicates that nulls are allowed under the view's documented conditions. ALL_TAB_COLUMNS describes accessible objects. USER_TAB_COLUMNS describes owned objects, and the DBA variant requires appropriate dictionary access. The three selected fields do not expose every detail. For example, consult length, precision, and scale fields, or the complete definition, when the declared datatype size matters.
See ALL_TAB_COLUMNS.
Read definitions before applying changes
For many objects, the SQL tab displays SQL for creating the object. Read it to understand the definition. The video's definition excerpt contains an ellipsis. It is not a complete executable script.
Before applying generated SQL, review the complete target, object names, statement, and consequences. A syntactically valid DDL statement causes an implicit commit before execution, even when execution subsequently fails. Successful DDL also causes an implicit commit afterward. Resolve pending transactional work deliberately before running DDL. Merely opening a definition tab is different from executing its statement.
See COMMIT.
The database checks privileges for each operation. CREATE SESSION allows a login. Table access and DBA navigator operations require their own appropriate privileges. An insufficient-privileges error, such as ORA-01031, calls for checking the intended operation and delegated access. It is not a reason to switch routine inspection to an unnecessarily powerful account.
If other work introduced pending changes, explicitly choose the intended commit or rollback. SQL Developer can present a disconnect decision. Removing a client connection definition does not drop the database user or table.
Recap
Connect to the intended database, confirm the session, select the intended object, then explain its rows and definition. For a Basic connection, the hostname and listener port identify the endpoint, and the service name identifies the service used to reach the database. In a multitenant database, use a service configured for the intended pluggable database. A service name can differ from the container name. The saved connection name is a client alias, so a name such as ROOT can still refer to a PDB. A successful Test shows that the entered details can make a connection. Session context and permission for each intended operation still have to be checked. Passwords belong in the connection dialog, not in saved SQL or shared screenshots. In an ordinary database-authenticated session, USER identifies the logged-on database user, and SYS_CONTEXT('USERENV','CON_NAME') reports the current container in a CDB. Repeat that check after changing a worksheet connection or reconnecting. An unshared worksheet can have a separate session, so do not assume all open windows share transactions. APP_OWNER qualifies the table's schema when the connected account is APP_USER. ORDER BY makes the requested presentation order explicit. Execute Statement runs the current statement at the cursor and presents a result grid. With an explicitly selected statement, it runs the selection. Run Script uses Script Runner and presents output in Script Output. Review a saved script's client commands before reusing it. The Data tab shows row values. The Columns tab shows names, data types, and nullability. That description is metadata. Support user is a row value. EMPLOYEE_NAME VARCHAR2(80) NOT NULL says the column stores variable-length character values with a declared length limit and requires a non-null value. ALL_TAB_COLUMNS describes accessible objects. NULLABLE = 'N' indicates a non-null requirement, and the three selected fields do not include every detail, such as length, precision, and scale. Read the SQL tab before applying generated SQL. The video's excerpt contains an ellipsis and is not a complete executable script. Syntactically valid DDL causes an implicit commit before execution, even when execution subsequently fails, and successful DDL causes an implicit commit afterward. Opening a definition tab does not execute its statement. CREATE SESSION allows a login. Table access and DBA navigator operations require their own privileges. An error such as ORA-01031 calls for checking the intended operation and delegated access, not for switching routine inspection to an unnecessarily powerful account. On disconnect, choose the intended commit or rollback if other work left pending changes. Removing a saved client connection does not drop the database user or table.
No comments:
Post a Comment