Feed aggregator

TIME part in CAST (value AS DATE)

Tom Kyte - 42 min 17 sec ago
I am trying compare dates. Following SQL returns one row <code>SELECT COUNT(1) FROM ( SELECT 1 FROM DUAL WHERE TRUNC(CAST(CAST('13-JUN-22 05.47.49.000000000 PM' AS TIMESTAMP) AS DATE)) = TO_DATE('13-JUN-22','DD-MON-YY') );</code> where as the same query without TRUNC returns zero rows <code>SELECT COUNT(1) FROM ( SELECT 1 FROM DUAL WHERE (CAST(CAST('13-JUN-22 05.47.49.000000000 PM' AS TIMESTAMP) AS DATE)) = TO_DATE('13-JUN-22','DD-MON-YY') );</code> I believe (maybe I am wrong) that although the CAST convert the TIMESTAMP into DATE, in the first SQL, nevertheless ,there is a TIME part that still remains in it, which is eliminated by the TRUNC . Is this conclusion correct and what is value of the TIME part?
Categories: DBA Blogs

Resume failed rman duplication

Tom Kyte - 42 min 17 sec ago
I tried to duplicate source database to create physical standby database using the below command duplicate target database for standby FROM ACTIVE DATABASE USING COMPRESSED BACKUPSET SPFILE nofilenamecheck; I have a very big database size with multiple big data files. After finish 70% of database duplication, i discovered one of the big data files will take very long time to be finished. So i want to use section size to speed up the duplication using the below command duplicate target database for standby FROM ACTIVE DATABASE SECTION SIZE 500M USING COMPRESSED BACKUPSET SPFILE nofilenamecheck; My question: ============ if i cancel the running duplication script and re_run it again, i will lose all the progress done before. - It will start duplication from scratch? - The big file which reach to around 50% of duplication, it will resume or starting from scratch. regards,
Categories: DBA Blogs

パティシエの専門学校に通う必要性と学費について

The Feature - Thu, 2022-08-11 20:54

パティシエというのはあくまでもお菓子作りをする職人を指すものであり、国家資格などの有資格者を指すものではありません。なので、普通の学校を卒業してケーキ屋へ就職するという手順でもなることができる職業です。しかし、現場では知識や経験がある方が求められている場合が多いため、一般的には専門学校などを卒業した上で就職をするというケースが多いと言えるでしょう。パティシエの学校では、お菓子作りの基礎的なことを勉強するだけではなく、お菓子の伝統なども学ぶことができますし、お菓子作りに関するさまざまな資格を取得することもできます。

なので、実際に仕事をする際にそれらの幅広い知識や資格を活かすことができると言えるでしょう。しかし気になるのは、パティシエの専門学校に通うための学費がどれぐらいかかるのかということです。一般的には、2年間学校へ通う場合だと300万~400万円程度の学費がかかるのが相場だと言われています。この金額は、他の専門学校より高いと言えますが、これは授業の中で使われる材料費が高くなるという理由があるからです。

それに大手の学校の場合だと、良い材料を使うことが重要視されることが多いため、より材料費が高くなる傾向があると言われています。また、パティシエの専門学校では、本場であるフランスへの留学がプログラムとして組まれていることも多く、その場合だと留学費用がさらにかかることになるでしょう。留学期間が2ヵ月程度の場合だと、50万円程度の費用が必要になると言われています。

Categories: APPS Blogs

パティシエの専門学校に通う必要性と学費について

Marian Crkon - Thu, 2022-08-11 20:54
パティシエというのはあくまでもお菓子作りをする職人を指すものであり、国家資格などの有資格者を指すものではありま...

Pickleball 002 – Einstieg ins Doppel

The Oracle Instructor - Wed, 2022-08-10 09:09

Doppel ist sicherlich die beliebteste Variante in der Pickleball gespielt wird – viele spielen gar keine Einzel. Dieser kurze Artikel hilft vielleicht beim Einstieg. Das Beste vorweg: Pickleball im Doppel ist so ziemlich die inklusivste Form von Sport die man sich denken kann. Männer können gegen Frauen antreten, die ältere Generation gegen Jüngere, alte Hasen gegen Neueinsteiger. In praktisch jeder möglichen Kombination haben trotzdem alle ihren Spaß auf dem Feld und kommen auf ihre Kosten. Die Community ist gegenüber Neuankömmlingen sehr aufgeschlossen und wertschätzend. Das und die Inklusivität sind nach meiner Meinung auch die Hauptgründe, warum Pickleball so rapide an Popularität gewinnt.

Pickleball wird vorn entschieden

Im Tennis bin ich zwar eher ein Grundlinienspieler, aber das ist beim Pickleball nicht erfolgversprechend. Das kurze Spiel in der Nicht-Volley-Zone (NVZ) – auch Dinking genannt – und Volleys sind hier meistens spielentscheidend. Daher sollte man besonders Dinking und Volleys üben.

Üben für Ballsicherheit und Konsistenz

Übung macht den Pickleball-Meister, denn die meisten Spiele werden nicht so sehr gewonnen sondern durch unerzwungene Fehler verloren. Ballsicherheit ist Trumpf, natürlich auch und gerade im Doppel, wo man seinem Partner nicht gern viele unforced errors zumuten möchte. Eine gute Praxis ist es, vor dem eigentlichen Spiel zum Aufwärmen ein Kurz-Kurz-Spiel an der NVZ zu machen: Aufschlag diagonal, dann müssen erst mindestens 5 Dinks in die NVZ erfolgen, bevor der Ball freigegeben wird. Oder etwa, man muss erst 7 Volleys in Folge schaffen, bevor das Spiel anfängt.

Warum 0-0-2?

Die Zählweise im Doppel kann anfangs verwirrend sein. Ich hab mich jedenfalls zunächst etwas schwer getan und darum dies Video aufgenommen:

Hilfreich ist es außerdem, wenn man sich im Doppel merkt, auf welcher Seite man am Anfang gestanden hat. Stehe ich zum Beispiel anfangs auf der rechten (geraden) Seite, so werde ich zukünftig immer, wenn mein Team einen geraden Punktestand hat, rechts stehen. Also bei 0,2,4,6,8,10 für mein Team sollte ich immer rechts stehen. Bei 1,3,5,7,9,11 sollte entsprechend mein Partner auf der linken (ungeraden) Seite stehen. Als Gedächtnisstütze nehme ich Schweißbänder: Zwei wenn ich rechts anfange und eines wenn ich links anfange.

Elementare Doppel-Strategie

Die typische Verhaltensweise eines Doppel-Teams habe ich hier kurz geschildert. Natürlich gibt es noch mehr an Feinheiten, aber als Starthilfe sollte das erstmal reichen. Außerdem: So kompliziert ist Pickleball auch eigentlich nicht.

So, ich hoffe, das war hilfreich und ermutigend, um mit dem Pickleball spielen loszulegen – wir freuen uns schon darauf, euch auf dem Platz zu begegnen!

Categories: DBA Blogs

Granting ALL on Database Objects

Pete Finnigan - Tue, 2022-08-09 09:06
I was asked by a friend a few days ago a few questions related to the granting of ALL on a database object such as a table or a PL/SQL package. For example - GRANT ALL ON OWNER.TABLE TO DAVE....[Read More]

Posted by Pete On 09/08/22 At 12:46 PM

Categories: Security Blogs

専門学校で学んだ知識でパティシエが務まるのか

The Feature - Mon, 2022-08-08 20:53

「食」にかかわる仕事をしている人はたくさんいて、職業で分類するだけでも相当な数に分けることができます。ただ、細かいジャンルに分ける前の段階でいえば「調理師」「製菓衛生士」というのが業界を二分している存在といえるため、このうちいずれかに属している人がほぼすべてといっても過言ではありません。製菓衛生士の花形といえる「パティシエ」になる方法は、主に専門学校などの養成施設を経て資格を取得するというものです。調理師になるためにのパティシエ同様に専門学校などを経て資格を取得するという流れが多いですが、必ずこの方法でなければいけないということではありません。

現在、飲食業として業を成す際に必要な資格は「衛生責任者」というもので、調理師など専門的なものではありません。一切関連する知識を持たない人でも、数日程度の研修を受ければ誰でも取得することができます。研修中に安全安心な店舗経営において必要な最低限度の知識を学ぶことができるので、その知識(証明)さえできれば十分です。当然、パティシエになるためにも専門学校などを出る必須性はないのですが、「基本的な技術を学習することができる」というメリットには期待できると考えられています。

たしかに、多少なりとも訓練を積めば戦力となり得る可能性が高まりますが、少なくともよほどの努力をしない限りは実践で使い物になるような技術を訓練期間だけで身に付けられる可能性は低いです。

Categories: APPS Blogs

専門学校で学んだ知識でパティシエが務まるのか

Marian Crkon - Mon, 2022-08-08 20:53
「食」にかかわる仕事をしている人はたくさんいて、職業で分類するだけでも相当な数に分けることができます。ただ、細...

Truncate Partitions on Referenced-Partitioned Table

Tom Kyte - Mon, 2022-08-08 16:46
Hi, Can you please have a look at below scenario Table Name: EMP Primary Key EMPID Columns: EMPID NUMBER, NAME VARCHAR2(10) RECORD_DATE DATE Table Name: EMP_HIST Foreign Key: EMP.EMPID EMP & EMP_HIST are referenced-partitioned on column: RECORD_DATE Now I am trying to truncate one of partition on EMP table but getting ORA-02266: unique/primary keys in table referenced by enabled foreign keys 02266. 00000 - "unique/primary keys in table referenced by enabled foreign keys" alter table EMP truncate partition PYTR_P_MAXVALUE; I tried truncate child table EMP_HIST using: alter table EMP_HIST truncate partition PYTR_P_MAXVALUE; -- its a success but still I am not able to alter table EMP truncate partition PYTR_P_MAXVALUE; Could you please help here.
Categories: DBA Blogs

Purging records of a large table.

Tom Kyte - Mon, 2022-08-08 16:46
Hi Tom, What plan do you suggest to purge a table which is of size 377Gb approx? We are currently following this plan, - Creating a temporary table with the required data(Approx 86 crore records). - Drop the constraints(15 constraints). - Truncate the original table. - Make the indexes unsuable(4 Indexes). - Insert the data from Temporary table to Original table. (It took 2 hours) - Rebuilt one index to test.(It took 1hour 30mins) - Recreated one constraint. (It took 52 mins) We feel we cannot follow this approach for production environment. Can you suggest us a good plan to do this activity? Please let me know if you need more details.
Categories: DBA Blogs

Error in connecting to ATP from SQL Developer ORA-12529: TNS:connect request rejected based on current filtering rules

Tom Kyte - Mon, 2022-08-08 16:46
Hi, I created my free tier ATP database. For connecting it from SQLDeveloper I downloaded the wallet file. However I allways get this error when try to connect from SQLDeveloper: Estado: Fallo:Fallo de la prueba: Listener refused the connection with the following error: ORA-12529, TNS:connect request rejected based on current filtering rules (CONNECTION_ID=XuDEQk4nQUGBhm/R+5Uuvg==) I also asked the same in : https://stackoverflow.com/questions/73259314/ora-12529-for-connecting-to-oracle-atp-database?noredirect=1#comment129381369_73259314 I really appreciate any help. Thanks
Categories: DBA Blogs

query

Tom Kyte - Mon, 2022-08-08 16:46
Hi Tom, I have a table as follows: SQL> desc t; Name Null? Type ----------------------------------------------------- -------- --------------- NAME VARCHAR2(20) TIME_SHIFT NUMBER(4) AUTO_ID NUMBER STATE VARCHAR2(3) SQL> select * from t; NAME TIME_SHIFT AUTO_ID STATE -------------------- ---------- ---------- ----- a 12 1 A a 13 2 B a 13.4 3 C a 18 4 A a 19 5 B a 22 6 A a 22 7 B b 8 8 C b 9 9 D c 1 10 C c 2 11 C c 3 12 C c 4 13 C 13 rows selected. I'd like to generate a report based on the following conditions: - same name - different state - time shift range +/- 1 (time_shift-1 <= value <= time_shift+1) among rows If the result matches these conditions, I called it a "GROUP". The desired output will look like this: auto_id_group avg_time_shift name ------------- -------------- ---- 1,2,3 12.8 a 4,5 18.5 a 6,7 22 a 8,9 8.5 b where, auto_id_group - comma seperated sorted auto_id within "GROUP" avg_time_shift - average of time_shift within "GROUP" if the data were: NAME TIME_SHIFT AUTO_ID STATE -------------------- ---------- ---------- ----- a 12 1 A a 13 2 B a 13.4 3 C a 14.4 4 A ... 14.4 row WILL NOT be in the 1,2,3 group since state in the "GROUP" has to be different. if the data were: NAME TIME_SHIFT AUTO_ID STATE -------------------- ---------- ---------- ----- a 12 1 A a 13 2 B a 13.4 3 C a 14.4 4 D ... would that 14.4 row be in the 1,2,3 group -- even though the spread from the first is 2.4? >>> Sorry about confusion, Tom. There will be no 1,2,3 group but 1,2,3,4 group since they have: - same name - different state - time shift range +/- 1 (PREVIOUS_ROW_time_shift-1 <= CURRENT_ROW_SHIFT <= PREVIOUS_ROW_time_shift+1) among rows. For example, 13-12=1, 13.4-13 <1, 14.4-13.4=1 Thanks,
Categories: DBA Blogs

Protect your APEX app from URL Tampering – in just a few clicks

Jeff Kemp - Sun, 2022-08-07 19:40

Recently I’ve been reviewing and updating my knowledge of APEX security, especially protection from URL tampering. I’ve read the documentation, a number of blogs, and heard from people with experience in the field such as Lino. By default, when you create a new application in APEX you get the following security settings set automatically, which is a good start:

  • Application Session State Protection is Enabled.
  • Each page has Page Access Protection set to Arguments Must Have Checksum.
  • Each Application Item has Protection Level set to Restricted – May not be set from browser.
  • Each Primary Key Item* created by a wizard has Protection Level set to Checksum Required – Session Level.

(* that is, any item mapped from a table column that is, or forms part of, a Primary Key constraint).

These default settings are considered best practice. If you change these, it becomes your responsibility to ensure that your application is protected against security vulnerabilities from URL tampering.

For page items, however, the Protection Level defaults to Unrestricted. This is ok for Form items because the page fetch process will set their values on page load, rendering any attempt at URL tampering ineffective.

For non-form page items, unless the Page Access Protection is relaxed (Unrestricted), leaving items unrestricted is safe since URL tampering is blocked for the entire page anyway. At runtime, if a malicious visitor tries to modify the item value via the URL, they will get the error “No checksum was provided to show processing for a page that requires a checksum when one or more request, clear cache, or argument values are passed as parameters.

Error message "No checksum was provided to show processing for a page that requires a checksum when one or more request, clear cache, or argument values are passed as parameters."

However, what if a developer later needs to change the page to Unrestricted? They may unwittingly introduce a potential URL tampering issue because one or more items were not protected.

The majority of these items are editable input items, so the fact that someone may input a value via the URL is not a big deal. However, for Hidden and Display Only items, it is common for application logic to depend on their values; this logic may be adversely affected by malicious values supplied via the URL.

In some cases, this default is needed in order for the application to work. Some examples when an item must be left Unrestricted are:

  • An item is changed by a Dynamic Action (whether via a Set Item Value, via the Items to Return of a Execute Server-side Code action, or in some custom JavaScript), and cannot have Value Protected set because the page may be submitted.
  • We do actually intend the item to be set via the URL, e.g. when an external web page has a link that sets the item’s value.

In all these cases, the application must be designed to ensure it does not “trust” the value of these items; it should apply suitable checks to ensure the values are valid.

In most cases, it is best practice to set the item Protection Level to Checksum Required – Session Level (or Restricted – May not be set from browser where supported).

You can use a query like this to discover all items that may need to be reviewed:

select
    i.application_id,
    i.page_id,
    i.page_name,
    i.region,
    i.item_name,
    i.display_as
from apex_application_page_items i
where i.application_id = :app_id
and i.item_protection_level = 'Unrestricted'
and i.display_as_code in ('NATIVE_HIDDEN','NATIVE_DISPLAY_ONLY')
order by i.application_id, i.page_id, i.region, i.item_name;
Report showing APPLICATION_ID, PAGE_ID, PAGE_NAME, REGION, ITEM_NAME, DISPLAY_AS, listing two Display Only items (P1_DISPLAY_ONLY_ITEM, P1_DISPLAY_ONLY_ITEM_DA) and two Hiden items (P1_HIDDEN_ITEM, P1_HIDDEN_ITEM_DA) that have Item Protection Level set to "Unrestricted".

Other excellent options are using third-party security scanners such as APEXSec and APEX-SERT to alert you to potential issues in your application. We mandate the use of tools like these internally at Oracle for our own applications and they are a great help.

Using the Session State Protection Wizard

One easy step you can take right now is to use the Session State Protection wizard. It gives you a quick overview of what level of protection your application has against URL tampering, and gives an easy way of fixing the relevant attributes in bulk.

You can access the wizard via Shared Components > Session State Protection

Screenshot of part of App Builder Shared Components; under the Security heading, we want to click on "Session State Protection".

Alternatively, you can access the wizard via Edit Application Definition > Security > Session State Protection > Manage Session State Protection

Screenshot of part of the App Builder Edit Security Attributes page, under the Security tab. In the Session State Protection section, we want to click on the button "Manage Session State Protection".

The wizard starts by showing an overview of the current state of your application’s protection against URL tampering.

Screenshot of the App Builder Session State Protection overview page.
It indicates that my application has Session State Protection = Enabled.
It shows that one page is set to "Arguments Must Have Checksum", one page allows "No URL Access", and one page is "Unrestricted".
It indicates that all 9 Page Items in the application are set to "Unrestricted".
It indicates that of the application's Application Items, one is set to "Restricted - May not be set from browser", one is "Checksum Required - Session Level", and one is "Unrestricted".
Next to each category a ">" icon button is shown.
At the bottom of the page is the button "Set Protection".

You can see if your application has Session State Protection enabled (which it should, really), and if any pages, page items, and/or application items are unprotected. In my sample app here, it’s obvious that there are some potential security issues that need to be reviewed.

You can click the > buttons next to each category to list all the pages and items that need to be reviewed.

The main things to watch out for are Pages, Page Items, and Application Items that are set to Unrestricted. Other values are generally fine.

If you see any Items which are set to Checksum Required but not at the Session Level, you may find that a developer has simply set them incorrectly and you should consider changing them to Session Level. However, there are some scenarios where the other levels (Application Level, or User Level) are required.

Now, I might now go through the application page-by-page and set the protection level on each page and item as appropriate. This could be a laborious process for a large application.

A good alternative is to use this wizard to set the protection level in bulk. In this case, I’m going to click Set Protection.

Screenshot of page 1 of the Session State Protection Wizard.
It is asking to Select an Action - either "Disable", or "Configure". I have selected "Configure".
Buttons at the bottom of the page allow me to Cancel, or go to the Next page.I’ve selected the action Configure, then click Next. Screenshot of page 2 of the Session State Protection Wizard.
This page allows me to select the Page Access Protection (defaulted to "Arguments Must Have Checksum"), the Page Data Entry Item Protection, the Page Display-Only Item Protection, and the Application Item Protection. These last three are all defaulted to "Checksum Required - Session Level".
Buttons at the bottom of the page allow me to go to the Previous page, Cancel, or go to the Next page.The wizard now gives me the opportunity to modify the protection level on my pages and items in bulk. I’m going to accept the defaults (Arguments Must Have Checksum / Checksum Required – Session Level) because they are appropriate for most cases in my application. Screenshot of the last page of the Session State Protection Wizard.
This allows me to confirm the changes that will be made to pages and items in the application.
Buttons at the bottom of the page allow me to go to the Previous page, Cancel, or Finish.After reviewing the summaries of the changes that the wizard will make, I click Finish. Screenshot of the App Builder Session State Protection overview page, after running the wizard.
The page now indicates that all 3 pages in my application are set to "Arguments Must Have Checksum", that all 9 Page Items and the 3 Application Items are now set to "Checksum Required - Session Level".

Perfect!

Final Steps

Now, I need to check for hidden page items that are now restricted that might need to be returned to Unrestricted. Otherwise, users will see the error “Session state protection violation” when they submit the page, if a dynamic action has changed them.

 This may be caused by manual alteration of protected page item P1_DISPLAY_ONLY_ITEM_DA. If you are unsure what caused this error, please contact the application administrator for assistance."

The following query will alert me to any Hidden items that have Value Protected switched off (e.g. because they need to be submitted):

select
    i.application_id,
    i.page_id,
    i.page_name,
    i.region,
    i.item_name,
    i.display_as
from apex_application_page_items i
where i.application_id = :app_id
and i.item_protection_level != 'Unrestricted'
and i.display_as_code = 'NATIVE_HIDDEN'
and i.attribute_01 = 'N' -- Value Protected
order by i.application_id, i.page_id, i.region, i.item_name;
Report showing APPLICATION_ID, PAGE_ID, PAGE_NAME, REGION, ITEM_NAME, DISPLAY_AS listing one entry for "P1_HIDDEN_ITEM_DA", a hidden item that is set to "Unrestricted" and has attribute_01 (Value Protected) set to "N".

Now I can review this item to check if Value Protected really needed to be switched off. If the page is never submitted, or the item is never changed by any dynamic actions, this could be switched On. Otherwise, I need to set the item protection to Unrestricted in order for the page to work.

Having made changes to the application, I need to test to ensure I haven’t introduced any issues. My focus will be mainly on the following areas:

  1. Navigation – e.g. do the View or Edit buttons in all reports still work?
  2. Dynamic actions – e.g. do all the dynamic actions and custom javascript still work on all pages that set item values?

For #1, I’m looking for any links that include item values that were not correctly built. If the application generates any links using just string concatenation, it will fail if the target page expects a checksum. The application should build these links using declarative link attributes if possible, or by calling apex_page.get_url (or apex_util.prepare_url at least).

For #2, I would test to ensure that after triggering a dynamic action or javascript code that modifies an item’s value, that the form is still submitted (saved) without error.

Further Reading

The format of the ROWID

Hemant K Chitale - Sun, 2022-08-07 04:31

 A ROWID is a method of identifying the physical location of a row in an Oracle Database.  An Index on a Table captures the ROWIDs for the rows holding the index key values and these entries in the Index are how an Index lookup redirects a query to the row (i.e. physical location) in the table.

A ROWID (called an Extended ROWID) consists of 4 components :

    -    DataObject Number

    -    DataFile Number Relative to the Tablespace

    -    DataBlock Number (within the DataFile)

    -    RowNumber within the DataBlock


A Partitioned Table actually consists of multiple segments.  Each segment has a different DataObject Number.

Here is a quick demo of the difference between a normal (Non-Partitioned) Table and a Partitioned Table :


The Normal Table :



SQL> create table NONPARTITIONED (id_col number, data_col varchar2(1000)) pctfree 99 tablespace HEMANT_DATA;

Table created.

SQL>
SQL> insert into NONPARTITIONED
2 values (1, dbms_random.string('X',999));

1 row created.

SQL> insert into NONPARTITIONED
2 values (2,'This is the second row');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select rowid, id_col, substr(data_col,1,24)
2 from NONPARTITIONED
3 order by id_col
4 /

ROWID ID_COL SUBSTR(DATA_COL,1,24)
------------------ ---------- ------------------------------------------------------------------------------------------------
AAAT70AAgAAAACTAAA 1 CBXBRIP5ZNQ9VPZNC4HHVJJH
AAAT70AAgAAAACXAAA 2 This is the second row

SQL>
SQL> l
1 select id_col,
2 dbms_rowid.rowid_object(rowid) ObjectNumber,
3 dbms_rowid.rowid_relative_fno(rowid) RelativeFileNumber,
4 dbms_rowid.rowid_block_number(rowid) BlockNumber
5 from NONPARTITIONED
6* order by id_col
SQL> /

ID_COL OBJECTNUMBER RELATIVEFILENUMBER BLOCKNUMBER
---------- ------------ ------------------ -----------
1 81652 32 147
2 81652 32 151

SQL>


Because I created the Table with PCTFREE 99 and inserted a long string in the first row, the second row was created in a different block.  Both Blocks are in the same Relative File Number (32) and belong to the same Object (ObjectNumber 81652).  Is this really the Object ID ?



The Partitioned Table :


SQL> l
1 create table PARTITIONED (id_col number, data_col varchar2(1000))
2 partition by range (id_col)
3 (
4 partition P_1 values less than (2) tablespace HEMANT_DATA,
5 partition P_2 values less than (3) tablespace HEMANT_DATA,
6 partition P_3 values less than (4) tablespace HEMANT_DATA,
7 partition P_MAX values less than (MAXVALUE) tablespace HEMANT_DATA
8 )
9* tablespace HEMANT_DATA
SQL> /

Table created.

SQL>
SQL> insert into PARTITIONED
2 values (1, dbms_random.string('X',999));

1 row created.

SQL> insert into PARTITIONED
2 values (2,'This is the second row');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> select rowid, id_col, substr(data_col,1,24)
2 from PARTITIONED
3 order by id_col
4 /

ROWID ID_COL SUBSTR(DATA_COL,1,24)
------------------ ---------- ------------------------------------------------------------------------------------------------
AAAT77AAfAAAAJ3AAA 1 RFU3DNMCD6GXL2ZNV9DDGBG2
AAAT78AAfAAAAZ3AAA 2 This is the second row

SQL>
SQL> l
1 select id_col,
2 dbms_rowid.rowid_object(rowid) ObjectNumber,
3 dbms_rowid.rowid_relative_fno(rowid) RelativeFileNumber,
4 dbms_rowid.rowid_block_number(rowid) BlockNumber
5 from PARTITIONED
6* order by id_col
SQL> /

ID_COL OBJECTNUMBER RELATIVEFILENUMBER BLOCKNUMBER
---------- ------------ ------------------ -----------
1 81659 31 631
2 81660 31 1655

SQL>


In this case, the two rows are in different Blocks not because of the PCTFREE (which has defaulted to 10) but because they are in different Segments -- as you can see from the ObjectNumbers being different for the two rows.
(You might have also noticed that these were created in a separate datafile, FILENUMBER 31 instead of 32 {as was for the first table}, but that is because Oracle tries to allocate new segments across different datafiles)

In the ROWID format the ObjectNumber is actually the *Data Object Number* that identifies the  Segment, not the Object Number of the Table.

Thus, to verify the Segments of the two tables, I can query and check :


SQL> l
1 select object_name, subobject_name, object_type, object_id, data_object_id
2 from user_objects
3 where object_name in ('NONPARTITIONED','PARTITIONED')
4 and object_type in ('TABLE','TABLE PARTITION')
5* order by 1, 2 nulls first, 4
SQL> /

OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
---------------- ---------------- ----------------------- ---------- --------------
NONPARTITIONED TABLE 81652 81652
PARTITIONED TABLE 81658
PARTITIONED P_1 TABLE PARTITION 81659 81659
PARTITIONED P_2 TABLE PARTITION 81660 81660
PARTITIONED P_3 TABLE PARTITION 81661 81661
PARTITIONED P_MAX TABLE PARTITION 81662 81662

6 rows selected.

SQL>



Thus, for the NONPARTITIONED Table, the Object_ID and Data_Object_ID and that returned by DBMS_ROWID are all the same -- 81652.  
But the logical entry for the PARTITIONED Table has an Object_ID of 81658 but, without any segment and, therefore, without a Data_Object_ID.
The rows in this Partitioned Table are actually created in the two different Partition Segments with the corresponding Data_Object_ID  (81659 and 81660).



We know that when we rebuild a Table, the ROWID changes.  But this is actually because a new Segment is allocated.  

Thus, if I were to do a MOVE of the "Normal" Table :



SQL> alter table NONPARTITIONED move;

Table altered.

SQL>
SQL> l
1 select id_col,
2 dbms_rowid.rowid_object(rowid) ObjectNumber,
3 dbms_rowid.rowid_relative_fno(rowid) RelativeFileNumber,
4 dbms_rowid.rowid_block_number(rowid) BlockNumber
5 from NONPARTITIONED
6* order by id_col
SQL> /

ID_COL OBJECTNUMBER RELATIVEFILENUMBER BLOCKNUMBER
---------- ------------ ------------------ -----------
1 81663 32 155
2 81663 32 156

SQL>
SQL> l
1 select object_name, subobject_name, object_type, object_id, data_object_id
2 from user_objects
3 where object_name in ('NONPARTITIONED','PARTITIONED')
4 and object_type in ('TABLE','TABLE PARTITION')
5* order by 1, 2 nulls first, 4
SQL> /

OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE OBJECT_ID DATA_OBJECT_ID
---------------- ---------------- ----------------------- ---------- --------------
NONPARTITIONED TABLE 81652 81663
PARTITIONED TABLE 81658
PARTITIONED P_1 TABLE PARTITION 81659 81659
PARTITIONED P_2 TABLE PARTITION 81660 81660
PARTITIONED P_3 TABLE PARTITION 81661 81661
PARTITIONED P_MAX TABLE PARTITION 81662 81662

6 rows selected.

SQL>


Executing a MOVE of the Non-Partitioned Table resulted in a change of the *Data Object Number* (i.e DATA_OBJECT_ID) (from 81652 to 81663) without changing the OBJECT_ID.



For a couple of more interesting aspects of ROWIDs, see this YouTube video "Think you know how the ROWID works? Think again!" by Connor McDonald




Categories: DBA Blogs

The old old coolness - HTMX | Execute HTTP request directly from HTML tag

Andrejus Baranovski - Sat, 2022-08-06 13:47
Developing a modern Web app with Django and Tailwind? Thinking of using React, Vue, or another fancy JS library for UI implementation? I would recommend spending some time and trying HTMX. With HTMX you can execute partial requests and return HTML content to replace web page regions. HTMX properties are defined directly on HTML tags, this makes it easy to use and flexible.

 

パティシエになるために専門学校に進学するなら

The Feature - Fri, 2022-08-05 20:53

パティシエは人気のある職業です。そのため、パティシエを養成する専門学校も数多くあります。数ある学校の中から自分にあったものを見つけるにはどうしたらいいのでしょうか。まずは講師陣を調べてみる方法があります。

自分が目指したいパティシエ像がはっきりしている人におすすめの方法です。一口にお菓子といってもタルト、スポンジ系、ムース系とさまざまなので、その中で何を得意としてやっていきたいのかがわかれば、それを得意とする講師がいるかどうかがカギとなります。パンフレット類では有名な人が名義貸しで講演会程度でしか授業に関わっていなくても掲載されていることがあります。どの専門学校でもオープンキャンパスが行われているので、そういったもので確かめてみるといいでしょう。

次に実習先から選ぶ方法があります。学校内の授業だけでなく、インターンシップを取り入れているかどうか、調べてみましょう。これもオープンキャンパスで在校生から情報を得るとよりわかりやすいでしょう。具体的にパティシエ像をもっておらず、なんとなく意識している人については、専門学校で学べるプラスアルファの部分をよく見るとよいでしょう。

今やどの専門学校も競争なので「うちの学校の特色はこれだ」というものを打ち出しているところが多いです。職人である前に社会人として必要な知識を身につけるビジネスマナーの授業を取り入れているところもあります。また、お菓子に関連してカフェの勉強ができるところもあります。カリキュラムでこういう情報は手に入れられます。

しっかりと見極め、自分にあった学校を選んでください。

Categories: APPS Blogs

パティシエになるために専門学校に進学するなら

Marian Crkon - Fri, 2022-08-05 20:53
パティシエは人気のある職業です。そのため、パティシエを養成する専門学校も数多くあります。数ある学校の中から自分...

Indexes with or without TRIM

Tom Kyte - Fri, 2022-08-05 15:46
If I have an index, for example, by CD_CLIENT, CD_COMMOD, CD_MERCAD, and there is a query that uses them with TRIM. So: ....... <code>WHERE TRIM( CUSTOMER_CD ) = ... AND TRIM(CD_COMMOD) = ... AND TRIM(CD_MARKET) = ...</code> Should I make another index that has the TRIM ? Or is that enough? Thank you very much!!
Categories: DBA Blogs

Your suggestions on OLAP technology

Tom Kyte - Fri, 2022-08-05 15:46
We are evaluating OLAP technology for implementing some analytics for our product. We are reviewing technologies from Oracle, Microsoft and other vendors. Correct my if I am wrong, OLAP technology with Oracle looks like an early stage feature. I got this assumption when comparing the features, ease of use etc with other vendors. Even Microsoft also well in advance in that area. I couldn?t see much customer references for Oracle OLAP. Please comment on the following issue as you have more expertise with the different technologies and industry? What are your suggestions on choosing OLAP technology? What are your suggestions on Oracle OLAP? Should we consider it? If yes, why?
Categories: DBA Blogs

NVARCHAR2 > 2000 - MAX_STRING_SIZE

Tom Kyte - Fri, 2022-08-05 15:46
Hello, I inherited from a schema that is using nvarchar2. I am trying to recreate that schema but I am struggling to create table with nvarchar2 that have a size of 4000 (> 2000). I have changed the max_string_size to "extended" but it does not works. I works varchar2 with e.g. 32676 bytes. Here are the operation I made. <code> sqlplus / as sysdba ALTER SESSION SET CONTAINER=CDB$ROOT; ALTER SYSTEM SET max_string_size=extended SCOPE=SPFILE; shutdown immediate; startup upgrade; ALTER PLUGGABLE DATABASE ALL OPEN UPGRADE; EXIT; cd $ORACLE_HOME/rdbms/admin mkdir -p /home/oracle/tmp/log_utl32k $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS --force_pdb_mode 'UPGRADE' -d $ORACLE_HOME/rdbms/admin -l '/home/oracle/tmp/log_utl32k' -b utl32k_cdb_pdbs_output utl32k.sql sqlplus / as sysdba shutdown immediate; startup; ALTER PLUGGABLE DATABASE ALL OPEN READ WRITE; EXIT; mkdir -p /home/oracle/tmp/utlrp $ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -u SYS --force_pdb_mode 'READ WRITE' -d $ORACLE_HOME/rdbms/admin -l '/home/oracle/tmp/utlrp' -b utlrp_cdb_pdbs_output utlrp.sql sqlplus / as sysdba create table testvc (name varchar2(32676)); >>Table created. create table testnvc (name nvarchar2(4000)); >>ORA-00910: specified length too long for its datatype </code> I have checked several resources but I cannot find a solution. Any suggestion are welcomed.
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator