DBMS_JAVA Privilege Error?

It’s possible to get an error after granting privileges to an external file system. One of those errors is tedious to resolve until you understand the rules governing Java NIO file permissions.

You grant privileges to external file systems as the sys user with the grant_permission procedure of the dbms_java package, like

SQL> BEGIN
  2    DBMS_JAVA.GRANT_PERMISSION('IMPORTER'
  3                               ,'SYS:java.io.FilePermission'
  4                               ,'C:\Data\Upload'
  5                               ,'read' (more...)

Working Around an ASM Startup Problem

Scenario

I recently hit the problem covered in MOS note “ASM Instance Is Not Coming Up ORA-00064 (1,4468736,Kfchl Array) Kfchl Array (Doc ID 1328620.1)” mid-way through a Grid Infrastructure upgrade from 11.2.0.2 BP16 to 11.2.0.4 BP3 on Exadata. Specifically, it was the application of prerequisite patch 17783101 (required for downgrades) to node 3 that hit the problem. At this point nodes 1 and 2 had been successfully patched (more...)

Better Data Modeling: My Top 3 Reasons why you should put Foreign Keys in your Data Warehouse

This question came up at the recent World Wide Data Vault Consortium. Seems there are still many folks who build a data warehouse (or data mart) that do not include FKs in the database. The usual reason is that it “slows down” load performance. No surprise there. Been hearing that for years. And I say […]

Top 10 queries from v$active_session_history

Description This query return the top 10 queries by resource consumption (CPU+IO+WAIT) in the last hour from v$active_session_history. Be careful, this view is part of the diagnostic pack, you should not query this view if you don’t have license for it. top 10 queries from v$active_session_history [crayon-533b91631cebb871999454/] This query return top queries by resources consumed, [...]

The post Top 10 queries from v$active_session_history appeared first on Oracle DBA Scripts and Articles.

SCAN VIP Troubleshooting

We had a client that was running into a strange issue on their Exadata where new connections coming in through the SCAN were failing.  After doing some troubleshooting, it was discovered that it was related to one of the SCAN listeners not properly accepting requests from new sessions.  The VIP and listener were running, and everything looked normal.

We had the following SCAN setup:

SCAN VIP # VIP IP
1 172.25.2.70
2 (more...)

Speaking at Collaborate 2014

I’m a little behind on my updating my blog with images of conferences where I will be speaking (I’ll get to that later and hopefully fix it).  In the meantime, I wanted to let everyone know that I will be speaking at IOUG Collaborate 2014 this year.  IOUG has decided to hold the conference in Las Vegas, NV.  Should be a fun conference; after all everyone knows the saying “What happens in (more...)

NoSQL vs. NewSQL vs. traditional RDBMS

I frequently am asked questions that boil down to:

  • When should one use NoSQL?
  • When should one use a new SQL product (NewSQL or otherwise)?
  • When should one use a traditional RDBMS (most likely Oracle, DB2, or SQL Server)?

The details vary with context — e.g. sometimes MySQL is a traditional RDBMS and sometimes it is a new kid — but the general class of questions keeps coming. And that’s just for short-request use (more...)

Juggernaut

One of the problems of “knowing” so much about Oracle is that the more you know the more you have to check on each new release of the software. An incoming ping on my posting “Lock Horror” reminded me that I was writing about 11.2.0.1, and the terminal release is 11.2.0.4, and the whole thing may have changed in 12.1.0.1 – so I ought to re-run some tests (more...)

How to Diagnose a Locking Issue

A user ran an update statement and noticed that it did not seem to be doing anything:
 
SQL> conn gordon/bennett
Connected.
SQL> update andrew.emp
  2  set ename = 'COLIN'
  3  where ename = 'BRIAN'
  4  /
 
I looked up his SID in V$SESSION:
 
SQL> select sid from v$session
  2  where username = 'GORDON'
  3  /
 
       SID
----------
      393
 
SQL>

I ran the following (more...)

Remote Surprise

Here is an example of surprising behaviour from a remote DB from an OTN forum thread

Setup a link to a remote DB (I’ve used an actual remote DB and not tested a loopback)

Remote DB:

create table t1
(col1  varchar2(1));

Local DB:

create or replace view v1 
as 
select count(*) c1 from t1@l1;

Then alternate variations on this sequence of events:

1. On local DB execute SELECT:

SELECT * FROM v1;

2. On remote (more...)

Oracle Memory Troubleshooting, Part 4: Drilling down into PGA memory usage with V$PROCESS_MEMORY_DETAIL

If you haven’t read them – here are the previous articles in Oracle memory troubleshooting series: Part 1Part 2, Part 3.

Let’s say you have noticed that one of your Oracle processes is consuming a lot of private memory. The V$PROCESS has PGA_USED_MEM / PGA_ALLOC_MEM columns for this. Note that this view will tell you what Oracle thinks it’s using – how much of allocated/freed bytes it has kept track of. While (more...)

Battling Bigfile Backup Bottlenecks

Last Friday I kicked off a database backup to an NFS destination, using the standard "backup as compressed backupset database" syntax. Loyal readers of this blog may recall that I'm the proud custodian of a 25 Tb database, so this backup normally takes a few days, with an expected completion on Monday morning. However it was still running on Wednesday, and reviewing the logs I saw that there was just 1 channel (of the original (more...)

Diagnostics

Here’s a little test you might want to try. Examine the following script, and decide what sort of symptoms you would see in the AWR report.


create global temporary table gtt1(n1 number);

execute dbms_workload_repository.create_snapshot;

insert into gtt1 values(1);
truncate table gtt1;

-- repeat insert/truncate for a total of 100 cycles

execute dbms_workload_repository.create_snapshot;

-- generate an AWR report across the interval.

I don’t need anyone to tell me their results – but if your (more...)

EMDIAG Repvfy 12c kit – troubleshooting part 1

The following blog post continue the EMDIAG repvfy kit series and will focus on how to troubleshoot and solve the problems reported by the kit.

The repository verification kit reports number of problems with our repository which we are about to troubleshoot and solve one by one. It’s important to notice that some of the problem are related so solving one problem could also solve another one.

Here is the output I’ve got for my (more...)

Min/Max

One of my most-repeated observations about trouble-shooting Oracle is that things break when you start combining features. Here’s an example that demonstrates the point.

It’s possible to create “descending” indexes – or indexes with descending columns, as I prefer to call them, and there’s a special “min/max range scan” optimizer operation for a particular kind of index usage – demonstrated in the following code fragment (running under 11.2.0.4, and reporting the rowsource (more...)

New installation cookbook for SUSE Linux Enterprise Server 11 SP3

Exactly what it says on the tin, I’ve added a new installation cookbook for SUSE 11 SP3 which creates Violin on a set of 4k devices.

I’ve started setting the add_random tunable of the noop I/O scheduler because it seems to give a boost in performance during benchmarking runs. If I can find the time, I will blog about this at some point…

For more details read this document from Red Hat.


Filed under: Blog, (more...)

Moving a Table Deletes its Statistics

Statistics are important as they help the optimizer to work out the execution plan for a SQL statement. If you move a table, this deletes its statistics so you need to analyze it again afterwards. You can see this in the example below. First I created a table:

SQL> create table object_list
  2  as select * from dba_objects
  3  /

Table created.

SQL>

When you create a table it has no statistics so (more...)

Moving Forward

Seems to be quite a bit of buzz in the enterprise software user community these days about moving forward.  Budgets have loosened up, users want better experiences, in-house IT providers want to reduce maintenance and infrastructure investments, C-level officers want better and more timely information on strategic initiatives, and everybody wants to be agile (even though there are multiple visions of agile, we all want it).  So it seems the big question lately is "how (more...)

Oracle 12c Multitenant workshop – Bangalore chapter

Thanks to all folks who attended Oracle 12c Multitenant workshop in Bangalore. It was really a great session and well received by all the participants. I appreciate all your interests to know about Multitenant, play with the new architecture and explore the changes. Few points to make note from this event – 1. The question … Continue reading

Hidden DBMS_JAVA Nuance

It always happens when I’m in a hurry. Yes, I ran into one of those pesky little features with Oracle’s DBMS_JAVA package. While I try to write entries with proper GeSHi case semantics, like everyone else I tend to write PL/SQL initially in lowercase. That led me to the discovery of this wonderful error message:

BEGIN
*
ERROR at line 1:
ORA-29532: Java CALL TERMINATED BY uncaught Java exception:
oracle.aurora. (more...)