Oracle Common Commands - CodeSwordsman

Frequent sqlplus connections, user and tablespace management, expdp/impdp and RMAN backups, plus session, lock and slow SQL troubleshooting.

Oracle 11g / 19cUpdated 2026-10-3026 commands

Connections & Instance

sqlplus app/<PASSWORD>@10.0.0.12:1521/ORCLPDB1

sqlplus / as sysdba

Check listener status and registered services, the first stop when connections fail

lsnrctl status

Show the instance name and its state: OPEN, MOUNTED or STARTED

select instance_name, status from v$instance;

Show the database name and open mode

select name, open_mode from v$database;

Show the service names registered by the instance

show parameter service_names

Create a user with a default tablespace and unlimited quota

create user app identified by "<PASSWORD>" default tablespace users quota unlimited on users;

Grant basic connect and resource privileges; grant finer privileges in production

grant connect, resource to app;

Grant cross-schema read access to a single table

grant select on app.orders to report;

List users with their status and default tablespace

select username, account_status, default_tablespace from dba_users order by username;

Unlock an account, common after repeated failed logins

alter user app account unlock;

Show roles granted to a user

select * from dba_role_privs where grantee = 'APP';

List tablespaces and their status

select tablespace_name, status from dba_tablespaces;

Show data file paths, sizes and autoextend flags

select file_name, bytes / 1024 / 1024 as mb, autoextensible from dba_data_files;

Extend a tablespace by adding a data file with an autoextend limit

alter tablespace users add datafile '/u01/app/oracle/oradata/ORCL/users02.dbf' size 1g autoextend on next 256m maxsize 8g;

Without a maxsize limit a file can fill the disk; set it based on available capacity

Sum space used by segments per tablespace

select tablespace_name, round(sum(bytes) / 1024 / 1024) as used_mb from dba_segments group by tablespace_name;

Export a schema logically; a DIRECTORY object must exist first

expdp app/<PASSWORD>@ORCL schemas=app directory=dpdump dumpfile=app_20261030.dmp logfile=exp_app.log

Import into a schema, optionally renaming it with remap_schema

impdp app/<PASSWORD>@ORCL schemas=app directory=dpdump dumpfile=app_20261030.dmp remap_schema=app:app_new

Start RMAN connected locally as SYSDBA

rman target /

RMAN full backup including archived logs; the database must run in ARCHIVELOG mode

backup database plus archivelog;

Check whether ARCHIVELOG is enabled; point-in-time recovery requires it

select log_mode from v$database;

Show active sessions and the SQL they run

select sid, serial#, status, sql_id from v$session where status = 'ACTIVE';

Show blocked sessions and who blocks them

select sid, event, blocking_session, seconds_in_wait from v$session where blocking_session is not null;

Risky

Kill a session by SID and serial number; rollback can take time

alter system kill session '145,28931';

Find the ten statements with the highest cumulative elapsed time

select sql_id, elapsed_time / 1000000 as seconds, sql_text from v$sql order by elapsed_time desc fetch first 10 rows only;

FETCH FIRST requires 12c+; on 11g wrap the query with rownum <= 10

Create an AWR snapshot manually to compare performance windows

exec dbms_workload_repository.create_snapshot();