DBA Blogs

Above & Beyond for deploying Oracle Database / Weblogic As Docker Container On Oracle ...

As Oracle Public Cloud computing continues to transform every business and industry, developers at global enterprises and emerging startups alike are increasingly leveraging container technologies to...

We share our skills to maximize your revenue!
Categories: DBA Blogs

March 2019 – New Webinar Dates Announced for “Oracle Indexing Internals and Best Practices”

Richard Foote - Tue, 2018-11-13 22:19
I’m very excited to announce two new Webinar events for my acclaimed “Oracle Indexing Internals and Best Practices” training event, running in March 2019 !! For details of all the extensive content covered in the webinars, please visit my Indexing Seminar page. The webinars will run for 4 hours each day, spanning a full week period (Monday […]
Categories: DBA Blogs

Impact of Altering an Oracle sequence from ORDER to NO ORDER

Tom Kyte - Tue, 2018-11-13 13:46
I have a table TAX_INFO where the primary key TAX_INFO_ID is generated using a sequence SEQ_TAX_INFO_ID. Below is the sequence definition. It is defined as ORDERED at the moment <code>CREATE SEQUENCE SEQ_TAX_INFO_ID MINVALUE 1 MAXVALUE 999999999...
Categories: DBA Blogs

Manually refresh materialized view in trigger

Tom Kyte - Tue, 2018-11-13 13:46
Hello, I have a set of MV that are dependant each other and from master tables. All are elligible to be fast-refreshed but for a reason I don't know, in some tables after a delete or update the fast refresh is longer than the complete one. So ...
Categories: DBA Blogs

pragma autonomous in exception

Tom Kyte - Tue, 2018-11-13 13:46
can you create pragma autonomous in exception handling
Categories: DBA Blogs

Database security

Tom Kyte - Tue, 2018-11-13 13:46
Hello, i'd like a suggestion about the use case below We have a database with 2 schemas, for the schemas 1 and 2 every objects are full granted <code>grant all on "object_name" to public</code> Each users have default role <code>"Resourc...
Categories: DBA Blogs

Partitioning -- 9 : System Partitioning

Hemant K Chitale - Tue, 2018-11-13 08:59
System Partitioning, introduced in 11g, unlike all the traditional Partitioning methods, requires that all DML specify the Target Partition.  For a System Partitioned Table, the RDBMS does not use a "high value" rule to determine the Target Partition but leaves it to (actually requires) the application code (user) to specify the Partition.

In my opinion, this seems like the precursor to Oracle Database Sharding.

SQL> create table sys_part_table
2 (id_column number,
3 data_element_1 varchar2(50),
4 data_element_2 varchar2(50),
5 entry_date date)
6 partition by SYSTEM
7 (partition PART_A tablespace PART_TBS_A,
8 partition PART_B tablespace PART_TBS_B,
9 partition PART_C tablespace PART_TBS_C)
10 /

Table created.

SQL>


Notice that I did not specify a Partition Key (column).  The Partitions are not mapped to specific values / range of values in a Key column.

Any DML must specify the Target Partition.

SQL> insert into sys_part_table
2 values (1, 'First Row','A New Beginning',sysdate)
3 /
insert into sys_part_table
*
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on
tables partitioned by the System method


SQL>
SQL> !oerr ora 14701
14701, 00000, "partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method"
// *Cause: User attempted not to use partition-extended syntax
// for a table partitioned by the System method
// *Action: Must use of partition-extended syntax in contexts mentioned above.

SQL>
SQL> insert into sys_part_table partition (PART_A)
2 values (1, 'First Row','A New Beginning',sysdate)
3 /

1 row created.

SQL> insert into sys_part_table partition (PART_B)
2 values (2,'Second Row','And So It Continues',sysdate)
3 /

1 row created.

SQL>


I have to specify the Target Partition for my INSERT statement. This, obviously, also applies to DELETE and UPDATE statements.   However, I can run a SELECT statement without filtering (pruning) to any Target Partition(s) -- i.e. a SELECT statement that does not use the PARTITION clause will span across all the Partitions.

SQL> select * from sys_part_table;

ID_COLUMN DATA_ELEMENT_1
---------- --------------------------------------------------
DATA_ELEMENT_2 ENTRY_DAT
-------------------------------------------------- ---------
1 First Row
A New Beginning 13-NOV-18

2 Second Row
And So It Continues 13-NOV-18


SQL>


With Tablespaces assigned to the Partitions (see the CREATE table statement above),  I  can have each Partition mapped to a different underlying Disk / Disk Group.
.
.
.
Categories: DBA Blogs

Strange behaviour with excecute immediate.

Tom Kyte - Mon, 2018-11-12 19:26
Hi, Had problems with SQLLive, (500 response), therefore the examples are her. I have a strange behaviour with execute immediate, where it behaves differently from within a PL/SQL procedure than it does when running it standalone. Here is th...
Categories: DBA Blogs

Difference between named sequence and system auto-generated

Tom Kyte - Mon, 2018-11-12 19:26
Hello, Guys. A new db (12c) will have lots of tables with sequence used as PK. What is the difference between named sequence and system auto-generated in Oracle 12c? What would be the best approach?
Categories: DBA Blogs

Row Chaining

Tom Kyte - Mon, 2018-11-12 19:26
Hi Tom What is row chaining/migration ? what are the consequences of Row Chaining/Migration ? How I can find whether it is there in my database or not ? and if it is there what is the solution to get rid from it? Thanks in advance Howie
Categories: DBA Blogs

Run procedures or functions in parallel

Tom Kyte - Mon, 2018-11-12 19:26
hello, I've web app which is using procedures and functions inside packages most of the time, in some cases procedures and functions execution taking long time to return data (as SYS_REFCURSOR). the problem is that when other users execute other p...
Categories: DBA Blogs

Export Tables from Oracle-12c to Oracle-10g

Tom Kyte - Mon, 2018-11-12 19:26
Why the following table is not being Exported from Oracle-12c to Oracle-10g Table : <code>create table stock(ModID varchar(20) primary key, Name varchar(30), Type varchar(15) ,mQty number, cmpID number, price number, Warranty number);</code> ...
Categories: DBA Blogs

Counting specific days between to two dates

Tom Kyte - Mon, 2018-11-12 19:26
Hi Tom, ? have a case that i need to count specific days between two dates. For example i have a table that contains contract startdate, enddate and specific date like 15. 15 means every 15th day of months. i need to count specific dates. for exa...
Categories: DBA Blogs

User_dump_dest is inconsistent with the actual trace path

Tom Kyte - Mon, 2018-11-12 19:26
If my question is too simple or meaningless, you can ignore it. Why does my user_dump_dest parameter get a different path than the actual path? I run this example: <code>EODA@muphy>select c.value || '/' || d.instance_name || '_ora_' || a.spi...
Categories: DBA Blogs

copy partition table stats

Tom Kyte - Mon, 2018-11-12 19:26
Hi Team , as per the requirement from application team , we need to copy table stats from one table to other table . Both source and destination table are partition tables . here we tested out in local system below steps : 1. created dum...
Categories: DBA Blogs

Join like (1=1)

Tom Kyte - Mon, 2018-11-12 19:26
Hi All, I am sorry if this is pretty basic,but it is intriguing me a bit. I saw a join written like Inner Join table B on (1=1) Why join like this should be written and under what scenario.Thanks in advance.
Categories: DBA Blogs

Chart your SQL direct with Apache Zeppelin Notebook

Kubilay Çilkara - Mon, 2018-11-12 14:00
Do you want a notebook where you can write some SQL queries to a database and see the results either as a chart or table? 

As long as you can connect to the database with a username and have the JDBC driver, no need to transfer data into spreadsheets for analysis, just download (or docker) and use Apache Zeppelin notebook and chart your SQL directly! 


I was impressed by the ability of Apache Zeppelin notebook to chart SQL queries directly. To configure this open source tool and start charting your SQL queries just point it your database JDBC endpoint and then start writing some SQL in real time.

See below how simple this is, just provide your database credentials and you are ready to go.



The notebook besides JDBC to any database, in my case I used a hosted Oracle cloud account, can also handle interpreters like: angular, Cassandra, neo4j, Python, SAP and many others. 

You can download Apache Zeppelin and configure on localhost or you can run it on docker like this

 docker run -d -p 8080:8080 -p 8443:8443 -v $PWD/logs:/logs -v $PWD/notebook:/notebook  xemuliam/zeppelin 



Categories: DBA Blogs

Partitioning -- 8 : Reference Partitioning

Hemant K Chitale - Mon, 2018-11-12 08:43
Like Interval Partitioning, another enhancement in 11g is Reference Partitioning.

Reference Partitioning allows you to use a Referential Integrity Constraint to equi-partition a "Child" Table with a "Parent" Table.

Here is a quick demonstration :

SQL> l
1 create table orders
2 (order_id number primary key,
3 order_date date not null,
4 customer_id number)
5 partition by range (order_date)
6 (partition P_2017 values less than (to_date('01-JAN-2018','DD-MON-YYYY')),
7 partition P_2018 values less than (to_date('01-JAN-2019','DD-MON-YYYY'))
8* )
SQL> /

Table created.

SQL>
SQL> l
1 create table order_lines
2 (line_unique_id number primary key,
3 order_id number not null,
4 order_line_id number,
5 product_id number,
6 product_quantity number,
7 constraint order_lines_fk foreign key (order_id)
8 references orders(order_id)
9 )
10* partition by reference (order_lines_fk)
SQL> /

Table created.

SQL>
SQL> col high_value format a28 trunc
SQL> col table_name format a16
SQL> col partition_name format a8
SQL> select table_name, partition_name, high_value
2 from user_tab_partitions
3 where table_name in ('ORDERS','ORDER_LINES')
4 order by table_name, partition_position
5 /

TABLE_NAME PARTITIO HIGH_VALUE
---------------- -------- ----------------------------
ORDERS P_2017 TO_DATE(' 2018-01-01 00:00:0
ORDERS P_2018 TO_DATE(' 2019-01-01 00:00:0
ORDER_LINES P_2017
ORDER_LINES P_2018

SQL>


Notice the "automatically" created Partitions for the ORDER_LINES ("Child") Table that match those for the ORDERS ("Parent") Table.

.
.
.

Categories: DBA Blogs

Moving data across DB link when one database uses pass-through configuration

Tom Kyte - Sat, 2018-11-10 12:26
There is a source with the settings below: NLS_LANGUAGE AMERICAN NLS_TERRITORY CIS NLS_CURRENCY ?. NLS_ISO_CURRENCY CIS NLS_NUMERIC_CHARACTERS , NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD.MM.RR NLS_DATE_LANGUAGE AMERICAN NLS_CHARACTERSET W...
Categories: DBA Blogs

Oracle Sequence and EXPDP/Historical Data

Tom Kyte - Fri, 2018-11-09 18:06
Hello, Ask TOM Team. We are designing a new database and there are lots of tables with identity column. The sequence used by each table is a named sequence (we are not using the system-generated sequence). The default values of these tables is lik...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs