Oracle

From Halfface
Revision as of 11:37, 30 April 2020 by Ekaanbj (talk | contribs) (Created page with "=sqlplus64= Connect to oracle database. sqlplus64 user/password@hostname sqlplus / as sysdba Connect to oracle database non standard port. sqlplus64 -L user/password@"(DESC...")
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to navigation Jump to search

sqlplus64

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

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;

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;