Oracle: Difference between revisions
Jump to navigation
Jump to search
(→UPDATE) |
|||
Line 126: | Line 126: | ||
SELECT table_name FROM user_tables ORDER BY table_name; | SELECT table_name FROM user_tables ORDER BY table_name; | ||
=UPDATE= | =UPDATE= | ||
ALTER USER ishupgrade quota unlimited on | ALTER USER ishupgrade quota unlimited on USERS; |
Revision as of 11:25, 15 December 2021
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
connect to oracle database
ORACLE_HOME=/oracle/product/12.1.0/dbhome /bin/sqlplus SYSTEM/password@\"1.1.1.1:1521/SID\"
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
SELECT table_name FROM user_tables ORDER BY table_name;
UPDATE
ALTER USER ishupgrade quota unlimited on USERS;