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)
- Windows: registry key
HKLM\Software\Oracle(valueINST_LOC), defaultC:\Program Files\Oracle\Inventory. - Linux / AIX: directory given in the
oraInst.locfile (under/etc). - Solaris:
oraInst.locfile under/var/opt/oracle. - Inventory file:
ContentsXML/inventory.xml; usual location$ORACLE_BASE/oraInventory(Linux/Unix).
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
- Disk space and tablespaces
- System logs
- Audit records
- Sessions
- Backups
- Batch jobs
- Enterprise Manager