Oracle: Difference between revisions
Jump to navigation
Jump to search
(27 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= | ||
Connect to oracle database. | Command to connect to database. | ||
=Connect to oracle database.= | |||
sqlplus64 user/password@hostname | sqlplus64 user/password@hostname | ||
sqlplus / as sysdba | sqlplus / as sysdba | ||
Connect to oracle database non standard port. | =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)))" | 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. | =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 | 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 | ||
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. | =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 DISTINCT OWNER FROM ALL_OBJECTS; | ||
Name of database. | |||
=select view(select database)= | |||
ALTER SESSION SET current_schema = other_user; | |||
=Name of database.= | |||
SELECT NAME FROM v$database; | SELECT NAME FROM v$database; | ||
Database version | =Database version= | ||
SELECT * FROM v$version; | SELECT * FROM v$version; | ||
Size of database | =Size of database= | ||
SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files; | SELECT SUM (bytes) / 1024 / 1024 / 1024 AS GB FROM dba_data_files; | ||
Generate random number | =Generate random number= | ||
SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL; | SELECT ROUND (DBMS_RANDOM.VALUE () * 100) + 1 AS random_num FROM DUAL; | ||
Create user | =Create user= | ||
create user root identified by "S3cretPasSw0rd"; | create user root identified by "S3cretPasSw0rd"; | ||
Grant access. | =Grant access.= | ||
grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, - | grant CREATE SESSION, ALTER SESSION, CREATE DATABASE LINK, - | ||
CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, - | CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, - | ||
Line 26: | 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= | |||
select | select | ||
substr(a.spid,1,9) pid, | substr(a.spid,1,9) pid, | ||
Line 41: | Line 67: | ||
and type='USER' | and type='USER' | ||
order by spid; | order by spid; | ||
=which query takes load= | |||
select * from | select * from | ||
(select sql_text, | (select sql_text, | ||
Line 57: | Line 84: | ||
set pages 22 lines 80 | set pages 22 lines 80 | ||
ttitle off | 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; |
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;