• DDL Compiler: It processes the DDL (Data Definition Language)
statements into a set of table containing meta data
• DML Compiler: It processes the DML (Data Manipulation Language)
statements into low level instruction, so that they can be executed.
• Query Processor: receives a query request in the form of SQL text,
parses it, generates an execution plan, and execute it. The query
processor communicate with the DML Compiler.
• Query optimizer choose the most efficient means of executing a SQL
statement.
• Authorization Manager: It ensures role-based access control, i.e,.
checks whether the particular person is privileged to perform the
requested operation or not.
• Integrity Manager: It checks the integrity constraints when the database
is modified.
• Transaction Manager: It controls concurrent access by performing the
operations in a scheduled way that it receives the transaction.
• Buffer Manager: It is responsible for cache memory and the transfer of
data between the secondary storage and main memory.
• Recovery manager: deals with system failure problems.
Chapitre 2 : queries
To open an existing pluggable database :
● connect sys@orclpdb as sysdba
● Alter pluggable database orclpdb open ;
to create pluggable database :
● connect sys as sysdba
create pluggable database name
admin user user_name identified by user_password
default tablespace tab_name
datafile path_adrress size 500m
path_prefix=’a path’ ;
create a tablespace
● create tablespace tab_name datafile ‘path_file’ size 100m ;
create a temporary tablespace
● create temporary tablespace tab_name tempfile‘path_file’
size 100m ;
create user
● create user user_name identified by user_password
default tablespace tab_name ;
pour donner tous les privilèges
grant all privileges to user_name ;
desc user_users ;
● username , user_id , created , default_tablespace ,
temporary_tablespace …
select username from all_users ; => tous les utilisateurs de la
database
select username from user_users; => username de l' utilisateur
courant
desc user_tables;
● table_name ,tablespace_name , status
select table_name from user_tables;
les tables de l' utilisateur courant
desc user_objects;
● object_name, object_type , object_id …
les objets de l' utilisateur courant
desc user_indexes ; un
desc all_indexes ; tous
● index_name , index_type , table_owner , table_name …
desc user_constraints ; un
desc all_constraints ; tous
● constraint_name ,constraint_type , owner , table_name …
desc user_tablespaces ; un
● tablespace_name , next_extent , block_size …
desc user_segments ; un
desc user_extents ;
● tablespace_name , extent_id , segment_name
desc v$database
● dbid , name , created , open_mode …
desc v$instance
● instance_name ,instance_number , host_name ..
desc v$parameter
● num,name,type,value…
desc v$sga
● name,value ,con_id
desc v$process
● pid , addr , program
chapitre 3 : les contraintes
Clé primaire : + contrainte unique
CREATE TABLE TableName (
ID INT PRIMARY KEY,
Name VARCHAR(50) UNIQUE,
-- Other columns...
);
……………………………………………………………………………………
CREATE TABLE TableName (
ID INT,
Name VARCHAR(50),
-- Other columns...
);
ALTER TABLE TableName
ADD CONSTRAINT PK_TableName_ID PRIMARY KEY (ID),
ADD CONSTRAINT UQ_TableName_Name UNIQUE (Name);
……………………………………………………………………………………
CREATE TABLE TableName (
ID INT,
Name VARCHAR(50),
-- Other columns...
CONSTRAINT PK_TableName_ID PRIMARY KEY (ID),
CONSTRAINT UQ_TableName_Name UNIQUE (Name)
);
cle etrangere
CREATE TABLE ChildTable (
ChildID INT PRIMARY KEY,
ParentID INT,
-- Other columns...
FOREIGN KEY (ParentID) REFERENCES ParentTable(ParentID)
);
……………………………………………………………………………………
CREATE TABLE ChildTable (
ChildID INT PRIMARY KEY,
ParentID INT,
-- Other columns...
);
ALTER TABLE ChildTable
ADD CONSTRAINT FK_ChildTable_ParentTable FOREIGN KEY (ParentID)
REFERENCES ParentTable(ParentID);
CREATE TABLE ChildTable (
ChildID INT PRIMARY KEY,
ParentID INT,
-- Other columns...
CONSTRAINT FK_ChildTable_ParentTable FOREIGN KEY (ParentID)
REFERENCES ParentTable(ParentID)
);
not null
ALTER TABLE YourTable
MODIFY YourColumn INT NOT NULL;
check
ALTER TABLE YourTable
ADD CONSTRAINT CHK_YourTable_YourColumn CHECK (YourColumn > 0);
default
ALTER TABLE YourTable
MODIFY YourColumn INT DEFAULT 0;
les procedure : examples tp4
CREATE [OR REPLACE ] PROCEDURE procedure_name (parameter_list)
IS
[declaration statements]
BEGIN
[execution statements]
EXCEPTION
[exception handler]
END [procedure_name ];
triggers form : more examples in tp 5
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT [OR] | UPDATE [OR] | DELETE }
[OF column_name]
ON table_name
[FOR EACH ROW]
[FOLLOWS | PRECEDES another_trigger]
[ENABLE / DISABLE ]
[WHEN condition]
DECLARE
declaration statements
BEGIN
executable statements
EXCEPTION
exception_handling statements
END;
chapter 4 : authorization
general syntax :
GRANT privilege [ON table/view] TO user [WITH GRANT OPTION]
example :
GRANT select, delete, update ON employee TO administrateur;
granting everything to a user :
GRANT ALL PRIVILEGES TO user;
granting a privilege to all users
GRANT privileges TO PUBLIC;
revoke privilege on something for a user
REVOKE privilege [ON table/view] FROM user;
ex:
REVOKE delete ON employee FROM developer;
roles :
CREATE ROLE rolename;
GRANT privileges to rolename;
GRANT rolename to username;
profile
CREATE PROFILE profile_name LIMIT { resource_parameters |
password_parameters};
ressources
• SESSIONS_PER_USER – specify the number of concurrent sessions that a
user can have when connecting to the Oracle database.
• CPU_PER_SESSION – specify the CPU time limit for a user session,
represented in hundredth of seconds.
• CPU_PER_CALL – specify the CPU time limit for a call such as a parse,
execute, or fetch, expressed in hundredths of seconds.
• CONNECT_TIME – specify the total elapsed time limit for a user session,
expressed in minutes.
• IDLE_TIME – specify the number of minutes allowed for periods of
continuous inactive time during a user session. Note that the
long-running queries and other operations will not be subject to this
limit.
• LOGICAL_READS_PER_SESSION – specify the allowed number of data blocks
read in a user session, including blocks read from both memory and disk.
• LOGICAL_READS_PER_CALL – specify the allowed number of data blocks read
for a call to process a SQL statement.
• PRIVATE_SGA – specify the amount of private memory space that a session
can allocate in the shared pool of the system’s global area (SGA).
• COMPOSITE_LIMIT – specify the total resource cost for a session,
expressed in service units. The total service units are calculated as a
weighted sum of CPU_PER_SESSION, CONNECT_TIME,
LOGICAL_READS_PER_SESSION, and PRIVATE_SGA.
password
• FAILED_LOGIN_ATTEMPTS – Specify the number of consecutive failed login
attempts before the user is locked. The default is 10 times.
• PASSWORD_LIFE_TIME – specify the number of days that a user can use the
same password for authentication. The default value is 180 days.
• PASSWORD_REUSE_TIME – specify the number of days before a user can
reuse a password.
• PASSWORD_REUSE_MAX – specify the number of password changes required
before the current password can be reused. Note that you must set values
for both PASSWORD_REUSE_TIME and PASSWORD_REUSE_MAX parameters make
these parameters take effect.
• PASSWORD_LOCK_TIME – specify the number of days that Oracle will lock
an account after a specified number of consecutive failed logins. The
default is 1 day if you omit this clause.
• PASSWORD_GRACE_TIME – specify the number of days after the grace period
starts during which a warning is issued and login is allowed. The
default is 7 days when you omit this clause.
bit map index
CREATE BITMAP INDEX Fonction_ib_idx ON Client.Fonction
