Oracle

info

In oracle, generally speaking, there are number of facts that I will mention in following section:

• Each database can have many Schema/User (Logical division).
• Each database can have many tablespaces (Logical division).
• A schema is the set of objects (tables, indexes, views, etc) that belong to a user.
• In Oracle, a user can be considered the same as a schema.
• A database is divided into logical storage units called tablespaces, which group related logical structures together. For example, tablespaces commonly group all of an application’s objects to simplify some administrative operations.
• You may have a tablespace for application data and an additional one for application indexes.

sqlplus64

Command to connect to database.

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\"

get sane output

SET PAGESIZE 10000;
set linesize 32767;
set wrap off;
set long 2000000;

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

On oracle server

ps -ef | grep [p]mon

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');

look at sessions over 60 min

set linesize 32767;
set wrap off;
set long 2000000;
select s.username,s.sid,s.serial#,s.last_call_et/60 mins_running,q.sql_text from v$session s 
join v$sqltext_with_newlines q
on s.sql_address = q.address
 where status='ACTIVE'
and type <>'BACKGROUND'
and last_call_et> 60
order by sid,serial#,q.piece;

look at queries over 60 seconds.

set linesize 32767;
set wrap off;
set long 2000000;
select s.sid, s.serial#, p.spid, s.username, s.schemaname, s.program, s.terminal, s.osuser
 from v$session s
 join v$process p
  on s.paddr = p.addr
 where s.type != 'BACKGROUND';

listener logfile

listener/trace/listener.log

show tables

Show tables that are accessible by the current user

SELECT table_name FROM user_tables ORDER BY table_name;

Show tables for specific user.

SELECT * FROM all_tables WHERE OWNER = '${USER}' ORDER BY table_name;

Show all tables.

SELECT table_name FROM dba_tables;

Grant permissions to table space

ALTER USER ishupgrade quota unlimited on USERS;

select

select * from USERS where rownum <= 10 order by name

list all tablespaces

SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM USER_TABLESPACES;