Development

Business Logic in Oracle Application Builder Cloud Service

Shay Shmeltzer - Tue, 2017-03-07 16:35

As you start building more complex applications in Oracle Application Builder Cloud Service, you'll might need to define more complex interactions between objects or validations on rows of data or specific fields.

In the new version of ABCS that we rolled out in February we added these type of capabilities.

There are several things you could do with the new Business Rules section of ABCS

  • Triggers - allow you create logic that will be executed in specific CRUD events such as when you insert, remove or update a record.
  • Object Validators - allowing you to define checks across multiple fields in your object
  • Field Validators - allowing you to define a check on specific field values.
  • Object Functions - reusable functions that can be called from the other points

Note that these logic points will be running on the server side of ABCS. 

In the video below I demonstrate a couple of these capabilities. You'll learn how to update one object when a specific value is set in another object's field. You'll also see how to check whether a combination of values in fields in a record is valid.

Check it out:

<p> </p>

Here is a screenshot of the nice logic flow editor:

Categories: Development

60 SQL Interview Questions and Answers

Complete IT Professional - Mon, 2017-03-06 05:00
Are you going for a job where you need to know SQL, such as a Database Developer or Database Administrator? Brush up on your interview questions with this extensive list of SQL interview questions. This collection of interview questions on SQL has been collated from my experience with SQL and from various websites. It contains […]
Categories: Development

ORA-00907: missing right parenthesis Solution

Complete IT Professional - Fri, 2017-03-03 05:00
Did you get an ORA-00907: missing right parenthesis error? Learn what caused it and how to resolve it in this article. ORA-00907 Cause When working with Oracle SQL, all left parenthesis (the “(” character) must be paired with a right parenthesis character (the “)” character). If there are more left parentheses than right parentheses, then […]
Categories: Development

ORA-06502 PLSQL numeric or value error Solution

Complete IT Professional - Wed, 2017-03-01 05:00
Are you getting an ORA-06502 error message when working with Oracle SQL? Learn how to resolve it and what causes it in this article. ORA-06502 Cause The cause of the “ORA-06502 PL/SQL numeric or value error” can be one of many things: A value is being assigned to a numeric variable, but the value is […]
Categories: Development

Updating the Oracle EM12c Agent Configuration Properties in Bulk

Arun Bavera - Tue, 2017-02-28 14:41
 
1. Goto Enterprise->Job ->Activity->Choose from ‘create Job’ list -> Agent Configuration Operation ->Click Go
image
Give job name and Add all the Agent Targets you want to modify
clip_image004
Goto Parameter tab and Add Custom properties or Change the existing listed properties values.
For example:
Name:     Log.log.level
Value:     ERROR
imageThis job does not require any credentials.
Click Submit:









Categories: Development

Oracle GROUP BY – The Complete Guide

Complete IT Professional - Mon, 2017-02-27 05:00
The Oracle GROUP BY clause is a powerful clause, especially when analysing large amounts of data. Learn what the GROUP BY clause is and everything you can do with it in this article. In this article, we will cover: What Is The GROUP BY Clause? Sample Data Examples of Aggregate Functions with GROUP BY Filtering […]
Categories: Development

ORA-06550 line N column N Solution

Complete IT Professional - Fri, 2017-02-24 05:00
Are you getting the ORA-06550 error when running an SQL statement? Learn the cause of this error and the solution in this article. Demonstration of the Error To demonstrate this error, I can run this code: CREATE OR REPLACE PROCEDURE testProcedure AS   textValue VARCHAR2(3); BEGIN   textValue := someOtherValue; END; If I compile this […]
Categories: Development

ORA-00933 SQL command not properly ended Solution

Complete IT Professional - Wed, 2017-02-22 05:00
Are you getting the ORA-00933: SQL command not properly ended error? Learn what causes it and how to resolve it in this article. ORA-00933 Cause You have run an SQL statement and have gotten this error: ORA-00933: SQL command not properly ended What causes this error? Most likely, the SQL statement you’re running has a […]
Categories: Development

APEX 5.1 New Features - neuer Termin

Denes Kubicek - Tue, 2017-02-21 00:33
APEX 5.1 ist endlich da. Oracle Application Express wird mit jedem Release besser und zieht immer mehr Entwickler weltweit in seinen Bann. Es ist einfach, einfache wie auch komplexe Applikationen auf Basis des Oracle Stacks zu entwickeln. Es macht sogar richtig Spaß !

Mit APEX 5.1 sind als wichtiges neues Feature die Interactive Grids mit dazu gekommen. Wir haben sehr lange auf eine moderne Möglichkeit gewartet, Excel - ähnliche Funktionen auf einer Webseite mit APEX zu implementieren. Jetzt ist es endlich soweit :) . Sogar Master-Detail-Detail-Detail-... Beziehungen sind umsetzbar, unsere Anwender werden sich freuen.

Darüber hinaus gibt es auch in vielen anderen Bereichen wichtige Neuerungen, die uns das Leben erleichtern. Gleichzeitig sind aber auch einige wichtige Dinge zu beachten, damit wir ein reibungsloses Upgrade durchführen können.

In diesem Kurs lernen Sie die neuen Funktionalitäten von Oracle Application Express 5.1 kennen, insbesondere wie Sie diese erfolgreich in der Praxis einsetzen.

Lernen Sie von und diskutieren Sie mit den weltweit bekannten Oracle APEX Experten:

Denes Kubicek, langjährige Projekterfahrung in den Bereichen Oracle und APEX mit multisite Applikationen, Preisträger des "Oracle APEX Developer of the Year 2008" Awards des Oracle Magazines, ein Oracle ACE Director und sehr bekannt im Oracle APEX OTN Forum für seine Beispielapplikation und

Dietmar Aust, erfahrener Oracle Consultant mit Spezialisierung auf Oracle APEX, Oracle ACE und aktiv in den OTN Foren zu APEX und Oracle XE, mit regelmäßigen Präsentationen auf den einschlägigen Oracle Konferenzen (DOAG, ODTUG, Oracle Open World).



Categories: Development

Database Star Academy Membership is Now Open

Complete IT Professional - Mon, 2017-02-20 21:15
The Database Star Academy membership is now open! Here’s what you need to know. What Is the Database Star Academy Membership? It’s a membership site with a monthly fee, that gives you access to many different online video courses and PDF guides related to Oracle database development. What’s Included? As part of your membership, you […]
Categories: Development

Why Focused Learning Is Better For Your Career

Complete IT Professional - Mon, 2017-02-20 05:00
In this article, I’ll explain what I mean by “focused learning” and why it’s a good idea for your career. What is Focused Learning? When we want to learn something, we tend to research on it. We look for websites, articles, videos on the topic. Or we enrol in a university course, if it’s career […]
Categories: Development

How to speed up slow unicode migration of a table with xmltype columns

XTended Oracle SQL - Sun, 2017-02-19 18:46

Recently I have had an issue with slow unicode migration of the database upgraded from 10g to 12.1.0.2. The main problem was a table with xmltype: we spent about 4 hours for this table(~17GB) during test migration, though all other tables (~190GB) migrated just for about 20 minutes.
We used DMU(Database Migration Assistant for Unicode), and the root cause of the problem was update statement generated by DMU for this table:

update  /*+ PARALLEL(A,16)*/ "RRR"."T_XMLDATA" A  set A."SYS_NC00011$" = SYS_OP_CSCONV(A."SYS_NC00011$", 'AL16UTF16')

“SYS_NC00011$” was internal hidden CLOB column used to store XMLTYPE. As you can see DMU added PARALLEL hint, but though oracle can use parallel dml for xmltype since 12.1.0.1, we can’t use it because of its’ limitations:

Changes in Oracle Database 12c Release 1 (12.1.0.1) for Oracle XML DB

Parallel DML Support for XMLType
Support for parallel DML has been improved for XMLType storage model binary XML using SecureFiles LOBs. The performance and scalability have been improved for both CREATE TABLE AS SELECT and INSERT AS SELECT.

Restrictions on Parallel DML

Parallel DML can be done on tables with LOB columns provided the table is partitioned. However, intra-partition parallelism is not supported.

For non-partitioned tables with LOB columns, parallel INSERT operations are supported provided that the LOB columns are declared as SecureFiles LOBs. Parallel UPDATE, DELETE, and MERGE operations on such tables are not supported.

Btw, Oracle didn’t support parallel dml for xmltype on previous releases:

No Parallel DML for XMLType – DML operations on XMLType data are always performed in serial. Parallel DML is not supported for XMLType. (Parallel query and DDL are supported for XMLType.)

So I had to use manual parallelization:
1. Monitor “Convert application tables” step through “View Table Conversion progress” and press “Stop” button during conversion of this table.
2. Create table with ROWIDs of this table and split them into 16 groups:

create table tmp_rids as 
select rowid rid, ntile(16)over(order by rowid) grp 
from t_xmldata;

3. Execute

ALTER SYSTEM SET EVENTS '22838 TRACE NAME CONTEXT LEVEL 1,FOREVER'; 

to avoid “ORA-22839: Direct updates on SYS_NC columns are disallowed”
4. Start 16 sessions and each of them have to update own part:

update t_xmldata A 
set A."SYS_NC00011$" = SYS_OP_CSCONV(A."SYS_NC00011$", 'AL16UTF16') 
where rowid in (select rid from tmp_rids where grp=&grp);
commit;

5. Disable event 22838:

ALTER SYSTEM SET EVENTS '22838 TRACE NAME CONTEXT OFF'; 

6. Open “View Table Conversion progress” window, click on this table and change “Retry” to “Skip” option for the update step.

This simple method allowed to make unicode migration about 16 times faster.

Categories: Development

Script to get OMS ,Agent and DB Plugin Patch details

Arun Bavera - Sat, 2017-02-18 18:17

 

GETTING OMS  PATCH DETAILS

export OMS_HOME=/u01/middleware/oms
export ORACLE_HOME=$OMS_HOME
export PLUGIN_HOME=/u01/middleware/plugins/oracle.sysman.db.oms.plugin_12.1.0.8.0/

DT=$(date '+%m%d%Y')

LOG=/tmp/EM_Patch_details_${DT}.txt

echo "1.Getting the OMS system patches" >${LOG}
echo "=====================================================================================" >>${LOG}

$ORACLE_HOME/OPatch/opatch lsinventory -details >>${LOG}

echo "" >>${LOG}
echo "======================================================================================" >>${LOG}
echo "2.Getting the OMS DB PLUGIN patches" >>${LOG}
echo "======================================================================================" >>${LOG}
$ORACLE_HOME/OPatch/opatch lsinventory -oh $PLUGIN_HOME -details >>${LOG}

 

GETTING AGENT PATCH DETAILS

export AGENT_BASE=/u01/app/oracle/product/agent12c
export AGENT_HOME=${AGENT_BASE}/core/12.1.0.5.0
export ORACLE_HOME=${AGENT_HOME}
export PLUGIN_HOME=${AGENT_BASE}/plugins/oracle.sysman.db.agent.plugin_12.1.0.8.0/

DT=$(date '+%m%d%Y')

LOG=/tmp/AGENT_Patch_details_${DT}.txt

echo "1.Getting the AGENT system patches" >${LOG}
echo "=====================================================================================" >>${LOG}

$ORACLE_HOME/OPatch/opatch lsinventory -details >>${LOG}

echo "" >>${LOG}
echo "======================================================================================" >>${LOG}
echo "2.Getting the AGENT DB PLUGIN patches" >>${LOG}
echo "======================================================================================" >>${LOG}
$ORACLE_HOME/OPatch/opatch lsinventory -oh $PLUGIN_HOME -details >>${LOG}

Categories: Development

Coming Soon: Database Star Academy Membership

Complete IT Professional - Fri, 2017-02-17 05:00
The Database Star Academy membership will be launching very soon. Read on to find out more. What Is The Database Star Academy Membership? Over the last couple of years, I’ve developed several video courses and PDF guides to help database developers improve their SQL skills and their career. Up until now, I’ve made these available […]
Categories: Development

Weekly Link Roundup – Feb 17, 2017

Complete IT Professional - Fri, 2017-02-17 00:39
Here’s a collection of interesting articles I’ve read this week. Articles I’ve Read Truncate 12c https://jonathanlewis.wordpress.com/2017/02/16/truncate-12c/ Jonathan Lewis writes an article about some improvements to the TRUNCATE statement in Oracle 12c, and how it relates to “on delete cascade”. THere’s a small example and a good explanation on how it works.   Step by Step […]
Categories: Development

Comma separated search and search with checkboxes in Oracle APEX

Dimitri Gielis - Thu, 2017-02-16 16:30

When you have a classic report in Oracle Application Express (APEX) and want to make it searchable you typically add a Text Item in the region, enable Submit on Enter and add a WHERE clause to your SQL statement.

Here’s an example:

Classic Report with Search (text item)

Your SQL statement probably looks like this:

select CUSTOMER_ID,
CUST_FIRST_NAME,
CUST_LAST_NAME,
CUST_STREET_ADDRESS1,
CUST_CITY,
CUST_STATE,
CUST_POSTAL_CODE,
CUST_EMAIL,
CREDIT_LIMIT
from DEMO_CUSTOMERS
where CUSTOMER_ID = :P4_SEARCH

When you want to search for multiple customers separated by a comma, how do you do that?
So in my search field I add for example: 1,2,3 and expect to see 3 customers.

There’re a couple of options you have, I’ll list three below:

  1. INSTR

    where INSTR(','||:P4_SEARCH||',', ',' || CUSTOMER_ID || ',') > 0
  2. REGEXP_LIKE

    where REGEXP_LIKE(CUSTOMER_ID, '^('|| REPLACE(:P4_SEARCH,',','|') ||')$')
  3. REGEXP_SUBSTR

    where customer_id in to_number((
    select regexp_substr(:P4_SEARCH,'[^,]+', 1, level)
    from dual
    connect by regexp_substr(:P4_SEARCH, '[^,]+', 1, level) is not null
    ))

Which one to choose? It depends what you need… if you need readability, maybe you find INSTR easier to understand. If you need performance, maybe the last option is the better choice… so as always it depends. If you want to measure the performance you can look at the Explain Plan (just copy the SQL in SQL Workshop and hit the Explain tab).

The Explain Plan for the first SQL looks like this:

Explain Plan INSTR

The Explain Plan for the last SQL looks like this:

Explain Plan REGEXP_SUBSTR

The above technique is also useful when you use want checkboxes above your report, so people can make a selection. For example we select the customers we want to see:

Classic Report with checkbox selection

The where clause would be identical, but instead of a comma (,) you would use a colon (:), so the first statement would be:

where INSTR(':'||:P4_SEARCH||':', ':' || CUSTOMER_ID || ':') > 0

Happy searching your Classic Report :)

Categories: Development

ORA-00936: missing expression Solution

Complete IT Professional - Wed, 2017-02-15 05:00
Did you get an ORA-00936: missing expression error? Learn what it means and how to resolve it in this article. ORA-00936 Cause The error you’ve gotten is this: ORA_00936: missing expression Oracle’s official “cause and action” that appears along with the error is: Cause: A required part of a clause or expression has been omitted. […]
Categories: Development

Where do you specify the Date Format Mask

Dimitri Gielis - Mon, 2017-02-13 10:32

When reviewing Oracle APEX applications I often see hardcoded date or timestamp formats.
You can define your date formats in multiple places in your application. In your item or column attributes, as part of your code e.g.TO_CHAR(sysdate, ‘DD-MON-YYYY HH24:MI’) or if you want to make it more reusable you might create a substitution string. That will all work, but you can make your life easier and for the ones looking or needing to maintain your code…

APEX itself provides in the Globalization attributes (Shared Components) a place where you can define your default date and format masks for your entire applications. I consider this a best practice to use those fields, as it’s defined in one logical place, so when you need to change your format, you do it once and you’re done. In your custom SQL and PL/SQL code you can also reference those format masks by predefined substitution strings:

  • APP_NLS_DATE_FORMAT
  • APP_DATE_TIME_FORMAT
  • APP_NLS_TIMESTAMP_FORMAT
  • APP_NLS_TIMESTAMP_TZ_FORMAT

e.g. TO_CHAR(sysdate, :APP_NLS_DATE_FORMAT)

Here’s a screenshot which shows which substitution string corresponds with which field:

Application Attributes - Globalization

You can define the format mask you want, or you can click the arrow to see most used format masks represented with an example. To make it a bit easier, I put the format mask (in red) next to it, so you see the underlying format mask more easily:

Possible date format masks defined in the pop-up

If you need to make the format mask dynamic, for example using different format masks for different language, APEX doesn’t allow you to translate that substitution string through Text Messages, but you can work around it by using your own substitution string and have that dynamically filled. In the Globalization Attributes you would add instead of a hardcoded format mask your own substitution string e.g. &MY_TRANSLATED_DATE.FORMAT.

Categories: Development

UNION vs UNION ALL: What’s The Difference?

Complete IT Professional - Mon, 2017-02-13 05:00
What’s the difference between UNION and UNION ALL in Oracle SQL? There are a few. Learn what they are in this article. What Is UNION and UNION ALL? First of all, let’s explain what they are. UNION and UNION ALL are both “set operators”. They are keywords you can use to combine two sets of […]
Categories: Development

row pieces, 255 columns, intra-block row chaining in details

XTended Oracle SQL - Sat, 2017-02-11 20:54

You may know about Intra-block row chaining which may occur when the number of columns in a table are more than 255 columns.
But do you know that intra-block chaining works with inserts only? not updates!

Documentation says:

When a table has more than 255 columns, rows that have data after the 255th column are likely to be chained within the same block. This is called intra-block chaining. A chained row’s pieces are chained together using the rowids of the pieces. With intra-block chaining, users receive all the data in the same block. If the row fits in the block, users do not see an effect in I/O performance, because no extra I/O operation is required to retrieve the rest of the row.

A bit more details:
1. One row piece can store up to 255 columns
2. Oracle splits fields by row pieces in reverse order
3. Oracle doesn’t store trailing null fields in a row (not in row piece)
4. Next row piece can be stored in the same block only with inserts. When you run update, oracle will place new row piece into another block.

I’ll show in examples with dumps:

Example 1:
1. Create table TEST with 355 columns (c_1, c_2, …, c_355)
2. insert into test(c_300) values(2)
3. dump data blocks

test code

drop table test purge;
set serverout on;
alter session set tracefile_identifier='test1';
declare
   c varchar2(32000);
   v varchar2(32000);
   cols varchar2(32000):='c_1 number(1,0)';
   vals varchar2(32000):='null';
   
   ndf int;
   nbl int;
begin
   for i in 2..355 loop
      cols:=cols||',c_'||i||' number(1,0)';
      vals:=vals||','||case 
                         when i = 300 then '2'
                         else 'null'
                       end;
   end loop;
   c:='create table test('||cols||')';
   v:='insert into test values('||vals||')';
   dbms_output.put_line(c);
   dbms_output.put_line(v);
   execute immediate (c);
   execute immediate (v);
   
   for r in (select 'alter system dump datafile '||file#||' block '||block# cmd 
             from (
                   select distinct file#,block# 
                   from v$bh 
                   where class#=1 
                   and objd in (select o.data_object_id from user_objects o where object_name='TEST') 
                   order by 1,2
                  )
            )
   loop 
       execute immediate r.cmd;
   end loop;
end;
/
disc;

[collapse]
grep -P “^(bdba|block_row_dump|tl: |col )” test1.trc

bdba: 0x018019f3
block_row_dump:

bdba: 0x018019f4
block_row_dump:

bdba: 0x018019f5
block_row_dump:

bdba: 0x018019f6
block_row_dump:

bdba: 0x018019f7
    block_row_dump:
        tl: 260 fb: -----L-- lb: 0x1  cc: 255
            col  0: *NULL*
            col  1: *NULL*
            col  2: *NULL*
            col  3: *NULL*
               ...
            col 252: *NULL*
            col 253: *NULL*
            col 254: [ 2]  c1 03
        tl: 54 fb: --H-F--- lb: 0x1  cc: 45
            col  0: *NULL*
            col  1: *NULL*
            col  2: *NULL*
            col  3: *NULL*
               ...
            col 42: *NULL*
            col 43: *NULL*
            col 44: *NULL*

The resulted dump file shows us:
1. Both row pieces are in the same block 0x018019f4
2. They contain only first 300 columns, (trailing 55 columns are NULLs)
3. First row piece contains columns c_46 – c_300,
4. Second row piece contains columns c_1 – c_45 (they all are NULLs)

Example 2.
But let’s test an update with the same table:
1. Create table TEST with 355 columns (c_1, c_2, …, c_355)
2. insert into test(c_1) values(null)
3. update test set c_300=2
4. dump data blocks

Test code 2

drop table test purge;
set serverout on;
alter session set tracefile_identifier='test2';
declare
   c varchar2(32000);
   cols varchar2(32000):='c_1 number(1,0)';
begin
   for i in 2..355 loop
      cols:=cols||',c_'||i||' number(1,0)';
   end loop;
   c:='create table test('||cols||')';
   execute immediate (c);
   execute immediate ('insert into test(c_1) values(null)');
   execute immediate 'update test set c_300=3';
   commit;
   for r in (select 'alter system dump datafile '||file#||' block '||block# cmd 
             from (
                   select distinct file#,block# 
                   from v$bh 
                   where class#=1 
                   and objd in (select o.data_object_id from user_objects o where object_name='TEST') 
                   order by 1,2
                  )
            )
   loop 
       execute immediate r.cmd;
   end loop;
end;
/
disc;

[collapse]
Dump:

bdba: 0x018019f3
    block_row_dump:
        tl: 260 fb: -----L-- lb: 0x1  cc: 255
            col  0: *NULL*
            col  1: *NULL*
            col  2: *NULL*
               ...
            col 251: *NULL*
            col 252: *NULL*
            col 253: *NULL*
            col 254: [ 2]  c1 04

bdba: 0x018019f4
block_row_dump:

bdba: 0x018019f5
block_row_dump:

bdba: 0x018019f6
block_row_dump:

bdba: 0x018019f7
    block_row_dump:
        tl: 54 fb: --H-F--- lb: 0x1  cc: 45
            col  0: *NULL*
            col  1: *NULL*
            col  2: *NULL*
               ...
            col 42: *NULL*
            col 43: *NULL*
            col 44: *NULL*

As you can see, there is no intra-block chaining – second row piece was created in another block.

Example 3.
Now I want to show how oracle splits already chained rows:
1. Create table TEST with 355 columns (c_1, c_2, …, c_355)
2. insert into test(c_1) values(1)
3. update test set c_300=2
4. update test set c_301=3
5. update test set c_302=4
6. dump data blocks

Test code 3

drop table test purge;
set serverout on
alter session set tracefile_identifier='test3';

declare
   cols varchar2(32000):='c_1 number(1,0)';
   
   procedure print_and_exec(c varchar2) as
   begin
      dbms_output.put_line(c);
      execute immediate c;
   end;
begin
   for i in 2..355 loop
      cols:=cols||',c_'||i||' number(1,0)';
   end loop;
   print_and_exec ('create table test('||cols||')');
   print_and_exec ('insert into test(c_1) values(1)');
   print_and_exec ('update test set c_300=2');
   print_and_exec ('update test set c_301=3');
   print_and_exec ('update test set c_302=4');
   commit;
   execute immediate 'alter system flush buffer_cache';
   execute immediate 'select count(*) from test';
   for r in (select 'alter system dump datafile '||file#||' block '||block# cmd 
             from (
                   select distinct file#,block# 
                   from v$bh 
                   where class#=1 
                   and objd in (select o.data_object_id from user_objects o where object_name='TEST') 
                   order by 1,2
                  )
            )
   loop 
       execute immediate r.cmd;
   end loop;
end;
/
disc;

[collapse]

bdba: 0x018019f3
    block_row_dump:
        tl: 10 fb: -------- lb: 0x1  cc: 1
            col  0: *NULL*

bdba: 0x018019f4
    block_row_dump:
        tl: 264 fb: -----L-- lb: 0x1  cc: 255
            col  0: *NULL*
            col  1: *NULL*
               ...
            col 249: *NULL*
            col 250: *NULL*
            col 251: *NULL*
            col 252: [ 2]  c1 03
            col 253: [ 2]  c1 04
            col 254: [ 2]  c1 05

bdba: 0x018019f5
block_row_dump:

bdba: 0x018019f6
    block_row_dump:
        tl: 10 fb: -------- lb: 0x1  cc: 1
            col  0: *NULL*

bdba: 0x018019f7
    block_row_dump:
        tl: 56 fb: --H-F--- lb: 0x1  cc: 45
            col  0: [ 2]  c1 02
            col  1: *NULL*
            col  2: *NULL*
            col  3: *NULL*
               ...
            col 42: *NULL*
            col 43: *NULL*
            col 44: *NULL*

This dump shows us 4 row pieces: First row piece contains 255 columns, second – 45, and 2 row pieces – just by one row.
So we can analyze it step-by-step:
2. insert into test(c_1) values(1)
After insert we have just one row piece with 1 field.

3. update test set c_300=2
After this update, we have 2 row pieces:
1) c_1-c_45
2) c_46-c_300

4. update test set c_301=3
This update split row piece c_46-c_300 into 2 row pieces:
1) c_46
2) c_47-c_301
So we have 3 row pieces now: c_1-c_45, c_46, c_47-c_301

5. update test set c_302=4
This update split row piece c_47-c_301 into 2 row pieces:
1) c_47
2) c_48-c_302
And we’ve got 4 row pieces: c_1-c_45, c_46, c_47, c_48-c_302

You can try Example 4 and see how many blocks you can get, and all of them (except last one) will have only 1 column each:

Test code 4

drop table test purge;
set serverout on
alter session set tracefile_identifier='test4';

declare
   cols varchar2(32000):='c_1 char(3)';
   
   procedure print_and_exec(c varchar2) as
   begin
      dbms_output.put_line(c);
      execute immediate c;
   end;
begin
   for i in 2..355 loop
      cols:=cols||',c_'||i||' char(3)';
   end loop;
   print_and_exec ('create table test('||cols||')');
   print_and_exec ('insert into test(c_1) values(null)');
   commit;
   for i in 256..355 loop
      execute immediate 'update test set c_'||i||'='||i;
   end loop;
   commit;
   execute immediate 'alter system flush buffer_cache';
   for r in (select 'alter system dump datafile '||file#||' block '||block# cmd 
             from (
                   select distinct file#,block# 
                   from v$bh 
                   where class#=1 
                   and objd in (select o.data_object_id from user_objects o where object_name='TEST') 
                   order by 1,2
                  )
            )
   loop 
       execute immediate r.cmd;
   end loop;
end;
/
disc;

[collapse]

grep

bdba: 0x01801281
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801282
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801283
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801284
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801285
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801286
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801287
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801288
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801289
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180128a
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180128b
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180128c
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180128d
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180128e
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180128f
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801291
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801292
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801293
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801294
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801295
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801296
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801297
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801298
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x01801299
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180129a
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180129b
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180129c
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180129d
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180129e
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x0180129f
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a1
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a2
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a3
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a4
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a5
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a6
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a7
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a8
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012a9
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012aa
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ab
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ac
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ad
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ae
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012af
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b1
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b2
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b3
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b4
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b5
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b6
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b7
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b8
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012b9
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ba
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012bb
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012bc
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012bd
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012be
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012bf
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c1
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c2
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c3
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c4
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c5
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c6
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c7
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c8
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012c9
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ca
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012cb
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012cc
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012cd
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012ce
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012cf
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d1
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d2
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d3
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d4
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d5
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d6
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d7
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d8
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012d9
block_row_dump:
bdba: 0x018012da
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012db
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012dc
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012dd
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018012de
block_row_dump:
tl: 558 fb: -----L-- lb: 0x1  cc: 255
col  0: *NULL*
col  1: *NULL*
col  2: *NULL*
   ...
col 152: *NULL*
col 153: *NULL*
col 154: *NULL*
col 155: [ 3]  32 35 36
col 156: [ 3]  32 35 37
col 157: [ 3]  32 35 38
col 158: [ 3]  32 35 39
  ...
col 251: [ 3]  33 35 32
col 252: [ 3]  33 35 33
col 253: [ 3]  33 35 34
col 254: [ 3]  33 35 35
bdba: 0x018012df
block_row_dump:
bdba: 0x018019f3
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019f4
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019f5
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019f6
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019f7
block_row_dump:
tl: 10 fb: --H-F--- lb: 0x2  cc: 1
col  0: *NULL*
bdba: 0x018019f8
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019f9
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019fa
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019fb
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019fc
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019fd
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019fe
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*
bdba: 0x018019ff
block_row_dump:
tl: 10 fb: -------- lb: 0x1  cc: 1
col  0: *NULL*

[collapse]

Categories: Development

Pages

Subscribe to Oracle FAQ aggregator - Development