Useful OracleDB Command

gistfile1.txt

/* Sqlplus */
`ORACLE_SID=<SID> ORACLE_HOME=/oracle/CWDB01/product/12.1.0 /oracle/CWDB01/product/12.1.0/bin/sqlplus "sys as SYSDBA"`

/* Setting up directory for using import/export datapump */
CREATE DIRECTORY db_restore AS '<MOUNT FOLDER IN CONTAINER>';
GRANT read, write ON DIRECTORY db_restore TO sys;
GRANT DATAPUMP_EXP_FULL_DATABASE,DATAPUMP_IMP_FULL_DATABASE TO sys;

/* Import using data pump */
expdp \"sys/passwd@localhost/ORCLCDB as sysdba\" full=Y DIRECTORY=db_restore dumpfile=export-db.dmp
expdp \"sys/passwd@localhost/ORCLCDB as sysdba\" SCHEMAS=<SCHEMA_NAME> VERSION=11.2 DIRECTORY=db_restore DUMPFILE=export-db.dmp

/* Export using data pump */
impdp \"sys/passwd@localhost/ORCLCDB as sysdba\" full=Y DIRECTORY=db_restore dumpfile=db.dmp
expdp \"sys/passwd@localhost/ORCLCDB as sysdba\" SCHEMAS=<SCHEMA_NAME> VERSION=11.2 DIRECTORY=db_restore DUMPFILE=db.dmp





/* Allow running of scripts */
ALTER SESSION SET "_ORACLE_SCRIPT"=true;

/* All userspaces */
SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM USER_TABLESPACES;

/* All users */
SELECT * FROM USER_USERS;

/* Current version */
SELECT * FROM V$VERSION;

/* Privileges for users */
select * from dba_sys_privs;

/* To list down the procedures. */
SELECT * FROM ALL_OBJECTS WHERE OBJECT_TYPE IN ('FUNCTION','PROCEDURE','PACKAGE') and OWNER = '<USER>';

/* Show all dba context */
SELECT namespace, schema, package, type  FROM dba_context;

/* Show active session for username */
SELECT * FROM V$SESSION WHERE STATUS = 'ACTIVE' and username='QAUSER';

/* Kill session by */
ALTER SYSTEM KILL SESSION '<sid, serial#>';

/* Create context */
CREATE OR REPLACE CONTEXT <Context Name> USING <Schema>.<Package> ACCESSED GLOBALLY;


/* Error */
/* ORA-12514: TNS:listener does not currently know of service requested in connect descriptor */
/* Use the following to get the correct service name */
select value from v$parameter where name='service_names';

/* error while loading shared libraries: libsqlplus.so: cannot open shared object file: No such file or directory */
ORACLE_SID=<service_name> LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib ORACLE_HOME=/usr/lib/oracle/12.1/client64 /usr/lib/oracle/12.1/client64/bin/sqlplus <host>/<password>@<host>:<port>/<service_name>