Feed aggregator

need to access hana columnar table data in Oracle 11g through remote source

Tom Kyte - Sun, 2016-11-27 19:46
what will be the performance impact, if I remote source hana tables to Oracle 11g for real-time data read and update some oracle master table. as Oracle is OLTP and hana is used as data warehouse. Can you pls suggest, the performance impact due to ...
Categories: DBA Blogs

Can I do it with PostgreSQL? – 4 – External tables

Yann Neuhaus - Sun, 2016-11-27 12:59

In the last posts of this series we talked about restore points, how you could do things that would require the dual table in Oracle and how you can make use of tablespaces in PostgreSQL. In this post we’ll look at what my colleague Clemens thinks is one of the greatest features in Oracle. Can you do external external tables in PostgreSQL?

The easy answers is: yes, of course you can. And you can do it in various ways. To start with we’ll need a sample file were we can load data from. For the test here we’ll use this one. Note that this file uses Windows line feeds which you’ll need to convert to unix style if you are working on Linux like me. You can use VI to do this.

Once you extracted the file the content looks like this:

postgres@pgbox:/home/postgres/ [PG961] head -2 FL_insurance_sample.csv
policyID,statecode,county,eq_site_limit,hu_site_limit,fl_site_limit,fr_site_limit,tiv_2011,tiv_2012,eq_site_deductible,hu_site_deductible,fl_site_deductible,fr_site_deductible,point_latitude,point_longitude,line,construction,point_granularity
119736,FL,CLAY COUNTY,498960,498960,498960,498960,498960,792148.9,0,9979.2,0,0,30.102261,-81.711777,Residential,Masonry,1

So, we have a total of 18 columns and 36634 rows to test with. Should be fine :)

How can we bring that into PostgreSQL? Clemens talked about SQL*Loader in his post. There is a similar project for PostgreSQL called pg_bulkload which we’ll not be talking about. We will look at two options you can use to load data from files into PostgreSQL which are available by default:

  1. copy
  2. file_fdw

What we need no matter with which option we go first is the definition of the table. These are the columns we need:

postgres@pgbox:/home/postgres/ [PG961] head -1 FL_insurance_sample.csv | sed 's/,/,\n/g'
policyID,
statecode,
county,
eq_site_limit,
hu_site_limit,
fl_site_limit,
fr_site_limit,
tiv_2011,
tiv_2012,
eq_site_deductible,
hu_site_deductible,
fl_site_deductible,
fr_site_deductible,
point_latitude,
point_longitude,
line,
construction,
point_granularity

So the create table statement will look something like this:

(postgres@[local]:5439) [postgres] > create table exttab ( policyID int,
                                                           statecode varchar(2),
                                                           county varchar(50),
                                                           eq_site_limit numeric,
                                                           hu_site_limit numeric,
                                                           fl_site_limit numeric,
                                                           fr_site_limit numeric,
                                                           tiv_2011 numeric,
                                                           tiv_2012 numeric,
                                                           eq_site_deductible numeric,
                                                           hu_site_deductible numeric,
                                                           fl_site_deductible numeric,
                                                           fr_site_deductible numeric,
                                                           point_latitude numeric,
                                                           point_longitude numeric,
                                                           line varchar(50),
                                                           construction varchar(50),
                                                           point_granularity int);
CREATE TABLE

Now that we have the table we can use copy to load the data:

(postgres@[local]:5439) [postgres] > copy exttab from '/home/postgres/FL_insurance_sample.csv' with csv header;
COPY 36634
(postgres@[local]:5439) [postgres] > select count(*) from exttab;
 count 
-------
 36634
(1 row)

Quite fast. But there is a downside with this approach. As Clemens mentions in his posts one of the benefits of external tables in Oracle is that you can access the file via standard SQL and do transformations before the data arrives in the database. Can you do the same with PostgreSQL? Yes, if you use the file_fdw foreign data wrapper.

The file_fdw is available by default:

(postgres@[local]:5439) [postgres] > create extension file_fdw;
CREATE EXTENSION
Time: 442.777 ms
(postgres@[local]:5439) [postgres] > \dx
                        List of installed extensions
   Name   | Version |   Schema   |                Description                
----------+---------+------------+-------------------------------------------
 file_fdw | 1.0     | public     | foreign-data wrapper for flat file access
 plpgsql  | 1.0     | pg_catalog | PL/pgSQL procedural language

(postgres@[local]:5439) [postgres] > create server srv_file_fdw foreign data wrapper file_fdw;
CREATE SERVER
(postgres@[local]:5439) [postgres] > create foreign table exttab2  ( policyID int,
                                statecode varchar(2),
                                county varchar(50),
                                eq_site_limit numeric,     
                                hu_site_limit numeric,     
                                fl_site_limit numeric,     
                                fr_site_limit numeric,     
                                tiv_2011 numeric,          
                                tiv_2012 numeric,          
                                eq_site_deductible numeric,
                                hu_site_deductible numeric,
                                fl_site_deductible numeric,
                                fr_site_deductible numeric,
                                point_latitude numeric,    
                                point_longitude numeric,   
                                line varchar(50),          
                                construction varchar(50),  
                                point_granularity int)     
server srv_file_fdw options ( filename '/home/postgres/FL_insurance_sample.csv', format 'csv', header 'true' );
CREATE FOREIGN TABLE

(postgres@[local]:5439) [postgres] > select count(*) from exttab2;
 count 
-------
 36634
(1 row)

From now on you can work with the file by accessing it using standard SQL and all the options you have with SQL are available. Very much the same as Clemens states in his post: “Because external tables can be accessed through SQL. You have all possibilities SQL-queries offer. Prallelism, difficult joins with internal or other external tables and of course all complex operations SQL allows. ETL became much easier using external tables, because it allowed to process data through SQL joins and filters already before it was loaded in the database.”

 

Cet article Can I do it with PostgreSQL? – 4 – External tables est apparu en premier sur Blog dbi services.

Exadata migration

Syed Jaffar - Sun, 2016-11-27 12:21
Had a wonderful Sangam16 conference in India, and received much applaud for the two presentations delivered,  Oracle 12c multitenancy and Exadata migration best practices.

After a very short trip to India, life started to be business as usual again, and become busy. Was fully occupied with multiple assignments: Oracle EBS database health check assessment at a client for 2 days, GI/RDBMS/PSU deployments on Oracle Sun Super Cluster M7, Exadata configuration preparation and 9 databases migration to Exadata during the week-end.

Over the last week-end, we (me and my colleague) were pretty busy with 9 databases migration to Exadata. There were a few challenges , and learned a few new things too. I would like to discuss couple of scenarios that were interesting:

One of the databases had corrupted blocks, and the expdp was keep failing with ORA-01555: snapshot too old: rollback segment number  with name "" too small. Our initial thoughts were tuning undo_retention, increasing the undo tablespace, setting an event, etc. Unfortunately, none of the workarounds helped in the situation. We then cameacross a MOS note which explains that an ORA-01555 with "", no rollback segment name is probably due to corrupted blocks. After applying the solution explained in the note, we managed to export/import the database successfully. My colleague has blogged about the scenario at his blog: http://bit.ly/2fBOxm7

Another database is running on Windows x86 64-bit, and its full of LOBs, hence, the datapumps (expdp) took significant time, as NFS filesystem used to store the  dump file. We then thought of doing direct RMAN restore from source to target, as the database on Windows x86 64bit and Linux x86 64-bit are the same (Litten) Endian formats. As per one of the MOS notes, we can also do the Data Guard setup, and do RMAN restore. However, RMAN recovery would fail with ORA-600, as cross platform redo conversion won't be possible. We are now thinking of taking a cold backup (consistent) and do a complete restore with reset logs option.

Stayed tuned for more updates on this.



The real cost of an exception

Tom Kyte - Sun, 2016-11-27 01:26
Hello, Tom. I my project we have a package with a pretty standard structure, I think. It has public procedures calling in turn private procedures, calling innermost procedures eventually. Simplifying, something like this: -------------------------...
Categories: DBA Blogs

Bad cardinality in join with column with skewed data

Tom Kyte - Sun, 2016-11-27 01:26
Hi guys. I have a problem with the estimation of the cardinality of a skewed column The distribution of the data is as follows: <code>select m.m_pricelist_id, count(*) from m_pricelist_version m group by m.m_pricelist_id 2 3 ;</code>...
Categories: DBA Blogs

Another Thanksgiving Email

Tim Tow - Sat, 2016-11-26 16:06
Here is another email we got Wednesday from a Dodeca Excel Add-In for Essbase customer.  This customer has a lot of VBA macros running some automation with Essbase and asked for some assistance.  We told it was as easy as replacing they Essbase function declarations file with our Dodeca Add-In  function declarations file, and then setting the variables that contain the location for the Dodeca-Essbase server.  In other words, replace this file:

With this file:





Easy, right? Here is the email:

I hadn't had a chance to test this massive file out with the latest version of the add-in yet, but was more than pleasantly surprised when I replaced the add-in code for the Dodeca wrapper and the only thing I had to do was change the connection to our new server and the file was live! It's truly drop and go! Thanks so much!!!!



Categories: BI & Warehousing

ADF 12.2.1.1 New Feature - Masonry Layout Custom Size Dashboard

Andrejus Baranovski - Sat, 2016-11-26 13:44
ADF 12.2.1.1 and 12.2.1.2 respectively comes with improvement for Masonry Layout component. Now we can define custom sizes for tiles - 9.4.1 How to Use a masonryLayout Component. This helps to define larger tiles and organize dashboard layout in more accurate way.

I have uploaded working demo app on GitHub, you can download it directly from repository or browse through the code - ADFAltaApp. I will be using this app for ADF Bindings webinar - Live Webinar - Master Class - ADF Bindings Explained.

To access Masonry Layout dashboard with custom tile sizes, go to Employees section and open Profile tab. You should see such dashboard layout (one tile 2x4, one tile 4x2 and two tiles 2x2). All four tiles are defined with custom size:


Masonry Layout is responsive out of the box. On narrow screen tiles will be re-arranged automatically:


Custom tiles for Masonry Layout are defined through CSS. You should create custom style class and set it for Masonry Layout component. I have define custom style class - RedSamuraiDashboard:


Each tile group with custom size must be defined in CSS separately. Width and hight should be proportional. If you define 250px for size 2, this means size for 4 must be 500px:


Masonry Layout tiles are assigned with style class which defines size:


You could have ADF region inside tile, it renders perfectly well:

Online Redo Log Switching from RMAN Backup

Michael Dinh - Sat, 2016-11-26 11:28

I was troubleshooting backup from standby databases and encountered an oddity which I wanted to verify.

Backing Up Archived Redo Logs with RMAN

Before beginning the backup, RMAN switches out of the current redo log group, and archives all online redo logs that have not yet been archived, up to and including the redo log group that was current when BACKUP command with any of the following clauses:
PLUS ARCHIVELOG
ARCHIVELOG ALL
ARCHIVELOG FROM …

 

 


Use REST API to get the data

Dylan's BI Notes - Sat, 2016-11-26 10:37
We now see more and more data are available in REST API.  Here are some of the data sources I worked earlier: ServiceNow Table API, REST API for Oracle Service Cloud, Here is a check list to enable a data source for BI: 1. How does the service perform the authentication? In the case of […]
Categories: BI & Warehousing

Lot of time to add partitions to a Big Table

Tom Kyte - Sat, 2016-11-26 07:06
Hi Tom, I have a big table with 6 partitions(hash), the table have <b>270 Million records</b>, i try to add 2 partitions(i want to have 8 partitions for use "power of 2"), after prepare temp space and undo space, then i run process to add to partiti...
Categories: DBA Blogs

Synonyms

Tom Kyte - Sat, 2016-11-26 07:06
Hi, Please answer the below questions. 1. What Is the Difference between PUBLIC and PRIVATE synonyms? 2.Do we need GRANT privilages to acces PUBLIC SYNONYM? 3.Is PUBLIC Synonym a NON-Schema Object Or Schema Object?
Categories: DBA Blogs

INSTEAD OF TRIGGERS

Tom Kyte - Sat, 2016-11-26 07:06
INSTEAD OF triggers are executed instead of DML statements that fired it. Is it correct??
Categories: DBA Blogs

Drop MetaData

Tom Kyte - Sat, 2016-11-26 07:06
Hi, I installed 11g(enterprise) on my personal laptop.I Droped the Metadata DBA_OBJECTS,DBA_USERS(my intention is to know DB fuctioning),my Data base working properly.But When I execute Select query(SELECT * FROM dba_objects) getting *ora-10775 ...
Categories: DBA Blogs

Moving table partitions

Tom Kyte - Sat, 2016-11-26 07:06
Tom, Is it possible to move a table partition from one tablespace to another in the same way it is now possible to move a table from one tablespace to another ?
Categories: DBA Blogs

Oracle database In Memory

Tom Kyte - Sat, 2016-11-26 07:06
Hello Tom, I seen a new feature for Oracle 12c : In-Memory database cache. I seen it's a new memory area to organize data in column format for BI/DWH queries. I would like to know how Oracle decides when use the data from In-memory area or from...
Categories: DBA Blogs

Links for 2016-11-25 [del.icio.us]

Categories: DBA Blogs

Reading xml data from nested xml nodes using PL/SQL

Tom Kyte - Fri, 2016-11-25 12:46
Hello Tom, I am new to xml and don't know Java and, unfortunately, do not have time to learn it right now as I need to have a little prototype done of this in a couple of days. I did a lot of reading and reviewed many of the OTN references but I...
Categories: DBA Blogs

Oracle 12.2 new features: long names

Tom Kyte - Fri, 2016-11-25 12:46
I was just going through https://blogs.oracle.com/sql/entry/12_things_developers_will_love and checking the new Oracle 12.2 features, and noticed something about the "Loooooooooooooooong Names". So say if I define a variable as v_table_name use...
Categories: DBA Blogs

Synonyms

Tom Kyte - Fri, 2016-11-25 12:46
Hi, Please Go through the below code SQL> select * from san; --querying tbale NUM ---------- 1 2 SQL> select * from sam; --querying table VAR ---------- santhosh reddy SQL> SE...
Categories: DBA Blogs

Relation

Tom Kyte - Fri, 2016-11-25 12:46
Hi, Is it possible to create a relationship between tables without using foreign key?
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator