Oracle 1Z0-007: Creating Other Database Objects

Creating Other Database Objects

Create, maintain and use sequences

a sequence is an object in Oracle that is used to generate a number sequence, also know as auto-numbering

/*syntax*/

				CREATE SEQUENCE sequence_name

				MINVALUE value


				MAXVALUE value

				START WITH  value

				INCREMENT BY value

				CACHE value;


				/*example*/
 
				create sequence supplier_seq


				minvalue 1

				maxvalue 99999

				START WITH  1

				INCREMENT BY  1

				CACHE 20;/*puts 20 rows in cache*/

Create and maintain indexes

/*syntax*/

				CREATE [UNIQUE] INDEX index_name
 
				ON table_name (column1, column2, . column_n)

				[ COMPUTE STATISTICS];

				create index yankee_idx

				ON players(last_name);


				--more than one field
				CREATE INDEX supplier_idx
				ON supplier (supplier_name, city);
				--create statistics
				CREATE INDEX supplier_idx
				ON supplier (supplier_name, city)
				COMPUTE STATISTICS;


				/*alter or rename an index*/

				ALTER INDEX index_name

				RENAME TO new_index_name;


				/*alter index example*/

				alter index yankee_idx
				rename to yankees_index_name;--made it plural



				/*compute statisitcs syntax*/

				ALTER INDEX index_name

				REBUILD COMPUTE STATISTICS;


				/*computing index stats example*/
				alter index yankees_idx

				rebuild compute statistics;


				/*drop index sytax*/


				DROP INDEX index_name;


				/*example*/

				drop index yankees_idx;

Create private and public synonyms

A synonym is an alternative name for objects such as tables, views, sequences, stored procedures, and other database objects.

/*Creating or replacing a synonym - syntax*/

				create [or replace]  [public]  synonym [schema .] synonym_name

				for [schema .] object_name [@ dblink];


				/*example*/

				create public synonym suppliers

				for app.suppliers;


				select * from suppliers;


				/*redefine it*/

				create or replace public synonym suppliers

				for app.suppliers;


				/*Dropping a synonym - syntax*/

				drop  [public]  synonym  [schema .]  synonym_name  [force];



				/*example*/

				drop public synonym suppliers;

				This drop statement would drop the synonym called suppliers that we defined earlier.

Updated: July 30, 2005; Joe Gakenheimer