LLI Wiki — My Digital Knowledge Base

Oracle administration queries cheat sheet

Common Oracle administration queries and commands: instance, users, control files, orapwd and sys_context.

Cheat sheet of common Oracle administration queries and commands: session, instance, users, control files, password file and sys_context.

Language (NLS_LANG)

select * from v$diag_info;
ALTER SESSION SET NLS_LANGUAGE=ENGLISH;

Linux / Unix:

echo $NLS_LANG
export NLS_LANG=AMERICAN_AMERICA.WE8MSWIN1252

On Windows, the NLS_LANG value is stored in the registry (regedit, key HKLM\Software\Oracle, under the relevant ORACLE_HOME key). It can be changed, for example, to AMERICAN_AMERICA.WE8ISO8859P1.

Compilation errors

show errors;
select * from all_errors;

Users and schemas

select user from dual;
select username from sys.all_users;
select username from dba_users order by 1;

Switch the current schema and check it:

ALTER SESSION SET CURRENT_SCHEMA = <schema_name>;
select sys_context('userenv', 'current_schema') from dual;

Identify the database, service and instance

Information Query Where to find it
Service name select sys_context('userenv','service_name') from dual; tnsnames.ora
Instance name select instance_name from v$instance; init.ora
Database name select ora_database_name from dual;
Session SID select sys_context('USERENV','SID') from dual; listener.ora
Global name select * from global_name;
Service (parameter) select value from v$parameter where name like '%service_name%';

Other useful commands:

ALTER SYSTEM SWITCH LOGFILE;
select log_mode from v$database;
select owner, count(*) from dba_tables group by owner;
lsnrctl status
tnsping servername

tnsnames.ora and listener.ora live in $ORACLE_HOME/network/admin.

Database characteristics

SELECT NAME, DBID, LOG_MODE, FLASHBACK_ON, OPEN_MODE, CREATED
  FROM v$database;

SELECT PROPERTY_NAME, PROPERTY_VALUE, DESCRIPTION
  FROM DATABASE_PROPERTIES
 ORDER BY PROPERTY_NAME;

SELECT INSTANCE_NAME, VERSION FROM V$INSTANCE;

Detected feature usage (for example Partitioning):

SELECT NAME, DETECTED_USAGES
  FROM DBA_FEATURE_USAGE_STATISTICS
 ORDER BY NAME;

Log and trace files

select value from v$diag_info;
show parameter background
show parameter background_dump_dest

select name from v$parameter where name = 'background_dump_destination';
find / -name 'al*.log' 2>/dev/null
ls -ltr /oracle/app/oracle/diag/rdbms/<DB>/<DB>/trace/al*

List tables and columns

SELECT table_name FROM user_tables;
SELECT table_name FROM all_tables;      -- tables accessible to the user
SELECT table_name FROM dba_tables;      -- all tables (DBA privileges)

SELECT DISTINCT OWNER, OBJECT_NAME FROM DBA_OBJECTS
 WHERE OBJECT_TYPE = 'TABLE' AND OWNER = 'APP_SCHEMA';

select owner, table_name, column_name from ALL_TAB_COLUMNS
 where owner = 'APP_SCHEMA';

Instances installed on a Linux server

cat /etc/oratab | grep -v '^#\|^\s*$' | cut -d: -f 1

List the init*.ora and spfile*.ora files of every ORACLE_HOME:

for ORACLEHOME in `cat /etc/oratab | grep -v '^#\|^\s*$' | cut -d: -f 2 | uniq`
do
    ls -1 $ORACLEHOME/dbs/init*.ora   | sed -n 's/init\(.*\).ora/\1/p'
    ls -1 $ORACLEHOME/dbs/spfile*.ora | sed -n 's/spfile\(.*\).ora/\1/p'
done

Oracle inventory (oraInventory)

Create a user

create user app_user identified by "<PASSWORD>";
grant connect to app_user;
grant resource to app_user;
grant unlimited tablespace to app_user;

Create a schema with its tablespace:

select * from dba_temp_files;
select name from v$tablespace;
select * from dba_data_files;

create tablespace app_ts datafile '<path_to_datafile>' size 100M
  autoextend on next 100M maxsize 2000M
  extent management local segment space management auto;

create user app_user identified by "<PASSWORD>"
  default tablespace app_ts temporary tablespace temp;

Lock, unlock, expire:

ALTER USER app_user ACCOUNT LOCK;
ALTER USER app_user ACCOUNT UNLOCK;
ALTER USER app_user PASSWORD EXPIRE;

Password file (remote connections)

show parameter remote_login_passwordfile
alter system set remote_login_passwordfile=exclusive scope=spfile;
orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=<PASSWORD> entries=10

On Windows:

orapwd file=%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora password=<PASSWORD>

Step-by-step startup

startup nomount;
alter database mount;
alter database open;

startup mount;
alter database open;

startup restrict;
alter system disable restricted session;

Control files

select value from v$parameter where name like '%control_file%';

-- binary backup
alter database backup controlfile to '<path>/control.ctl';
-- text (script) backup
alter database backup controlfile to trace as '<path>/control_trace.sql';

Session context with sys_context

select dbid from v$database;
select sys_context('userenv','sid') from dual;
select sys_context('userenv','os_user') from dual;       -- client OS user
select sys_context('userenv','host') from dual;          -- client host
select sys_context('userenv','db_domain') from dual;
select sys_context('userenv','ip_address') from dual;    -- client IP
select sys_context('userenv','instance_name') from dual;
select sys_context('userenv','db_name') from dual;
select sys_context('userenv','session_user') from dual;
select sid, serial# from v$session
 where audsid = sys_context('userenv','sessionid');

Example of a logon trigger that blocks direct logins for an account and requires a given database link through DBLINK_INFO:

CREATE OR REPLACE TRIGGER dbl_logon_trg
AFTER LOGON ON DATABASE
DECLARE
  v_username VARCHAR2(30)  := sys_context('USERENV','SESSION_USER');
  v_dbl_info VARCHAR2(200) := sys_context('USERENV','DBLINK_INFO');
BEGIN
  IF v_username = 'ETL_USER' THEN
    IF v_dbl_info IS NULL THEN
      write_log('failed: direct login', v_username, v_dbl_info);
      raise_application_error(-20101, 'Direct login not allowed.');
    ELSIF v_dbl_info NOT LIKE 'SOURCE_GLOBAL_NAME=SRC_DB, DBLINK_NAME=ETL_DBL%' THEN
      write_log('failed: wrong dblink', v_username, v_dbl_info);
      raise_application_error(-20102, 'Login from wrong link not allowed.');
    ELSE
      write_log('successful login', v_username, v_dbl_info);
    END IF;
  END IF;
END dbl_logon_trg;

Useful grants:

grant create view to system;
grant create materialized view to system;
select granted_role from dba_role_privs where grantee in ('SYSTEM');

Daily tasks

Mastodon LLIMastodon SocialFosstodon