Oracle Startup / Shutdown / Status Check

Open SQL Shell

Login as oracle user
Open sqlplus shell
# sqlplus / as sysdba SQL*Plus: Release 12.2.0.1.0 Production on Thu Dec 24 23:26:47 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production SQL>

Database Startup

Start the Database
SQL> startup; ORACLE instance started. Total System Global Area 771751936 bytes Fixed Size 8797536 bytes Variable Size 566231712 bytes Database Buffers 192937984 bytes Redo Buffers 3784704 bytes Database mounted. Database opened.

Database Shutdown

Stop the Database
SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down.

Database Status Check

Check Database uptime
SQL> SELECT to_char(startup_time,'DD-MON-YYYY HH24:MI:SS') "DB Startup Time" FROM sys.v_$instance; DB Startup Time ----------------------------- 24-DEC-2020 22:26:33
Show Service Name
SQL> show parameter service_name; NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ service_names string CDB1234
Check Current DB Version
SQL> SELECT banner FROM v$version WHERE ROWNUM = 1; BANNER -------------------------------------------------------------------------------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Check Installed DB Version
SQL> SELECT * FROM V$VERSION; BANNER -------------------------------------------------------------------------------- CON_ID ---------- Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0 PL/SQL Release 12.2.0.1.0 - Production 0 CORE 12.2.0.1.0 Production 0 BANNER -------------------------------------------------------------------------------- CON_ID ---------- TNS for Linux: Version 12.2.0.1.0 - Production 0 NLSRTL Version 12.2.0.1.0 - Production 0
Connect to a Specific Database
sqlplus 'system/oracle@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=192.168.50.17)(Port=1521))(CONNECT_DATA=(SID=THE_SID)))'
Show All Database Names
SQL> SELECT TABLESPACE_NAME FROM USER_TABLESPACES; TABLESPACE_NAME ------------------------------ SYSTEM SYSAUX UNDOTBS1 TEMP USERS
Show All Tablespaces
SQL> SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB FROM dba_data_files ORDER BY 1; FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME MB ------------------------------ ---------- /u01/app/oracle/oradata/CDB1234/sysaux01.dbf SYSAUX 481 /u01/app/oracle/oradata/CDB1234/system01.dbf SYSTEM 829 /u01/app/oracle/oradata/CDB1234/undotbs01.dbf UNDOTBS1 72 FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME MB ------------------------------ ---------- /u01/app/oracle/oradata/CDB1234/users01.dbf USERS 5
Show Views in the Database
SQL> SELECT view_name, owner FROM sys.all_views where VIEW_NAME LIKE 'SCHEMA_%' ORDER BY owner, view_name; VIEW_NAME -------------------------------------------------------------------------------- OWNER -------------------------------------------------------------------------------- SCHEMA_EXPORT_OBJECTS SYS SCHEMA_EXPORT_PATHS SYS
Find all active locks on tables within the session
SQL> select c.owner, c.object_name, c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine from v$locked_object a , v$session b, dba_objects c where b.sid = a.session_id and a.object_id = c.object_id;