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 user
ALTER USER statement changes a user's system settings such as password.
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_name
CLOSE cursor_name statement closes the cursor and clears it of data. To completely remove the cursor, use the DEALLOCATE CURSOR statement.
SQL> COMMIT;
COMMIT TRANSACTION statement saves all work begun since the beginning of the transaction (since the BEGIN TRANSACTION statement was executed).
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 index_name ON table_name(column_name1, [column_name2], ...);
the contents of the indexed field(s).
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 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 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 user
CREATE USER statement creates a new user account complete with user ID and password.
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 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_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 database_name;
DROP DATABASE statement completely deletes a database, including all data and the database's physical structure on disk.
DROP INDEX index_name;
DROP INDEX statement removes an index from a table.
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 table_name;
DROP TABLE statement drops a table from a database.
DROP TRIGGER trigger_name
DROP TRIGGER statement removes a trigger from a database.
DROP VIEW view_name;
DROP VIEW statement removes a view from a database.
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 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 <tableref> [, <tableref> ...]
FROM specifies which tables are used and/or joined.
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 <col> [, <col> ...]
GROUP BY statement groups all the rows with the same column value.
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_list>
ORDER BY statement orders the returned values by the specified column(s).
ROLLBACK TRANSACTION statement effectively cancels all work done within a transaction (since the BEGIN TRANSACTION statement was executed).
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 [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.
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 <search_cond>
WHERE statement limits the rows retrieved to those meeting the search condition.
* gets all the columns of a particular table.
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:
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
Other parameters available with the ALTER TABLESPACE command are
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 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 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:
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.
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.