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)

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?

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;


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


"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

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.

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.