Oracle is one of the most widely used database management systems, offering a rich set of features and powerful performance. This Oracle Study Note covers the basics of database management and SQL, providing an in-depth exploration of key concepts.
-
Data Dictionary: Oracle's data dictionary consists of a series of views and tables that store information about database objects, privileges, and other metadata. For example,
dba_tablespaces
provides the status of all tablespaces,dba_users
displays user information,user_sys_privs
lists system privileges for users, anduser_tab_privs_made
records table permissions granted to other users. Other views, such asuser_col_privs_made
anduser_role_privs
, help monitor and manage column-level and role-based permissions. -
SQL Statements: SQL is the language used to interact with Oracle. It includes commands for creating users, modifying user properties (such as passwords and default tablespaces), granting and revoking privileges, creating and managing objects (like tables, columns, and constraints), and performing data operations (insert, update, delete). For instance,
CREATE USER
creates a new user,ALTER USER
modifies user attributes,GRANT
andREVOKE
manage privileges, andINSERT INTO
,UPDATE
, andDELETE FROM
are used to manipulate data. -
Constraints and Indexes: Oracle offers various types of constraints, such as NOT NULL, PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK, which ensure data integrity and consistency. Indexes speed up data retrieval.
-
Views and Synonyms: A view is a virtual table based on one or more tables, simplifying complex queries and protecting data. A synonym provides an alias for objects, allowing users to access different objects with the same name, improving database accessibility.
-
Transactions and Rollbacks: In Oracle, a transaction is a group of logical operations that must either all succeed or all fail, ensuring data consistency. Use
COMMIT
to commit a transaction,ROLLBACK
to undo changes, andSAVEPOINT
to set a rollback point. -
SQL Utility Commands: Useful SQL commands include
DESCRIBE
(to view table structure),SELECT * FROM dual
(for connection testing),SPOOL
(to save query results to a file),SHOW
(to display session settings), andHELP
(to provide help information).