ALTER DATABASE
ALTER DATABASE database_name;
ALTER DATABASE command changes the size
or settings of a database. Its syntax varies widely among different database
systems.
ALTER USER
ALTER USER user
ALTER USER statement changes a user's
system settings such as password.
BEGIN TRANSACTION
1> BEGIN TRANSACTION transaction_name
2> transaction type
3> if exists
4> begin
BEGIN TRANSACTION statement signifies
the beginning of a user transaction. A transaction ends when it is either
committed (see COMMIT TRANSACTION) or canceled (see ROLLBACK TRANSACTION). A
transaction is a logical unit of work.
CLOSE CURSOR
close cursor_name
CLOSE cursor_name statement closes the
cursor and clears it of data. To completely remove the cursor, use the
DEALLOCATE CURSOR statement.
COMMIT TRANSACTION
SQL> COMMIT;
COMMIT TRANSACTION statement saves all
work begun since the beginning of the transaction (since the BEGIN TRANSACTION
statement was executed).
CREATE DATABASE
SQL> CREATE DATABASE database_name;
database_name creates a new database.
Many different options can be supplied, such as the device on which to create
the database and the size of the initial database.
CREATE INDEX
CREATE INDEX index_name
ON table_name(column_name1,
[column_name2], ...);
the contents of the indexed field(s).
CREATE PROCEDURE
create procedure procedure_name
[[(]@parameter_name
datatype [(length) | (precision [, scale])
[= default][output]
[, @parameter_name
datatype [(length) | (precision [, scale])
[= default][output]]...[)]]
[with recompile]
as SQL_statements
CREATE PROCEDURE statement creates a new
stored procedure in the database. This stored procedure can consist of SQL
statements and can then be executed using the EXECUTE command. Stored
procedures support input and output parameters passing and can return an
integer value for status checking.
CREATE TABLE
CREATE TABLE table_name
(
field1 datatype [ NOT NULL ],
field2 datatype [ NOT NULL ],
field3 datatype [ NOT NULL ]...)
CREATE TABLE statement creates a new
table within a database. Each optional field is provided with a name and data
type for creation within that table.
CREATE TRIGGER
create trigger trigger_name
on table_name
for {insert, update, delete}
as SQL_Statements
CREATE TRIGGER statement creates a
trigger object in the database that will execute its SQL statements when its
corresponding table is modified through an INSERT, UPDATE, or DELETE. Triggers
can also call stored procedures to execute complex tasks.
CREATE USER
CREATE USER user
CREATE USER statement creates a new user
account complete with user ID and password.
CREATE VIEW
CREATE VIEW <view_name> [(column1,
column2...)] AS
SELECT <table_name column_names>
FROM <table_name>
using the CREATE VIEW statement. After a
view is created, it can be queried and data within the view can be modified.
DEALLOCATE CURSOR
deallocate cursor cursor_name
DEALLOCATE CURSOR statement completely
removes the cursor from memory and frees the name for use by another cursor.
You should always close the cursor with the CLOSE CURSOR statement before
deallocating it.
DECLARE CURSOR
declare cursor_name cursor
for select_statement
DECLARE CURSOR statement creates a new
cursor from the SELECT statement query. The FETCH statement scrolls the cursor
through the data until the variables have been loaded. Then the cursor scrolls
to the next record.
DROP DATABASE
DROP DATABASE database_name;
DROP DATABASE statement completely
deletes a database, including all data and the database's physical structure on
disk.
DROP INDEX
DROP INDEX index_name;
DROP INDEX statement removes an index
from a table.
DROP PROCEDURE
drop procedure procedure_name
DROP PROCEDURE statement drops a stored
procedure from the database; its function is similar to the DROP TABLE and DROP
INDEX statements.
DROP TABLE
DROP TABLE table_name;
DROP TABLE statement drops a table from
a database.
DROP TRIGGER
DROP TRIGGER trigger_name
DROP TRIGGER statement removes a trigger
from a database.
DROP VIEW
DROP VIEW view_name;
DROP VIEW statement removes a view from
a database.
EXECUTE
execute [@return_status = ]
procedure_name
[[@parameter_name =] value |
[@parameter_name =] @variable [output]...]]
EXECUTE command runs a stored procedure
and its associated SQL statements. Parameters can be passed to the stored
procedure, and data can be returned in these parameters if the output keyword
is used.
FETCH
fetch cursor_name [into
fetch_target_list]
FETCH command loads the contents of the
cursor's data into the provided program variables. After the variables have
been loaded, the cursor scrolls to the next record.
FROM
FROM <tableref> [, <tableref>
...]
FROM specifies which tables are used
and/or joined.
GRANT
GRANT role TO user
or
GRANT system_privilege TO {user_name |
role | PUBLIC}
GRANT command grants a privilege or role
to a user who has been created using the CREATE USER command.
GROUP BY
GROUP BY <col> [, <col> ...]
GROUP BY statement groups all the rows
with the same column value.
HAVING
HAVING <search_cond>
HAVING is valid only with GROUP BY and
limits the selection of groups to those that satisfy the search condition.
INTERSECT
INTERSECT returns all the common
elements of two SELECT statements.
ORDER BY
ORDER BY <order_list>
ORDER BY statement orders the returned
values by the specified column(s).
ROLLBACK TRANSACTION
ROLLBACK TRANSACTION statement
effectively cancels all work done within a transaction (since the BEGIN
TRANSACTION statement was executed).
REVOKE
REVOKE role FROM user;
or
REVOKE {object_priv | ALL [PRIVILEGES]}
[, {object_priv | ALL [PRIVILEGES]} ]
...
ON [schema.]object
FROM {user | role | PUBLIC} [, {user |
role | PUBLIC}] ...
REVOKE command removes a database
privilege from a user, whether it be a system privilege or a role.
SELECT
SELECT [DISTINCT | ALL]
SELECT statement is the beginning of
each data retrieval statement. The modifier DISTINCT specifies unique values
and prevents duplicates. ALL is the default and allows duplicates.
SET TRANSACTION
SQL> SET TRANSACTION (READ ONLY | USE
ROLLBACK SEGMENT);
SET TRANSACTION enables the user to
specify when a transaction should begin. The READ ONLY option locks a set of
records until the transaction ends to ensure that the data is not changed.
UNION
UNION statement returns all the elements
of two SELECT statements.
WHERE
WHERE <search_cond>
WHERE statement limits the rows
retrieved to those meeting the search condition.
* gets all the columns of a particular
table.
The Syntax for ALTER TABLESPACE
The ALTER TABLESPACE command is run with
the following syntax:
SYNTAX:
ALTER TABLESPACE tablespace
[LOGGING or NOLOGGING]
[ADD DATAFILE file_specification
[AUTOEXTEND OFF]
or [AUTOEXTEND ON [NEXT number K or M]
[MAXSIZE UNLIMITED or MAXSIZE number K or M]]
[, file_specification
[AUTOEXTEND OFF]
or [AUTOEXTEND ON [NEXT number K or M]
[MAXSIZE UNLIMITED or MAXSIZE number K or M]]
[RENAME DATAFILE `filename' [, `filename]...
TO `filename' [, `filename']...]
[COALESCE]
[DEFAULT STORAGE storage_clause]
[MINIMUM EXTENT number [K or M]]
[ONLINE]
[OFFLINE NORMAL or OFFLINE TEMPORARY or
OFFLINE IMMEDIATE]
[BEGIN BACKUP or END BACKUP]
[READ ONLY or READ WRITE]
[PERMANENT or TEMPORARY]
The parameters used to alter the tablespace are defined as
follows:
LOGGING--This parameter specifies
that redo log information is kept on table, index, and partition operations.
This is the default. You can disable logging by using the NOLOGGING option on
these operations.
NOLOGGING--This parameter specifies that logging not be done on operations
that support the NOLOGGING option.
ADD
DATAFILEfile_specification--This parameter
specifies that a datafile or datafiles specified by file_specification will be
added to the tablespace.
file_specification consists of the
`filename' SIZE number (K or M) [REUSE] component. file_specification is used
to define the name, and the initial size in kilobytes (K) or megabytes (M) of
the datafile. The REUSE parameter allows you to use the name of an existing
file.
Additional qualifiers to the ADD
DATAFILE parameter are
AUTOEXTEND OFF--This parameter
specifies that the autoextend feature is disabled on this datafile.
AUTOEXTEND O--This parameter specifies that that autoextend feature is
enabled. Additional qualifiers to the AUTOEXTEND ON parameter are
NEXTnumber K or M--When a datafile autoextends itself, it extends by number K
(kilobytes) or number M (megabytes).
MAXSIZE UNLIMITE--This specifies that the maximum size of the datafile is limited
only by disk space and OS-specific maximum sizes.
MAXSIZEnumber K or M--This specifies that the maximum size autoextend will grow this
datafile to is number. This size is either specified in kilobytes (K) or
megabytes (M).
Other parameters available with the
ALTER TABLESPACE command are
RENAME DATAFILE `filename' [,
`filename]... TO `filename'--This command is used to rename one or more
datafiles in the [, `filename']... tablespace.
COALESC--This command is used to coalesce the tablespace as described
previously.
DEFAULT STORAGE
storage_claus--This command is used to
change the default storage parameters for the tablespace. These default storage
parameters are used when schema objects are created unless a storage parameter
is specified.
MINIMUM EXTENTnumber [K or
M]--This specifies the minimum size of an extent that is created on
the tablespace. The minimum extent size will be number K (kilobytes) or number
M (megabytes).
ONLINE--This is used to bring the tablespace online.
OFFLINE NORMAL--This is used to bring
the tablespace offline normally as described previously.
OFFLINE TEMPORARY--This is used to bring the tablespace offline temporarily, as
described previously.
OFFLINE IMMEDIATE--This is
used to force the tablespace offline with the IMMEDIATE option.
BEGIN BACKUP--This takes the tablespace offline and defers all writes to the
datafiles while the backup is taking place.
END BACKUP--This brings the tablespace back online and writes out all changes
that have occurred since the BEGIN BACKUP.
READ ONLY--This modifies the tablespace to be read-only. The read-only
tablespace is described later today.
READ WRITE--This modifies a read-only tablespace to be read-write.
PERMANENT--This converts the tablespace from temporary to permanent status.
TEMPORARY--This converts the tablespace from permanent to temporary status.
As you have seen, the ALTER TABLESPACE
command allows many changes to be made to the tablespace. Changes to
tablespaces should always be logged when they occur. If the database ever needs
to be re-created, this information is critical.
The STORAGE Clause
The STORAGE clause is very important
because it is used to specify the initial size and characteristics of the
tablespace as well as the future growth of that tablespace.
The Syntax for the STORAGE Clause
The STORAGE clause has the following
syntax:
SYNTAX:
STORAGE
(
[INITIAL number K or M]
[NEXT number K or M]
[MINEXTENTS number]
[MAXEXTENTS number or MAXEXTENTS
UNLIMITED]
[PCTINCREASE number]
[FREELISTS number]
[FREELIST GROUPS number]
[OPTIMAL [number K or M] or [NULL]]
)
The parameters used in the STORAGE clause are defined as follows:
INITIAL number K or M--This
parameter specifies the initial size of the extents. These extents are created
when the schema object is created. This parameter specifies the size to be
number K (kilobytes) or number M (megabytes). The default is 5 data blocks. The
size is rounded up to the nearest multiple of 5 blocks.
NEXTnumber K or M--The NEXT
parameter specifies the size of subsequent extents to be number K (kilobytes)
or number M (megabytes). This number is also rounded up to the nearest multiple
of 5 data blocks, and defaults to 5 data blocks.
MINEXTENTSnumber--This specifies the minimum number of extents created when the
schema object is created. Each of these extents is the size of the INITIAL
extent, and Oracle uses NEXT and PCTINCREASE to calculate the size of
subsequent extents. The default value is 1 except for rollback segments, where
the default is 2.
MAXEXTENTSnumber--This specifies the maximum number of extents that can be created
for a schema object. This includes the first extent.
MAXEXTENTS UNLIMITE--This specifies that the maximum number of extents that can be
created for a schema object is unlimited. Oracle does not recommend that you
use this option with any schema objects except for rollback segments.
PCTINCREASEnumber--This specifies the size of extents after the second extent (that
is, from the third extent on). The initial extents are sized via the INITIAL
parameter. The next extent is sized via the NEXT parameter. If the PCTINCREASE
parameter is nonzero, all subsequent extents are sized as NEXT multiplied by
PCTINCREASE number. This product is a percentage, so 40 means 40% larger, and
so on. A value of 0 specifies that all subsequent extents are the same size as
specified in the NEXT parameter. The default value is 50, except for rollback
segments, which can only have a PCTINCREASE of 0.
FREELISTSnumber--The FREELISTS parameter specifies the number of sets of freelists
for each of the freelist groups of tables, indexes, partitions and clusters. A
freelist is a linked list of available data blocks in the extent that have free
space greater than PCTFREE. These are essentially lists of blocks that are
available for inserts. By having more than one freelist, you can reduce
contention on inserts.
FREELIST GROUP number--FREELISTS GROUPS specifies the number of groups of freelists in a
parallel-server environment. This allows each instance to have its own set of
freelists. FREELIST GROUPS is a parallel-server-only parameter.
OPTIMAL number K or M]--This parameter applies only to rollback segments. It specifies the
ideal size
of the rollback segment. Because the rollback segment grows as
described tomorrow, this parameter describes what size Oracle should attempt to
keep them.
OPTIMAL [NULL--This parameter specifies that the rollback segments never shrink,
as they can with the OPTIMAL parameter set to a value. This is described in
detail tomorrow.
These storage parameters can be used not
only in the creation of tablespaces, but also in the creation of schema
objects, as you will see later in the book. The size and characteristics of the
tablespaces can be very important to the performance of the system.
--------------------------------------------------------------------------------
NOTE: For tablespaces, you specify the
DEFAULT STORAGE options. These are used as the default values for the schema
objects that you will be creating. Your schema object creation options override
the default storage parameters.
--------------------------------------------------------------------------------
Using the STORAGE Clause
By using the STORAGE clause, you can be
very efficient with how the schema objects are stored. If you know you will be
loading a large amount of data that will be stored in a certain table, it is
much more efficient to have a few large extents rather than many small extents.
This is typically be done using the STORAGE clause on the schema objects like
so:
CREATE TABLESPACE ts_1
DATAFILE `D:\database\ts_1_a.dbf' SIZE
20M,
E:\database\ts_1_b.dbf SIZE 20M
DEFAULT STORAGE
(
INITIAL 2M NEXT 2M PCTINCREASE 0
MINEXTENTS 2
);
This creates the tablespace ts_1 with
two datafiles and two initial extents. To create the same tablespace but allow
the second datafile to autoextend, you can affix the additional parameters as
follows:
CREATE TABLESPACE ts_1
DATAFILE `D:\database\ts_1_a.dbf' SIZE
20M,
E:\database\ts_1_b.dbf SIZE 20M AUTOEXTEND
ON NEXT 1M MAXSIZE 30M
DEFAULT STORAGE
(
INITIAL 2M NEXT 2M PCTINCREASE 0
MINEXTENTS 2
);
Remember that the DEFAULT STORAGE clause
is used for the creation of extents. Extents are used to hold schema objects.
When the schema objects are created and grow, the default storage parameters
are used. These parameters are simply defaults for the schema objects that are
created on these tablespaces. Schema objects created with their own storage
parameters override the tablespace defaults.
No comments:
Post a Comment