DBA Blogs

Connect Power BI to GCP BigQuery using Simba Drivers

Ittichai Chammavanijakul - Fri, 2018-09-21 21:56

Power BI can connect to GCP BigQuery through its provided connector. However, some reported that they’ve encountered the refresh failure as seen below. Even though the error message suggests that the quota for the API requests per user per minute may be exceeded, some reported that the error still occurs even if with a small dataset is being fetched.

In my case, by simply disabling parallel loading table (Options and settings > Options > Data Load), I no longer see this issue. However, some still said it did not help.

An alternative option is to use another supported ODBC or JDBC driver from Simba Technologies Inc. which is partnered with Google.

Setup

  • Download the latest 64-bit ODBC driver from here.
  • Install it on the local desktop where Power BI Desktop is installed. We will have to install the same driver on the Power BI Gateway Server if the published report needs to be refreshed on Power BI Service.

Configuration

  • From Control Panel > Administrator > ODBC Data Source Administrator > System DSN, click Configure on the Google BigQuery.
  • Follow the instructions from the screens below.

When connecting on Power BI, Get Data > choose ODBC.

Categories: DBA Blogs

Clob data type error out when crosses the varchar2 limit

Tom Kyte - Fri, 2018-09-21 04:26
Clob datatype in PL/SQL program going to exception when it crosses the varchar2 limit and giving the "Error:ORA-06502: PL/SQL: numeric or value error" , Why Clob datatype is behaving like varchar2 datatype. I think clob can hold upto 4 GB of data. Pl...
Categories: DBA Blogs

Migrating Oracle 10g on Solaris Sparc to Linux RHEL 5 VM

Tom Kyte - Fri, 2018-09-21 04:26
Hi, if i will rate my oracle expertise i would give it 3/10. i just started learning oracle, solaris and linux 2months ago and was given this task to migrate. yes our oracle version is quite old and might not be supported anymore. Both platforms ...
Categories: DBA Blogs

"secure" in securefile

Tom Kyte - Fri, 2018-09-21 04:26
Good Afternoon, My question is a simple one. I've wondered why Oracle decided to give the new data type the name "securefile". Is it because we can encrypt it while before with basicfile, we couldn't encrypt the LOB? Also, why not call it "se...
Categories: DBA Blogs

Pre-allocating table columns for fast customer demands

Tom Kyte - Fri, 2018-09-21 04:26
Hello team, I have come across a strange business requirement that has caused an application team I support to submit a design that is pretty bad. The problem is I have difficulty quantifying this, so I'm going you can help me all the reasons why ...
Categories: DBA Blogs

move system datafiles

Tom Kyte - Fri, 2018-09-21 04:26
Hi Tom, When we install oracle and create the database by default (not manually) ...the system datafiles are located at a specific location .. Is is possible to move these (system tablespace datafiles) datafiles from the original location to...
Categories: DBA Blogs

how does SKIPEMPTYTRANS work?

Tom Kyte - Fri, 2018-09-21 04:26
I am wondering how does SKIPEMPTYTRANS work? when does ogg judge a transaction empty or not? if it does the judgement in the middle transction? how does ogg know it's a empty transaction? provided that it did not update mapped tables before the jud...
Categories: DBA Blogs

In-Database Archiving

Tom Kyte - Wed, 2018-09-19 15:46
Hi, Currently i am using list partitioning based on a status column to classify the data as ACTIVE and EXPIRED. And then the corresponding partitions are exported and then dropped from Prod. The problem with this approach is the internal data m...
Categories: DBA Blogs

TDE Column vs TDE tablespace when to use

Tom Kyte - Wed, 2018-09-19 15:46
Hi, I have gone through the TDE column and TDE tablespace encryption. Most cases TDE tablespace option is found to be better compared to TDE column option. Wanted to know what advantage TDE column encryption gives or rather the use cases for TD...
Categories: DBA Blogs

In explain plan one of the tables in the query is not even scanned for joining , Do you know why ?

Tom Kyte - Wed, 2018-09-19 15:46
I am running a query like below explain plan for select count(1) from A , B where A.column1=1 and A.column2=3 and A.column3=b.column3(+) When I check the explain plan I can see the sort aggregate and other things ,However I do not see...
Categories: DBA Blogs

How to allow private connectivity across organizations(GCP)?

Surachart Opun - Wed, 2018-09-19 02:41
It's interesting, when you would like to allow private connectivity across two VPC networks that they belong to the different project/organization on Google Cloud Platform (GCP).

As google document that we can use VPC Network Peering? VPC Network Peering is a decentralized or distributed approach to multi-project networking. Additional, it works with Compute Engine, Kubernetes Engine,and App Engine flexible environments.

I did a lab about Virtual Private Cloud (VPC) Network Peering. There shows to do VPC Network Peering between VPC networks in the same project. So, I would like to see how it works on across organizations.

Example: On My Organization (my project), I would like to connect server (Private IP Address) on another Organization (another project).

As a subnet CIDR prefix in one peered VPC network cannot overlap with a subnet CIDR prefix in another peered network. So, both VPC networks must have the different CIDR prefix.

My Organization [myproject] {ubuntu-test, default/10.146.0.2} <======> No organization [qwiklabs-gcp***]{privatenet-us-vm/172.16.0.2}

On No organization [qwiklabs-gcp***]: VPC network name is "privatenet".

VPC:
Firewall:


My Organization [myproject]: I used "default" VPC and default firewall.


Then, starting to create "VPC Network Peering".

- To create "VPC Network Peering" on myproject:
Networking => "VPC network" => "VPC network peering".
Click "Create Peering Connection".  name = "peering-to-lab".
Note: you must know Project ID and VPC network name for network destination.































It should show "Waiting for peer network to connect".

-  To create "VPC Network Peering" on another Project: On No organization [qwiklabs-gcp***], "Create Peering Connection".  name = "peering-to-mygcp".


After clicking "Create". It should show "Connected" on both projects (if configuration corrects) like.

On myproject:

- Finally, test connection: ssh to my vm and test (ssh) connection to {privatenet-us-vm/172.16.0.2}.
Note: (as firewall allow icmp/ssh). No need to do on firewall.

opun@ubuntu-test:~$ ssh 172.16.0.2
opun@172.16.0.2's password:
Linux privatenet-us-vm 4.9.0-8-amd64 #1 SMP Debian 4.9.110-3+deb9u4 (2018-08-21) x86_64
The programs included with the Debian GNU/Linux system are free software;
the exact distribution terms for each program are described in the
individual files in /usr/share/doc/*/copyright.
Debian GNU/Linux comes with ABSOLUTELY NO WARRANTY, to the extent
permitted by applicable law.
Last login: Wed Sep 19 06:22:05 2018 from 10.146.0.2
Could not chdir to home directory /home/opun: No such file or directory
$ w
 06:22:55 up 43 min,  2 users,  load average: 0.00, 0.00, 0.00
USER     TTY      FROM             LOGIN@   IDLE   JCPU   PCPU WHAT
opun     pts/1    10.146.0.2       06:22    1.00s  0.00s  0.00s w ************Reference: https://cloud.google.com/vpc/docs/vpc-peering
Categories: DBA Blogs

#Exasol Database whoami

The Oracle Instructor - Wed, 2018-09-19 02:03

This little script displays some useful meta-information:

SQL_EXA> create schema myschema;
EXA: create schema myschema;

Rows affected: 0

SQL_EXA> create or replace script whoami as
 output('Current User: '.. tostring(exa.meta.current_user))
 output('Current Schema: '.. tostring(exa.meta.current_schema))
 output('Session ID: '.. tostring(exa.meta.session_id))
 output('Database Version: '.. tostring(exa.meta.database_version))
 output('Number of Nodes: '.. tostring(exa.meta.node_count))
 /
EXA:create or replace script whoami as...

Rows affected: 0
SQL_EXA> col output for a40;
COLUMN   output ON
FORMAT   a40
SQL_EXA> execute script whoami with output;
EXA: execute script whoami with output;

OUTPUT
----------------------------------------
Current User: SYS
Current Schema: MYSCHEMA
Session ID: 1612024483893367379
Database Version: 6.1.0-alpha1
Number of Nodes: 1

5 rows in resultset.

All available metadata is documented here (Chapter 3. Concepts -> 3.6 UDF scripts).

Categories: DBA Blogs

ERROR WITH IMPORT OPERATION IN DATABASE 11G RELEASE 1 AND DEVELOPER 10G

Tom Kyte - Tue, 2018-09-18 21:26
Hi Tom, I installed Oracle Database 11G in the folder (E:\DB11)and successfully imported a user to it. But when you install the developer 10G IN FOLDER(E:\DEV10). and TRY TO DO a user import into the database ORACLE 11G, i am receiving the follo...
Categories: DBA Blogs

Collection to retrieve data as pipelined taking more time even the query taking lesser time

Tom Kyte - Tue, 2018-09-18 21:26
Hi sir, Have facing issue with the time. The query which is used in dynamic cursor type like 'open for select" taking 550 ms but the output we used as collection passing as pipelined value. At that time it's taking more time like 50 s. Please give...
Categories: DBA Blogs

Need script to compare table data of same table in different oracld databases

Tom Kyte - Tue, 2018-09-18 21:26
Hi, I have a requirement where table1 exists in 2 diiferent oracle dbs . I need to write a shell script or pl sql block which would compare every column of the table in db 1 and db2 and give following output from unix. It should be able to take any ...
Categories: DBA Blogs

Selection from union view by table identifier

Tom Kyte - Tue, 2018-09-18 21:26
Hi, I have a view like <code>create view V_TAB as (select 1 as id, value from TAB1 union all select 2 as id, value from TAB2 ); </code> I would expect Oracle be able to optimize the following query and execute a selection only on one tabl...
Categories: DBA Blogs

Oracle Forms V Oracle APEX Check List

Tom Kyte - Tue, 2018-09-18 03:06
Oracle Forms has some strength and is still the best BackOffice tool from ORACLE from my Point of view. Here are some issues that I miss with APEX. Maybe you already have these Options in 18.x.? Can you check this list: 1. 100% accessiblity for...
Categories: DBA Blogs

Display blob pdf

Tom Kyte - Tue, 2018-09-18 03:06
Dear, I have table lob_table( id number ,doc blob ,namefile varchar (200)) I would like to display blob doc who is pdf file and print it in sqldeveloper. i have create this procedure is it corrects ? CREATE OR REPLACE PROCEDURE PROC2 AS...
Categories: DBA Blogs

What privilege to view package body

Tom Kyte - Mon, 2018-09-17 08:46
Hi Tom: I have a problem when i grant the package privilege to the other user. A is a normal user which used in factory environment. user B is for app team which can not create anything. First I grant create any procedure ,execute any procedure...
Categories: DBA Blogs

Hardware resource planning

Tom Kyte - Mon, 2018-09-17 08:46
Hello, Thanks for taking up this question. I am interested in understanding how to optimize the hardware resources (cores, memory, disk space) required for Oracle without impacting performance. There are multiple virtual machines in a VMwa...
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs