Blending Notes: Oracle 11g & SQL Server 2012-The structure of a table

There are multiple ways of retrieve table information. Few days ago I was desperately looking for an Equivalent Command to Oracle command of 'DESC' OR 'DESCRIBE' in SQL SERVER because I badly wanted to get some information about an existing database. This post mainly focus on describing MS SQL Server table using sp_columns and ‘DESCRIBE’ command of Oracle which retrieves column information for the specified table.

For an instance, Employee table includes information like table name, table owner, details about columns, and its physical storage size information referred to as metadata.

These kind of information of a table can retrieves by using the DESCRIBE command of Oracle.

Syntax- Oracle:

• ¿       DESC[RIBE] <SCHEMA>.tablename

DESCRIBE key word can be shortened to DESC and schema can be omitted. 

Describing EMPLOYEE table using DESCRIBE command in Oracle 

 Syntax- SQL SERVER:

•      EXEC sp_columns @table_name = ‘EMPLOYEE’;

Above catalog stored procedure returns column information for the EMPLOYEE table also has ability to specifically mention the object owner of the table as well. If you want to retrieve only one column of catalog information you can simply specified as follows,

Walkthrough SQL Server 2012: AdventureWorks

Where did I go wrong with Adventureworks ? 

Adventureworks sample database launched with SQL Server 2012. Earlier we used Northwind and Pubs sample databases however Adventureworks has become a vital aspect of learning new features within SQL Server 2012 and you can downloaded from CodePlexsite(http://msftdbprodsamples.codeplex.com/releases ). 

Every so often I run into something that seems so simple that should just work but doesn’tL .  I tried to attach the Adventureworks sample database (AdventureWroks2012 Data File) using SQL Server Management Studio as follows, 

® Right Click on Databases  
® Attach 
® click Add 
® select the AdventureWorks mdf file

SQL*Loader

SQL*Loader
	Conventional path		Direct path
	·         Uses the database buffer cache		·         Avoid the database buffer cache
Method of Saving Data	Loads use SQL processing and a database COMMIT operation for saving data. The insertion of an array of records is followed by a COMMIT operation. Each data load may involve several transactions.		·         Use data saves to write blocks of data to Oracle data files.  ·         faster than the conventional path
The following features differentiate a data save from COMMIT: •         During a data save, only full database blocks are written to the database. •         The blocks are written after the high-water mark (HWM) of the table. •         After a data save, the HWM is moved. •         Internal resources are not released after a data save. •         A data save does not end the transaction. •         Indexes are not updated at each data save.
Logging Changes		generates undo and redo data (Always generates redo entries ) ------------------------------------------------ loading generates redo entries that are similar to any DML statement	No undo data is generated, Ability to switch off the redo Generates redo only under specific conditions
Enforcing Constraints		All enabled constraints are enforced in the same way that they are during any DML operation. Use INSERT statements (Enforces all constraints)	Table is locked for DML Only below constraints can be enforced; ·         UNIQUE ·         PRIMARY KEY ·         NOT NULL
Firing the INSERT Triggers		Fires INSERT triggers The WHILE INSERT triggers are fired during conventional loads	Does not fire INSERT triggers They are disabled before a direct path load and re enabled at the end of the load
Loading into Clustered Tables		Clustered tables can be loaded with conventional path loads only	Direct loads cannot be used to load rows into clustered tables
Locking		Allows other users to modify tables during load operation	Prevents other users from making changes to tables during load operation While a direct path load is in progress, other transactions cannot make changes to the tables that are being loaded. The only exception to this rule is when several parallel direct load sessions are used concurrently.

Shortcuts to losing your data

                 

Please stand by..

We are Experiencing Technical Difficulties !!!

Loss of DATA can break a business.It will effect to the business objective and that can be influence into organisation, information system and security program. Threats have a significant impact on Databases and it can be physical or non physical threats. Data can be misplaced because of hardware failures, data can mistakenly delete from user, data corruption, and environmental disruption.

According to research by the University of Texas, “Only six percent of companies suffering from a catastrophic data loss survive, while 43 percent never reopen and 51 percent close within two years.” Further it state that, some of the organisation never recover from major losses of data. Jim Martin, the chief operating officer with Worldport Ireland state that, majority of data in organisations is not stored in central repository off site. World Trade Center is one of the convenient locations for business and most of the financial and investment organisations are located, which was destroyed in the attack on 2001. Hardware failures, breakdowns are almost natural incidence on occasion, users are no longer able to access the data on the databases and it has major impact on large amounts of corporate data. These kinds of situations emphasise the importance of proper disaster and recovery plan. Also importance of maintain a remote site replication of data is an efficient means of protection.

1.     User Error

Human error causes most data loss; Erroneous updates, delete from database or can be overwriting. Deleting the contents of a table and dropping database objects can mistakenly happen. 

2.     Data Corruption

Data can be corrupt because of the Software bugs or Virus attacks. 

3.     Media Failure

A failure within the electronic circuits or hardware components may cause for data loss.

4.     Disaster

Data can loss because of natural event or human behavior such as Terrorist attacks. It can be electrical Failures or complete loss of a power to a geographic area because of weather related (earthquakes, tsunamis) or other causes of power outage.

Oracle and SQL server both provide strong recoverability features; whereas in some aspects superior to the other. ORACLE supports incremental backups which is a strong option when managing large scale databases, where this has to be accomplished through transaction log applying in SQL server. A scheduled oracle database with full, differential and incremental backups would guarantee 100% recoverability along with the support of Data guard. Database mirroring is equivalent to oracle’s data guard, but important features oracle offer is yet unavailable in SQL server database mirroring,

Comparing Database snapshot with oracle’s flashback, oracle stands way up high. Oracle flashback is as easy as issuing a query where in SQL server snapshot has to be created and then look for the changes.

Data corruption and human error causes most data loss, in this scenario it is important to back up data from database since there is no option other than to recreate the data. Data can be corrupt because of the virus attacks or bugs however it can be directly effect to entire database, its associated table or few files.

In the event of a disaster, Property, computer equipment can be caused and valuable data can be loss.  Recovery from this type of data loss requires hardware redundancy and mirroring. There should be a proper disaster recovery plan for database with manageable process. Because of the large quantity of data organisations should ensure the backup strategy and backup medium is stored at an offsite location.

Considering the options offered by ORACLE and SQL server it was evident that ORACLE is in the lead and rich in options. It offers smoother restoration, recovery, zero data loss and also easy recovery from user errors. 

Data as a Corporate Asset

As businesses today face more competition, organisations spend millions on updating their systems. They are finding new solutions to increase the effectiveness of operations to provide innovative services in order to survive customer base.

Mainly, large scale organisations consider data as a corporate asset because it has direct involvement to the growth of business. Although data is useful to the business there are several key challenges that need to be addressed.

Large scale organisations always deal with a vast quantity of data hence it can be extremely difficult to manage data.  They collect and store huge amount of data regarding the vendors, customers, employees, inventory, markets and competitors. Generally, this helps to make better decisions in the ever increasing reliance of businesses. It is important to protecting data against all causes of loss or damage.

Data is Vital to any organisation and large scale organisations have several databases to store different kinds of data. Because of the rapid growth of data in the organisation raises the data accuracy and protection. When there is data duplicates, incomplete or inaccurate data it directly impact to the customer satisfaction loyalty.  Data has quantifiable value that is important to achieve business objectives while considering the business strategies of the organisation. However data needs to be accurate and up to date, accessible and secure, usable and well-governed when managing the data.

Data and information are vital resource in an organisation and it should be properly manage and secured. They provide more attention on databases since corporate organisational data and information have become the lifeblood of large scale organisations and frequently generating large volumes of data.

However Customer data are just as valuable as money. Loss of data can break a business and it will effect to the business objective. It is important to give more attentions on the area of data protection in an effective and efficient way.