DBA Blogs

Alter Table

Tom Kyte - Mon, 2018-10-01 02:46
Hi Tom , i want ALTER TABLE by adding columns to it , if the columns already exist int the table then it doesn't add , else adds the columns . how i can do that ? Regards, Adil
Categories: DBA Blogs

Partitioning -- 6 : Hash Partitioning

Hemant K Chitale - Sun, 2018-09-30 06:25
Unlike Range or List Partitioning where you define the rule which identifies which Partition a row will be inserted into (based on the value in the Partition Key Column(s)),  Hash Partitioning relies on Oracle applying a "hashing formula (algorithm)" to "randomly" distribute incoming rows across the available Partitions in the table.
This would be useful when you want to break up a table into smaller physical segments (maybe into even separate Tablespaces on different disks) without considering grouping of data.  In Date based Range Partitioning data is grouped into different Partitions -- i.e. physical segments on disk --  based on the Date value (e.g. by Month or Year).  In List Partitioning, data is grouped based on the value in the Partition Key Column.

Here is a small example on Hash Partitioning :

SQL> create table iot_incoming_data
2 (data_item_number number,
3 data_item_key varchar2(32),
4 data_item_value varchar2(64),
5 data_item_timestamp timestamp)
6 partition by hash (data_item_number)
7 (partition p1 tablespace hash_ptn_1,
8 partition p2 tablespace hash_ptn_2,
9 partition p3 tablespace hash_ptn_3,
10 partition p4 tablespace hash_ptn_4)
11 /

Table created.

SQL>


In this definition of the table, I have "randomly" distributed incoming rows across 4 Partitions in 4 different Tablespaces.  Given the incoming "data_item_number" values (either machine generated or from a sequence), each of the 4 Partitions would be equally loaded.
(In contrast, in Date based Range Partitioning of, say, a SALES table, you might have fewer rows in older Partitions and an increasing number of rows in new Partitions as your business and Sales Volume grow over time !).

Unlike Range Partitioning, Hash Partitioning will not perform well for a "range based query"  (e.g. a range of sales dates or a range of data item numbers).  It is suitable for "equality" or "in-list" predicates.  If you do need a range based query, you would need a Global Index.

Note that it is advised that you should use a Power of 2 for the number of Hash Partitions.

Let me demonstrate insertion of data :

SQL> insert into iot_incoming_data
2 select rownum,
3 dbms_random.string('X',16),
4 dbms_random.string('X',32),
5 systimestamp
6 from dual
7 connect by level < 10001;

10000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> exec dbms_stats.gather_table_stats('HEMANT','IOT_INCOMING_DATA');

PL/SQL procedure successfully completed.

SQL> select partition_name, num_rows
2 from user_tab_partitions
3 where table_name = 'IOT_INCOMING_DATA'
4 order by partition_position
5 /

PARTITION_NAME NUM_ROWS
------------------------------ ----------
P1 2471
P2 2527
P3 2521
P4 2481

SQL>


Note that I have inserted the 10,000 rows from a single session.  In the real world, you would have multiple sessions concurrently inserting rows into the table.
Based on the Hashing algorithm that Oracle used (note : this is internal to Oracle and we cannot use any custom algorithm), Oracle has more or less evenly distributed the incoming rows across the 4 Partitions.

Let me select some random rows from the Partitions :

SQL> select data_item_number  
2 from iot_incoming_data partition (P1)
3 where rownum < 6
4 order by 1;

DATA_ITEM_NUMBER
----------------
8361
8362
8369
8379
8380

SQL>
SQL> select data_item_number
2 from iot_incoming_data partition (P2)
3 where rownum < 6
4 order by 1
5 /

DATA_ITEM_NUMBER
----------------
8087
8099
8101
8105
8109

SQL>
SQL> select data_item_number
2 from iot_incoming_data partition (P3)
3 where rownum < 6
4 and data_item_number < 100
5 order by 1
6 /

DATA_ITEM_NUMBER
----------------
2
5
8
18
20

SQL>
SQL> select data_item_number
2 from iot_incoming_data partition (P4)
3 where rownum < 6
4 and data_item_number between 1000 and 1100
5 order by 1
6 /

DATA_ITEM_NUMBER
----------------
1001
1002
1005
1008
1009

SQL>


(The first two queries returned rows with values greater than 8000 simply because I didn't specify a range of values as a filter and those rows came from the first few blocks that Oracle read from the buffer cache).
Note how the DATA_ITEM_NUMBER values indicate "near-random" distribution of rows across the Partitions.  It is likely that if I had created multiple sessions concurrently running inserts into the table, distribution of the rows would have been even more "random".



Categories: DBA Blogs

Insertion over db links creating extra rows than expected

Tom Kyte - Fri, 2018-09-28 19:46
Hi Tom, iam facing a weird issue . below is the scenario, My package creates insert statements for 4 tables which lie on someother oracle 11g db. tab1 tab2 tab3 tab4 The same 4 tables exists in 7 servers. (admin and cus1 to cu6). ...
Categories: DBA Blogs

HOW TO RESOLVE THE ERROR :- ora-01406 fetched column value was truncated

Tom Kyte - Fri, 2018-09-28 01:26
HOW TO RESOLVE THE ERROR :- ora-01406 fetched column value was truncated
Categories: DBA Blogs

Outer join with row archival

Tom Kyte - Fri, 2018-09-28 01:26
LiveSQL-Link: https://livesql.oracle.com/apex/livesql/s/hblhxmq40jtini45sivyqj4le <code> create table test_table (id number(10),name varchar2(10)) row archival; insert into test_table (id,name) values (1,'name1'); insert into test_table (id...
Categories: DBA Blogs

Find Closest Matching Single Record

Tom Kyte - Fri, 2018-09-28 01:26
I want a Query that should fetch a single record based on match conditions: table looks like <code> create table SERVICES ( srvc VARCHAR2(10) not null, location VARCHAR2(10), grp VARCHAR2(10), empno VARCHAR2(10), pric...
Categories: DBA Blogs

Renaming a RAC cluster

DBA Scripts and Articles - Thu, 2018-09-27 09:34

Introduction Renaming an Oracle RAC cluster is not an easy thing, unfortunately for me I had to do this today because the name chosen for the newly installed cluster was wrong. Oracle does not provide a simple command to do this and you have to go through a deconfiguration/reconfiguration of the whole cluster. Changing the … Continue reading Renaming a RAC cluster

The post Renaming a RAC cluster appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

create JSON from fields

Tom Kyte - Thu, 2018-09-27 07:06
Hi, I have relation 'resources' in database with 2 fields, 'id' and 'data'. 'id' eg. 25 and 'data' eg. <code>{"href":null,"id":"25","publicIdentifier":null,"description":null,"category":null,"validFor":null,"name":null,"lifecycleState":null,"type":"R...
Categories: DBA Blogs

DBMS_JOB.SUBMIT doesn't do anything in Oracle 12c (12.2.0.1.0)

Tom Kyte - Thu, 2018-09-27 07:06
Hi guys, I have a problem when I try to start a stored procedure with DBMS_JOB.SUBMIT. I have the package "dafneMultithread": <code> CREATE OR REPLACE PACKAGE DAFNE.dafneMultithread AS PROCEDURE start_job(p_procedure_name IN VARCHAR2...
Categories: DBA Blogs

Query all tables and all columns for a specific value

Tom Kyte - Thu, 2018-09-27 07:06
Hi Oracle Masters, I wonder if oracle is capable of returning the table name and column name based on a specific value only. e.g. Which table and column in oracle DB that has a value of 'ORACLE'? This is mainly for determining the mapping of th...
Categories: DBA Blogs

LISTAGG .. WITHIN GROUP (ORDER BY ..) is ignored in UPDATE .. RETURNING lause

Tom Kyte - Thu, 2018-09-27 07:06
Consider this script (which I've also put on Live SQL: https://livesql.oracle.com/apex/livesql/s/hbfb7x0c47ubwjpygs3klr3wj) <code>CREATE TABLE t ( id NUMBER(10) GENERATED ALWAYS AS IDENTITY NOT NULL PRIMARY KEY, category NUMBER(10) NOT NUL...
Categories: DBA Blogs

How do I find out if a particular trigger is executing or is being internally ignored from execution inn Oracle 11gR2?

Tom Kyte - Thu, 2018-09-27 07:06
Hi Tom, I am a frequent reader of AskTom. Your in-depth and precose answers are always great. I would like to know if there is any SQL (based on Data Dictionary, v$ or x$ views) that I can use to find out id a particular db trigger is executin...
Categories: DBA Blogs

Looping JSON array in FOR loop

Tom Kyte - Wed, 2018-09-26 12:46
Hello, My requirement is to perform various actions once I fetch the 'id' JSON element from the results JSON. Below JSON is generated from a third-party vendor for which I don't have a control to change the structure. The issue is, for some reason...
Categories: DBA Blogs

Statspack "SQL ordered by Elapsed" and ORA-1555

Tom Kyte - Wed, 2018-09-26 12:46
I got this in the alert log of a database: <code> Wed Aug 15 13:21:29 CEST 2018 ORA-01555 caused by SQL statement below (SQL ID: 4dhx1332z74nf, Query Duration=94491 sec, SCN: 0x000c.cdffd21c): Wed Aug 15 13:21:29 CEST 2018 SELECT /*+ FIRST_ROW...
Categories: DBA Blogs

Seeing dbms_output buffer from another session?

Tom Kyte - Wed, 2018-09-26 12:46
Hi, I have a sqlplus session that is hanging and I want to see what dbms_output is in its buffer. If I kill the session, sql*plus will never print the serveroutput, and I will lose whatever was in the dbms_output buffer. Is there some way I can s...
Categories: DBA Blogs

gc buffer busy acquire ion RAC on stress load

Tom Kyte - Wed, 2018-09-26 12:46
Hi, we have Oracle 12c (12.1.0.1 SE) RAC on 2-node Windows 2012 R2 OS. I am testing simple performance test using jmeter which starts 500 concurrent users ant each of then inserts a one row in table 1000 times. Jmeter makes a commit after each in...
Categories: DBA Blogs

Stop/Start all RAC databases at once

DBA Scripts and Articles - Wed, 2018-09-26 12:33

Introduction Stopping all RAC databases running on an ORACLE_HOME at once and saving the state before shutting them down is really helpfull when you are doing patching on a server. In my case I have databases running on some nodes and not the others, it’s getting complicated to keep track of which database is running … Continue reading Stop/Start all RAC databases at once

The post Stop/Start all RAC databases at once appeared first on Oracle DBA Scripts and Articles (Montreal).

Categories: DBA Blogs

Run class Java in Oracle from other databases

Tom Kyte - Tue, 2018-09-25 18:26
Hello Is it possible to create a Java class using JDBC and run a job, I have seen that it has how to execute Java classes in SQL Developer, I would like it to work in Oracle itself, not in SQL Developer
Categories: DBA Blogs

Overlapping of dates

Tom Kyte - Tue, 2018-09-25 18:26
I have two scenarios explained below: 1. Suppose say, I have an organization with start date as 01/JAN/2017 and end date as 31/DEC/2017. The speciality of my organization has the start date as 01/OCT/2017 and end date as 10/OCT/2018. So when the qu...
Categories: DBA Blogs

Simulation of long time for query parsing

Tom Kyte - Tue, 2018-09-25 00:06
Hi Tom, How can I force a long query parsing time for Oracle or how to prepare such a complicated query? I want to simulate a situation where Oracle parses a query for 30 seconds before execution. Within 30 seconds I want to modify the synonym ...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs