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

Function to gather the stats on the latest partition of a table

Tom Kyte - Fri, 2018-11-09 18:06
I have a function - ANALYZE_TABLE as below <code>create or replace FUNCTION ANALYZE_TABLE (P_TAB_NAME IN VARCHAR2) RETURN NUMBER IS PRAGMA AUTONOMOUS_TRANSACTION; BEGIN DBMS_STATS.GATHER_TABLE_STATS(USER,P_TAB_NAME); RETURN 0; END;</cod...
Categories: DBA Blogs

Not exist without subquery

Tom Kyte - Fri, 2018-11-09 18:06
I have 4 tables <code>table item_values (item_key, item_value) table filter_model (filter_key) table item_includes (include_key, filter_key_fk, item_key, include_value) table item_excludes (exclude_key, filter_key_fk, item_k...
Categories: DBA Blogs

sum() at each level in hierarchial query

Tom Kyte - Fri, 2018-11-09 18:06
consider the following query select level,sys_connect_by_path(last_name,'\') name,salary from employees start with last_name='King' connect by prior employee_id=manager_id is it possible to get the result like name sum_of_all_l...
Categories: DBA Blogs

Date in comparison with string

Tom Kyte - Fri, 2018-11-09 18:06
Hi Tom, I have a table named RTBS_SUBS_EVENT and there is a date column named expiry_date. expiry_date is a partitioned column too. a column named ID and it's indexed. NLS_DATE_FORMAT of the database is 'DD-MON-RR'. I run this query and th...
Categories: DBA Blogs

Join elimination

Tom Kyte - Fri, 2018-11-09 18:06
Hello All, While making some tests using LiveSQL, which is Oracle 18c, I found a strange behavior regarding join elimination. That is, join elimination DOES happen even if the constraints are DEFERRED ! My LiveSQL test script can be found he...
Categories: DBA Blogs

Is there any point backing up flashback 'flb' files?

Tom Kyte - Fri, 2018-11-09 18:06
Hi, Is there any point/benefit in backing up flb files to tape using an OS utility? Or, to put it another way: would backing up flb files just be a waste of tape? Thanks
Categories: DBA Blogs

Sorting -- sort a string like a number.

Tom Kyte - Thu, 2018-11-08 23:46
I have a alphanumeric column in the database. It contains only numeric values.Is it possible to do a numeric sort directly by a sql command like "order by <column>"
Categories: DBA Blogs

Blocks read using index vs. full table scan

Tom Kyte - Thu, 2018-11-08 23:46
Hi Tom, I have a 3 part question about performance... 1) The first question is sort of a introductory summary question. 2) Then I have an example ending with a question. 3) Finaly I have a very specific 'important' question. Why is it some...
Categories: DBA Blogs

Do we know for sure from the trace file, that the application uses transactions on the database?

Tom Kyte - Thu, 2018-11-08 05:26
We got deadlock. In the trace file there is the deadlock graph: <code> Deadlock graph: ---------Blocker(s)-------- ---------Waiter(s)--------- Resource Name process session holds waits process session holds wai...
Categories: DBA Blogs

Using REGEXP to find ten consecutive digits with possible separators

Tom Kyte - Thu, 2018-11-08 05:26
I HAVE THIS FREE FLOWING DATA FIELD IN A VARCHAR2 COLUMN THAT I AM CLEANING UP. I WOULD LIKE TO GET THOSE VALUES THAT HAVE A TOTAL OF 10 DIGITS IN ALL AND MIGHT CONTAIN THESE CHARACTERS : . OR - OR / OR ( ) CREATE TABLE E ( PHONE VARCHAR2(5...
Categories: DBA Blogs

Oracle Sequence vs GUID

Tom Kyte - Thu, 2018-11-08 05:26
We're developing a new system in for the company with .NET and Oracle Database 12cR2 using RAC. It basically does the following: receives invoices in XML format, then validates it according to business rules. A requirement is to give to the clien...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs