How to get table structure in Oracle?

There is one data dictionary table called user_tab_columns which store metadata about table.
In this the column_name, data_type and data_length are usually we want.
For this purpose.

SQL> set lines 200
SQL> select column_name,data_length,data_type from user_tab_columns where table_name = 'DEPT';

Output of above query will show following metadata of DEPT table.

COLUMN_NAME DATA_LENGTH DATA_TYPE
------------------------------ ----------- -------------------
DEPTNO 22 NUMBER
DNAME 14 VARCHAR2
LOC 13 VARCHAR2

How to spool data in Excel file?

Hi friends, sometimes we require to spool or export resultset of sql query in excel file without using third party tool. Here is very simple example of How to spool data in Excel file?
Excample:
SQL> spool c:\dept.csv
SQL> select dname || ',' || deptno from dept;

DEPTNO','DNAME
--------------------------------------------------------------------------------
10,ACCOUNTING
20,RESEARCH
30,SALES
40,OPERATIONS

SQL> spool off

Hope this will help you.

Single click login to Oracle Database

If you are tired to type login/password repeadetly then this is a nice stuff to implement.

1. open notepad in windows
2. type the following line :
3. sqlplusw scott/tiger
4. now save the file as sa.bat on desktop
5. now click on this batch file
6. if you are accessing from oracle client then you need to put tns name in syntax of step 3 like following:
7. sqlplusw scott/tiger@orcl

See what happens.......

What is SQL Developer?

Oracle SQL Developer is a free graphical tool for database development. With SQL Developer, you can browse database objects, run SQL statements and SQL scripts, and edit and debug PL/SQL statements. You can also run any number of provided reports, as well as create and save your own. SQL Developer enhances productivity and simplifies your database development tasks.

Oracle DBA Required Skills & Responsibilities

Required Skills:

• Good understanding of the Oracle database, related utilities and tools
• A good understanding of the underlying operating system
• A good knowledge of the physical database design
• Ability to perform both Oracle and operating system performance tuning and monitoring
• Knowledge of ALL Oracle backup and recovery scenarios
• A good knowledge of Oracle security management
• A good knowledge of how Oracle acquires and manages resources
• A good knowledge Oracle data integrity
• Sound knowledge of the implemented application systems
• Experience in code migration, database change management and data management through the various stages of the development life cycle
• A sound knowledge of both database and system performance tuning
  
• A DBA should have sound communication skills with management, development teams, vendors and systems administrators
• Provide a strategic database direction for the organisation
• A DBA should have the ability to handle multiple projects and deadlines
• A DBA should possess a sound understanding of the business

DBA Responsibilities:

• Installation, configuration and upgrading of Oracle server software and related products
• Evaluate Oracle features and Oracle related products
• Establish and maintain sound backup and recovery policies and procedures
• Take care of the Database design and implementation
• Implement and maintain database security (create and maintain users and roles, assign privileges)
• Perform database tuning and performance monitoring
• Perform application tuning and performance monitoring
• Setup and maintain documentation and standards
• Plan growth and changes (capacity planning)
• Work as part of a team and provide 7x24 support when required
• Perform general technical trouble shooting and give consultation to development teams
• Interface with Oracle Corporation for technical support.