1) Which of the following file is read to start the instance?
a. Controlfile b. Initialization Parameter file
c. Data files d. None
Ans: B.
Explanation: It will read init.ora parameter file for starting the instance.
2) Which file is read when database is mounted?Ans: Control file.
Explanation: Control file is read while we are mounting the database.
3) What actions will occur if we issue command startup at SQL prompt immediately then?
Ans: Instance is started, Database is mounted & then Database is opened.
4) What do dirty buffers comprises of?
Ans: Buffers modified but nit yet written to disk.
Explanation: Modified buffers in database buffer cache (SGA), which has not written to disk.
5) Which init.ora parameter is used to size database buffer cache?
Ans: db_cache_size or db_block_buffers.
6) What do library cache consists of?
Ans: Hold parsed versions of executed sql statements & Hold parsed versions of pl/sql program unit.
Explanation: Consists of both parsed versions of sql & pl/sql.
7) How can we size shared pool?
Ans: shared_pool_size.
Explanation: we have to specify shared_pool_size=
8) What does program global area (PGA) contains?
Ans: Users program variables, Users session information, & User defined cursors.
Explanation: PGA contains program variables, session information, and cursors. Not a SQL statement.
9) What happens during process of checkpoint?
Ans: It’s an event of recording modified blocks in database buffer cache onto data files.
Explanation: When checkpoint occurs it will invoke the DBWR to write dirty blocks from database buffer cache to database files.
10) What does SMON does?
Ans: Crash recovery, Clean up temporary segments & Coalescing free space
Explanation: SMON will do crash recovery; cleaning of temporary segments and coalescing free space. But it does not take care of background process.
11) Which file is read to start the instance?
Ans: Initialization parameter.
Explanation: It will read init.ora parameter file for starting the instance.
12) Which file is read when database is mounted?
Ans: Control file.
Explanation: control file is read while we are mounting the database.
13) What happens when we issue command startup at SQL prompt immediately?
Ans: Instance is started, Database is mounted & Database is opened.
14) Does PGA is a part of System Global Area?
Ans: NO.
Explanation: PGA (Program global area) is not a part of SGA. It is a separate memory structure.
15) What do dirty buffers comprises of?
Ans: Buffers modified but not yet written to disk.
Explanation: Modified buffers in database buffer cache (SGA), which has not written to disk.
16) Which init.ora parameter is used to size database buffer cache?
Ans: db_cache_size.
Explanation: If we want to change the size of database buffer cache we have to specify db_cache_size or db_block_buffers.
17) What do the library cache consists of?
Ans: Hold parsed versions of executed sql statements & Hold parsed versions of pl/sql program unit.
Explanation: Consists of both parsed versions of sql & pl/sql.
18) How can we size shared pool?
Ans: shared_pool_size.
Explanation: we have to specify shared_pool_size=
19) What does program global area (PGA) contains?
Ans: Users program variables, Users session information, & User defined cursors.
Explanation: PGA contains program variables, session information, and cursors. Not a SQL statement.
20) What happens during process of checkpoint?
Ans: It’s an event of recording modified blocks in database buffer cache onto data files.
Explanation: When checkpoint occurs it will invoke the DBWR to write dirty blocks from database buffer cache to database files.
21) What does SMON does?
Ans: Crash recovery, Clean up temporary segments & Coalescing free space
Explanation: SMON will do crash recovery; cleaning of temporary segments and coalescing free space. But it does not take care of background process.
22) The total number of Base tables that get created into sys account?
Ans: 1762.
Explanation: 1762 base tables will get created in sys account.
23) What is the status of your database when we run the create database file (ex. Cr8demo.sql)?
Ans: nomount.
Explanation: Database status should be in nomount state. Because for mounting a database it requires a control file.
24) Who are the users that created automatically the moment the database is created?
Ans: sys, Scott.
Explanation: sys and Scott users will get created when we create a database.
25) What is the table space that accommodates base tables?
Ans: system.
Explanation: It creates base tables in system table space.
26) What is the default table space for sys user?
Ans: system.
Explanation: Default table space for sys user is system.
27) Data Dictionary Views are static?
Ans: True.
Explanation: Data dictionary views are nothing but DBA_, ALL_, and USER_.
28) Is the database creation successful with this command?
Ans: True.
Explanation: It will use OMF for creating control file and data files.
29) What happens when ‘SHUTDOWN NORMAL’ is issued?
Ans: Database and redo buffers are written to disk, Background processes are terminated & the next startup does not require any instance recovery.
Explanation: When we issue a command SHUTDOWN NORMAL it will wait for connected users to disconnect but it doesn’t allow to any user to logged into the database.
30) Does the names & locations of data files is present in parameter file?
Ans: NO.
Explanation: It doesn’t maintain the location and names of data files that will be maintained by control file.
31) Can we create a tablespace with multiple datafiles at a single stroke?
Ans: Yes.
Explanation: We can create tablespace in single stroke with command SQL>Create tablespace
32) Can a datafile be associated with two different tablespaces?
Ans: No.
Explanation: One datafile can associated to one tablespace not more than one tablespace.
33) Suppose your database has max_datafiles limit of 80 and we want to add files above this limit which file we need to modify?
Ans: Controlfile.
Explanation: In controlfile we have to change MAXDATAFILES=
34) Which view tells us all the tablespaces in your database?
Ans: v$tablespace.
Explanation: V$TABLESPACE view give the tablespace details in a database.
35) Can we bring system tablespace offline when the database is up?
Ans: No.
Explanation: We cannot make system tablespace offline because it contains base tables.
36) What is the dafault initial extent size when the tablespace is dictionary managed?
Ans: 5 blocks.
Explanation: When we create a dictionary managed tablespace it will give the initial extent as 5*
37) Which parameter should be added in init.ora file for creating tablespace with multiple blocksizes?
Ans: db_nk_cache_size=n.
Explanation: We have to add db_Nk_cache_size=
38) What is the value for storage clause pctincrease when the tablespace extent management is local (uniform)?
Ans: 0%.
Explanation: PCTINCREASE for locally managed tablespace is 0%.
39) What is the command that combines all the smaller contiguous free extents in the tablespace into one larger extent?
Ans: coalesce.
Explanation: Coalesce is used to combine all the smaller contiguous free extents in the tablespace into one larger extent. Merge and sum are SQL commands related to table and add extents is not valid.
40) If the system datafile to be renamed, the database must be in which mode?
Ans: mount.
Explanation: For renaming a datafile belonging to system tablespace our database should be in mount state because system contains all the base tables when we open a database it will continuosly update the base tables evenif we are not performing transactions.
41) After creating a tablespace what is the default value for segment space management in 9i?
Ans: manual.
Explanation: Its manual in 9i and Oracle 10g it’s AUTO.
42) A tablespace was created with extent management as local. After that the tablespace extent management was changed from local to dictionary. What would be the next extent size?
Ans: 1m.
Explanation: Its 1m after the change.
43) If we create a tablespace with extent management dictionary and block size 8k with default storage initial 10k. After creating this tablespace whatvalue it will show for initial_extent in dba_tablespaces?
Ans: 40k.
Explanation: If extent management is dictionary then database requires initial extent size atleast (block_size*5), here its 8k*5=40k.
44) Can we create a table with your own parameters like ( initial 300k next 300k minextents) on tablespace whose extent management is local?
Ans: YES.
Explanation: Yes we can create.
45) A locally managed tablespace is made offline what is the status of bytes column in dba_data_files?
Ans: It shows the null value.
46) Can we resize a datafile where the related tablespace is in offline mode?
Ans: NO.
Explanation: We cannot do it.
47) DBA changed a datafile’s autoextend value to on, what is the default value for increment_by (column) located in dba_data_files?
Ans: 1m.
Explanation: When we changed datafile to Autoextend on then value of increment_by column in DBA_DATA_FILES will be 1m (by default) means after filling of datafile complete it will increase the size of datafile by 1m everytime.
48) Can we drop a object when the tablespace is in read only mode?
Ans: YES.
Explanation: Yes we can do it.
49) We are trying to create a table with your own storage parameters in a locally managed tablespace. Guess what happens?
Ans: It will create table with default storage parameters at tablespace level.
50) Extent deallocation for a segment is done when ___________?
Ans: dropped, truncate.
Explanation: When we dropped or truncate a object it will deallocate the extents for that segment.
51) What type of data is available in rollback segments?
a. previous image b. post updated image
c. meta data d. no data
52) One of these is not the purpose of rollback segments?
a. Undo previous command b. read consistency
c. Crash recovery d. backup support
53) What is the default status of rollback segment the moment it is created?
a. offline b. online
c. deferred d. pending
54) What is the storage parameter that is unique to rollback segments?
a. initial b. dictionary
c. optimal d. shrink
55) Suppose a rollback segment is occupied by a transaction and in the mean time the rollback segment is brought offline at that moment what is the status of tat rollback segment?
a. offline b. deferred
c. pending offline d. cannot be made offline
56) What does high water mark size (hwm size) in rollback segment state?
a. the max size rollback segment b. the optimal size of rollback
as grown ever segment
c. the min size rollback segment d. None
has ever been
57) Suppose the users tablespace is bought offline which has some open transaction later the user said commit, what is the status of the rollback segment at this stage?
a. deffered b. optimal
c. pending offline d. offline
58) To make rollback segments online the moment the database is started what is the file we need to modify?
a. controlfile b. logfile
c. init.ora d. orapwd file
59) Can a rollback segment hold multiple entries?
a. No b. Yes
60) Can we drop an undo tablespace which currently in use?
a. Yes b. No
61) Can we create permanent objects in default temporary tablespace of a DB?
a. YES b. NO
62) Can we make a tempfile read only?
a. YES b. NO
63) Which of the following view, by which we can find out the default temporary tablespace of a DB?
a. dba_temp_files b. v$tempfile
c. database_properties d. db_properties
e. None
64) what is the extent_management value for the temporary tablespace created in 10g?
a. Local b. Dictionary
c. System d. User
e. None
65) What is the minimum size for a temporary file to be created?
a. 1030k b. 1040k
c. 1041k d. 1031k
e. 1050k
66) What is the value for allocation_type column in dba_tablespaces view for temporary tablespace?
a. SYSTEM b. LOCAL
c. USER d. UNIFORM
67) Which of the following cmd is used to make the temporary TS as default temporary TS of DB?
a. SQL>alter database default b. SQL>alter database default
temporary tablespace tablespace temporary
c. SQL> alter database temporary d. SQL>alter system set default
tablespace
68) Which of the following conditions should meet to convert permanent TS into Temporary?
a. Extent Management Local Auto b. Extent Management Local
and TS must be empty Uniform & TS must be empty
c. Extent Management Dictionary & d. None
TS must be empty
69) What is the command to convert a permanent TS into temporary?
a. SQL>alter database tablespace b. alter tablespace
c. SQL>alter tablespace permanent d. SQL>alter database permanent
70) Can we create temporary tablespace with “SEGMENT SPACE MANAGEMENT AUTO” ?
a. YES b. NO
71) Create user without mentioning default tablespace clause. Then by default which tablespace allocate for that user?
a. system b. user_data
c. temp d. SYSAUX
e. Default TS of DB
72) One user assigned select on
<><><><><>to another user with grant option after that this second user assigned same privilege to third user. After that first user revoked this privilege from second user. Then third user can e use that already assigned privilege?
a. NO b. YES
73) If DBA created one role with some privileges and assigned this role this users. After that he want revoke on privilege from that users how?
a. revoke from
c. revoke from
74) DBA created one profile and assigned to users for applicable for that which parameter we need set in init.ora?
a. timed_statistics=true b.resource_limits=true
c. resource_limit=true d. none
75) Which privilege is necessary for a normal user to change his password?
a. create any table b. create session
c. alter user d. alter any user
76) How to manually lock user account?
a. user
c. alter user
by
77) From which view user can see his privilege?
a. user_role_privs b. dba_sys_privs
c. session_privs d. role_role_privs
78) One user as quota on two tablespaces. Can he create his tables other than default tablespace?
a. NO b. YES
79) System user granted DBA to normal user. Now can this user revoke DBA from system?
a. YES b. NO
80) For creating password file for sys in which directory we are created?
a. $HOME b. $ORACLE_HOME/rdbms/admin
c. $ORACLE_HOME/dbs d. $ORACLE_HOME/sqlplus/admin
81) What is the default location of listener.ora file?
a. $ORACLE_HOME/rdbms/admin b. $ORACLE_OME/dbs
c. $ORACLE_HOME/network/admin/ d. $ORACLE_HOME/network/tools/
samples samples
82) The listener service is stopped after giving connection to a client. What is the status of client?
a. connection will lost b. connection will be continued by
giving an error message
c. the client session hangs d. connection will be continued
without any messages
83) What is the command to start the listener for particular parameters set?
a. lsnrctl reload
c. tnsping
84) Can we start the listener service for a database which is not yet started/opened?
a. YES b. NO (minimum the DB must be in
mount state)
b. NO (First the DB should be opened) d. None of the above
85) In which file do we set this parameter FAILOVER=ON to use failover server option of oracle?
a. init.ora file b. tnsnames.ora
c. listener.ora d. bot listener.ora & tnsnames.ora
c. controlfile
86) Can we start multiple database services with in one listener service?
a. NO b. YES
c. YES & its only in Oracle 9i
87) Can I have multiple listeners for a single database?
a. YES b. NO
c. YES & its only in Oracle 9i
88) What is the view do we query to find out the users who are connected using oracle networking?
a. DBA_USERS b. DBA_NET_INFO
c. V$SESSION d. DBA_CLIENT_INFO
89) After some modifications to listener file, How can I refresh the already running listener service without stopping it?
a. lsnrctl start
c. lsnrctl status
90) Which operations we can perform using network connections?
a. DML b. DDL
c. A & B d. Only DML’s
91) Which background process is needed to create materialized view?
a. ckpt & cjq0 b. lgrw & reco
c. dbw0 & reco d. reco & cjq0
92) Which parameter do we use to start to reco process?
a. job_queue_process b. reco_processes
c. distributed_transactions d. global_names
93) Is it mandatory to put the parameter global_names=true for creating database links?
a. YES b. NO
94) For creating database links is it necessary to put some value for distributed_transactions parameter?
a. No (Not required at client) b. Yes (Needed only at client)
c. Yes (Needed at both client & server) d. Yes (Needed only at server)
95) Which background process refresh the materialized view on a given refresh interval?
a. cjq0 b. reco
c. arc0 d. ckpt
96) Can we do any DML operations on materialized view?
a. Yes ( Only its not possible with b. Yes (Only with refresh fast option)
Refresh fast option)
c. No d. Yes
97) How many refresh options do we have for creating materialized view?
a. Yes b. No
98) What is the segment type for a materialized view?
a. view b. table
c. materialized view d. synonym
99) Can we manually refresh any materialized view?
a. Yes b. No
100) What is the syntax to drop materialized view?
a. SQL>DROP VIEW
cascade;
c.SQL>DROP MATERIALIZED VIEW d.SQL>DROP
ANSWERS TO THE QUESTIONS FROM Q51 – Q100
51) Ans: A (Previous image).
Explanation: The main purposes of rollback segments are to maintain before/previous image of data.
52) Ans: D.
Explanation: Backup support is not a function of rollback segment
53) Ans: A (offline).
Explanation: After creating rollback segment the default status will be offline.
54) Ans: C (optimal).
Explanation: For a rollback segment unique storage parameter is optimal for shrinking.
55) Ans: C (Pending offline).
Explanation: When rollback supporting one transaction and in mean time if you are making that rollback segment offline then the status of rollback segment will be pending offline because one active transaction was going on that rollback segment.
56) Ans: A (The max size rollback segment has grown ever).
Explanation: High water mark size indicates the max size of rollback segment has grown ever in his life time.
57) Ans: A (deferred).
Explanation: The status of rollback segment will be deferred.
58) Ans: C (init.ora).
Explanation: We have to modify init.ora parameter file to online any rollback segment when DB is started.
59) Ans: B (YES).
Explanation: YES rollback segments can hold multiple entries. It work on First in First serve basis.
60) Ans: B (NO).
Explanation: Oracle donot allow to drop undo tablespace which in use, because other than sys user system rollback segment cannot use any other users.
61) Ans: B.
Explanation: We cannot create an permanent object in any temporary tablespace.
62) Ans: B.
Explanation: DBA cannot make any tempfile read only
63) Ans: C.
Explanation: By database_properties we can find which TS is default temporary TS for a database.
64) Ans: A.
Explanation: It’s a Local, because extent information of dictionary managed tablespace will be stored in data dictionary and locally managed tablespace extent information will be stored in locally in same tablespace so it will reduce the burden on dictionary. If temporary tablespace is dictionary managed then its burden is on data dictionary.
65) Ans: C.
Explanation: We can create temporary file wit minimum 1041k.
66) Ans: D.
Explanation: Oracle will uniformly allocate extents for temporary tablespace.
67) Ans: A.
Explanation: SQL>alter database default emporary tablespace
68) Ans: C.
Explanation: That tablespace should be dictionary managed and must be empty.
69) Ans: B.
Explanation: Alter tablespace
70) Ans: B.
Explanation: No we cannot create temporary tablespace with segment space management auto.
71) Ans: E.
Explanation: From 10g onwards which is the default tablespace for database that will be assign to the user. In 9i it is system tablespace.
72) Ans: A.
Explanation: No the user cannot
73) Ans: C.
Explanation: We have to revoke the privilege from the role. We cannot revoke directly from the user.
74) Ans: C.
Explanation: Resourse_limit=true we have set init.ora file so profile will effect on user.
75) Ans: B.
Explanation: User require create session privilege to change his own password because he is own that whole schema.
76) Ans: B.
Explanation: Alter user
77) Ans: C.
Explanation: session_privs will show the privileges for that user.
78) Ans: B.
Explanation: Yes if user is having quota on different tablespaces he can create is own objects on that tablespaces.
79) Ans: A.
Explanation: User can revoke a dba privilege from sysdba. Because Oracle database requires at any point of time one DBA only.
80) Ans: C.
Explanation: We have to create your password file in ORACLE_HOME/dbs directory only then only it will read that password file.
81) Ans: C.
Explanation: Bydeafult listener.ora file will be available in $ORACLE_HOME/network/admin/samples.
82) Ans: D.
Explanation: Connection will be continued without any messages because listener as already authenticated that user for database.
83) Ans: B.
Explanation: LSNRCTL START
84) Ans: A.
Explanation: Listener is independent from database that’s why we can start/stop the listener without database also.
85) Ans: B.
Explanation: In TNSNAMES.ORA file we have to specify FAILOVER=ON to use failover server option of Oracle networking.
86) Ans: B.
Explanation: Yes we can start n number of services with one listener.
87) Ans: A.
Explanation: Yes we can configure n number of listener for one database.
88) Ans: C.
Explanation: We can query V$SESSION to find out all the information of users who are logged in to your database. We can find out from where user logged in, at what time he logged in, etc.
89) Ans: B.
Explanation: We can use reload option to refresh already running listener.
90) Ans: C.
Explanation: We can use DML and DDL operations using network connection because using oracle networking directly your login to user SCHEMA.
91) Ans: D.
Explanation: cjq0 process is required to refresh materialized views and reco is required for maintaining distributed transactions between database.
92) Ans: C.
Explanation: DISTRIBUTED_TRANSACTIONS parameter is responsible for distributed transactions. From 9i onwards this reco is mandatory backgroud process so Oracle deprecated this parameter.
93) Ans: B.
Explanation: It is not mandatory to put parameter global_names=true for creating database link because this parameter we have to set when we are creating global database links.
94) Ans: B.
Explanation: Its required only at client side.
95) Ans: A.
Explanation: CJQ0 background process will refresh the materialized view after every refresh interval.
96) Ans: C.
Explanation: No materialized view is only read only we cannot perform any DML operations on materialized views.
97) Ans: B.
Explanation: We have only three refresh options for creating materialized view (COMPLETE,FAST,FORCE).
98) Ans: A.
Explanation: Yes we can refresh materialized view manually using DBMS_MVIEW package.
99) Ans: B.
Explanation: When we create a materialized view that a local table in database so segment type of materialized view is TABLE.
100) Ans: C.
Explanation: SQL> DROP MATERIALIZED VIEW
**************************************************************************************
Question : What are the components of physical database structure of Oracle database
totally there are 6 files(components) of physical database structure.3 mandatory & 3 optional. Three mandatory files are : 1> datafile : store actual data 2> control file : strores stuctural & status information of database. 3> redolog file : stores changed/committed data Three optional files are : 4> parameter file : strores all size related parameters note : this file is mandatory for a1st time u create a database, then it is optional. 5> archivelog file : its offline copy of redolog files 6> password file : used to make normal user to behave as a super user.
Question : What are the components of logical database structure of Oracle database
Tablespace, segments, extens, data Blocks.
A logical unit of storage of databse is called Tablespace.
Segments is a space alloocated for a specific logical storage structure within a tablespce.
Extents: Space allocated to a segments.
Datablocks: Oracle server manage the storage space in the datafiles in units is called data blocks or oracle blocks.
Question : What is a tablespace
An Oracle database consists of one or more logical storage units called tablespaces, which collectivley store all of the database's data.
Each tablespace in an ORACLE database consists of one or more files called datafiles, which are physical structures that conform with the operating system in which Oracle is running.
For Example::: A simple Oracle Database have one tablespace and one datafile
Question : What is SYSTEM tablespace and when is it created
System tablespace is the memory allocated by oracle for creation of objects,views,indexes.
this is created automatically by oracle when the Database is created
SYSTEM TABLESPACE USAGE NOTES:
Username - Name of the user
Created - User creation date
Profile - Name of resource profile assigned to the user
Default Tablespace - Default tablespace for data objects
Temporary Tablespace - Default tablespace for temporary objects
Only SYS, SYSTEM and possibly DBSNMP should have their default tablespace set to SYSTEM.
select USERNAME,
CREATED,
PROFILE,
DEFAULT_TABLESPACE,
TEMPORARY_TABLESPACE
from dba_users
order by USERNAME
Objects in SYSTEM TS
OBJECTS IN SYSTEM TABLESPACE NOTES:
Owner - Owner of the object
Object Name - Name of object
Object Type - Type of object
Tablespace - Tablespace name
Size - Size (bytes) of object
Any user (other than SYS, SYSTEM) should have their objects moved out of the SYSTEM tablespace
select OWNER,
SEGMENT_NAME,
SEGMENT_TYPE,
TABLESPACE_NAME,
BYTES
from dba_segments
where TABLESPACE_NAME = 'SYSTEM'
and OWNER not in ('SYS','SYSTEM')
order by OWNER, SEGMENT_NAME
Question : Explain the relationship among database, tablespace and data file. What is schema
Databases, tablespaces and datafiles are closely related, but they have important differences:
--- A Oracle Database consists of one or more tablespaces
--- Each Table space in an Oracle database consists of one or more files called datafiles.
--- A database's data is collectively stored in the datafiles that constitute each tablespace of the database.
When a database user is created, a corresponding schema with the same name is created for that user. A schema is a named collection of objects that include Tables, Triggers, constraints, Indexes, Views etc. A user can only be associated with one schema, and that is the same name as the user's. Username and schema are often used interchangeably.
Question : What are Schema Objects
A schema is a collection of database objects. A schema is owned by a database user and has the same name as that user. Schema objects are logical structures created by users. Objects may define areas of the database to hold data, such as tables or indexes, or may consist just of a definition, such as a views or synonyms.
There is no relationship between a tablespace and a schema. Objects in the same schema can use storage in different tablespaces, and a tablespace can contain data from different schemas.
Schema objects can be created and manipulated using SQL. As an administrator, you can create and manipulate schema objects, just as you do with the logical and physical structures of your database using Oracle Enterprise Manager. The underlying SQL is generated for you by Oracle Enterprise Manager.
Question : Can objects of the same schema reside in different tablespaces
Yes , it can .
for example if you specify a different tablepace (B) for indexes, the indexes of the tables that the user create would be residing in B , and the table would reside in the user's default tablespace A.
Question : What is an Oracle view
A view is a logical table which makes a complex query easy.We can even create a complex view by joining two tables.
Question : What are the advantages of views
Views provide many advantages, like:
(a) They restrict access to the whole data, because they display only selective columns.
(b) They can be used to make complex queries easy. A user can use a simple query on a view to display data from multiple tables, without having the knowledge of how to join tables in queries.
(c) Different views can be created from the same data as per the requirements of different types of use groups.
Security
l Query Simplification
l Allows Different Perspective
l Schema Transparency / Location Transparency
l Schema Consistency
l Allows work-around for SQL limitations
Security
• to provide an additional level of table security by restricting access to a
predetermined set of rows and/or columns of a table
CREATE VIEW emp_sal_hist_v
AS
SELECT ratehist.employee, ratehist.beg_date, ratehist.pay_rate
FROM ratehist, employee
WHERE ratehist.company = employee.company
AND ratehist.employee = employee.employee
AND USER = employee.user_id;
Query Simplification
For example, a single view might be defined with a join, which is a collection of related columns or rows in multiple tables. However, the view hides the fact that this information actually originates from several tables. Saving of complex queries also permits simplified commands for an end-user who does not know how to make joins and/or cryptic business rules governing a join.
Allows Different Perspective
For example, the columns of a view can be renamed without affecting the tables on which the view is based.
Columns cannot be dropped from tables in version 7.x, but you could recreate views without the unnecessary column.
Schema Transparency / Location Transparency
• Ability to hide the schema of data from the application, and therefore the user.
• For example, if a view's defining query references three columns of a four column table and a fifth column is added to the table, the view's definition is not affected and all applications using the view are not affected.
• Views can also be used to join tables across database schemas OR across databases (using remote links), thereby encapsulating schema names from the end user.
Schema Consistency
• If a web application is accessing legacy data and then we migrate over to a new system.
• Identify legacy tables accessed through the web.
• Create a view look-alike for each legacy table and have it return the samedata.
• Though not a long-term solution, will allow intermediate means of allowing the web application to run while the interface is rebuilt to the new system.
this is all about advantages of view.
Question : What is an Oracle sequence
A sequence is a database object created by a user that can be used to generate unique integers. A typical usage of sequences is to generate primary key values which are unique for each row.
It is generated and incremented (or decremented) by an internal Oracle routine. It can be used by multiple users and for multiple tables too. A sequence can be used instead of writing an application code for sequence-generating routine.
Question : What is a private synonym and public synonym
To create a private synonym in your own schema, you must have CREATE SYNONYM system privilege.
To create a private synonym in another user's schema, you must have CREATE ANY SYNONYM system privilege.
To create a PUBLIC synonym, you must have CREATE PUBLIC SYNONYM system privilege.
Use the CREATE SYNONYM statement to create a synonym, which is an alternative name for a table, view, sequence, procedure, stored function, package, materialized view, Java class schema object, user-defined object type, or another synonym.
Synonyms provide both data independence and location transparency. Synonyms permit applications to function without modification regardless of which user owns the table or view and regardless of which database holds the table or view. However, synonyms are not a substitute for privileges on database objects. Such privileges must be granted to a user before the user can use the synonym. ‘
Commentaires
Enregistrer un commentaire