What is the Oracle Intelligent Agent? (for DBA)
The Oracle Intelligent Agent (OIA) is an autonomous process that needs to run on a remote node in the network to make the node OEM manageable. The Oracle Intelligent Agent is responsible for:
. Discovering targets that can be managed (Database Servers, Net8 Listeners, etc.);
. Monitoring of events registered in Enterprise Manager; and
. Executing tasks associated with jobs submitted to Enterprise Manager.
Can one write scripts to send alert messages to the console?
Start the OEM console and create a new event. Select option "Enable Unsolicited Event". Select test "Unsolicited Event". When entering the parameters, enter values similar to these:
Event Name: /oracle/script/myalert
Object: *
Severity: *
Message: *
One can now write the script and invoke the oemevent command to send alerts to the console. Look at this example: oemevent /oracle/script/myalert DESTINATION alert "My custom error message" where DESTINATION is the same value as entered in the "Monitored Destinations" field when you've registered the event in the OEM Console.
Start the OEM console and create a new event. Select option "Enable Unsolicited Event". Select test "Unsolicited Event". When entering the parameters, enter values similar to these:
Event Name: /oracle/script/myalert
Object: *
Severity: *
Message: *
One can now write the script and invoke the oemevent command to send alerts to the console. Look at this example: oemevent /oracle/script/myalert DESTINATION alert "My custom error message" where DESTINATION is the same value as entered in the "Monitored Destinations" field when you've registered the event in the OEM Console.
Where can one get more information about TCL? (for DBA)
One can write custom event checking routines for OEM using the TCL (Tool Command Language) language. Check the following sites for more information about TCL:
. The Tcl Developer Xchange - download and learn about TCL
. OraTCL at Sourceforge - Download the OraTCL package
. Tom Poindexter's Tcl Page - Oratcl was originally written by Tom Poindexter
One can write custom event checking routines for OEM using the TCL (Tool Command Language) language. Check the following sites for more information about TCL:
. The Tcl Developer Xchange - download and learn about TCL
. OraTCL at Sourceforge - Download the OraTCL package
. Tom Poindexter's Tcl Page - Oratcl was originally written by Tom Poindexter
What is import/export and why does one need it? (for DBA)
The Oracle export (EXP) and import (IMP) utilities are used to perform logical database backup and recovery. They are also used to move Oracle data from one machine, database or schema to another.
The imp/exp utilities use an Oracle proprietary binary file format and can thus only be used between Oracle databases. One cannot export data and expect to import it into a non-Oracle database. For more information on how to load and unload data from files, read the SQL*Loader FAQ.
The export/import utilities are also commonly used to perform the following tasks:
. Backup and recovery (small databases only)
. Reorganization of data/ Eliminate database fragmentation
. Detect database corruption. Ensure that all the data can be read.
. Transporting tablespaces between databases
. Etc.
The Oracle export (EXP) and import (IMP) utilities are used to perform logical database backup and recovery. They are also used to move Oracle data from one machine, database or schema to another.
The imp/exp utilities use an Oracle proprietary binary file format and can thus only be used between Oracle databases. One cannot export data and expect to import it into a non-Oracle database. For more information on how to load and unload data from files, read the SQL*Loader FAQ.
The export/import utilities are also commonly used to perform the following tasks:
. Backup and recovery (small databases only)
. Reorganization of data/ Eliminate database fragmentation
. Detect database corruption. Ensure that all the data can be read.
. Transporting tablespaces between databases
. Etc.
What is a display item?
Display items are similar to text items but store only fetched or assigned values. Operators cannot navigate to a display item or edit the value it contains.
Display items are similar to text items but store only fetched or assigned values. Operators cannot navigate to a display item or edit the value it contains.
What are the types of visual attribute settings?
Custom Visual attributes Default visual attributes Named Visual attributes. Window
Custom Visual attributes Default visual attributes Named Visual attributes. Window
Can one export a subset of a table? (for DBA)
From Oracle8i one can use the QUERY= export parameter to selectively unload a subset of the data from a table. Look at this example:
exp scott/tiger tables=emp query=\"where deptno=10\"
From Oracle8i one can use the QUERY= export parameter to selectively unload a subset of the data from a table. Look at this example:
exp scott/tiger tables=emp query=\"where deptno=10\"
What are the two ways to incorporate images into a oracle forms application?
Boilerplate Images
Image_items
Boilerplate Images
Image_items
What do you mean by a block in forms4.0?
Block is a single mechanism for grouping related items into a functional unit for storing, displaying and manipulating records.
Block is a single mechanism for grouping related items into a functional unit for storing, displaying and manipulating records.
How is possible to restrict the user to a list of values while entering values for parameters?
By setting the Restrict To List property to true in the parameter property sheet.
By setting the Restrict To List property to true in the parameter property sheet.
What is SQL*Loader and what is it used for? (for DBA)
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.
SQL*Loader is a bulk loader utility used for moving data from external files into the Oracle database. Its syntax is similar to that of the DB2 Load utility, but comes with more options. SQL*Loader supports various load formats, selective loading, and multi-table loads.
How can a cross product be created?
By selecting the cross products tool and drawing a new group surrounding the base group of the cross products.
By selecting the cross products tool and drawing a new group surrounding the base group of the cross products.
What is the difference between boiler plat images and image items?
Boiler plate Images are static images (Either vector or bit map) that you import from the file system or database to use a graphical elements in your form, such as company logos and maps. Image items are special types of interface controls that store and display either vector or bitmap images. Like other items that store values, image items can be either base table items(items that relate directly to database columns) or control items. The definition of an image item is stored as part of the form module FMB and FMX files, but no image file is actually associated with an image item until the item is populate at run time.
Boiler plate Images are static images (Either vector or bit map) that you import from the file system or database to use a graphical elements in your form, such as company logos and maps. Image items are special types of interface controls that store and display either vector or bitmap images. Like other items that store values, image items can be either base table items(items that relate directly to database columns) or control items. The definition of an image item is stored as part of the form module FMB and FMX files, but no image file is actually associated with an image item until the item is populate at run time.
What are the triggers available in the reports?
Before report, Before form, After form , Between page, After report.
Before report, Before form, After form , Between page, After report.
Why is a Where clause faster than a group filter or a format trigger?
Because, in a where clause the condition is applied during data retrievalthan after retrieving the data.
Because, in a where clause the condition is applied during data retrievalthan after retrieving the data.
What are the various types of Exceptions ?
User defined and Predefined Exceptions.
User defined and Predefined Exceptions.
Can we define exceptions twice in same block ?
No.
No.
Describe Oracle database's physical and logical structure ?
Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.
Physical : Data files, Redo Log files, Control file.
Logical : Tables, Views, Tablespaces, etc.
Can you increase the size of a tablespace ? How ?
Yes, by adding datafiles to it.
Yes, by adding datafiles to it.
What is the use of Control files ?
Contains pointers to locations of various data files, redo log files, etc.
Contains pointers to locations of various data files, redo log files, etc.
What is the use of Data Dictionary ?
Used by Oracle to store information about various physical and logical Oracle structures e.g. Tables, Tablespaces, datafiles, etc
What are the advantages of clusters ?
Access time reduced for joins.
Access time reduced for joins.
What are the disadvantages of clusters ?
The time for Insert increases.
The time for Insert increases.
Can Long/Long RAW be clustered ?
No.
No.
Can null keys be entered in cluster index, normal index ?
Yes.
Yes.
Can Check constraint be used for self referential integrity ? How ?
Yes. In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.
Yes. In the CHECK condition for a column of a table, we can reference some other column of the same table and thus enforce self referential integrity.
What are the min. extents allocated to a rollback extent ?
Two
Two
What are the states of a rollback segment ? What is the difference between partly available and needs recovery ?
The various states of a rollback segment are :
ONLINE, OFFLINE, PARTLY AVAILABLE, NEEDS RECOVERY and INVALID.
The various states of a rollback segment are :
ONLINE, OFFLINE, PARTLY AVAILABLE, NEEDS RECOVERY and INVALID.
What is the difference between unique key and primary key ?
Unique key can be null; Primary key cannot be null.
Unique key can be null; Primary key cannot be null.
An insert statement followed by a create table statement followed by rollback ? Will the rows be inserted ?
No.
No.
an you define multiple savepoints ?
Yes.
Yes.
Can you Rollback to any savepoint ?
Yes.
Yes.
What is the maximum no. of columns a table can have ?
254.
254.
What is the significance of the & and && operators in PL SQL ?
The & operator means that the PL SQL block requires user input for a variable. The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable. If a transaction is very large, and the rollback segment is not able to hold the rollback information, then will the transaction span across different rollback segments or will it terminate ? It will terminate (Please check ).
The & operator means that the PL SQL block requires user input for a variable. The && operator means that the value of this variable should be the same as inputted by the user previously for this same variable. If a transaction is very large, and the rollback segment is not able to hold the rollback information, then will the transaction span across different rollback segments or will it terminate ? It will terminate (Please check ).
Can you pass a parameter to a cursor ?
Explicit cursors can take parameters, as the example below shows. A cursor parameter can appear in a query wherever a constant can appear. CURSOR c1 (median IN NUMBER) IS SELECT job, ename FROM emp WHERE sal > median;
Explicit cursors can take parameters, as the example below shows. A cursor parameter can appear in a query wherever a constant can appear. CURSOR c1 (median IN NUMBER) IS SELECT job, ename FROM emp WHERE sal > median;
What are the various types of RollBack Segments ?
Public Available to all instances
Private Available to specific instance
Public Available to all instances
Private Available to specific instance
Can you use %RowCount as a parameter to a cursor ?
Yes
Yes
Which symbol preceeds the path to the table in the remote database ?
@
@
Are views automatically updated when base tables are updated ?
Yes
Yes
Can a trigger written for a view ?
No
No
If all the values from a cursor have been fetched and another fetch is issued, the output will be : error, last record or first record ?
Last Record
Last Record
A table has the following data : [[5, Null, 10]]. What will the average function return ?
7.5
7.5
Is Sysdate a system variable or a system function?
System Function
System Function
Consider a sequence whose currval is 1 and gets incremented by 1 by using the nextval reference we get the next number 2. Suppose at this point we issue an rollback and again issue a nextval. What will the output be ?
3
3
Definition of relational DataBase by Dr. Codd (IBM)?
A Relational Database is a database where all data visible to the user is organized strictly as tables of data values and where all database operations work on these tables.
A Relational Database is a database where all data visible to the user is organized strictly as tables of data values and where all database operations work on these tables.
What is Multi Threaded Server (MTA) ?
In a Single Threaded Architecture (or a dedicated server configuration) the database manager creates a separate process for each database user. But in MTA the database manager can assign multiple users (multiple user processes) to a single dispatcher (server process), a controlling process that queues request for work thus reducing the databases memory requirement and resources.
In a Single Threaded Architecture (or a dedicated server configuration) the database manager creates a separate process for each database user. But in MTA the database manager can assign multiple users (multiple user processes) to a single dispatcher (server process), a controlling process that queues request for work thus reducing the databases memory requirement and resources.
Which are initial RDBMS, Hierarchical & N/w database ?
RDBMS - R system
Hierarchical - IMS
N/W - DBTG
RDBMS - R system
Hierarchical - IMS
N/W - DBTG
What is Functional Dependency
Given a relation R, attribute Y of R is functionally dependent on attribute X of R if and only if each X-value has associated with it precisely one -Y value in R
Given a relation R, attribute Y of R is functionally dependent on attribute X of R if and only if each X-value has associated with it precisely one -Y value in R
What is Auditing ?
The database has the ability to audit all actions that take place within it.
a) Login attempts, b) Object Accesss, c) Database Action Result of Greatest(1,NULL) or Least(1,NULL) NULL
The database has the ability to audit all actions that take place within it.
a) Login attempts, b) Object Accesss, c) Database Action Result of Greatest(1,NULL) or Least(1,NULL) NULL
While designing in client/server what are the 2 imp. things to be considered ?
Network Overhead (traffic), Speed and Load of client server
Network Overhead (traffic), Speed and Load of client server
When to create indexes ?
To be created when table is queried for less than 2% or 4% to 25% of the table rows.
To be created when table is queried for less than 2% or 4% to 25% of the table rows.
How can you avoid indexes ?
TO make index access path unavailable - Use FULL hint to optimizer for full table scan - Use INDEX or AND-EQUAL hint to optimizer to use one index or set to indexes instead of another. - Use an expression in the Where Clause of the SQL.
TO make index access path unavailable - Use FULL hint to optimizer for full table scan - Use INDEX or AND-EQUAL hint to optimizer to use one index or set to indexes instead of another. - Use an expression in the Where Clause of the SQL.
Can database trigger written on synonym of a table and if it can be then what would be the effect if original table is accessed.
Yes, database trigger would fire.
Yes, database trigger would fire.
Can you alter synonym of view or view ?
No
No
Can you create index on view ?
No
No
What is the difference between a view and a synonym ?
Synonym is just a second name of table used for multiple link of database. View can be created with many tables, and with virtual columns and with conditions. But synonym can be on view.
Synonym is just a second name of table used for multiple link of database. View can be created with many tables, and with virtual columns and with conditions. But synonym can be on view.
What is the difference between alias and synonym ?
Alias is temporary and used with one query. Synonym is permanent and not used as alias.
Alias is temporary and used with one query. Synonym is permanent and not used as alias.
What is the effect of synonym and table name used in same Select statement ?
Valid
Valid
What's the length of SQL integer ?
32 bit length
32 bit length
What is the difference between foreign key and reference key ?
Foreign key is the key i.e. attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.
Foreign key is the key i.e. attribute which refers to another table primary key. Reference key is the primary key of table referred by another table.
Can dual table be deleted, dropped or altered or updated or inserted ?
Yes
Yes
If content of dual is updated to some value computation takes place or not ?
Yes
Yes
If any other table same as dual is created would it act similar to dual?
Yes
Yes
For which relational operators in where clause, index is not used ?
<> , like '% ...' is NOT functions, field +constant, field || ''
<> , like '% ...' is NOT functions, field +constant, field || ''
Assume that there are multiple databases running on one machine. How can you switch from one to another ?
Changing the ORACLE_SID
Changing the ORACLE_SID
What are the advantages of Oracle ?
Portability : Oracle is ported to more platforms than any of its competitors, running on more than 100 hardware platforms and 20 networking protocols.
Market Presence : Oracle is by far the largest RDBMS vendor and spends more on R & D than most of its competitors earn in total revenue. This market clout means that you are unlikely to be left in the lurch by Oracle and there are always lots of third party interfaces available.
Backup and Recovery : Oracle provides industrial strength support for on-line backup and recovery and good software fault tolerence to disk failure. You can also do point-in-time recovery.
Performance : Speed of a 'tuned' Oracle Database and application is quite good, even with large databases. Oracle can manage > 100GB databases.
Multiple database support : Oracle has a superior ability to manage multiple databases within the same transaction using a two-phase commit protocol.
Portability : Oracle is ported to more platforms than any of its competitors, running on more than 100 hardware platforms and 20 networking protocols.
Market Presence : Oracle is by far the largest RDBMS vendor and spends more on R & D than most of its competitors earn in total revenue. This market clout means that you are unlikely to be left in the lurch by Oracle and there are always lots of third party interfaces available.
Backup and Recovery : Oracle provides industrial strength support for on-line backup and recovery and good software fault tolerence to disk failure. You can also do point-in-time recovery.
Performance : Speed of a 'tuned' Oracle Database and application is quite good, even with large databases. Oracle can manage > 100GB databases.
Multiple database support : Oracle has a superior ability to manage multiple databases within the same transaction using a two-phase commit protocol.
What is a forward declaration ? What is its use ?
PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before calling it. This declaration at the start of a subprogram is called forward declaration. A forward declaration consists of a subprogram specification terminated by a semicolon.
PL/SQL requires that you declare an identifier before using it. Therefore, you must declare a subprogram before calling it. This declaration at the start of a subprogram is called forward declaration. A forward declaration consists of a subprogram specification terminated by a semicolon.
What are actual and formal parameters ?
Actual Parameters : Subprograms pass information using parameters. The variables or expressions referenced in the parameter list of a subprogram call are actual parameters. For example, the following procedure call lists two actual parameters named emp_num and amount:
Eg. raise_salary(emp_num, amount);
Formal Parameters : The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters. For example, the following procedure declares two formal parameters named emp_id and increase: Eg. PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL;
Actual Parameters : Subprograms pass information using parameters. The variables or expressions referenced in the parameter list of a subprogram call are actual parameters. For example, the following procedure call lists two actual parameters named emp_num and amount:
Eg. raise_salary(emp_num, amount);
Formal Parameters : The variables declared in a subprogram specification and referenced in the subprogram body are formal parameters. For example, the following procedure declares two formal parameters named emp_id and increase: Eg. PROCEDURE raise_salary (emp_id INTEGER, increase REAL) IS current_salary REAL;
What are the types of Notation ?
Position, Named, Mixed and Restrictions.
Position, Named, Mixed and Restrictions.
What all important parameters of the init.ora are supposed to be increased if you want to increase the SGA size ?
In our case, db_block_buffers was changed from 60 to 1000 (std values are 60, 550 & 3500) shared_pool_size was changed from 3.5MB to 9MB (std values are 3.5, 5 & 9MB) open_cursors was changed from 200 to 300 (std values are 200 & 300) db_block_size was changed from 2048 (2K) to 4096 (4K) {at the time of database creation}.
The initial SGA was around 4MB when the server RAM was 32MB and The new SGA was around 13MB when the server RAM was increased to 128MB.
In our case, db_block_buffers was changed from 60 to 1000 (std values are 60, 550 & 3500) shared_pool_size was changed from 3.5MB to 9MB (std values are 3.5, 5 & 9MB) open_cursors was changed from 200 to 300 (std values are 200 & 300) db_block_size was changed from 2048 (2K) to 4096 (4K) {at the time of database creation}.
The initial SGA was around 4MB when the server RAM was 32MB and The new SGA was around 13MB when the server RAM was increased to 128MB.
What is PL/SQL?
PL/SQL is Oracle's Procedural Language extension to SQL. The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.
PL/SQL is Oracle's Procedural Language extension to SQL. The language includes object oriented programming techniques such as encapsulation, function overloading, information hiding (all but inheritance), and so, brings state-of-the-art programming to the Oracle database server and a variety of Oracle tools.
Is there a PL/SQL Engine in SQL*Plus?
No. Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine. Thus, all your PL/SQL are send directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and send to the database individually.
Share This :
No. Unlike Oracle Forms, SQL*Plus does not have a PL/SQL engine. Thus, all your PL/SQL are send directly to the database engine for execution. This makes it much more efficient as SQL statements are not stripped off and send to the database individually.
0 comments:
Post a Comment