1. What is an Oracle instance?
Overview of an Oracle Instance :-
Every running Oracle database is associated with an Oracle instance. When adatabase is started on a database server (regardless of the type of computer),Oracle allocates a memory area called the System Global Area (SGA) and startsone or more Oracle processes. This combination of the SGA and the Oracleprocesses is called an Oracle instance. The memory and processes of an instancemanage the associated database’s data efficiently and serve the one or multipleusers of the database.
The Instance and the Database
After starting an instance, Oracle associates the instance with the specifieddatabase. This is called mounting the database. The database is then ready to beopened, which makes it accessible to authorized users.
Multiple instances can execute concurrently on the same computer, each accessingits own physical database. In clustered and massively parallel systems (MPP),the Oracle Parallel Server allows multiple instances to mount a single database.
Only the database administrator can start up an instance and open the database.If a database is open, the database administrator can shut down the database sothat it is closed. When a database is closed, users cannot access theinformation that it contains.
Security for database startup and shutdown is controlled via connections toOracle with administrator privileges. Normal users do not have control over thecurrent status of an Oracle database.
2)What is a view?
View:-
A view is a tailored presentation of the data contained in one or more tables(or other views). Unlike a table, a view is not allocated any storage space, nordoes a view actually contain data; rather, a view is defined by a query thatextracts or derives data from the tables the view references. These tables arecalled base tables.
Views present a different representation of the data that resides within thebase tables. Views are very powerful because they allow you to tailor thepresentation of data to different types of users.
Views are often used to:
• provide an additional level of table security by restricting access to apredetermined set of rows and/or columns of a table• hide data complexity• simplify commands for the user• present the data in a different perspective from that of the base table• isolate applications from changes in definitions of base tables• express a query that cannot be expressed without using a view
What is referential integrity?Rules governing the relationships between primary keys and foreign keys oftables within a relational database that determine data consistency. Referentialintegrity requires that the value of every foreign key in every table be matchedby the value of a primary key in another table.
3)Name the data dictionary that stores user-defined constraints?
USER_CONSTRAINTS
4)What is a collection of privileges?user_tab_privs_madeuser_tab_privs_recd
Snapshot: A snapshot is a read-only copy of a table or a subset of a table.
5)What is a cursor?
cursor is a private sql work area used to perform manipulations on data using pl\sql.adv:1.mainly used for multiple row manipulations and locking columns.note: data which is populated into the cursor is known as active dataset.
cursors are of two types1.implicit2.explicitimplicit———attributes or properties for implicit cursor1.sql%is open:attribute returns a boolean value stating wether the cursor is open or closed.2.sql % found: returns boolean value stating whether the record is found in the cursor.3.sql%notfound : returns a boolean value stating whether the record is not found in the cursor4.sql %rowcount :returns a pneumeric value stating no.of rows executed in the cursor.
explicit cursors—retrives multiple rows.************adv: users can perform locks on th data in the cursorattributes1.% is open2.% found3.% not found4.% rowcount
Note: DATA which is populated in the cursor is known as active data set.
WE CAN WRITE TWO CURSORS IN ONE PROGRAM
WE CAN WRITE A CURSOR SPECIFYING PARAMETERS
CURSOR WITH UPDATE CLAUSE IS USED TO PERFORM LOCKS ON DATA.
6)What is a sequence?
Ans:It is a database object to auto generate numbers.
7)Name the data dictionary that stores user-defined Stored procedures?
ans :- user_objects
8)Why Use Sql* Loader in Oracle Database?
Answer: The Sql Loader module of the oracle database Management System loads data into an existing ORACLE table from an external files.It is available locally only on CMS and PCs with oracle version 5. Throughout this documentation the CAR database described in Referance A is used for illustration.There are several methods others than using SQL *Loader of inserting data into a table.1. The Sql insert command may be used from the SQL * Plus module,for Example :insert into CAR values(…)where the values to be inserted into a row of the table are listed inside the parentheses. Dates and Characters data must be Surrounded by single quotes; items are seperated by commas.2. Sql*Forms allows you to add rows interactively using forms. The forms may contain default values and checks for invalid data.3. ODL loads the table from a control file and separate fixed format data file. ODL is available on all versions of ORACLE . SQL * Loader is much more flexible than ODL and will eventually supersede it on all systems.
We can execute a function in sql query
select functionname(paramaters) from dual;
Suppose i am created a function add which returns the addition of two numbers then
select add(3,4) from dual;7
ODBC stands for open database connectivity
trigger is a stored procedure which auotomatically fired on a table whenever any dml operation is affected in the table.we can create ddl trigger,database trigger(logon,logoff,startup,startoff),audit triggers
pseudo column the column which does not exist in a tableex;rownum,rowid,level etc
9)what is a synonym ?
Ans. A synonym is an alternative name for tables,views,procedures and other database objectsgenerally when we have more than one schema and we wish to access an object of a different schema then we create synonyms for that object in the schema where we wish to access the object.
Syntax:create synonym synonym-name for schemaname.object-name
10)what is an exception ?
Exception is an event that causes suspension of normal program execution.In oracle there are serveral types of exceptions1) Pre-defined exceptions like NO_DATA_FOUND,TOO_MANY_ROWS2) User-defined exceptions which would validate the business logic3) unnamed system exceptions which are raised due to errors in the application code .. you can name these exceptions using PRAGMA EXCEPTION_INIT4)Unnamed programmer-defined exceptions. Exceptions that are defined and raised in the server by the programmer. In this case, the programmer provides both an error number (between -20000 and -20999) and an error message, and raises that exception with a call to RAISE_APPLICATION_ERROR.
for all the exceptions raised oracle fills in sqleerm and sqlcode variable which provide the error message and error code for the exception raised.
11)What are pseudo-columns in SQL? Provide examples?
A pseudocolumn behaves like a table column, but is not actually stored in the table. You can select from pseudocolumns, but you cannot insert, update, or delete their values.
Examples:CURRVAL,NEXTVAL,ROWID,LEVEL
12)What is a schema ?
A)Dabase user account is called a schema.
A) A schema is a collection of logical objects owned by a user. A user in that regard is an account registered with the Oracle server.
Once u login into ur account/user, u can access other user’s schema objects like i can write scott.emp, if at all i’ve acces to scott user’s emp table.A) schema is a logical collection of database objects like tables, views, pkgs, procedures, triggers, etc. It usually has an associated database user.
13)What is a co-related sub-query?
It is very similar to sub-queries where the parent query is executed based on the values returned by sub-quries. but when come’s to co-related subqueries for every instance of parent query subquery is executed and based on the result od sub-query the parent query will display the record as we will have refernce of parent quries in su-queries we call these as corelated subquries.
so, we can define co-related sub query as for every record retrival from the sub query is processed and based on result of process the parent record is displayed.
14)what is trigger?
Trigger is an event. It is used prevent the invalid entries of the data.Therehas a different types of trigger are available.1)rowlevel triggerbefore insert,before delete,before updateafter insert,after delete,after update2)statement level triggerbefore insert,before delete,before updateafter insert,after delete,after update3)INSTEAD OF trigger4)Schema level Triggers5)System level Triggers
No comments:
Post a Comment