Oracle: Difference between revisions

From Halfface
Jump to navigation Jump to search
 
(23 intermediate revisions by the same user not shown)
Line 1: Line 1:
=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=
=sqlplus64=
Command to connect to database.
=Connect to oracle database.=
=Connect to oracle database.=
  sqlplus64 user/password@hostname
  sqlplus64 user/password@hostname
Line 8: Line 19:
  sqlplus64 -L user/password@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=10.111.222.1)(PORT=1523))(CONNECT_DATA=(SID=SIDDATABASE)))" AS SYSDBA
  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
  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(page)=
SET PAGESIZE 10000;
set linesize 32767;
set wrap off;
set long 2000000;
Oneliner.
SET PAGESIZE 10000; set linesize 32767; set wrap off; set long 2000000;
=View databases.=
=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.
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 DISTINCT OWNER FROM ALL_OBJECTS;
=select view(select database)=
=select view(select database)=
  ALTER SESSION SET current_schema = other_user;
  ALTER SESSION SET current_schema = other_user;
Line 29: Line 52:
  CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE -
  CREATE TRIGGER, CREATE TYPE, CREATE VIEW, UNLIMITED TABLESPACE -
  to root;
  to root;
==show running queries==
=show running queries=
  select
  select
       substr(a.spid,1,9) pid,
       substr(a.spid,1,9) pid,
Line 44: Line 67:
  and type='USER'
  and type='USER'
  order by spid;
  order by spid;
==which query takes load==
 
=which query takes load=
  select * from
  select * from
  (select sql_text,
  (select sql_text,
Line 60: Line 84:
  set pages 22 lines 80
  set pages 22 lines 80
  ttitle off
  ttitle off
=who am i=
=who am i=
show user;
show user;
Line 67: Line 92:
     (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW"
     (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW"
     FROM DUAL;
     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;

Latest revision as of 08:54, 10 February 2022

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(page)

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

Oneliner.

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;