Oracle SQL
  • LICENSE

Monthly Archives: March 2018

Create External Table as Select

Posted on March 9, 2018 by Roger MacNicol Posted in curious, oracle, SmartScan Leave a comment

I was looking through a test script and saw something I didn’t know you could do in Oracle. I mentioned it to an Oracle ACE and he didn’t know it either. I then said to the External Table engineers “Oh I see you’ve added this cool new feature” and he replied dryly – “Yes, we added it in Oracle 10.1”. Ouch! So just in case you also didn’t know, you can create an External Table using a CTAS and the ORACLE_DATAPUMP driver.

This feature only work with the ORACLE_DATAPUMP access driver (it does NOT work with with the LOADER, HIVE, or HDFS drivers) and we can use it like this:

SQL> create table cet_test organization external
  2  (
  3    type ORACLE_DATAPUMP
  4    default directory T_WORK
  5    location ('xt_test01.dmp','xt_test02.dmp')
  6  ) parallel 2
  7  as select * from lineitem
 
Table created.

Checking the results shows us

-rw-rw---- ... 786554880 Mar 9 10:48 xt_test01.dmp 
-rw-rw---- ... 760041472 Mar 9 10:48 xt_test02.dmp

This can be a great way of creating a (redacted) sample of data to give to a developer to test or for a bug repro to give to support or to move between systems. 

oracle oracle undocumented behaviour Roger MacNicol SmartScan undocumented oracle

Understanding External Table URowids

Posted on March 8, 2018 by Roger MacNicol Posted in External tables, oracle, SmartScan Leave a comment

I finally found time to get back to External Tables and have a list of blog posts I need to write on this topic. Here’s a brief one.

DBMS_ROWID will nicely break down a heap table’s rowid for you into file number, block number, and row number but it doesn’t handle the rowids coming from External Tables. So let’s look at how to make sense of them. They fall under the datatype UROWID which is a nominally opaque rowid defined by the data source. The first byte of a UROWID tells you which data source it came from and consequently how to deconstruct it.

The easiest way to see what is happening is via the SQL Dump function:

SQL> column xtrowid format a55
SQL> select c_custkey, dump(rowid,16) "XTROWID" from c_et 
  2> where c_custkey < 10;

 C_CUSTKEY XTROWID
---------- -------------------------------------------------------
         1 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,0,1
         2 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,0,2
         3 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,0,3
         4 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,0,4
         5 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,0,5
         6 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,0,6
         7 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,0,7
         8 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,0,8
         9 Typ=208 Len=17: 4,0,1,27,a2,0,0,0,0,0,0,0,0,0,0,0,9

9 rows selected.
Continue reading→
external tables oracle Roger MacNicol SmartScan
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL