Blogs
Impact on Packages - Assign permissions directly vs Assign permission via Roles
Submitted by girish.rohini on Wed, 2012-04-11 08:43Document 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
- girish.rohini's blog
- Login to post comments
- Read more
An export is not a backup
Submitted by John Watson on Thu, 2010-05-27 15:48Posts on Orafaq reveal that a significant subset of database administrators believe
that an export is a backup. This paper summarizes what an export is, what a backup is,
and why the two are different.
About Apps 11i Clone – A Human Touch
Submitted by saibalguha on Thu, 2013-03-21 05:56Apps clone is like cloning a human being. Please read the detail below.
1) Run adpreclone on DB/CM/AP tiers
This is pre-requisite step of cloning 11i prod and will not change anything in any system. This script will only create on $COMMON_TOP, a directory called "clone", with a basic structure of executable, scripts, templates and java files.
- saibalguha's blog
- Login to post comments
- Read more
Oracle Database 12c new feature: the row limit clause
Submitted by John Watson on Sat, 2013-03-02 07:42An age old problem with SQL is pagination through a row set. You want to display rows one page at a time, in some sort of order. The issue is architectural: SQL is a set oriented language. It deals with rows as a group, not individually, making it impossible to paginate through a set because that would require navigation from row to row. There are work arounds, there are potential programming bugs, and there is a Database 12c New Feature: the row limit clause. All demonstrations below are done with 12c final beta, release 12.1.0.1.0. Below is the old and the new way, with a bit of reverse engineering.
12 MArch 2013 update: Content removed - it exceeded what is permitted for the pre-release status of the product.
Sorry about that, will repost when I can.
JW.
- John Watson's blog
- Login to post comments
- Read more
Deterministic function vs scalar subquery caching. Part 1
Submitted by xtender on Sun, 2013-02-10 16:29I recently did a comparison caching mechanisms of scalar subquery caching(SSC) and deterministic functions in 11.2. Unfortunately, I do not have enough time to do a full analysis, so I will post it in parts.
Today’s topics:
1. Both mechanisms are based on hash functions.(About hash tables and hash collisions for scalar subquery caching excelent wrote Tom Kyte and Jonathan Lewis(“Cost-Based Oracle fundamentals” chapter 9))
2. Deterministic caching depends on fetch size(arraysize) – results cached only within one fetch call, ssc has no this limitation.
3.
- xtender's blog
- Login to post comments
- Read more
Oracle database 12c: The Multithreaded Database
Submitted by John Watson on Sat, 2013-02-09 09:58A 12c new feature that may have significant performance and scalability implications is the multithreaded database. All releases of Oracle below 12.x (and 12.x by default) run on Unix in a multi process model. The various background processes (typically at least fifty for 12c, thirty or forty for 11g) run as separate operating system processes. Furthermore, the dedicated server processes that support sessions also run as separate operating system processes. An Oracle instance will usually be running as hundreds (perhaps thousands) of processes. The multithreaded database changes this.
12 MArch 2013 update: Content removed - it exceeded what is permitted for the pre-release status of the product.
Sorry about that, will repost when I can.
JW.
- John Watson's blog
- Login to post comments
- Read more
Just another version of Tom Kyte’s runstats (runstats_pkg)
Submitted by xtender on Mon, 2013-01-21 15:28I want to share my modifications of Tom Kyte's runstats package, which include:
- Any number of runs sets for analyzing
- Standalone: No need to create other objects
- Ability to specify session SID for statistics gathering
- Ability to specify what to gather: latches, stats or both
- Separate mask filters for output by statname and latchname
- Ability to specify difference percentage for output separately for latches and statistics
- More accurate with some statistics because of avoiding global temporary table usage, but less in
- xtender's blog
- Login to post comments
- Read more
Oracle Z to A -- I N
Submitted by zainmdm on Sun, 2012-11-04 23:32Oracle Database A to Z question and answers and
very importantly the Experiences also you can share here...
The Blog is for Both the beginners and as well as experienced people to discuss and gain knowledge in Oracle.
Here we are going to share every thing about Oracle....
And Simply saying, we will only work on oracle related but nothing else here....
We will Eat Oracle, Drink Oracle and Walk Oracle
- zainmdm's blog
- Login to post comments
Diff B/W Truncate & Delete
Submitted by arunbharat2001 on Sat, 2012-10-20 11:03Delete
1.DML data can be recovered by rollback before commit
2.Remove any subset of data
3.Delete is slower when table have numerous indexes and triggers
4.DML triggers fire for delete mthod
5.data can be recovered after commit by flashback method
6.high water mark will remain the same
7.data deleted info capture with returning clause
8. you can not delete data if function based index is invalid
9. can not delete data from complex views.
10.
- arunbharat2001's blog
- Login to post comments
- Read more
Oracle Database 12c: New Features - Pluggable Databases
Submitted by mikerajendran on Sun, 2012-10-07 00:30
Oracle Database 12c: New Features – Pluggable Databases by Michael Rajendran
Introduction
Oracle has leap forwarded the middleware technologies especially the database technology into the cloud. So far Oracle has been the traditional RDBMS database suitable for the private enterprise data centers within corporate walls.

