Oracle Data Dictionary

INDEXES USER_INDEXES USER_IND_COLOUMNS USER_OBJECT ROLE_SYS_PRIVS ROLE_TAB_PRIVS USER_ROLE_PRIVS USER_TAB_PRIVS_MADE USER_TAB_PRIVS_RECD USER_COL_PRIVS_MADE USER_COL_PRIVS_RECD USER_SYS_PRIVS DBA_JOBS DBA_JOBS_RUNNING

MATERIALIZED VIEWS DBMS_VREFRESH DBA_MVIEWS USER_REFRESH USER_REFRESH_CHILDEREN

TRIGGERS

PROCEDURES

FUNCTIONS

USER_ERRORS

PACKAGES

To view the source code for exisiting procs,funcs,pack and pack bodies can be queriedfrom the followingdata dicrionary views: USER_SOURCE ALL_SOURCE DBA_SOURCE

INBUILT PACKAGES

DBMS_SQL DBMS_DDL DBMS_UTILITY DBMS_JOB

DBMS_OUTPUT

CONTAINS NAME OF THE INDEX AND ITS UNIQUENESS CONTAINS INDEX NAME, COLOMN NAME, TABLE NAME

TYPE VIEW VIEW TABLE

SYSTEM PRIVILEGES GRANTED TO ROLE TABLE PRIVILEGES GRANTED TO ROLE ROLE ACCESSIBLE BY USER OBJECT PRIVILEGES GRANTED ON USER'S OBJECT OBJECT PRIVILEGES GRANTED TO USER OBJECT PRIVILEGES GRANTED ON THE COLOMS OF THE USER OBJECTS OBJECT PRIVILEGES GRANTED ON THE COLOMS TO THE USER LISTS SYSTEM PRIVILEGES GRANTED TO THE USER DISPLAY INFO NO. OF JOBS IN THE QUEUE DISPLAY INFO NO. OF JOBS THAT HAVE RUN

VIEW VIEW VIEW VIEW VIEW VIEW VIEW VIEW VIEW VIEW

The materialized views arein DBA_MVIEWS Information about exisiting refresh groups can be queried Information about exisiting refresh groups can be queried

PACKAGE VIEW VIEW

Must have "alter table" or "alter any table" and "create trigger privileges the create trigger system privilege is part of the resource role provided with oracle The privileges needed for triggered transactions cannot come form roles; they must be granted directly to the creator of the trigger.

"GRANT EXECUTE ON MY_PROCDURE TO DORA" "CREATE SYNONYM NEW_BOOK FOR PRACTICE.NEW_BOOK" FOR EXECUTING THE REMOTE PROCDURES--> "EXECUTE NEW_BOOK@REMOTE_CONNECT('ONCE REMOVED')" THE CREATOR OF THE PROCEDURE NEEDS THE PRIVILEGES ON THE UNDERLYING TABLES IN THAT PROCDURE BUT INVOKER DOESN’T NEED IT, THE PRIVILEGES NEEDED FOR PROCEDURES,PACKAGE AND FUNCTIONS CANNOT COME FROM ROLES;THEY MUST BE GRANTED DIRECTLY TO OWNER OF THE PROC.

Functions can return value with help of return and func can be directly refrenced in quries create procedure system privilege is needed which is part of resource role "SHOW ERROR" command in sql plus displays the errors associated with most recently created procedure object To view errors associated with previously created procdural objects. TABLE

custom func cannot be used in CHECK or DEFAULT constraints and cannot manipulate any database value RAISE_APPLICATION_ERROR method can be called from within proc,func,pack For creating package specification or package body ,need the CREATE PROCEDURE system privilege and if package is in others schema then need the CREATE ANY PROCEDURE sys privlg

For the procedural objects owned by the user VIEW For the procedural objects owned by the user or to which the user has been granted access VIEW For all procedural objects in the database VIEW valid values for the type colom are : PROCEDURE,FUNCTION,PACKAGE,PACKAGE BODY,JAVA SOURCE,TYPE,TYPE BODY.

METHODS OPEN_CURSOR, PARSE('CURSOR_NAME','QUERY'), BIND_VARIABLE, EXECUTE(CURSOR_NAME), FETCH_ROWS, CLOSE_CURSOR(CURSORNAME) ALTER_COMPILE('OBJECT_TYPE','OWNER','OBJECT_NAME'), ANALYZE_OBJECT, COMPUTE,ESTIMATE,DELETE SUBMIT(JOB => :BINDVARIABLE,WHAT=>'OVER_PACK.ADD_DEP()', NEXT_DATE=>TRUNC(SYSDATE+1),INTERVAL=>'TRUNC(SYSDATE+1)' RUN(1).REMOVE(1),BROKEN(1,TRUE) PUT,NEW_LINE,PUT_LINE,GET_LINE,GET_LINES,ENABLE/DISABLE

PROCEDURE PROCEDURE

METHODS

NOTES

SHOULD HAVE SELECT PRIVILEGE ON SYS.DBA_JOBS SHOULD HAVE SELECT PRIVILEGE ON SYS.DBA_JOBS

DBMS_MVIEW.REFRESH('MATERIALIZED VIEW','C||'F'||'?'), ADD,MAKE,SUBSTRACT,CHANGE,DESTROY AND REFRESH

Materialized views that belong to a refresh group do not have to be in same schema, but they have to be in all sotred within same data base

ALTER PROCEDURE NEW_BOOK COMPILE

TO REOMPILE THE PROC

ALTER FUNCTION NEW_BOOK COMPILE DROP PROCEDURE OVERDUE_CHARGES

TO REOMPILE THE FUNC

DROP FUNCTION OVERDUE_CHARGES

THIS PACKAGE GIVES THE ALTER AND ANALYZE SQLSTATEMENTS THROUGH PL/SQL TO ANALYZE OBJECT TO ANALYZE MORE THAN ONE OBJECT

TOPIC INDEX

SEQUENCES

SYNONYM PRIVELEGES

DATABASE LINKS EXTERNAL TABLES

To create the query in another schema or another schema's table ,you must have CREATE ANY INDEX AND GLOBALE To create any index in any schema u must have create table privilege USER_INDEXES view contains the name of the index and its unique ness USER_IND_COLUMNS view you cannot modify index first drop it and then recreate it

To varify the next available sequence no. query to USER_SEQUENCES(view) table IN LAST_NUMBER COLOMN ,IF NO

ONLY A DBA CAN DROP AND CREATE THE PUBLIC SYNONYM OR THE USER TO WHOME DBA HAVE GRANTED T PRIVELEGES ARE RIGHTS TO EXECUTE PERTICULAR SQL STATEMENTS CURRENT SYSTEM PRIVILEGES CAN BE FOUND IN DICITIONARY VIEW SESSION_PRIVS ROLES ARE CREATED BY DBA YOU CAN USE ALTER VIEW OR ALTER PROCEDURE COMMANDS TO RECOMPILE THEM a privilege granted on synonym is converted to privilege on base table refrenced by synonym

the account to which u will be connecting in remote database must have CREATE SESSION system privilege,These privile must have CREATE ANY DIRECTORY system privilege to create any dir object

INDEX

To create the query in another schema or another schema's table ,you must have CREATE ANY INDEX AND GLO

ATE ANY INDEX AND GLOBALE QUERY REWRITE privileger