This article describes method to aggregate and concatenate strings.

CREATE TABLE test_tbl
(
 grp_id   NUMBER,
 elm_id   NUMBER,
 elm_name VARCHAR2(25)
);

INSERT INTO test_tbl
SELECT 1, LEVEL, 'my name 1_'||LEVEL 
FROM dual 
CONNECT BY level < 5;

INSERT INTO test_tbl
SELECT 2, LEVEL, 'my name 2_'||LEVEL
FROM dual
CONNECT BY LEVEL < 5;

INSERT INTO test_tbl
SELECT 3, LEVEL, 'my name 3_'||LEVEL
FROM dual
CONNECT BY LEVEL < 1001;

COMMIT;

We have following data

SELECT * FROM test_tbl
ORDER BY 1, 2;

    GRP_ID     ELM_ID ELM_NAME                
---------- ---------- -------------------------
         1          1 my name 1_1              
         1          2 my name 1_2              
         1          3 my name 1_3              
         1          4 my name 1_4              
         2          1 my name 2_1              
         2          2 my name 2_2              
         2          3 my name 2_3              
         2          4 my name 2_4
         3          1 my name 3_1              
...
         3       1000 my name 3_1000

and purpose is to covert it to following layout

TEST_ID  CONVERTED
-------- ------------------------------------------------
      1  my name 1_1,my name 1_2,my name 1_3,my name 1_4
      2  my name 2_1,my name 2_2,my name 2_3,my name 2_4
      3  my name 3_1,...                    ,my name 3_1000

Continue reading →

This article shows how works table pseudocolumn ORA_ROWSCN. The column provides SCN (System Change Number) when last DML was executed on block or row level for a table . SCN system change number can be easily translated as timestamp using function SCN_TO_TIMESTAMP. The SCN as timestamp is approximate to 3 seconds.

Continue reading →

This article presents constraints types that can be created on view level:

• check option
• read only
• primary key
• unique key
• foreign key

Continue reading →

If you want to filter and analyze your audit data from AUD$ table you need to scan data using ACTION# column. It keeps information about audit actions but is NUMERIC columns. To find out meaning of the column check AUDIT_ACTIONS table

So using simple join between AUD# and AUDIT_ACTIONS tables filter data as you want

SELECT *
  FROM sys.aud$
 WHERE ntimestamp# > SYSDATE - 7
  AND action# IN (
        SELECT action 
          FROM audit_actions
        WHERE name IN 
          ('ALTER USER',
           'CREATE USER',
           'DROP USER'));

List of all audit actions

SELECT action, name 
  FROM audit_actions
ORDER BY 1;

Continue reading →

This article in short explain patch terminology used by Oracle.

Have a fun 🙂
Tomasz

Finding source code written by a developer is typical task executed by a DBA or a developer.

Generally there are two basic methods to get source code:

• select code from dictionary views – quick, simple, can return part of code, complicated to generate properly DDL
• generate code using DBMS_METADATA package – very powerful, returns full code, easy to generate DDL code

Continue reading →

In Oracle Database NOLOGGING option used together with DIRECT mode speeds up transactions executed in a database because it limits information that Oracle writes about such transactions into redo logs and finally impacts size of generated archivelogs.

That’s because Oracle just writes basic information about executed transaction without modified data to redo logs. Such transactions are called UNRECOVERABLE because in case of database recovery archivelogs don’t have real data only informations about executed transactions.

It can impact later restores and this article shows it.

Continue reading →

This article presents proxy connections that enables to login to other schema using own user authentication.

CONNECT user[schema to connect]/user password

Before 11G it was quite complicated yet possible using ALTER USER .. IDENTIFIED BY VALUES . Read following article: Oracle password reset

Continue reading →

This article describes in short how to kill a session in Oracle Database

Generally there are two methods to kill a session in Oracle Database:

• do it directly in the database
• do it on OS level – kill dedicated server process for the database

Continue reading →