Schemas in Oracle
1. Introduction to Schemas
In Oracle, a schema is a collection of database objects that are associated with a specific user. These objects can include tables, views, indexes, sequences, procedures, and other elements. A schema serves as a namespace for these objects, ensuring that names are unique within the schema but can be reused in different schemas.
2. Creating Schemas
To create a schema, you first need to create a user because a schema is inherently linked to a user. Here is an example of how to create a new user and their schema:
CREATE USER new_user IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO new_user;
This command creates a user named "new_user" with the password "password" and grants the CONNECT and RESOURCE privileges, which are necessary to create and manipulate schema objects.
3. Managing Schemas
Managing schemas involves creating, modifying, and deleting objects within the schema. Below are examples of common operations within a schema.
3.1. Creating Tables in a Schema
To create a table within a specific schema, ensure you are connected as the schema owner:
CREATE TABLE new_user.employees (
employee_id NUMBER PRIMARY KEY,
first_name VARCHAR2(50),
last_name VARCHAR2(50),
hire_date DATE
);
3.2. Modifying Objects in a Schema
To modify objects within a schema, such as adding a column to an existing table:
ALTER TABLE new_user.employees ADD (salary NUMBER);
3.3. Dropping Objects in a Schema
To remove objects from a schema, such as dropping a table:
DROP TABLE new_user.employees;
4. Viewing Schema Information
To view information about the objects within a schema, you can query the data dictionary views. Here are some examples:
4.1. Listing All Tables in a Schema
SELECT table_name FROM all_tables WHERE owner = 'NEW_USER';
4.2. Describing a Table's Structure
To view the structure of a specific table:
DESC new_user.employees;
4.3. Viewing Column Details
To view details about the columns in a table:
SELECT column_name, data_type, data_length FROM all_tab_columns WHERE table_name = 'EMPLOYEES' AND owner = 'NEW_USER';
5. Schema Security
Managing security within a schema involves granting and revoking privileges on schema objects. Here are examples:
5.1. Granting Privileges
To grant a privilege, such as SELECT, on a table to another user:
GRANT SELECT ON new_user.employees TO other_user;
5.2. Revoking Privileges
To revoke a previously granted privilege:
REVOKE SELECT ON new_user.employees FROM other_user;
6. Example: Working with Schemas
Let's walk through an example of creating a schema, adding objects to it, and performing basic operations.
6.1. Creating a User and Schema
CREATE USER student IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO student;
6.2. Creating Tables in the Schema
CREATE TABLE student.courses (
course_id NUMBER PRIMARY KEY,
course_name VARCHAR2(100),
instructor VARCHAR2(100)
);
6.3. Inserting Data into the Table
INSERT INTO student.courses (course_id, course_name, instructor)
VALUES (1, 'Database Systems', 'Dr. Smith');
INSERT INTO student.courses (course_id, course_name, instructor)
VALUES (2, 'Algorithms', 'Prof. Johnson');
6.4. Querying Data from the Table
SELECT * FROM student.courses;
COURSE_ID COURSE_NAME INSTRUCTOR
--------- --------------- -------------
1 Database Systems Dr. Smith
2 Algorithms Prof. Johnson
6.5. Updating Data in the Table
UPDATE student.courses SET instructor = 'Dr. Johnson' WHERE course_id = 2;
6.6. Deleting Data from the Table
DELETE FROM student.courses WHERE course_id = 1;
6.7. Dropping the Table
DROP TABLE student.courses;