Oracle notes for beginners

In this tutorial, Oracle notes for beginners. How to query commands useful in Oracle database.

Oracle notes for beginners

Oracle Database commands

Changing password in Oracle

ALTER USER user_name IDENTIFIED BY new_password;

Create a table

CREATE TABLE my_table (
    what   VARCHAR2(10),
    who    VARCHAR2(10),
    mark   VARCHAR2(10)
);

Insert values as the same with 3 commands below

INSERT INTO my_table (
    what,
    who,
    mark
) VALUES (
    'Devops',
    'Roles',
    '.com'
);

INSERT INTO my_table VALUES (
    'huu',
    'phan',
    '.com'
);

INSERT INTO my_table ( what ) VALUES ( 'Yeah!' );

Get the list of all tables in Oracle

SELECT
    owner,
    table_name
FROM
    all_tables

Query your permission in Oracle

select * from USER_ROLE_PRIVS where USERNAME= USER;
select * from USER_TAB_PRIVS where Grantee = USER;
select * from USER_SYS_PRIVS where USERNAME = USER;

Oracle check version

SELECT
    *
FROM
    v$version

Find Users logged into Oracle / PLSQL

SELECT
    username,
    program,
    machine,
    status,
    TO_CHAR(
        logon_time,
        'HH:MM:SS'
    )
FROM
    v$session
WHERE
    username = 'huupv' -- Username

The query for active users SQL Executed

SELECT
    a.sid,
    a.serial#,
    b.sql_text
FROM
    v$session a,
    v$sqlarea b
WHERE
        a.sql_address = b.address
    AND
        a.username = 'huupv';

Kill session in Oracle

Step 1: Identify the Session to be killed

SELECT
    s.inst_id,
    s.sid,
    s.serial#,
       --s.sql_id,
    p.spid,
    s.username,
    s.program
FROM
    gv$session s
    JOIN gv$process p ON
        p.addr = s.paddr
    AND
        p.inst_id = s.inst_id
WHERE
    s.type != 'BACKGROUND' and s.username ='huupv';

Note: The SID and SERIAL# values the relevant session.

Step 2: Kill Session

SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' POST_TRANSACTION; -- The POST_TRANSACTION clause waits for ongoing transactions to complete before disconnecting the session
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE; -- ALTER SYSTEM DISCONNECT SESSION

I will be updated later! Have a nice day! Oracle notes for beginners. Thank you for reading DevOpsRoles.com page