Oracle: Difference between revisions
Jump to navigation
Jump to search
Line 44: | Line 44: | ||
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 60: | Line 60: | ||
set pages 22 lines 80 | set pages 22 lines 80 | ||
ttitle off | ttitle off | ||
=who am i= | =who am i= | ||
show user; | show user; |
Revision as of 08:49, 23 September 2020
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;
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;