Book Review: Oracle 10g/11g Data and Database Management Utilities

From Technologies to Solutions:
Oracle 10g/11g Data and Database Management Utilitiesby Hector R. Madrid
PACKT Publishing

The book is well within its perimeters. It discusses in detail nearly all database management utilities that an administrator uses in his day-to-day activities. The book will prove to be helpful for DBAs in a junior role as well as for those who are preparing for database administration certification examinations. The language used in this book helps the reader understand the complex tools offered by Oracle even though he may not be aware of all the tools discussed.

The authenticity of the topics discussed and extensive examples/screen shots used are a real added advantage in understanding the utilities better. This book can also prove to be a good reference material for those involved in routine tasks not worrying about what all Oracle offers.

It deals with normal DBA tools like Data Pump, External tables, SQL Loader, Recovery Manager, Oracle Scheduler, Security Management, Database Configuration Assistant (DBCA) and much more. These are a must to be understood for a database administrator as well as a database developer/enthusiast. For a long time, there has been a demand for technical details of what a database administrator deals in his day-to-day life and this book addresses to this need. The book explains in detail the various tools the DBAs would use and then discusses the feature enhancements done in 11g. Also examples constitute a sizable portion of the book which are nice to have in a book of this magnitude.

In short this book deserves its place in your custody and will prove to be best in times of database administration activities.

Packt Publishing | English | ISBN: 1847196284 | 432 pages

PACKT Publishing
Buy Book from Amazon

Oracle SQL Developer Data Modeler

Oracle SQL Developer Data Modeler is the latest product offering from the Oracle Database Tools group. SQL Developer Data Modeler is an independent, standalone product with a full spectrum of data and database modeling tools and utilities, including modeling for Entity Relationship Diagrams (ERD), Relational (database design), Data Type and Multi-dimensional modeling, full forward and reverse engineering and DDL code generation. The Data Modeler imports from and exports to a variety of sources and targets, provides a variety of formatting options and validates the models through a predefined set of design rules.

Oracle SQL Developer Data Modeler can connect to any supported Oracle Database and is platform independent. A read-only SQL Developer Data Modeler Viewer is also available for download. The viewer allows users to open models created in the Data Modeler.

Click here for Oracle® SQL Developer Data Modeler User's Guide

Oracle® SQL Developer Data Modeler

Note: For download you require free OTN account. If you do not have one, read this for creating one account for you.

For more information visit Oracle website.

Puzzler from Steven Feuerstein (July 2009)

New Puzzler from Steven Feuerstein (July 2009)

TEST YOUR PL/SQL KNOWLEDGE
Which of the following identifiers is the name of the implicit cursor attribute that allows you to find out how many rows were updated by each individual statement processed by the FORALL statement?

SQL%ROWCOUNT
SQL%FORALL_ROWCOUNT
SQL%BULK_ROWCOUNT

To Understand about SQL%BULK_ROWCOUNT click here.

For subscribing to more Puzzlers from Steve and to answer to Steve click here.

How to rename oracle schema?

Renaming a schema is not an easy thing in Oracle. For reasons unknown Oracle does not allow you to rename a schema by a keyword such as

rename old_schema to new_schema;

or

alter user old_schema rename to new_schema;

THIS FACILITY DOES NOT EXIST in Oracle. There might be some utilities or some undocumented features which might leverage the renaming a schema.

But if you really want one way to rename the schema go for the traditional way of exporting the existing schema and import into a new schema. Use clause fromuser touser while importing.

But this too is not fully renaming schema as the privileges will not be imported.

Case Study - Oracle Database Lite

COCA-COLA ENTERPRISES (EGYPT)

"Using Oracle Lite, our pre-sales teams increased order accuracy from 75% to 95%, doubled the productivity of our delivery trucks, and cut administration costs by 50%." – Lobna El Dessouky, Chief Financial Officer, The Coca-Cola Bottling Company of Egypt

Coca-Cola Egypt has automated its pre-sales function with Oracle Lite. Based in Cairo, The Coca-Cola Bottling Company of Egypt (TCCBCE) owns and operates 11 bottling plants and 29 sales and distribution centers. The company has operated in Egypt since 1942 and, with 7,400 employees, is one of the country's major employers.

In 2002, TCCBCE wanted to overhaul its sales strategy to improve productivity, cut costs, and improve the quality of its service to customers. "Traditionally we used to send our distribution trucks to each customer's premises, take orders, and deliver what we could from stock," said Mohamed Shalaby, information systems manager at TCCBCE. "If customers required items that were not on the truck, orders were written down on paper and passed to warehouse staff back at the distribution center for manual processing.

Errors often occurred during processing, resulting in incorrect orders being dispatched and subsequently returned to us. In addition, orders took up to three days to process, resulting in unacceptable response times to customers and lost sales opportunities."

To remedy the situation, Coca-Cola decided to create pre-sales teams equipped with hand-held devices running on Oracle Lite that would visit customers and take orders electronically in advance of distribution. Upon returning to the office, the teams would synchronize orders with the company's Oracle enterprise resource planning (ERP) systems to ensure automated processing and rapid dispatch of accurate orders to customers.

A pilot implementation of this order handling system using Oracle Database Lite was rolled out in November 2002. "Oracle Egypt supported us throughout the implementation and provided IT training and ongoing support," Shalaby said. "Their commitment and expertise ensured that we had a working solution in less than two months. Our pre-sales teams welcomed the new system, which is based on Compaq iPaqs, because it is easy to use and increases their sales figures."

Excerpt from Oracle Database Lite 10gR3 Business White Paper. Read more about the whitepaper and download it here.

Oracle Database Lite

EXECUTIVE OVERVIEW
Oracle Database Lite 10g is an integrated and complete solution for rapid development and deployment of high-impact, mission critical applications for mobile and lightweight environments. Oracle Database Lite is an addition to Oracle Database 10g for enterprises wanting to increase employee productivity, reduce operation costs, or improve customer satisfaction. It extends the grid environment to mobile and embedded devices, allowing mobile workers to access enterprise data even in the absence of a network connection. Additionally, Oracle Database Lite uses data synchronization to allow these workers to reliably and securely exchange data with a corporate Oracle Database.

EXTEND THE GRID TO MOBILE DEVICES
As the performance and storage capabilities of mobile devices continue to grow, the need to access enterprise data from these devices also increases. Ideally, these mobile applications are constantly connected to central business information, presumably through a wireless protocol (Wi-Fi, GPRS, etc.). While the evolution of wireless networks has addressed some of these requirements, wireless access is not the optimal solution for many mobile business scenarios. Specifically, a constant network connection for mobile workers in these contexts is often:
• Not Possible
• Not Practical
• Not Desirable

Download the free Whitepaper from Oracle by clicking here.

Download page of Oracle Database Lite: Click here

What is Oracle Schema

Targeted Audience: Beginners

Till today I was of the illusion that USER and SCHEMA are both equal. But Oracle Guru Andrew Clarke describes the difference between both USER and SCHEMA in his blog. Thanks Clarke for your blog.

So understanding this difference we will define what an Oracle Schema is:

A Schema represents set of objects owned by the user of the same name. For example in an organization there are 100 employees, each of these employees should have a separate space where they manage their objects. It is synonymous with the concept of an employee been allocated a new cabin where he can keep or organize his belongings.

The same way in Oracle a user must be created for each database user. An organization can keep its own rules in naming the users but it is better to use a naming notation always in such cases. If a database user logs in to his space (using connect) he can create objects which becomes the schema.

What a schema can contain?
Just like a cabin where the employee sits; it can contain a PC, a deskphone, a cabinet for filing papers etc., a schema can contain various objects like tables, views, indexes etc. If you want to create any object it must be created inside any of available schemas.

How do I access my schema?
Accessing of a schema is guarded by a password which the DBA assigns at first. You can choose to change the password.

What is (Private) Synonym?

The web gives me a definition of Synonym as "A word that means the same as another word." Well Oracle also defines its Synonyms in the same way. Then what is a Private Synonym? It is nothing more than a Synonym accessible to users of the particular schema where the synonym is created. Public synonyms on the other hand once created by any user, will be accessible for all schemas in a particular database.

How to create a (Private) Synonym?

The syntax for creating a synonym is

CREATE [ OR REPLACE ] SYNONYM synonym_name FOR object;

Example:

CREATE SYNONYM clerk FOR employee;

In our context employee is a table (or can be a view). As Clerk is also an employee, it makes understanding more easy.

You can query from clerk as you would do from employee table/view. Now you must have got the idea of why a synonym is used.

Note: The usage of a (private) synonym from public synonym will be where you need to expose your not-so critical data to all database users. If your data is considered private, you should be creating private synonyms for this purpose.

How to destroy a Synonym?

To destroy or drop a synonym follow the syntax:

DROP SYNONYM synonym_name;

Example:

DROP SYNONYM clerk;

Click here to learn What is Public Synonym

What is Oracle Least Function?

Oracle SQL/PLSQL offers least function that will return least of values from a list provided. The syntax of the functions is:

LEAST (val1, val2, …valn)

Example:

SELECT least(10,9,35) low_value FROM dual;

The output is:

LOW_VALUE

---------------

Note: If there is at least one NULL value in the least, the output will be always NULL. This is because least will not be able to identify the next least value. For avoiding this problem always make sure of using either NVL or DECODE for passing values.

Generating sequential numbers without using user_objects

Sequence number generation in a query has been almost natural in reporting queries. You normally tend to use rownum pseudo column from either your table or if it is a generic query user_objects.

For example:

select rownum from user_objects where rownum <= 100;

The output will be:

ROWNUM

---------------

100

This is based on the assumption that there are more number of objects in user_objects view so as to return a high number. The more robust way to get an output without using such a large view was not literally possible in pre-9i era. Starting 9i Oracle has come up with a beautiful solution. You can use DUAL table in conjunction with CONNECT BY clause to come up with such a result.

Example:

select level from dual connect by level <=100;

This also will generate the same output generated in our first case.

What is Public Synonym?

The web gives me a definition of Synonym as "A word that means the same as another word." Well Oracle also defines its Synonyms in the same way. Then what is a Public Synonym? It is nothing more than a Synonym accessible to all users of a Oracle database.

How to create a Public Synonym?
The syntax for creating a public synonym is

CREATE [ OR REPLACE ] PUBLIC SYNONYM synonym_name FOR object;

Example:

CREATE PUBLIC SYNONYM clerk FOR employee;

In our context employee is a table (or can be a view). As Clerk is also an employee, it makes understanding more easy.

You can query from clerk as you would do from employee table/view. Now you must have got the idea of why a synonym is used.

Note: The usage of a public synonym from private synonym will be where you need to expose your not-so critical data to all database users. If your data is considered private, you should not be creating public synonyms for this purpose.

How to destroy a Public Synonym?

To destroy or drop a public synonym follow the syntax:

DROP PUBLIC SYNONYM synonym_name;

Example:

DROP PUBLIC SYNONYM clerk;

Click here to learn What is (Private) Synonym?

How to kill a process within windows BATch file

This article is result of a friend's query related to "How to kill a windows/DOS process within a windows Batch file.

This is the solution in brief:

START "do something window" dir
FOR /F "tokens=2" %I in ('TASKLIST /NH /FI "WINDOWTITLE eq do something window"' ) DO SET PID=%I
ECHO %PID%
TASKKILL /PID %PID%

FOR Loop is only required if there are multiple processes.

A link is what I am going to provide as for more information:

Click here to access the solution

CONNECT INTERNAL functionality

This is an existing Oracle Support note. This is note #18089.1

1) Introduction

~~~~~~~~~~~~~~~

This article describes the database DBA 'CONNECT INTERNAL' functionality.

In Oracle7 there are 3 special classes of user with privilege to perform special operations (such as shutdown and startup) on the database:

- A DBA user (SYSDBA)

- An OPERATOR user (SYSOPER)

- Connect INTERNAL privilege

This article discusses 'CONNECT INTERNAL' and the issues that determine if a user has permission to connect internal or not. The details here apply to releases 7.0.16 through to 8.1. If using Oracle 7.1.6 or higher Oracle strongly advises you use the SYSDBA or SYSOPER privileges instead as 'CONNECT INTERNAL' may be removed after Oracle 8.1 .

For details of SYSDBA and SYSOPER see <Note: 50507.1>

2) Local Connections

~~~~~~~~~~~~~~~~~~~~

To make local 'internal' connections to an Oracle instance a user must either:

a) Supply a password OR b) Be a user belonging to a special UNIX 'dba' group.

The first of these follows the same rules as defined in Section (3) below so is not discussed in this section. Here we concentrate on the rules for option (b).

2.1) DBA Group

~~~~~~~~~~~~~~

The 'DBA' group is chosen at installation time and is usually the group 'dba' by default.

The DBA group is compiled into the 'oracle' executable and so it the same for all databases running from a given ORACLE_HOME directory. The actual group being used as this

DBA group can be checked thus:

cd $ORACLE_HOME/rdbms/lib

cat config.[cs]

The line '#define SS_DBA_GRP "group"' should name the chosen DBA group. If you wish to change the DBA group change the group name shown in this file.

Eg: Change: #define SS_DBA_GRP "dba"

to: #define SS_DBA_GRP "mygroup"

To effect any changes to the DBA group and to be sure you are using the group defined in this file relink the Oracle executable as below. Be sure to shutdown all databases

before relinking:

Oracle Version < 7.3

~~~~~~~~~~~~~~~~~~~~

rm config.o

make -f oracle.mk config.o

make -f oracle.mk ioracle

Oracle Version >= 7.3

~~~~~~~~~~~~~~~~~~~~~

rm config.o

make -f ins_rdbms.mk config.o

make -f ins_rdbms.mk ioracle

(Note the above instructions may vary depending on your platform / release)

For a DBA group to be accepted by Oracle it must:

- Be compiled into the Oracle executable

- The group name must exist in /etc/group (or in 'ypcat group' if NIS is being used)

- It CANNOT be the group called 'daemon'

2..2) Local Connect Internal

~~~~~~~~~~~~~~~~~~~~

On UNIX systems a user may be a member of more than one group and should be allowed to connect internal without a password provided:

- One of the groups of which they are a member is the DBA group as defined in config.c (config.s on some platforms) and as linked into the 'oracle' executable.

- The DBA group is a valid group as defined in /etc/group (Or as defined in NIS by 'ypcat group')

- The users PRIMARY group (I.e.: the one shown by the 'id' command) is not the

special group 'daemon'.

It is quite common for the 'root' user to be required to have 'connect internal' privilege. Unfortunately it is also common for the root users primary group to be the group 'daemon' which precludes it from being allowed to connect internal. There are two ways to tackle this problem:

a) Make the root users PRIMARY group the DBA group

or b) Where available use the 'newgrp' command to change the users primary group to the DBA group.

Eg: $ newgrp dbagroup

$ sqldba mode=line

SQLDBA> connect internal

This can also be used in shellscripts thus:

newgrp dbagroup <<!

# Commands requiring connect internal privilege

# Eg: dbstart

or c) For systems where 'newgrp' is not available or does not work from scripts you can use 'su' instead. Eg:

su - oracle <<!

# Commands requiring connect internal privilege

Note: The user you 'su' to should be able to 'connect internal' without a password, for example by having their primary group as DBA.

Some Oracle releases have problems with identifying the DBA group when it is not the user’s primary group. If you encounter problems with connect internal and the DBA group is set correctly try making the users primary group the DBA group, or use 'newgrp' as in (b) above.

3) Remote Connect Internal

~~~~~~~~~~~~~~~~~~~~~~

Remote connect internal requires the database to be configured to allow remote DBA operations. The remote user will HAVE to supply a password in order to connect internal. This is a tightening in security from Oracle 6 where remote users could connect internal without a password.

I.e.: In Oracle7 to perform a remote connect internal you must use the syntax 'CONNECT INTERNAL/PASSWORD'

To allow remote internal connections you must:

- Set up a password file for the database on the server

- Set up any relevant init.ora parameters

- Set up any SQL*Net options required

3.1) Setting up a Password File

~~~~~~~~~~~~~~~~~~~~~~~

The connect internal password protection is controlled by an Oracle 'Password' file. The exact commands used to set up this file are different for different Oracle releases but the basic concept is that a special file is created to hold the 'connect internal' password.

Oracle Init.Ora Parameters Command

~~~~~~ ~~~~~~~~~~~~~~~~~~~ ~~~~~~~

7.1.x remote_login_passwordfile orapwd file=<fname> password=<pwd>

7.0.x orapasswd

7.1.x

~~~~~

To create a password file log in as the Oracle software owner and issue the command:

orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypasswd

using the required password. The file name is important and should be specified as above. You should create this file when the database is shut down.

To change a password:

- shut down the database,

- Rename the $ORACLE_HOME/dbs/orapw$ORACLE_SID file

- Issue a new orapwd command with a new password

7.0.x

~~~~~

To create a password file log in as the Oracle software owner and issue the command:

orapasswd

This will prompt for the ORACLE_HOME, the ORACLE_SID and the 'connect internal' password. Enter the required values.

To change a password re-run the 'orapasswd' tool.

3.2) Setting up the Init.Ora file

~~~~~~~~~~~~~~~~~~~~~~

To enable remote internal connections set the init.ora parameters thus:

7.1.x

~~~~~

Set REMOTE_LOGIN_PASSWORDFILE to either EXCLUSIVE or SHARED. EXCLUSIVE forces the password file to be tied exclusively to a single instance. To disable remote internal connections set REMOTE_LOGIN_PASSWORDFILE to NONE.

7.0.x

~~~~~

No init.ora changes are required for 7.0.x releases.

Note: The setting of REMOTE_OS_AUTHENT does NOT affect the ability to connect internal from a remote machine.

3.3) SQL*Net Options

~~~~~~~~~~~~~~~~

From 7.0.16 onwards there are no SQL*Net settings that control the ability to connect internal from remote machines. In particular the settings shown below are no longer required:

SQL*Net V1: The dbaon / dbaoff options are irrelevant

SQL*Net V2: The REMOTE_DBA_OPS_ALLOWED / REMOTE_DBA_OPS_DENIED parameters are irrelevant

4) Bugs and Special Notes

~~~~~~~~~~~~~~~~~~~~~~~~~

Common Errors

~~~~~~~~~~~

ORA-09910: Unable to find ORACLE password file entry for user.

On 7.0.x the $ORACLE_HOME/dbs/orapasswd file cannot be accessed.

ORA-01031: insufficient privileges

Connect Internal has been issued with no password.

For local connections the user is NOT in the DBA group as compiled into the 'oracle' executable.

For remote connections you must always supply a password.

This error can also occur after a successful connect internal/password if there REMOTE_LOGIN_PASSWORDFILE is either unset or set to NONE in the init.ora file.

ORA-01017: invalid username/password; logon denied

This is a fairly general error that indicates one of the following:

- REMOTE_LOGIN_PASSWORDFILE is set to NONE

- The password file does not exist

- The password supplied does not match the one in the password file

- The password file been changed since the instance was started

7.1.x - Deleting/Changing the 'orapw$SID' File <Bug:262100>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If you delete the Oracle password file while the instance is running you will NOT be able to connect internal from remote machines, even if you re-create the file. You must:

- Shutdown the instance (using a local connection)

- Create the new password file

- You can now connect internal remotely and restart the instance

7.1.3 - REMOTE_LOGIN_PASSWORDFILE ignored <Bug:209449>

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

If a password file exists at all then you can perform remote CONNECT INTERNAL operations even if the value of REMOTE_LOGIN_PASSWORDFILE is set to NONE (the default).

The workaround is to delete the password file to prevent remote internal connections.

7.1,7.2 - Security Loophole <Bug:321727>

~~~~~~~~~~~~~~~~~~~~~~~~~~~

Be aware that there is a potential security loophole in the orapwd implementation in Oracle 7.1 and 7.2. Details of this are NOT disclosed so as not to compromise existing users of the option. The fix is in Oracle 7.3.2.

What is Oracle Application Express?

Oracle Application Express or APEX as it is famously known is a rapid application development for the web closely coupled with Oracle's flagship product, its database. It was earlier known as HTML DB.

It's key features are:
Reporting
Forms
Charting
PDF Printing
Web Services
Spreadsheet Upload
Session State Management
User Interface Themes
Flow Control & Navigation
Conditionality on all Components
External Interfaces & Extensibility
Declarative BLOB Support
Optional Runtime-Only Installation
Security
SQL Workshop Tools
Supporting Objects Utility
Performance
Packaged Applications
Hosted Development

For more information read through What is Apex

Converting Forms using APEX-By David Peake

Modernize Oracle Forms applications with Oracle Application Express.

Oracle Forms has been around since before the days of client/server computing, and numerous Oracle Forms applications have been faithfully meeting business requirements for many years.

Converting Oracle Forms applications to use another development tool is a labor-intensive, time-consuming process, and recent rumors that Oracle will soon drop support for Oracle Forms are unfounded. With these things in mind, why would anyone want to convert their Oracle Forms applications to use another development tool?

Typically, Oracle Forms modernization projects convert legacy applications to use the latest Oracle Database version and enable developers to satisfy user demands for greater user interactivity and Web 2.0 capabilities. A modernization option for Oracle Forms applications that will deliver on both of these objectives is Oracle Application Express.

Oracle Application Express renders applications using HTML, and it has built-in Web 2.0 functionality such as interactive reports, Flash charts, and the ability to extend user interactivity by use of JavaScript and Asynchronous JavaScript and XML (Ajax). In addition, the rapid application development environment offered by Oracle Application Express is similar to that found in Oracle Forms, utilizing a declarative framework together with SQL and PL/SQL to define processes and enter business logic.

Understanding the Conversion Process

The capabilities for converting from Oracle Forms to Oracle Application Express in Oracle Application Express 3.2 generate an initial application design that you then enhance and expand to fully replicate the original application. The conversion maps blocks to pages and various item types, but it does not convert business logic stored in Oracle Forms triggers, program units, or libraries. After you generate the initial design, you can take full advantage of the rapid application development capabilities in Oracle Application Express to enhance the generated pages. Figure 1 describes the process flow of an application migration from Oracle Forms to Oracle Application Express.

Read the full article here:Oracle Magazine

Conclusion

Oracle Forms conversion in Oracle Application Express is not designed to fully replicate an Oracle Forms application. The conversion process does, however, offer significant benefit by generating user interface components and providing project management tracking capabilities.

The effort and resources required for each Oracle Forms conversion project are largely dependent on the size, complexity, and business logic implementation within the Oracle Forms application. I strongly recommend that you undertake a prototype or a proof of concept to properly plan each Oracle Forms conversion project before you begin.

David Peake is a principal product manager in Oracle's Server Technologies division. He has been with Oracle since 1993.

Wipro BPO partnered with Oracle

BANGALORE: Wipro Technologies said on Monday that its Business Process Outsourcing division, Wipro BPO has partnered with Oracle for 'best-of-breed HR platform solutions.'

Wipro has also selected The Hackett Group, a global strategic advisory firm, to provide empirical data, best practices and world-class performance insights on the development of its innovative bundled solution platform, it said in a release here.

The solution simPlify, allows employers to reduce and control cost as it provides an opportunity to centralize and standardize processes while eliminating duplicative management structures.

The solution has the ability to leverage a many-to-one technology capability, while maximizing service quality and HR customer satisfaction.

From News: Economic Times

Oracle buys Sun

Oracle announced today they have entered into a definitive agreement under which Oracle will acquire Sun at $9.50 a share in cash. The deal has been unanimously approved by Sun's board, but still needs to be approved by Sun's shareholders and financial regulators, the companies said.

"Oracle will be the only company that can engineer an integrated system — applications to disk — where all the pieces fit and work together so customers do not have to do it themselves", said Larry Ellison.

The deal means Oracle will be taking over the Java language and software, which underpins Oracle Fusion Middleware. Also Oracle picks up the Solaris operating system, described by the companies as the leading platform for Oracle's database products. However Oracle is as committed as ever to Linux and other open platforms, the company said.

QUERY parameter in Export Utility

This parameter is used in conjunction with TABLE parameter of exp (export) utility of Oracle. This parameter will enable selection of rows from the list of tables mentioned in TABLE parameter. The value to this parameter is a WHERE clause for a SELECT statement which you would normally issue.

For example if you want to query all records of employees for a particular department you will use:

SELECT *
FROM employees
WHERE dept = 10;

To export these rows into a file using exp utility you will follow the below syntax:

exp scott/tiger TABLES=employees QUERY=\"WHERE dept=10\"

Use \ for providing character or special characters like less than or greater than symbol inside the string. Also for operating system keywords you need to place \ as escape character.

For example:

exp scott/tiger TABLES=employees QUERY=\"WHERE name=\ANANTHA\' and sal \<1600\" The default value for QUERY parameter is NULL. Note: If there are multiple tables in TABLES parameter, this QUERY will be applied to all those tables.

You can also use ROWID for exporting, for example:

exp scott/tiger@slspnc1 tables=emp query=\"where ROWID='AAAMgzAAEAAAAAgAAB'\" file=test.dmp log=test.log

In Windows it is not required to put escape character for single quote character wheras you need to put escape character if you are using Linux or its variants.

exp scott/tiger@slspnc1 tables=emp query=\"where ROWID=\'AAAMgzAAEAAAAAgAAB\'\" file=test.dmp log=test.log

Restrictions When Using the QUERY Parameter:
The QUERY parameter cannot be specified for full, user, or tablespace-mode exports.
The QUERY parameter must be applicable to all specified tables.
The QUERY parameter cannot be specified in a direct path Export (DIRECT=y)
The QUERY parameter cannot be specified for tables with inner nested tables.
You cannot determine from the contents of the export file whether the data is the result of a QUERY export.

ORA-01658: Unable to create INITIAL extent for segment in tablespace %s

** Solved **

You will encounter this error when a tablespace is full or cannot extend automatically.

These are the possible solutions (if there are any other ways add a comment):

Solution 1: Resize Tablespace

alter database datafile '' resize M;

Solution 2: Add Datafile

alter tablespace tablespace_name add datafile '' size M autoextend M maxsize M|off>;

How to Pin objects to shared Pool?

How to Pin objects to shared Pool?
Pinning objects to the shared pool is a key to tuning your shared pool. Having objects pinned will reduce fragmentation and changes of encountering the ORA-04031 error.

You must determine which objects to pin. These are particular to your own database, the application you are running, the size of your database, and the activity on your database.

You need to pin objects when any of these happen:
1. If you have encountered the ORA-04031 already and need to resolve it or
2. You can also pin large packages frequently used by the users. (It is better to pin necessary objects while startup of database.

Pinning a package to shared pool

1. Oracle automatically loads SYS.STANDARD, SYS.DBMS_STANDARD and SYS.DIUTIL. Here is an example:

pk1 is a package with a variable called dummy. Assigning dummy to a value and then executing the package will load it into the shared pool:

Example:-

begin

pk1.dummy := 0 ; /* THIS ASSIGNMENT TO THE DUMMY VARIABLE IS BY */

end; /* EXECUTING THE PACKAGE. */

2.Then you must pin the package. Here is an example:

execute dbms_shared_pool.keep(owner.pk1);

Pinning stored procedure/function to shared pool

You can pin procedures and triggers with the dbms_shared_pool procedure. Either procedures or packages can be pinned with the 'P' flag, which is the default value (so you can leave it out). Triggers are pinned with 'R' and anonymous plsql blocks need any letter other than [p,P,r,R] as a flag. Refer to dbmspool.sql for more documentation.

Here is an example:

execute dbms_shared_pool.keep(owner.trigger, 'R');

What objects should I pin?

You can check the x$ksmlru fixed table. This table keeps track of the objects and the corresponding number of objects flushed out of the shared pool to allocate space for the load. These objects are stored and flushed out based on the Least Recently Used (LRU) algorithm.

Because this is a fixed table, once you query the table, Oracle will automatically reset the table, thus, you can only query the table once. Suggestion for workaround: spool the output to a file so you can capture the output for analysis.

describe x$ksmlru

Table or View x$ksmlru

Name Null? Type

------------------------------- -------- -------------- ADDR RAW(4)

INDX NUMBER

KSMLRCOM VARCHAR2(20)

KSMLRSIZ NUMBER

KSMLRNUM NUMBER

KSMLRNUM stores the number of objects that were flushed to load the large object. KSMLRISZ stores the size of the object that was loaded (contiguous memory allocated)

We do not need the other columns.

Here is an example of a query you issue to find all the objects that are larger than size 5k which you may want to pin:

select * from x$ksmlru where ksmlrsiz > 5000;

In general, pinning SYS.STANDARD ,SYS.DBMS_STANDARD & SYS.DIUTIL which are large packages used by Oracle, should help.

SYS.DIUTIL is used only during generations of SQL*forms so it may not be necessary to pin this package in your production database.

ORA-06502: What Oracle Documentation says about this error?

Here is what Oracle documentation says about this error:

ORA-06502: PL/SQL: numeric or value error string

Cause: An arithmetic, numeric, string, conversion, or constraint error occurred. For example, this error occurs if an attempt is made to assign the value NULL to a variable declared NOT NULL, or if an attempt is made to assign an integer larger than 99 to a variable declared NUMBER (2).

Action: Change the data, how it is manipulated, or how it is declared so that values do not violate constraints.

More Information:

These could be the errors:

This error mainly occurs due to one of the following:
1. If you assign NULL value to a NOT NULL field.
2. If you assign number which is larger than the precision of the field.
3. If you assign character values greater than the field width.

Click here to read more on solving this error

ORA-04031: shared memory error

How to solve this error?

1. Find out which application is causing this error. Zero down on which package/procedure is loaded and try to keep it in shared pool by pinning it.
2. Sometimes, the application may not give the errors. In which case, set an event in init.ora, as follows and generate a trace file.

event = "4031 trace name errorstack level 2"

or with 9i and higher and spfiles you can issue

alter system set events='4031 trace name errorstack level 2';

What to look for in the trace?
The trace contains a dump of state objects, when the error occurs.

Look for 'load=X' a few lines below that 'name='[name of the object]. So, this error occurs at the time loading this object. Pin that object in the shared pool, thereby keeping it.

How to Pin objects to shared Pool?
Pinning objects to the shared pool is a key to tuning your shared pool. Having objects pinned will reduce fragmentation and changes of encountering the ORA-04031 error.

You must determine which objects to pin. These are particular to your own database, the application you are running, the size of your database, and the activity on your database.

You need to pin objects when any of these happen:
1. If you have encountered the ORA-04031 already and need to resolve it or
2. You can also pin large packages frequently used by the users. (It is better to pin necessary objects while startup of database.

Pinning a package to shared pool

1. Oracle automatically loads SYS.STANDARD, SYS.DBMS_STANDARD and SYS.DIUTIL. Here is an example:

pk1 is a package with a variable called dummy. Assigning dummy to a value and then executing the package will load it into the shared pool:

Example:-

begin

pk1.dummy := 0 ; /* THIS ASSIGNMENT TO THE DUMMY VARIABLE IS BY */

end; /* EXECUTING THE PACKAGE. */

2.Then you must pin the package. Here is an example:

execute dbms_shared_pool.keep(owner.pk1);

Pinning stored procedure/function to shared pool

Here is an example:

execute dbms_shared_pool.keep(owner.trigger, 'R');

What objects should I pin?

describe x$ksmlru

Table or View x$ksmlru

Name Null? Type

------------------------------- -------- -------------- ADDR RAW(4)

INDX NUMBER

KSMLRCOM VARCHAR2(20)

KSMLRSIZ NUMBER

KSMLRNUM NUMBER

KSMLRNUM stores the number of objects that were flushed to load the large object. KSMLRISZ stores the size of the object that was loaded (contiguous memory allocated)

We do not need the other columns.

Here is an example of a query you issue to find all the objects that are larger than size 5k which you may want to pin:

select * from x$ksmlru where ksmlrsiz > 5000;

In general, pinning SYS.STANDARD ,SYS.DBMS_STANDARD & SYS.DIUTIL which are large packages used by Oracle, should help.

SYS.DIUTIL is used only during generations of SQL*forms so it may not be necessary to pin this package in your production database.

Subscribe to: Posts ( Atom )

More Information:

Labels