Oracle notes for beginners

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

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 command 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 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

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 updating later! Have a nice day!

, ,

About HuuPV

My name is Huu. I love technology and especially Devops Skill such as Docker, vagrant, git so forth. I likes open-sources. so I created DevopsRoles.com site to share the knowledge that I have learned. My Job: IT system administrator. Hobbies: summoners war game, gossip.
View all posts by HuuPV →

Leave a Reply

Your email address will not be published. Required fields are marked *

Comment moderation is enabled. Your comment may take some time to appear.

This site uses Akismet to reduce spam. Learn how your comment data is processed.