 |
| Radhe Radhe |
Oracle database
10g: SQL
Oracle
database Objects.
Object
|
Description
|
Table
|
Basic unit of storage; composed
|
View
|
Logically represents subsets of data from one or
more tables.
|
Sequence
|
Generates numeric values
|
Index
|
Improves the performance of some queries
|
Synonym
|
Gives alternative name to objects.
|
Database
objects:-
An oracle database can contain multiple
data structures. Each structure should be outlines in the database design so
that it can be create during the build stage of database development.
·
Table: Stores data.
·
View: Subset of data from one or more tables.
·
Sequence: Generates numeric
values.
·
Index: Improves the performance
of some queries.
·
Synonym: Gives alternative name
of objects.
Oracle
Table Structures
·
Tables can be created at any
time, even while users are using database.
·
You do not need to specify the
size of a table. The size is ultimately defined by the amount of space
allocated to the database as a whole. It is important, however, to estimate how
much space a table will use over time.
·
Table structure can be modified
online.
Naming Rules
You
name database tables and columns according to the standard rules for naming any
Oracle database object:
·
Table name and column name must begin with a letter and be
1-30 characters long.
·
Names must contain only the characters A-Z, a-z, 0-9, _(underscore),
$, and # (legal characters, but their use is discouraged).
·
Names must not duplicate the name of another object owned by
the same name Oracle server user.
·
Names must not be an Oracle server reserved word.
Naming Guidelines
Use
description names for tables and other database objects.
Note:
Names are case-insensitive. For example, EMPLOYEE is treated as the same name
as eMployees or eMpLOYEES.
Create Table Statement
You
create tables to store data by executing the SQL CREATE TABLE statement. This statement
is one of the DDL statements, which are a subset of SQL statement used to
create, modify, or remove Oracle Database structures. These statement have an immediate
effect on the database, and they also record information in the data
dictionary.
To
create a table, a user must have the CREATE TABLE privilege and a storage area
in which to create objects. The database administrator uses data control
language statements to grant privileges to users.
Syntax:-
Create
table student_data
(
Column
data type
)
Referencing Another User’s Tables
A
schema is a collection of objects. Schema objects are the logical structures
that directly refer to the data in a database. Schema objects include table,
views, synonyms, sequences, stored procedure, indexes, clusters, and database
links.
Syntax:-
Select * from userb.employees;
If
USERB wants to access the employees table that is owned by USERA, he must
prefix the table name with the schema name:
Syntax:
Select * from usera.employees;
j
