Ekaanbj: /* get sane output */

2022-02-10T08:54:52Z

get sane output

New page

=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;

Oracle - Revision history

Ekaanbj: /* get sane output */