Database Commands

Database Useful Commands
---------------------------
Listner Commands
-------------------
lsnrctl start
lsnrctl stop
lsnrctl status
lsnrctl service
Tnsping
-------------------
ping localhost
tnsping localhost
tnsping orcl
Tablespaces
---------------
select * from USER_TABLESPACES;
select * from V$TEMPFILE;
SELECT * FROM DBA_DATA_FILES;
SELECT * FROM v$datafile;
SELECT * FROM v$log;--REDO LOGS
SELECT * FROM v$archived_log;--ARCHIVE LOGS

 How to determine the database character set in oracle?
----------------------------------------------------------
select * from NLS_DATABASE_PARAMETERS
    where parameter='NLS_CHARACTERSET'; --AL32UTF8
   
SELECT value$ FROM sys.props$ WHERE name='NLS_CHARACTERSET';  --AL32UTF8 

select distinct(nls_charset_name(charsetid)) CHARACTERSET,
decode(type#, 1, decode(charsetform, 1, 'VARCHAR2', 2, 'NVARCHAR2','UNKNOWN'),
9, decode(charsetform, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKNOWN'),
96, decode(charsetform, 1, 'CHAR', 2, 'NCHAR', 'UNKNOWN'),
8, decode(charsetform, 1, 'LONG', 'UNKNOWN'),
112, decode(charsetform, 1, 'CLOB', 2, 'NCLOB', 'UNKNOWN')) TYPES_USED_IN
from sys.col$ where charsetform in (1,2) and type# in (1, 8, 9, 96, 112)
order by CHARACTERSET, TYPES_USED_IN;
Explian Plan
--------------
explain plan for select Statements;
select PLAN_TABLE_OUTPUT
    from table(dbms_xplan.display('plan_table',null,'basic'));

select * from V$SQL;
select * from V$SQL_PLAN;
select * from V$SQL_PLAN_STATISTICS_ALL;