Exploring The Database

Series of actions to determine database state
Show DB status:

SELECT instance_name,<br />
  instance_role,<br />
  version,<br />
  startup_time,<br />
  status<br />
FROM v$instance;<br />

<br />
INSTANCE_NAME INSTANCE_ROLE VERSION STARTUP_TIME STATUS<br />
---------------- ------------------ ----------------- ------------ ------------<br />
xe PRIMARY_INSTANCE 11.2.0.2.0 02-JUL-16 OPEN<br />

List Database version information

SELECT*<br />
FROM v$version;

<br />
BANNER<br />
--------------------------------------------------------------------------------<br />
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production<br />
PL/SQL Release 11.2.0.2.0 - Production<br />
CORE	11.2.0.2.0	Production<br />
TNS for 64-bit Windows: Version 11.2.0.2.0 - Production<br />
NLSRTL Version 11.2.0.2.0 - Production<br />

List installed Oracle products and version number.

select * from product_component_version

PRODUCT                                  VERSION                        STATUS<br />
---------------------------------------- ------------------------------ --------------------<br />
NLSRTL                                   11.2.0.2.0                     Production<br />
Oracle Database 11g Express Edition      11.2.0.2.0                     64bit Production<br />
PL/SQL                                   11.2.0.2.0                     Production<br />
TNS for 64-bit Windows:                  11.2.0.2.0                     Production<br />

List Oracle database general parameters

SELECT name, value, description<br />
FROM v$system_parameter;<br />

NAME                                     VALUE           DESCRIPTION<br />
---------------------------------------- --------------- ----------------------------------------<br />
lock_name_space                                          lock name space used for generating lock<br />
processes                                100             user processes<br />
sessions                                 172             user and system sessions<br />
timed_statistics                         TRUE            maintain internal timing statistics<br />
timed_os_statistics                      0               internal os statistic gathering interval<br />
resource_limit                           FALSE           master switch for resource limit<br />
license_max_sessions                     0               maximum number of non-system user sessio<br />
license_sessions_warning                 0               warning level for number of non-system u</p>
<p>(cut)<br />

//+ Explain system parameters

Database Character Set Informations

SELECT *<br />
FROM nls_database_parameters;

NLS_LANGUAGE                   AMERICAN<br />
NLS_TERRITORY                  AMERICA<br />
NLS_CURRENCY                   $<br />
NLS_ISO_CURRENCY               AMERICA<br />
NLS_NUMERIC_CHARACTERS         .,<br />
NLS_CHARACTERSET               AL32UTF8<br />
NLS_CALENDAR                   GREGORIAN<br />
NLS_DATE_FORMAT                DD-MON-RR<br />
NLS_DATE_LANGUAGE              AMERICAN<br />
NLS_SORT                       BINARY<br />
NLS_TIME_FORMAT                HH.MI.SSXFF AM<br />
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI<br />
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM<br />
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI<br />
NLS_DUAL_CURRENCY              $<br />
NLS_COMP                       BINARY<br />
NLS_LENGTH_SEMANTICS           BYTE<br />
NLS_NCHAR_CONV_EXCP            FALSE<br />
NLS_NCHAR_CHARACTERSET         AL16UTF16<br />
NLS_RDBMS_VERSION              11.2.0.2.0<br />

What is the current database state?

SELECT *<br />
FROM v$instance;

What tablespace are available?

SELECT *<br />
FROM v$tablespace;

TS# NAME                           INC BIG FLA ENC<br />
---------- ------------------------------ --- --- --- ---<br />
  0 SYSTEM                         YES NO  YES<br />
  2 UNDOTBS1                       YES NO  YES<br />
  1 SYSAUX                         YES NO  YES<br />
  4 USERS                          YES NO  YES<br />
  3 TEMP                           NO  NO  YES<br />
  5 APEX_1655289364460851          YES NO  YES<br />
  6 USER_DATA                      YES NO  YES<br />

What tables are avaiable to the current user?

SELECT *<br />
  FROM user_tables;<br />

How to find out all objects connected to current user?

<br />
SELECT *<br />
  FROM user_catalog;<br />

Does a table exist in current DB schema?

<br />
SELECT table_name<br />
  FROM user_tables<br />
  WHERE table_name = 'TABLE_NAME'<br />

Does a colum exist in a table?

<br />
SELECT column_name AS FOUND<br />
  FROM user_tab_cols<br />
  WHERE table_name = 'TABLE_NAME' AND column_name = 'COLUMN_NAME';<br />

How to find the schema name and the DB user name from an active session?

SELECT sys_context('USERENV', 'SESSION_USER') SESSION_USER, sys_context('USERENV', 'CURRENT_SCHEMA') CURRENT_SCHEMA
  FROM dual;

sys_context() function returns the value of parameter associated with the context namespace. USERENV is an Oracle provided namespace that describes the current session. Check the table Predefined Parameters of Namespace USERENV for the list of parameters and the expected return values.

Show tables with CLOB, BLOB, RAW, NCLOB columns

SELECT DISTINCT('SELECT DBMS_METADATA.GET_DDL(''TABLE'',''' ||table_name|| ''') from DUAL;') a
  FROM user_tab_columns
 WHERE data_type in ('CLOB','BLOB','RAW','NCLOB')
 ORDER BY a;

Pro Tip:

Use SQL Developer to export object DDLs with point n click.

Aditional reading:

Oracle documentation: DBMS_METADATA
Burleson on DBMS_METADATA

How to show all Oracle users and their files?

SELECT *
  FROM dba_users;

Oracle SQL query to know roles and roles privileges

select * from role_sys_privs

Oracle SQL query to know integrity rules

select constraint_name, column_name from sys.all_cons_columns

Resources

Ask Tom – StackOverflow Oracle –

Ask questions, get anwsers: