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
Ask Tom – StackOverflow Oracle –
Ask questions, get anwsers: