Oracle DB

Oracle DB Commands.md

Oracle DB Tips and Tricks

Connecting to a DB with sqlplus

You can connect both to a local or remote DB using the SQLPlus tool.

Syntax:

Run the SQLPlus command with following arguments.

$ sqlplus username/password@HOST:PORT/SERVICE

Where
- username/password: credentials for a user already present in the Oracle DB
- HOST: a local or remote host. 
- PORT: port e.g 1529 where the Oracle DB is listening.
- SERVICE: the service name you want to connect to.

If you cannot connect:

1. Check that the Host can actually be reached from the client where sqlplus is running.
2. Check the port number is correct
3. Check the username and password and service names.

Taking a DB Dump (expdp)

Reference

Taking a DB Export from SQL Developer

Tools > Database Export Tool

List of commands

Task	Command	Notes
List the grants given to your schema	select * from user_tab_privs;	This will list the grants for your currently-logged-in schema.

References

1. http://stackoverflow.com/a/23647579/682912

Oracle DB FAQs.md

Frequently Asked Questions

Schema

Also: what is the difference between a user and a schema?

• Context: Typically I come across statements, "log in as the schema XYZ"

• Reference

• Reference

• A schema should be considered a user account plus all objects contained within that user account.

• A schema is owned by a database user and has the same name as that user.

View

• A view can be considered a customized presentation of data. Or, a saved query.
• Views do not contain data, they retrieve data from the tables on which they are based.
• You can interact with views similar to tables (i.e query, update delete etc). These interactions will affect the underlying tables on which the view is based.

Materialized View

reference

• Unlike a regular view, a materialized view also stores the results of the query in a separate schema object.
• Since Materialized Views "replicate" the data, it is necessary to refresh the Materialized View for consistency.

Also, there is a "refresh privilege" that should be granted to a user to perform a refresh.

reference: refresh privilege

Synonym

• Synonyms are aliases for any table, view, materialized view, sequence, procedure etc etc.
• Synonyms do not require any separate storage.