Sunday, December 11, 2011

Oracle Database 11g Express Edition for Windows Download

Free to develop, deploy, and distribute

Oracle Database 11g Express Edition (Oracle Database XE) is an entry-level, small-footprint database based on the Oracle Database 11g Release 2 code base.  It's free to develop, deploy, and distribute; fast to download; and simple to administer. 
Oracle Database XE is a great starter database for:

  • Developers working on PHP, Java, .NET, XML, and Open Source applications
  • DBAs who need a free, starter database for training and deployment
  • Independent Software Vendors (ISVs) and hardware vendors who want a starter database to distribute free of charge
  • Educational institutions and students who need a free database for their curriculum.

    Download Link for Oracle 11g Express Edition for Windows


    Download Link for Oracle 11g Express Edition for Linux

     

Friday, December 9, 2011

MyOra - Free SQL Tool for Oracle


MyOra is a free SQL Tool for Oracle database Developers, DBAs and Companies. This tool is simple, fast and easy to use, requires no installation, no Oracle client and  no internet connection. Download MyOra to run SQL queries and Monitor database performance in Real Time.

Please download the below link

Date wise cumulative count in Mysql


Date wise cumulative count

Dear all,
I can take date wise count using group by in mysql.But i want date wise cumulative count in mysql.How?

For example, 

date count 
10-11-2011 200 
11-11-2011 500 
12-11-2011 700 

This is day wise count. 

But i want day wise cumulative count. 

date count 
10-11-2011 200 
11-11-2011 700 
12-11-2011 1400 

like this.


Answer

You can do that with a self-join, but only works with a single value per day:

CREATE TABLE t1 (DateField TIMESTAMP, CountValue INT);
INSERT INTO t1 VALUE ("2011-11-10",200),("2011-11-11",500),("2011-11-12",700);

SELECT t1a.DateField, SUM(t1b.CountValue) FROM t1 t1a, t1 t1b WHERE t1a.DateField>=t1b.DateField GROUP BY t1a.DateField;

datefield sum(t1b.countvalue)
2011-11-10 200
2011-11-11 700
2011-11-12 1400

If you have more than one value per date, you can probably make it work with a derived table.

Get tablename and schema names in PostGresql



select distinct schemaname from pg_tables where schemaname not in
('information_schema', 'pg_catalog');

select  schemaname,tablename from pg_tables

select tablename from pg_tables where schemaname='name_of_schema';

select * from pg_tables

postgres Backup and restore commands



postgres restore

C:/Program Files/PostgreSQL/8.4/bin\pg_restore.exe --host localhost --port 5432 --username postgres --dbname kanyakumari --verbose "D:\MySQL_From_SivaGanesh_HDD1\kanyakumari.backup"

Backup
C:/Program Files/PostgreSQL/8.4/bin\pg_dump.exe --host localhost --port 5432 --username postgres --format custom --verbose --file "D:\MySQL_From_SivaGanesh_HDD1\kanyakumari.backup_dist03.backup" --schema dist03 kanyakumari

Backup
C:/Program Files/PostgreSQL/8.4/bin\pg_dump.exe --host localhost --port 5432 --username postgres --format custom --blobs --verbose --file "D:\MySQL_From_SivaGanesh_HDD1\kanyakumari.backup_dist03.backup" kanyakumari

Thursday, December 8, 2011

What is Database Maintenance?


                Database maintenance is an activity which is designed to keep a database running smoothly. A number of different systems can be used to build and maintain databases, with one popular example being MYSQL. Maintenance of databases is generally performed by people who are comfortable and familiar with the database system and the specifics of the particular database, although some maintenance tasks can be performed by people who do not have experience.
One important aspect of database maintenance is simply backing up the database so that if anything happens, there will be another copy available. Some databasing systems actually do this automatically, sending a backup to another location every day, every week, or within any other set period of time. However, backups are not quite enough.
                  Database maintenance includes checking for signs of corruption in the database, looking for problem areas, rebuilding indexes, removing duplicate records, and checking for any abnormalities in the database which might signal a problem. The goal is to keep the database operating smoothly for users, so that ideally they never need to think about maintenance issues. A database which is not maintained can become sluggish, and people may start to experience problems when trying to access records.
                 Many servers have extensive databases which are used to serve up content to users on an internal network or on the Internet. An important part of server maintenance is database maintenance to confirm that these databases are working properly. This also includes checks for security flaws and other issues which could threaten the integrity of the database, ranging from viruses to records which are entered improperly.

Thursday, December 1, 2011

DIFFERENCE BETWEEN INNODB AND MyISAM


InnoDB is a high-reliability and high-performance storage engine for MySQL. Starting with MySQL 5.5, it is the default MySQL storage
engine. Key advantages of InnoDB include:
• Its design follows the ACID model, with transactions featuring commit, rollback, and crash-recovery capabilities to protect user
data.
• Row-level locking and Oracle-style consistent reads increase multi-user concurrency and performance.
• InnoDB tables arrange your data on disk to optimize common queries based on primary keys. Each InnoDB table has a primary
key index called the clustered index that organizes the data to minimize I/O for primary key lookups.
• To maintain data integrity, InnoDB also supports FOREIGN KEY referential-integrity constraints.
• You can freely mix InnoDB tables with tables from other MySQL storage engines, even within the same statement. For example,
you can use a join operation to combine data from InnoDB and MEMORY tables in a single query.


MyISAM: The MySQL storage engine that is used the most in Web, data warehousing, and other application environments. MyISAM
is supported in all MySQL configurations, and is the default storage engine prior to MySQL 5.5.5.


Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate
the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI
(MYIndex) extension.



Saturday, April 2, 2011

Data Pump Export (expdp) and Data Pump Import(impdp) in Oracle


Data Pump Export (expdp) and Data Pump Import (impdp) are server-based rather than client-based as is the case for the original export (exp) and import (imp). Because of this, dump files, log files, and sql files are accessed relative to the server-based directory paths. Data Pump requires that directory objects mapped to a file system directory be specified in the invocation of the data pump import or export.
SQL> create directory expdp_dir as '/u01/backup/exports';
then grant read and write permissions to the users who will be performing the data pump export and import.
SQL> grant read,write on directory expdp_dir to system, user1, user2, user3;
Invoking Data Pump Export
You can invoke the data pump export using a command line. Export parameters can be specified directly in the command line.
Full Export Mode
A full export is specified using the FULL parameter. In a full database export, the entire database is unloaded. This mode requires that you have the EXP_FULL_DATABASE role. Example :
$ expdp system/ DIRECTORY=expdp_dir DUMPFILE=expfull.dmp FULL=y LOGFILE=expfull.og
Schema Export Mode
The schema export mode is invoked using the SCHEMAS parameter. If you have no EXP_FULL_DATABASE role, you can only export your own schema. If you have EXP_FULL_DATABASE role, you can export several schemas in one go.Optionally, you can include the system privilege grants as well.
$ expdp hr/hr DIRECTORY=expdp_dir DUMPFILE=schema_exp.dmp SCHEMAS=hr,sh,oe
Table Export Mode
This export mode is specified using the TABLES parameter. In this mode, only the specified tables, partitions and their dependents are exported. If you do not have the EXP_FULL_DATABASE role, you can export only tables in your own
schema. You can only specify tables in the same schema.
$ expdp hr/hr DIRECTORY=expdp_dir DUMPFILE=tables_exp.dmp TABLES=employees,jobs,departments
Invoking Data Pump Import
The data pump import can be invoked in the command line. The export parameters can be specified directly in the command line.
Full Import Mode The full import mode loads the entire contents of the source (export) dump file to the target database.However, you must have been granted the IMP_FULL_DATABASE role on the target database. The data pump import is invoked using the impdp command in the command line with the FULL parameter specified in the same command line.
$ impdp system/ DIRECTORY=expdp_dir DUMPFILE=expfull.dmp FULL=y LOGFILE=impfull.og Schema Import Mode
The schema import mode is invoked using the SCHEMAS parameter. Only the contents of the specified schemas are load into the target database. The source dump file can be a full, schema-mode, table, or tablespace mode export files. If you have a IMP_FULL_DATABASE role, you can specify a list of schemas to load into the target database.
$ impdp hr/hr DIRECTORY=expdp_dir DUMPFILE=expfull.dmp SCHEMAS=hr,sh,oe
Table Import Mode
This export mode is specified using the TABLES parameter. In this mode, only the specified tables, partitions and their dependents are exported. If you do not have the EXP_FULL_DATABASE role, you can import only tables in your own schema.
$ impdp hr/hr DIRECTORY=expdp_dir DUMPFILE=expfull.dmp TABLES=employees,jobs,departments

Oracle export and import
Their prime purpose is to move logical objects out of and into the database respectively - for example dumping all of the tables owned by a user to a single file is achieved using the exp utility.
Type 'imp help=y' or 'exp help=y' for a detailed explanation of the available options for these utilities.

Using exp:
To export the entire database to a single file dba.dmp in the current directory.
- Login to server
exp SYSTEM/password FULL=y FILE=dba.dmp LOG=dba.log CONSISTENT=y
or
exp SYSTEM/password PARFILE=params.dat
where params.dat contains the following information
FILE=dba.dmp
GRANTS=y
FULL=y
ROWS=y
LOG=dba.log
To dump a single schema to disk (we use the scott example schema here)
- Login to server which has an Oracle client
exp / FIlE=scott.dmp OWNER=scott
To export specific tables to disk:
- Login to server which has an Oracle client
exp SYSTEM/password FIlE=expdat.dmp TABLES=(scott.emp,hr.countries)
-the above command uses two users : scott and hr
exp / FILE=scott.dmp TABLES=(emp,dept)
the above is only for one user
Using imp:
To import the full database exported in the example above.
imp SYSTEM/password FULL=y FIlE=dba.dmp
To import just the dept and emp tables from the scott schema
imp SYSTEM/password FIlE=dba.dmp FROMUSER=scott TABLES=(dept,emp)
To import tables and change the owner
imp SYSTEM/password FROMUSER=blake TOUSER=scott FILE=blake.dmp TABLES=(unit,manager)
To import just the scott schema exported in the example above
imp / FIlE=scott.dmp

Oracle Interview Questions


What Is Oracle?
Oracle is a company. Oracle is also a database server, which manages data in a very structured way. It allows users to store and retrieve related data in a multiuser environment so that many users can concurrently access the same data. All this is accomplished while delivering high performance. A database server also prevents unauthorized access and provides efficient solutions for failure recovery.
What Is an Oracle Database?
An Oracle database is a collection of data treated as a big unit in the database server.
What Is an Oracle Instance?
Every running Oracle database is associated with an Oracle instance. When a database is started on a database server (regardless of the type of computer), Oracle allocates a memory area called the System Global Area (SGA) and starts one or more Oracle processes. This combination of the SGA and the Oracle processes is called an Oracle instance. The memory and processes of an instance manage the associated database's data efficiently and serve the one or multiple users of the database.
What Is a Parameter File?
A parameter file is a file that contains a list of initialization parameters and a value for each parameter. You specify initialization parameters in a parameter file that reflect your particular installation. Oracle supports the following two types of parameter files:
  • Server Parameter Files - Binary version. Persistent.
  • Initialization Parameter Files - Text version. Not persistent.
What Is a Server Parameter File?
A server parameter file is a binary file that acts as a repository for initialization parameters. The server parameter file can reside on the machine where the Oracle database server executes. Initialization parameters stored in a server parameter file are persistent, in that any changes made to the parameters while an instance is running can persist across instance shutdown and startup.
What Is a Initialization Parameter File?
An initialization parameter file is a text file that contains a list of initialization parameters. The file should be written in the client's default character set. Sample initialization parameter files are provided on the Oracle distribution medium for each operating system. A sample file is sufficient for initial use, but you will probably want to modify the file to tune the database for best performance. Any changes will take effect after you completely shut down and restart the instance.
What is System Global Area (SGA)?
The System Global Area (SGA) is a memory area that contains data shared between all database users such as buffer cache and a shared pool of SQL statements. The SGA is allocated in memory when an Oracle database instance is started, and any change in the value will take effect at the next startup.
What is Program Global Area (PGA)?
A Program Global Area (PGA) is a memory buffer that is allocated for each individual database session and it contains session specific information such as SQL statement data or buffers used for sorting. The value specifies the total memory allocated by all sessions, and changes will take effect as new sessions are started.
What Is a User Account?
A user account is identified by a user name and defines the user's attributes, including the following:
  • Password for database authentication
  • Privileges and roles
  • Default tablespace for database objects
  • Default temporary tablespace for query processing work space
What Is the Relation of a User Account and a Schema?
User accounts and schemas have a one-to-one relation. When you create a user, you are also implicitly creating a schema for that user. A schema is a logical container for the database objects (such as tables, views, triggers, and so on) that the user creates. The schema name is the same as the user name, and can be used to unambiguously refer to objects owned by the user.
What Is a User Role?
A user role is a group of privileges. Privileges are assigned to users through user roles. You create new roles, grant privileges to the roles, and then grant roles to users.
What is a Database Schema?
A schema is a collection of logical structures of data, or schema objects. A schema is owned by a database user and has the same name as that user. Each user owns a single schema. Schema objects can be created and manipulated with SQL and include: tables, views, and other types of data objects.
What Is a Database Table?
A database table is a basic unit of data logical storage in an Oracle database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a datatype, such as VARCHAR2, DATE, or NUMBER; and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record.
What Is a Table Index?
Index is an optional structure associated with a table that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.
What Is an Oracle Tablespace?
An Oracle tablespace is a big unit of logical storage in an Oracle database. It is managed and used by the Oracle server to store structures data objects, like tables and indexes.
Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.
What Is an Oracle Data File?
An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.
What Is a Static Data Dictionary?
Data dictionary tables are not directly accessible, but you can access information in them through data dictionary views. To list the data dictionary views available to you, query the view DICTIONARY. Many data dictionary tables have three corresponding views:
  • An ALL_ view displays all the information accessible to the current user, including information from the current user's schema as well as information from objects in other schemas, if the current user has access to those objects by way of grants of privileges or roles.
  • A DBA_ view displays all relevant information in the entire database. DBA_ views are intended only for administrators. They can be accessed only by users with the SELECT ANY TABLE privilege. This privilege is assigned to the DBA role when the system is initially installed.
  • A USER_ view displays all the information from the schema of the current user. No special privileges are required to query these views.
What Is a Dynamic Performance View?
Oracle contains a set of underlying views that are maintained by the database server and accessible to the database administrator user SYS. These views are called dynamic performance views because they are continuously updated while a database is open and in use, and their contents relate primarily to performance. Although these views appear to be regular database tables, they are not. These views provide data on internal disk structures and memory structures. You can select from these views, but you can never update or alter them.
What Is a Recycle Bin?
Recycle bin is a logical storage to hold the tables that have been dropped from the database, in case it was dropped in error. Tables in recycle bin can be recovered back into database by the Flashback Drop action. Oracle database recycle save the same purpose as the recycle bin on your Windows desktop.
Recycle bin can be turned on or off in the recyclebin=on/off in your parametere file.


What Is a Database Table?
A database table is a basic unit of data logical storage in an Oracle database. Data is stored in rows and columns. You define a table with a table name, such as employees, and a set of columns. You give each column a column name, such as employee_id, last_name, and job_id; a datatype, such as VARCHAR2, DATE, or NUMBER; and a width. The width can be predetermined by the datatype, as in DATE. If columns are of the NUMBER datatype, define precision and scale instead of width. A row is a collection of column information corresponding to a single record.
How Many Types of Tables Supported by Oracle?
Oracle supports 4 types of tables based on how data is organized in storage:
  • Ordinary (heap-organized) table - This is the basic, general purpose type of table. Its data is stored as an unordered collection (heap)
  • Clustered table - A clustered table is a table that is part of a cluster. A cluster is a group of tables that share the same data blocks because they share common columns and are often used together.
  • Index-organized table - Unlike an ordinary (heap-organized) table, data for an index-organized table is stored in a B-tree index structure in a primary key sorted manner. Besides storing the primary key column values of an index-organized table row, each index entry in the B-tree stores the nonkey column values as well.
  • Partitioned table - Partitioned tables allow your data to be broken down into smaller, more manageable pieces called partitions, or even subpartitions. Each partition can be managed individually, and can operate independently of the other partitions, thus providing a structure that can be better tuned for availability and performance.
How To Create a New Table in Your Schema?
If you want to create a new table in your own schema, you can log into the server with your account, and use the CREATE TABLE statement. The following script shows you how to create a table:
>.\bin\sqlplus /nolog

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE tip (id NUMBER(5) PRIMARY KEY,
2 subject VARCHAR(80) NOT NULL,
3 description VARCHAR(256) NOT NULL,
4 create_date DATE DEFAULT (sysdate));

Table created.
This scripts creates a testing table called "tip" with 4 columns in the schema associated with the log in account "HR".
How To Create a New Table by Selecting Rows from Another Table?
Let's say you have a table with many data rows, now you want to create a backup copy of this table of all rows or a subset of them, you can use the CREATE TABLE...AS SELECT statement to do this. Here is an example script:
>.\bin\sqlplus /nolog

SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE emp_dept_10
2 AS SELECT * FROM employees WHERE department_id=10;
Table created.

SQL> SELECT first_name, last_name, salary FROM emp_dept_10;
FIRST_NAME LAST_NAME SALARY
-------------------- ------------------------- ----------
Jennifer Whalen 4400

How To Turn On or Off Recycle Bin for the Instance?
You can turn on or off the recycle bin feature for an instance in the instance parameter file with "recyclebin=on/off". You can also turn on or off the recycle bin feature on the running instance with a SQL*Plus command, if you log in as SYSTEM. See the following example:
SQL> connect SYSTEM/fyicenter
Connected.

SQL> SHOW PARAMETERS RECYCLEBIN
NAME TYPE VALUE
------------------------------------ ----------- -------
recyclebin string on

SQL> ALTER SYSTEM SET RECYCLEBIN = OFF;
System altered.

SQL> SHOW PARAMETERS RECYCLEBIN
NAME TYPE VALUE
------------------------------------ ----------- -------
recyclebin string OFF
Warning: Turning off the recycle bin feature will give your users hard times on recovering dropped tables.
How To View the Dropped Tables in Your Recycle Bin?
You can look what's in your recycle bin through the predefined view called RECYCLEBIN. You can use the SELECT statement to list the dropped tables as shown in the following script:
SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.

SQL> SELECT COUNT(*) FROM emp_dept_90;
COUNT(*)
----------
3

SQL> DROP TABLE emp_dept_90;
Table dropped.

SQL> COL original_name FORMAT A14
SQL> SELECT object_name, original_name, droptime FROM recyclebin;
OBJECT_NAME ORIGINAL_NAME DROPTIME
------------------------------ -------------- -------------------
BIN$uaSS/heeQuys53HgXRhEEQ==$0 EMP_DEPT_10 2006-04-01:18:57:37
BIN$gSt95r7ATKGUPuALIHy4dw==$0 EMP_DEPT_10 2006-04-01:19:59:11
BIN$bLukbcgSQ6mK1P2QVRf+fQ==$0 EMP_DEPT_90 2006-04-01:20:47:22
As you can use the EMP_DEPT_10 was dropped twice. If the same table was dropped multiple times, you need to restore by using the object name in the recycle bin with FLASHBACK statement.
Note that RECYCLEBIN is just an alias of USER_RECYCLEBIN.
How To Empty Your Recycle Bin?
If your recycle bin is full, or you just want to clean your recycle bin to get rid of all the dropped tables, you can empty it by using the PURGE statement in two formats:
  • PURGE RECYCLEBIN - Removes all dropped tables from your recycle bin.
  • PURGE TABLE table_name - Removes the specified table from your recycle bin.
Here is an example script on how to use the PURGE statement:
SQL> connect HR/fyicenter
Connected.

SQL> CREATE TABLE emp_dept_10
2 AS SELECT * FROM employees WHERE department_id=10;
Table created.

SQL> DROP TABLE emp_dept_10;
Table dropped.

SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.

SQL> DROP TABLE emp_dept_90;
Table dropped.

SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
5

SQL> PURGE TABLE emp_dept_90;
Table purged.

SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
4

SQL> PURGE RECYCLEBIN;
Recyclebin purged.

SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
0

How To Turn On or Off Recycle Bin for the Session?
If you want to control the recycle bin feature in your own session, you can use the ALTER SESSION statement to turn on or off. Here is an example SQL script:
SQL> connect HR/fyicenter
Connected.

SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
0

SQL> ALTER SESSION SET recyclebin = off;
Session altered.

SQL> CREATE TABLE emp_dept_90
2 AS SELECT * FROM employees WHERE department_id=90;
Table created.

SQL> DROP TABLE emp_dept_90;
Table dropped.

SQL> SELECT COUNT(*) FROM recyclebin;
COUNT(*)
----------
0
Warning: Turning off the recycle bin feature in your session will give yourself hard times on recovering dropped tables.
How To List All Tables in Your Schema?
If you log in with your Oracle account, and you want to get a list of all tables in your schema, you can get it through the USER_TABLES view with a SELECT statement, as shown in the following SQL script:
SQL> connect HR/fyicenter
Connected.

SQL> SELECT table_name, status, num_rows FROM USER_TABLES;
TABLE_NAME STATUS NUM_ROWS
------------------------------ -------- ----------
REGIONS VALID 4
LOCATIONS VALID 23
DEPARTMENTS VALID 27
JOBS VALID 19
EMPLOYEES VALID 107
JOB_HISTORY VALID 10
COUNTRIES VALID 25

7 rows selected.


What Is a Table Index?
Index is an optional structure associated with a table that allow SQL statements to execute more quickly against a table. Just as the index in this manual helps you locate information faster than if there were no index, an Oracle Database index provides a faster access path to table data. You can use indexes without rewriting any queries. Your results are the same, but you see them more quickly.

How To List All Indexes in Your Schema?
If you log in with your Oracle account, and you want to get a list of all indexes in your schema, you can get it through the USER_INDEXES view with a SELECT statement, as shown in the following SQL script:
SELECT index_name, table_name, uniqueness
FROM USER_INDEXES WHERE table_name = 'EMPLOYEES';
INDEX_NAME TABLE_NAME UNIQUENES
----------------------- --------------------- ---------
EMP_EMAIL_UK EMPLOYEES UNIQUE
EMP_EMP_ID_PK EMPLOYEES UNIQUE
EMP_DEPARTMENT_IX EMPLOYEES NONUNIQUE
EMP_JOB_IX EMPLOYEES NONUNIQUE
EMP_MANAGER_IX EMPLOYEES NONUNIQUE
EMP_NAME_IX EMPLOYEES NONUNIQUE
As you can see, the pre-defined table EMPLOYEES has 6 indexes defined in the default sample database.

How To Recover a Dropped Index?
If you have the recycle bin feature turned on, dropped indexes are stored in the recycle bin. But it seems to be command to restore a dropped index out of the recycle bin. FLASHBACK INDEX is not a valid statement. See the following script:
ALTER SESSION SET recyclebin = on;
Statement processed.

CREATE TABLE student (id NUMBER(5) PRIMARY KEY,
first_name VARCHAR(80) NOT NULL,
last_name VARCHAR(80) NOT NULL,
birth_date DATE NOT NULL,
social_number VARCHAR(80) UNIQUE NOT NULL);
Table created.

CREATE INDEX student_birth_date ON student(birth_date);
Index created.

DROP INDEX STUDENT_BIRTH_DATE;
Index dropped.

SELECT object_name, original_name, type, droptime FROM recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE DROPTIME
------------------------------ ------------------ ----- ----------
BIN$1LlsjTxERKOKKq+Cuz0I7A==$0 STUDENT_BIRTH_DATE INDEX 2006-04-01

FLASHBACK INDEX student_birth_date TO BEFORE DROP;
ORA-00905: missing keyword


How To See the Table Columns Used in an Index?
You can a list of indexes in your schema from the USER_INDEXES view, but it will not give you the columns used in each index in the USER_INDEXES view. If you want to see the columns used in an index, you can use the USER_IND_COLUMNS view. Here is an example script for you:
SELECT index_name, table_name, column_name
FROM USER_IND_COLUMNS WHERE table_name = 'EMPLOYEES';
INDEX_NAME TABLE_NAME COLUMN_NAME
-------------------- ---------------- ----------------
EMP_EMAIL_UK EMPLOYEES EMAIL
EMP_EMP_ID_PK EMPLOYEES EMPLOYEE_ID
EMP_DEPARTMENT_IX EMPLOYEES DEPARTMENT_ID
EMP_JOB_IX EMPLOYEES JOB_ID
EMP_MANAGER_IX EMPLOYEES MANAGER_ID
EMP_NAME_IX EMPLOYEES LAST_NAME
EMP_NAME_IX EMPLOYEES FIRST_NAME

Tablespace & Datafiles

What Is an Oracle Tablespace?
An Oracle tablespace is a big unit of logical storage in an Oracle database. It is managed and used by the Oracle server to store structures data objects, like tables and indexes.
What Is an Oracle Data File?
An Oracle data file is a big unit of physical storage in the OS file system. One or many Oracle data files are organized together to provide physical storage to a single Oracle tablespace.
How a Tablespace Is Related to Data Files?
Each tablespace in an Oracle database consists of one or more files called datafiles, which are physical structures that conform to the operating system in which Oracle is running.
How a Database Is Related to Tablespaces?
A database's data is collectively stored in the datafiles that constitute each tablespace of the database. For example, the simplest Oracle database would have one tablespace and one datafile. Another database can have three tablespaces, each consisting of two datafiles (for a total of six datafiles).
How To View the Tablespaces in the Current Database?
If you want to get a list of all tablespaces used in the current database instance, you can use the DBA_TABLESPACES view as shown in the following SQL script example:
SQL> connect SYSTEM/fyicenter
Connected.

SQL> SELECT TABLESPACE_NAME, STATUS, CONTENTS FROM USER_TABLESPACES;
TABLESPACE_NAME STATUS CONTENTS
------------------------------ --------- ---------
SYSTEM ONLINE PERMANENT
UNDO ONLINE UNDO
SYSAUX ONLINE PERMANENT
TEMP ONLINE TEMPORARY
USERS ONLINE PERMANENT
What Are the Predefined Tablespaces in a Database?
When you create a new database, Oracle server will create 4 required tablespaces for the new database:
  • SYSTEM Tablespace - Every Oracle database contains a tablespace named SYSTEM, which Oracle creates automatically when the database is created. The SYSTEM tablespace is always online when the database is open.
  • SYSAUX Tablespace - The SYSAUX tablespace was installed as an auxiliary tablespace to the SYSTEM tablespace when you created your database. Some database components that formerly created and used separate tablespaces now occupy the SYSAUX tablespace.
  • UNDO Tablespace - UNDO tablespaces are special tablespaces used solely for storing undo information. You cannot create any other segment types (for example, tables or indexes) in undo tablespaces. Each database contains zero or more undo tablespaces. In automatic undo management mode, each Oracle instance is assigned one (and only one) undo tablespace. Undo data is managed within an undo tablespace using undo segments that are automatically created and maintained by Oracle.
  • TEMP Tablespace - When the SYSTEM tablespace is locally managed, you must define at least one default temporary tablespace when creating a database. A locally managed SYSTEM tablespace cannot be used for default temporary storage.
How To View the Data Files in the Current Database?
If you want to get a list of all tablespaces used in the current database instance, you can use the DBA_TABLESPACES view as shown in the following SQL script example:
SQL> connect SYSTEM/fyicenter
Connected.

SQL> col tablespace_name format a16;
SQL> col file_name format a36;
SQL> SELECT TABLESPACE_NAME, FILE_NAME, BYTES FROM DBA_DATA_FILES;
TABLESPACE_NAME FILE_NAME BYTES
---------------- ---------------------------------- ----------
USERS \ORACLEXE\ORADATA\XE\USERS.DBF 104857600
SYSAUX \ORACLEXE\ORADATA\XE\SYSAUX.DBF 461373440
UNDO \ORACLEXE\ORADATA\XE\UNDO.DBF 94371840
SYSTEM \ORACLEXE\ORADATA\XE\SYSTEM.DBF 356515840

How To Create an Oracle Database?
There are two ways to create a new database:
  • Use the Database Configuration Assistant (DBCA) to create a database interactively.
  • Use the CREATE DATABASE statement to create a database manually.
How To Create an Oracle Database Manually?
Based on Oracle's Administrator Guide, there are 11 steps to create a database with the CREATE DATABASE statement:
  • Step 1: Decide on Your Instance Identifier (SID)
  • Step 2: Establish the Database Administrator Authentication Method
  • Step 3: Create the Initialization Parameter File
  • Step 4: Connect to the Instance
  • Step 5: Create a Server Parameter File (Recommended)
  • Step 6: Start the Instance
  • Step 7: Issue the CREATE DATABASE Statement
  • Step 8: Create Additional Tablespaces
  • Step 9: Run Scripts to Build Data Dictionary Views
  • Step 10: Run Scripts to Install Additional Options (Optional)
  • Step 11: Back Up the Database.

How To Build Data Dictionary View an New Database?
This is Step 9. The Oracle Administrator Guide suggests to run two SQL scripts provided by Oracle as shown bellow:
SQL> @/u01/oracle/rdbms/admin/catalog.sql

SQL> @/u01/oracle/rdbms/admin/catproc.sql