Ask Anantha

From Technologies to Solutions: Oracle 10g/11g Data and Database Management Utilities by 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,...

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 ...

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 .

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.

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 req...

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 wir...

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...

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 ...

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 --------------- 9 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.

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 The output will be: ROWNUM --------------- 1 2 3 . . 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 This also will generate the same output generated in our first case.

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 sy...

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

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> ...

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

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 HTM...

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 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.

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 \ You can also use ROWID for exporting, for example: exp scott/tiger@slspnc1 tables=emp query=\"where ROWID='AAAMgzAAEAAAAA...

** 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? 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  ...

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  

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-0403...