Skip navigation.

Blogs

Impact on Packages - Assign permissions directly vs Assign permission via Roles

Document depicts how Roles and Privileges behave for Packages

DB Version: 10.2.0.4

Users: TESTUSR (Table Owner)
TESTUSR_PKG (Package Onwer)
TESTUSR_APP (Application Owner)

Roles: TESTUSR_APP_ROLE
TESTUSR_PKG_ROLE

Table: TEST_TBL
Package: TEST_PKG

Table Text:
create table test_tbl(a number);

Package Text:
CREATE OR REPLACE PACKAGE test_pkg AS -- spec
PROCEDURE insert_test_tbl (
a NUMBER);
PROCEDURE delete_test_tbl (
a NUMBER);
END test_pkg;

CREATE OR REPLACE PACKAGE BODY test_pkg AS -- body
PROCEDURE insert_test_tbl (
a

12c SQL tuning: adaptive execution plans

The concept of an adaptive plan is that execution of a statement can start with one plan, and (during execution) switch to another. A pretty amazing capability. No need to run the statement to completion and try again: correct it in flight.

CTOUG re-launch meeting

I was at the Connecticut Oracle User Group re-launch meeting last week, talking about some of the 12c new features that I specially like: those related to what I call "the self-learning database".

Database 12c: automatic generation of extended statistics

Why does the CBO get it wrong? Often because it has insufficient information. No matter how often you analyze your tables, if your queries use multi-column predicates, the CBO will mis-calculate the cardinalities. You have to understand your data, and create extended statistics to correlate the columns. How many people do this? Hardly any. Not a problem any more: release 12c can do this for you. If you configure it appropriately.

Oracle Pipelined Table Functions

Oracle Pipelined Table Functions
________________________________________
Overview
Basically, when you would like a PLSQL (or java or c) routine to be the «source»
of data -- instead of a table -- you would use a pipelined function.
PIPELINED functions will operate like a table.
A PL/SQL function may be used in a data warehouse database to transform large amounts of data. This might also involve massaging the data in a series of transformations, each performed by different functions.

virtual columns in 11g

virtual columns in 11g
Oracle has supported stored expressions for many years, in views and function-based indexes. Most commonly, views enable us to store and modularise computations and expressions based on their underlying tables' columns. In more recent versions (since around the 8i timeframe), we have been able to index expressions using function-based indexes.

Grid Infrastructure and RAC: the cluster interconnect

A necessary part of a cluster is the cluster interconnect: the private network between the cluster nodes. This component is critical for both reliability and scalability, and configuring it for fault tolerance and capacity is a matter of burning importance for the DBA and his System Administrator (not to mention the end users). One question is, should this be managed by the operating system or by Oracle?

Finding islands – 4 methods in Oracle

Finding islands are classic problems in PL/SQL. The basic concept is that you have some sort of numbers, like these: 1, 2, 3, 5, 6, 8, 9, 10, 15, 20, 21, 22, 23, 25, 26. The islands problem involves identifying ranges of existing values.

Finding gaps with analytic functions

Finding gaps is classic problem in PL/SQL. The basic concept is that you have some sort of numbers (like these: 1, 2, 3, 5, 6, 8, 9, 10, 15, 20, 21, 22, 23, 25, 26), where there’s supposed to be a fixed interval between the entries, but some entries could be missing. The gaps problem involves identifying the ranges of missing values in the sequence. For these numbers, the solution will be as follows:
START_GAP END_GAP
4 4
7 7
11 14
16 19
24 24

First, run the following code, to create tab1 table:


CREATE TABLE tab1
(

Oracle DBA interview Questions

So many fresher Oracle DBAs and intermediate Oracle DBAs are searching online Oracle DBA interview Questions but unfortunately they are getting older and theoretical questions about Oracle basics. If you will prepare yourself from those content then you might get trouble to crack technical interview.

You need to prepare yourself about technical interview with consideration of recruiter company’s requirements. In most of cases, interviewer will ask questions about their own requirements.