Reset a sequence

alter sequence SEQUENCE_NAME increment by -10;
select SEQUENCE_NAME.NEXTVAL from dual;
alter sequence SEQUENCE_NAME increment by 1;

Synonym

create synonym synonym-name for object;
create public synonym synonym-name for object;

A synonym is an alias for one of the following objects:

• table
• object table
• view
• object view
• sequence
• stored procedure
• stored function
• package
• materialized view
• java class
• used defined object object type
• another synonym

The object does not need to exist at the time of its creation.

Synonyms cannot be used in a drop table, drop view or truncate table/cluster statements. If this is tried, it results in a ORA-00942: table or view does not exist

Public synonyms

A public synonym (create public synonym) is owned by public rather than the one who has created it. Therefore, a public synonym is valid for each schema.

Listing synonyms

Existing synonyms can be found via all_synonyms (or dba_synonyms, or user_synonyms).

Generally good DBA’s try to avoid public synonyms. They do make management of the database a bit easier, but they also have security and performance issues associated with them. Hence, try not to use public synonyms unless you have to.

You can have a public and private synonym of the same name. In fact, you can have a public and private synonym called EMP in the SCOTT schema and have a table called EMP in the same schema. In cases where you have multiple synonyms and/or a table present, it can get confusing which object you are using (this is another reason we hate public synonyms). There is an order of precedence with regards to the use of synonyms and local objects. This is:

1. Local objects will always be accessed first.

2. If a local object does not exist, the object with a private synonym will be accessed.

3. If a private synonym does not exist or the object does not exist, then the public synonym will be used.

Note that a synonym can be created for an object that does not exist, and an object with an associated synonym can be dropped without removing the synonym. This can cause all sorts of interesting problems for DBA’s, so be careful.

Removing Synonyms

– Drop public synony

SQL> DROP PUBLIC SYNONYM emp;

– Drop private synonym

SQL> DROP SYNONYM emp;

From http://www.dba-oracle.com/concepts/synonyms.htm

 

Oracle alter table

The ALTER TABLE statement allows you to rename an existing table. It can also be used to add, modify, or drop a column from an existing table.

 

Renaming a table

The basic syntax for renaming a table is:

ALTER TABLE table_name
 RENAME TO new_table_name;

For example:

ALTER TABLE suppliers
 RENAME TO vendors;

This will rename the suppliers table to vendors.

 

Adding column(s) to a table

Syntax #1

To add a column to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 ADD column_name column-definition;

For example:

ALTER TABLE supplier
 ADD supplier_name  varchar2(50);

This will add a column called supplier_name to the supplier table.

 

Syntax #2

To add multiple columns to an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
ADD (	column_1	column-definition,
	column_2	column-definition,
	…
	column_n	column_definition );

For example:

ALTER TABLE supplier
ADD (	supplier_name	varchar2(50),
	city	varchar2(45) );

This will add two columns (supplier_name and city) to the supplier table.

 

Modifying column(s) in a table

Syntax #1

To modify a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 MODIFY column_name column_type;

For example:

ALTER TABLE supplier
 MODIFY supplier_name   varchar2(100)     not null;

This will modify the column called supplier_name to be a data type of varchar2(100) and force the column to not allow null values.

 

Syntax #2

To modify multiple columns in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
MODIFY (	column_1	column_type,
	column_2	column_type,
	…
	column_n	column_type );

For example:

ALTER TABLE supplier
MODIFY (	supplier_name	varchar2(100)	not null,
	city	varchar2(75)		);

This will modify both the supplier_name and city columns.

 

Drop column(s) in a table

Syntax #1

To drop a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 DROP COLUMN column_name;

For example:

ALTER TABLE supplier
 DROP COLUMN supplier_name;

This will drop the column called supplier_name from the table called supplier.

 

Rename column(s) in a table
(NEW in Oracle 9i Release 2)

Syntax #1

Starting in Oracle 9i Release 2, you can now rename a column.

To rename a column in an existing table, the ALTER TABLE syntax is:

ALTER TABLE table_name
 RENAME COLUMN old_name to new_name;

For example:

ALTER TABLE supplier
 RENAME COLUMN supplier_name to sname;

This will rename the column called supplier_name to sname.

http://www.techonthenet.com/sql/tables/alter_table.php

Rename tables, columns and constraints in Oracle

In more recent versions of Oracle you can rename tables, columns and constraints.

Here are examples of the syntax:

ALTER TABLE table_name RENAME COLUMN column_name TO
new_column_name; (from Oracle9i Release 2)

ALTER TABLE table_name RENAME TO new_table_name;

ALTER TABLE table_name ADD CONSTRAINT constraint_name
PRIMARY KEY(column_name);  

ALTER TABLE table_name RENAME CONSTRAINT
constraint_name TO new_constraint_name;

ALTER INDEX index_name RENAME TO new_index_name;

It's also possible to drop a column:
ALTER TABLE table_name DROP COLUMN column_name;

From http://www.oradev.com/rename.jsp

How to create database link

1) tnsnames.ora add the correct entry [TNS's alias name]
2) create database link

To create a database link:
CREATE [SHARED | PUBLIC] DATABASE LINK <link-name>
[CONNECT TO <username> IDENTIFIED BY <password>]
[USING <connect-string>];

<connect-string> is service_name alias in tnsnames.ora
desc table_name@<link-name>
select sysdate from dual@<link-name>

Managing Oracle Synonyms

http://www.dba-oracle.com/concepts/synonyms.htm

Creating Synonyms

A synonym is named, and points to a specific object. For example, in the ROBERT schema we can create a private synonym for SCOTT.EMP using the create synonym command:

SQL> CREATE SYNONYM emp FOR SCOTT.EMP;

Note that we said that this was a private synonym. That means that only the ROBERT user can use the synonym. We can also create public synonyms using the create public synonym command as seen here:

SQL> CREATE PUBLIC SYNONYM emp FOR SCOTT.EMP;

Generally good DBA’s try to avoid public synonyms. They do make management of the database a bit easier, but they also have security and performance issues associated with them. Hence, try not to use public synonyms unless you have to.

You can have a public and private synonym of the same name. In fact, you can have a public and private synonym called EMP in the SCOTT schema and have a table called EMP in the same schema. In cases where you have multiple synonyms and/or a table present, it can get confusing which object you are using (this is another reason we hate public synonyms). There is an order of precedence with regards to the use of synonyms and local objects. This is:

1. Local objects will always be accessed first.

2. If a local object does not exist, the object with a private synonym will be accessed.

3. If a private synonym does not exist or the object does not exist, then the public synonym will be used.

Note that a synonym can be created for an object that does not exist, and an object with an associated synonym can be dropped without removing the synonym. This can cause all sorts of interesting problems for DBA’s, so be careful.

Removing Synonyms

The drop synonym command is used to drop public and private synonyms. Here is an example of dropping a private synonym and a public synonym with the drop synonym command:

SQL> — Drop public synony

SQL> DROP PUBLIC SYNONYM emp;

SQL> — Drop private synonym

SQL> DROP SYNONYM emp;

http://www.adp-gmbh.ch/ora/concepts/synonyms.html