Oracle

Connect to oracle database.

sqlplus64 user/password@hostname
sqlplus / as sysdba

Connect to oracle database non standard port.

sqlplus64 -L user/password@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.111.222.1)(PORT=1523))(CONNECT_DATA=(SID=SIDDATABASE)))"

Connect to oracle database non standard port as sysadm.

sqlplus64 -L user/password@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.111.222.1)(PORT=1523))(CONNECT_DATA=(SID=SIDDATABASE)))" AS SYSDBA
LD_LIBRARY_PATH=/usr/lib/oracle/12.1/client64/lib/ sqlplus64 -L SYS/password@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=cis.inter.net)(PORT=1521))(CONNECT_DATA=(SID=XE)))" AS SYSDBA

connect to oracle database

ORACLE_HOME=/oracle/product/12.1.0/dbhome /bin/sqlplus SYSTEM/password@\"1.1.1.1:1521/SID\"

View databases.

You can think of a mysql "database" as a schema/user in Oracle. If you have the privileges, you can query the DBA_USERS view to see the list of schemas.

SELECT DISTINCT OWNER FROM ALL_OBJECTS;

select view(select database)

ALTER SESSION SET current_schema = other_user;

Name of database.

SELECT NAME FROM v$database;

Database version

SELECT * FROM v$version;

Size of database

SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files;

Generate random number

SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL;

Create user

create user root identified by "S3cretPasSw0rd";

Grant access.

grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, -
CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, -
CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, - 
CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE -
to root;

show running queries

select
      substr(a.spid,1,9) pid,
      substr(b.sid,1,5) sid,
      substr(b.serial#,1,5) ser#,
      substr(b.machine,1,6) box,
      substr(b.username,1,10) username,
--       b.server,
      substr(b.osuser,1,8) os_user,
      substr(b.program,1,30) program
from v$session b, v$process a
where
b.paddr = a.addr
and type='USER'
order by spid;

which query takes load

select * from
(select sql_text,
cpu_time/1000000 cpu_time,
elapsed_time/1000000 elapsed_time,
disk_reads,
buffer_gets,
rows_processed
from v$sqlarea
order by cpu_time desc, disk_reads desc
)
where rownum < 21
/
spool off
set pages 22 lines 80
ttitle off

who am i

show user;

what is the time

SELECT TO_CHAR
   (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW"
    FROM DUAL;

what is the time

Using bash where document

cat << EOF | sqlplus -s sys/P@ssW0rd@sid as sysdba
SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL;
EOF

master or slave

select database_role from v$database;

get sid

select sys_context('userenv','instance_name') from dual;

install sqlplus

http://www.oracle.com/technetwork/topics/linuxx86-64soft-092277.html
rpm -ivh oracle-instantclient11.2-basic-11.2.0.2.0.x86_64.rpm
rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.2.0.x86_64.rpm

open cursors

set heading off;
select max(a.value) as highest_open_cur, p.value as max_open_cur from v$sesstat a, v$statname b, v$parameter p where a.statistic# = b.statistic#  and b.name = 'opened cursors current' and p.name= 'open_cursors' group by p.value;
exit

processes and sessions

set linesize 200;
set wrap off;
set long 200;
SELECT RESOURCE_NAME, CURRENT_UTILIZATION, MAX_UTILIZATION, LIMIT_VALUE
FROM V$RESOURCE_LIMIT
WHERE RESOURCE_NAME IN ( 'sessions', 'processes');