Oracle notes for beginners: Your Essential Guide to Getting Started

Introduction

In this tutorial, Oracle notes for beginners. How to query commands useful in Oracle database. Diving into the world of Oracle databases can be both exciting and overwhelming for beginners. With its robust features and capabilities, Oracle is a powerful tool for managing data effectively.

Oracle notes for beginners

Oracle Database commands

Changing passwords 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

Conclusion

Embarking on your journey with Oracle databases doesn’t have to be daunting. By understanding the basics and following the tips provided in this guide, you will gain the confidence and knowledge needed to effectively manage and manipulate data using Oracle.

Remember, practice and continuous learning are key to becoming proficient in any technology. Keep exploring, experimenting, and expanding your skills to unlock the full potential of Oracle in your projects. I will be updated later! Have a nice day! Oracle notes for beginners. Thank you for reading DevOpsRoles.com page