Interviews - C# Corner

afsal neelambra
pls send more sql quastions and answers
By afsal neelambra in SQL Server on Jul 12 2011
  • Aarti
    Nov, 2011 18

    1.What is Normalization in Database?
    Normalization is a technique to optimize redundancy of data .
    A relational database is basically composed of tables that contain related data.
    So the Process of organizing this data into tables is actually referred to as normalization.

    • 0
  • Satyapriya Nayak
    Jul, 2011 16

    DB Interview Questions<br><br>Q:&nbsp;&nbsp;&nbsp; What is SQL?<br>A:&nbsp;&nbsp;&nbsp; SQL stands for 'Structured Query Language'.<br>&nbsp;&nbsp; &nbsp;<br><br><br>Q:&nbsp;&nbsp;&nbsp; What is SELECT statement?<br>A:&nbsp;&nbsp;&nbsp; The SELECT statement lets you select a set of values from a table in a database. The values selected from the database table would depend on the various conditions that are specified in the SQL query.<br>&nbsp;&nbsp; &nbsp;<br><br><br>Q:&nbsp;&nbsp;&nbsp; How can you compare a part of the name rather than the entire name?<br>A:&nbsp;&nbsp;&nbsp; SELECT * FROM people WHERE empname LIKE '%ab%'<br>Would return a recordset with records consisting empname the sequence 'ab' in empname .<br>&nbsp;&nbsp; &nbsp;<br><br><br>Q:&nbsp;&nbsp;&nbsp; What is the INSERT statement?<br>A:&nbsp;&nbsp;&nbsp; The INSERT statement lets you insert information into a database.<br><br>Q:&nbsp;&nbsp;&nbsp; How do you delete a record from a database?<br>A:&nbsp;&nbsp;&nbsp; Use the DELETE statement to remove records or any particular column values from a database.<br>&nbsp;&nbsp; &nbsp;<br><br><br>Q:&nbsp;&nbsp;&nbsp; How could I get distinct entries from a table?<br>A:&nbsp;&nbsp;&nbsp; The SELECT statement in conjunction with DISTINCT lets you select a set of distinct values from a table in a database. The values selected from the database table would of course depend on the various conditions that are specified in the SQL query. Example<br>SELECT DISTINCT empname FROM emptable<br>&nbsp;&nbsp; &nbsp;<br><br><br>Q:&nbsp;&nbsp;&nbsp; How to get the results of a Query sorted in any order?<br>A:&nbsp;&nbsp;&nbsp; You can sort the results and return the sorted results to your program by using ORDER BY keyword thus saving you the pain of carrying out the sorting yourself. The ORDER BY keyword is used for sorting.<br><br>SELECT empname, age, city FROM emptable ORDER BY empname<br>&nbsp;&nbsp; &nbsp;<br><br><br>Q:&nbsp;&nbsp;&nbsp; How can I find the total number of records in a table?<br>A:&nbsp;&nbsp;&nbsp; You could use the COUNT keyword , example<br><br>SELECT COUNT(*) FROM emp WHERE age&gt;40<br>&nbsp;&nbsp; &nbsp;<br><br><br>Q:&nbsp;&nbsp;&nbsp; What is GROUP BY?<br>A:&nbsp;&nbsp;&nbsp; The GROUP BY keywords have been added to SQL because aggregate functions (like SUM) return the aggregate of all column values every time they are called. Without the GROUP BY functionality, finding the sum for each individual group of column values was not possible.<br>&nbsp;&nbsp; &nbsp;<br><br><br>Q:&nbsp;&nbsp;&nbsp; What is the difference among "dropping a table", "truncating a table" and "deleting all records" from a table.<br>A:&nbsp;&nbsp;&nbsp; Dropping : (Table structure + Data are deleted), Invalidates the dependent objects ,Drops the indexes<br>Truncating: (Data alone deleted), Performs an automatic commit, Faster than delete<br>Delete : (Data alone deleted), Doesn?t perform automatic commit<br>&nbsp;&nbsp; &nbsp;<br><br><br>Q:&nbsp;&nbsp;&nbsp; What are the Large object types suported by Oracle?<br>A:&nbsp;&nbsp;&nbsp; Blob and Clob.<br>&nbsp;&nbsp; &nbsp;<br><br><br>Q:&nbsp;&nbsp;&nbsp; Difference between a "where" clause and a "having" clause.<br>A:&nbsp;&nbsp;&nbsp; Having clause is used only with group functions whereas Where is not used with.<br>&nbsp;&nbsp; &nbsp;<br><br><br>Q:&nbsp;&nbsp;&nbsp; What's the difference between a primary key and a unique key?<br>A:&nbsp;&nbsp;&nbsp; Both primary key and unique enforce uniqueness of the column on which they are defined. But by default primary key creates a clustered index on the column, where are unique creates a nonclustered index by default. Another major difference is that, primary key doesn't allow NULLs, but unique key allows one NULL only.<br>&nbsp;&nbsp; &nbsp;<br><br><br>Q:&nbsp;&nbsp;&nbsp; What are cursors? Explain different types of cursors. What are the disadvantages of cursors? How can you avoid cursors?<br>A:&nbsp;&nbsp;&nbsp; Cursors allow row-by-row prcessing of the resultsets.<br>Types of cursors: Static, Dynamic, Forward-only, Keyset-driven. See books online for more information.<br>Disadvantages of cursors: Each time you fetch a row from the cursor, it results in a network roundtrip, where as a normal SELECT query makes only one rowundtrip, however large the resultset is. Cursors are also costly because they require more resources and temporary storage (results in more IO operations). Furthere, there are restrictions on the SELECT statements that can be used with some types of cursors.<br>Most of the times, set based operations can be used instead of cursors.<br><br>Q:&nbsp;&nbsp;&nbsp; What are triggers? How to invoke a trigger on demand?<br>A:&nbsp;&nbsp;&nbsp; Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.<br>Triggers can't be invoked on demand. They get triggered only when an associated action (INSERT, UPDATE, DELETE) happens on the table on which they are defined.<br>Triggers are generally used to implement business rules, auditing. Triggers can also be used to extend the referential integrity checks, but wherever possible, use constraints for this purpose, instead of triggers, as constraints are much faster.<br><br>Q:&nbsp;&nbsp;&nbsp; What is a join and explain different types of joins.<br>A:&nbsp;&nbsp;&nbsp; Joins are used in queries to explain how different tables are related. Joins also let you select data from a table depending upon data from another table.<br>Types of joins: INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.<br>Q:&nbsp;&nbsp;&nbsp; What is a self join?<br>A:&nbsp;&nbsp;&nbsp; Self join is just like any other join, except that two instances of the same table will be joined in the query. <br><br>Question: How to get which Process is Blocked in SQL SERVER ?<br><br>Answer:- There are two ways to get this sp_who and sp_who2 . You<br>cannot get any detail about the sp_who2 but its provide more information<br>then the sp_who . And other option from which we can find which process is<br>blocked by other process is by using Enterprise Manager or Management<br>Studio, these two commands work much faster and more efficiently than<br>these GUI-based front-ends.<br><br>Question: What is COMMIT &amp; ROLLBACK statement in SQL ?<br><br>Answer: Commit statement helps in termination of the current<br>transaction and do all the changes that occur in transaction persistent<br>and this also commits all the changes to the database.COMMIT we can also<br>use in store procedure. ROLLBACK do the same thing just terminate the<br>currenct transaction but one another thing is that the changes made to<br>database are ROLLBACK to the database.<br><br>Question:-What is diffrence between OSQL and Query Analyzer ?<br><br>Answer:-Both are the same but ther eis little diffrence OSQL is<br>command line tool whic is execute qery and display the result same a query<br>analyzer but query analyzer is graphical and OSQL is a command line<br>tool.OSQL have not ability like query analyzer to analyze queries and show<br>statics on speed of execution and other usefull thing about OSQL is that<br>its helps in scheduling.<br><br>Question: What is SQL whats its uses and its component ?<br><br>Answer: The Structured Query Language (SQL) is foundation for all relational database<br>systems. Most of the large-scale databases use the SQL to define all user<br>and administrator interactions. QL is Non-Procedural language . Its allow<br>the user to concentrate on specifying what data is required rather than<br>concentrating on the how to get it. The DML component of SQL comprises<br>four basic statements: * SELECT to get rows from tables * UPDATE to<br>update the rows of tables * DELETE to remove rows from tables *<br>INSERT to add new rows to tables<br><br>Question: What is DTS in SQL Server ?<br><br>Answer: If a organization is<br>big then it is also there that there is multiple option to store data some<br>people are using EXCEL some are using ACCESS and some of they are using<br>SQL SERVER and in some other format also but there a problem is arise that<br>how to merge that data into one format there is diffrent tool are there<br>for doing this funtion. One of product of SQL SERVER-2000 DTS helps in<br>this problem it provides a set of tool from that tool we can customise are<br>database acording to our need DTSRun is a command-prompt utility used to<br>execute existing DTS packages.<br><br>Question: What is the diffrence between SQL and Pl/Sql ?<br><br>Answer: We<br>can get modify, Retrieve by single command or statement in SQL but PL/SQL<br>process all SQL statements one at a time. With PL/SQL, an entire block of<br>statements process in a single command line.sql is structured query<br>language ,various queries are used to handle the database in a simplified<br>manner. while pl/sql is procedural language contains various types of<br>variable,functions and procedures and other major diffrence is Sql as the<br>name suggest it is just structured query language wheareas PLSQL is a<br>commbination of Programming language &amp;amp; SQL.<br><br>Question: What is the difference between UNION ALL Statement and UNION<br>?<br><br>Answer:- The main difference between UNION ALL statement and UNION<br>is UNION All statement is much faster than UNION,the reason behind this is<br>that because UNION ALL statement does not look for duplicate rows, but on<br>the other hand UNION statement does look for duplicate rows, whether or<br>not they exist.<br><br>Question: Write some disadvantage of Cursor ?<br><br>Answer:- Cursor plays<br>there row quite nicely but although there are some disadvantage of Cursor<br>. Because we know cursor doing roundtrip it will make network line busy<br>and also make time consuming methods. First of all select query gernate<br>output and after that cursor goes one by one so roundtrip happen.Another<br>disadvange of cursor are ther are too costly because they require lot of<br>resources and temporary storage so network is quite busy.<br><br>Question: What is Log Shipping and its purpose ?<br><br>Answer: In Log<br>Shipping the transactional log file from one server is automatically<br>updated in backup database on the other server and in the case when one<br>server fails the other server will have the same DB and we can use this as<br>the DDR(disaster recovery) plan.<br><br>Question: What are the null values in SQL SERVER ?<br><br>Answer: Before<br>understand the null values we have some overview about what the value is.<br>Value is the actual data stored in a particular field of particular<br>record. But what is done when there is no values in the field.That value<br>is something like &amp;lt;null&amp;gt;.Nulls present missing information. We can<br>also called null propagation.<br><br>Question: What is difference between OSQL and Query Analyzer<br>?<br>Answer: Both are same for functioning but there is a little<br>difference OSQL is command line tool which execute query and display the<br>result same a Query Analyzer do but Query Analyzer is graphical.OSQL have<br>not ability like Query Analyzer to analyze queries and show statistics on<br>speed of execution .And other useful thing about OSQL is that its helps in<br>scheduling which is done in Query Analyzer with the help of JOB. Sql<br>Server 2000 Query<br><br>Question: Write a Role of Sql Server 2005 in XML Web Services?<br><br>Answer:- SQL Server 2005 create a standard method for getting the<br>database engine using SOAP via HTTP. By this method, we can send SOAP/HTTP<br>requests to SQL Server for executing T-SQL batch statements, stored<br>procedures, extended stored procedures, and scalar-valued user-defined<br>functions may be with or without parameters.<br><br>Question: What are the different types of Locks ?<br><br>Answer: There are<br>three main types of locks that SQL Server (1)Shared locks are used for<br>operations that does not allow to change or update data, such as a SELECT<br>statement. (2)Update locks are used when SQL Server intends to modify a<br>page, and later promotes the update page lock to an exclusive page lock<br>before actually making the changes. (3)Exclusive locks are used for the<br>data modification operations, such as UPDATE, INSERT, or DELETE.<br><br>Question: Explain some SQL Server 2000 Query?<br><br>Answer: Here are some<br>sql server 2000 query like Sql Insert Query, Delete Sql Query, Update Sql<br>Query and Sql Create Query: 1) Sql Insert Query: a) How to encrypt<br>data by using Sql Insert Query. --: insert into<br>table_name(Tablecolumn1, tablecolumn2,. . . . .) values ('value1',<br>pwdencrypt('value'),. . . .)<br>b) How to copy data from one table to another with the help of Sql<br>Insert Query. --: insert into table_name(column1,column2,. . . . )<br>select column1, column2, . . . . from table_name2<br>c) Sql Insert Query using where clause --: insert into<br>tablename(column1,column2) select column1,column2 from tablename2 where<br>id=value.<br><br>Question: What is 'Write-ahead log' in Sql Server 2000 ?<br><br>Answer:<br>Before understanding it we must have an idea about the transaction log<br>files. These files are the files which holds the data for change in<br>database . Now we explain when we are doing some Sql Server 2000 query<br>or any Sql query like Sql insert query,delete sql query,update sql query<br>and change the data in sql server database it cannot change the database<br>directly to table .Sql server extracts the data that is modified by sql<br>server 2000 query or by sql query and places it in memory.Once data is<br>stores in memory user can make changes to that a log file is gernated this<br>log file is gernated in every five mintues of transaction is done. After<br>this sql server writes changes to database with the help of transaction<br>log files. This is called Write-ahead log.<br><br>Question: What do u mean by Extents and types of Extends ?<br>Answer:<br>An Extent is a collection of 8 sequential pages to hold database from<br>becoming fregmented. Fragment means these pages relates to same table of<br>database these also holds in indexing. To avoid for fragmentation Sql<br>Server assign space to table in extents. So that the Sql Server keep upto<br>date data in extents. Because these pages are continously one after<br>another. There are usually two types of extends:-Uniform and Mixed.<br>Uniform means when extent is own by a single object means all<br>collection of 8 ages hold by a single extend is called uniform. Mixed<br>mean when more then one object is comes in extents is known as mixed<br>extents.<br><br>Question: What is different in Rules and Constraints ?<br><br>Answer: Rules<br>and Constraints are similar in functionality but there is a An little<br>diffrence between them.Rules are used for backward compatibility . One the<br>most exclusive diffrence is that we an bind rules to a datatypes whereas<br>constraints are bound only to columns.So we can create our own datatype<br>with the help of Rules and get the input according to that.<br><br>Question: What is defaults in Sql Server and types of Defaults<br>?<br>Answer: Defaults are used when a field of columns is allmost common<br>for all the rows for example in employee table all living in delhi that<br>value of this field is common for all the row in the table if we set this<br>field as default the value that is not fill by us automatically fills the<br>value in the field its also work as intellisense means when user inputing<br>d it will automatically fill the delhi . There are two types of defaults<br>object and definations. Object deault:-These defaults are applicable on<br>a particular columns . These are usually deined at the time of table<br>designing.When u set the object default field in column state this column<br>in automatically field when u left this filed blank. Defination<br>default:-When we bind the datatype with default let we named this as<br>dotnet .Then every time we create column and named its datatype as dotnet<br>it will behave the same that we set for dotnet datatype.<br><br>Question: What Is Database ?<br><br>Answer: A database is similar to a<br>data file in that it is a storage place for data. Like a data file, a<br>database does not present information directly to a user; the user runs an<br>application that accesses data from the database and presents it to the<br>user in an understandable format.Database systems are more powerful than<br>data files in that data is more highly organized. In a well-designed<br>database, there are no duplicate pieces of data that the user or<br>application must update at the same time. Related pieces of data are<br>grouped together in a single structure or record, and relationships can be<br>defined between these structures and records.When working with data files,<br>an application must be coded to work with the specific structure of each<br>data file. In contrast, a database contains a catalog that applications<br>use to determine how data is organized. Generic database applications can<br>use the catalog to present users with data from different databases<br>dynamically, without being tied to a specific data format. A database<br>typically has two main parts: first, the files holding the physical<br>database and second, the database management system (DBMS) software that<br>applications use to access data. The DBMS is responsible for enforcing the<br>database structure, including: · Maintaining relationships between data in<br>the database. Ensuring that data is stored correctly, and that the rules<br>defining data relationships are not violated. · Recovering all data to a<br>point of known consistency in case of system failures.<br><br>Question: what is Relational Database ?<br><br>Answer: Although there are<br>different ways to organize data in a database, relational databases are<br>one of the most effective. Relational database systems are an application<br>of mathematical set theory to the problem of effectively organizing data.<br>In a relational database, data is collected into tables (called relations<br>in relational theory). A table represents some class of objects that are<br>important to an organization. For example, a company may have a database<br>with a table for employees, another table for customers, and another for<br>stores. Each table is built of columns and rows (called attributes and<br>tuples in relational theory). Each column represents some attribute of the<br>object represented by the table. For example, an Employee table would<br>typically have columns for attributes such as first name, last name,<br>employee ID, department, pay grade, and job title. Each row represents an<br>instance of the object represented by the table. For example, one row in<br>the Employee table represents the employee who has employee ID 12345. When<br>organizing data into tables, you can usually find many different ways to<br>define tables. Relational database theory defines a process called<br>normalization, which ensures that the set of tables you define will<br>organize your data effectively.<br><br>Question: What is Data Integrity and it's categories ?<br><br>Answer:<br>Enforcing data integrity ensures the quality of the data in the database.<br>For example, if an employee is entered with an employee_id value of 123,<br>the database should not allow another employee to have an ID with the same<br>value. If you have an employee_rating column intended to have values<br>ranging from 1 to 5, the database should not accept a value of 6. If the<br>table has a dept_id column that stores the department number for the<br>employee, the database should allow only values that are valid for the<br>department numbers in the company. Two important steps in planning tables<br>are to identify valid values for a column and to decide how to enforce the<br>integrity of the data in the column. Data integrity falls into these<br>categories: 1) Entity integrity 2) Domain integrity 3)<br>Referential integrity 4) User-defined integrity Entity Integrity:<br>Entity integrity defines a row as a unique entity for a particular table.<br>Entity integrity enforces the integrity of the identifier column(s) or the<br>primary key of a table (through indexes, UNIQUE constraints, PRIMARY KEY<br>constraints, or IDENTITY properties). Domain Integrity: Domain<br>integrity is the validity of entries for a given column. You can enforce<br>domain integrity by restricting the type (through data types), the format<br>(through CHECK constraints and rules), or the range of possible values<br>(through FOREIGN KEY constraints, CHECK constraints, DEFAULT efinitions,<br>NOT NULL definitions, and rules). Referential Integrity: Referential<br>integrity preserves the defined relationships between tables when records<br>are entered or deleted. In Microsoft® SQL Server? 2000, referential<br>integrity is based on relationships between foreign keys and primary keys<br>or between foreign keys and unique keys (through FOREIGN KEY and CHECK<br>constraints). Referential integrity ensures that key values are consistent<br>across tables. Such consistency requires that there be no references to<br>nonexistent values and that if a key value changes, all references to it<br>change consistently throughout the database. When you enforce referential<br>integrity, SQL Server prevents users from: · Adding records to a<br>related table if there is no associated record in the primary table. ·<br>Changing values in a primary table that result in orphaned records in a<br>related table. · Deleting records from a primary table if there are<br>matching related records. For example, with the sales and titles<br>tables in the pubs database, referential integrity is based on the<br>relationship between the foreign key (title_id) in the sales table and the<br>primary key (title_id) in the titles table. User-Defined: Integrity<br>User-defined integrity allows you to define specific business rules that<br>do not fall into one of the other integrity categories. All of the<br>integrity categories support user-defined integrity (all column- and<br>table-level constraints in CREATE TABLE, stored procedures, and<br>triggers).<br><br>Question: SQL Server runs on which TCP/IP port and From where can you<br>change the default port?<br><br>Answer: SQL Server runs on port 1433 but we<br>can also change it for better security and From the network Utility TCP/IP<br>properties --&amp;gt;Port number.both on client and the server.<br>Question: What is the use of DBCC commands? Answer: DBCC stands for<br>database consistency checker. We use these commands to check the<br>consistency of the databases, i.e., maintenance, validation task and<br>status checks.DBCC CHECKDB - Ensures that tables in the db and the indexes<br>are correctly linked.and DBCC CHECKALLOC To check that all pages in a db<br>are correctly allocated. DBCC SQLPERF - It gives report on current usage<br>of transaction log in percentage. DBCC CHECKFILEGROUP - Checks all tables<br>file group for any damage.<br><br><br>Question: What is the difference between a HAVING CLAUSE and a WHERE<br>CLAUSE?<br><br>Answer: Having Clause is basically used only with the GROUP BY<br>function in a query. WHERE Clause is applied to each row before they are<br>part of the GROUP BY function in a query.<br>Question: When do you use SQL Profiler? Answer: SQL Profiler utility<br>allows us to basically track Connections to the SQL Server and also<br>determine activities such as which SQL Scripts are running, failed jobs<br>etc.<br><br><br>Question: Can you explain the role of each service?<br><br>Answer: SQL<br>SERVER - is for running the databases SQL AGENT - is for automation such<br>as Jobs, DB Maintenance, Backups DTC - Is for linking and connecting to<br>other SQL Servers.<br><br>Question: What is Normalization ?<br><br>Answer: The logical design of the<br>database, including the tables and the relationships between them, is the<br>core of an optimized relational database. A good logical database design<br>can lay the foundation for optimal database and application performance. A<br>poor logical database design can impair the performance of the entire<br>system.<br>Normalizing a logical database design involves using formal methods to<br>separate the data into multiple, related tables. A greater number of<br>narrow tables (with fewer columns) is characteristic of a normalized<br>database. A few wide tables (with more columns) is characteristic of an<br>nonnomalized database. Reasonable normalization often improves<br>performance. When useful indexes are available, the Microsoft® SQL Server?<br>2000 query optimizer is efficient at selecting rapid, efficient joins<br>between tables.<br>Some of the benefits of normalization include: ·Faster sorting and<br>index creation. ·A larger number of clustered indexes. For more<br>information, Narrower and more compact indexes. ·Fewer indexes per<br>table, which improves the performance of INSERT, UPDATE, and DELETE<br>statements. ·Fewer null values and less opportunity for inconsistency,<br>which increase database compactness.<br>As normalization increases, so do the number and complexity of joins<br>required to retrieve data. Too many complex relational joins between too<br>many tables can hinder performance. Reasonable normalization often<br>includes few regularly executed queries that use joins involving more than<br>four tables.<br>Sometimes the logical database design is already fixed and total<br>redesign is not feasible. Even then, however, it might be possible to<br>normalize a large table selectively into several smaller tables. If the<br>database is accessed through stored procedures, this schema change could<br>take place without affecting applications. If not, it might be possible to<br>create a view that hides the schema change from the applications.<br><br>Question: Can you explain what View is in SQL ?<br>Answer: View is just<br>a virtual table nothing else which is based or we can say devlop with SQL<br>SELECT query.So we can say that its a real database table (it has columns<br>and rows just like a regular table),but one difference is that real tables<br>store data,but views can?t. View data is generated dynamically when the<br>view is referenced.And view can also references one or more existing<br>database tables or other views. We can say that it is filter of<br>database. Question: Can you tell me the difference between DELETE<br>&amp;amp;TRUNCATE commands? Answer: Delete command removes the rows from a<br>table based on the condition that we provide with a WHERE clause. Truncate<br>will actually remove all the rows from a table and there will be no data<br>in the table after we run the truncate command. <br><br><br><br><br>What is the Maximum allowed length of Record group Column?<br>Record group column names cannot exceed 30 characters.<br><br>Which parameter can be used to set read level consistency across multiple queries?<br>Read only<br><br>What are the different types of Record Groups?<br>Query Record Groups<br>NonQuery Record Groups<br>State Record Groups<br><br>From which designation is it preferred to send the output to the printed?<br>Previewer<br><br>What are difference between post database commit and post-form commit?<br>Post-form commit fires once during the post and commit transactions process, after the database commit occurs. The post-form-commit trigger fires after inserts, updates and deletes have been posted to the database but before the transactions have been finalized in the issuing the command. The post-database-commit trigger fires after oracle forms issues the commit to finalized transactions.<br><br>What are the different display styles of list items?<br>Pop_listText_listCombo box<br><br>Which of the above methods is the faster method?<br>performing the calculation in the query is faster.<br><br>With which function of summary item is the compute at options required?<br>percentage of total functions.<br><br>What are parameters?<br>Parameters provide a simple mechanism for defining and setting the valuesof inputs that are required by a form at startup. Form parameters are variables of type char,number,date that you define at design time.<br><br>What are the three types of user exits available ?<br>Oracle Precompiler exits, Oracle call interface, NonOracle user exits.<br><br>How many windows in a form can have console?<br>Only one window in a form can display the console, and you cannot change the console assignment at runtime.<br><br>What is an administrative (privileged) user? (for DBA )<br>Oracle DBAs and operators typically use administrative accounts to manage the database and database instance. An administrative account is a user that is granted SYSOPER or SYSDBA privileges. SYSDBA and SYSOPER allow access to a database instance even if it is not running. Control of these privileges is managed outside of the database via password files and special operating system groups. This password file is created with the orapwd utility.<br><br>What are the two repeating frame always associated with matrix object?<br>One down repeating frame below one across repeating frame.<br><br>What are the master-detail triggers?<br>On-Check_delete_masterOn_clear_detailsOn_populate_details<br><br>How does one connect to an administrative user? (for DBA )<br>If an administrative user belongs to the "dba" group on Unix, or the "ORA_DBA" (ORA_sid_DBA) group on NT, he/she can connect like this:<br>connect / as sysdba<br>No password is required. This is equivalent to the desupported "connect internal" method.<br>A password is required for "non-secure" administrative access. These passwords are stored in password files. Remote connections via Net8 are classified as non-secure. Look at this example:<br>connect sys/password as sysdba<br><br>How does one create a password file? (for DBA )<br>The Oracle Password File ($ORACLE_HOME/dbs/orapw or orapwSID) stores passwords for users with administrative privileges. One needs to create a password files before remote administrators (like OEM) will be allowed to connect.<br>Follow this procedure to create a new password file:<br>. Log in as the Oracle software owner<br>. Runcommand: orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=mypasswd<br>. Shutdown the database (SQLPLUS&gt; SHUTDOWN IMMEDIATE)<br>. Edit the INIT.ORA file and ensure REMOTE_LOGIN_PASSWORDFILE=exclusive is set.<br>. Startup the database (SQLPLUS&gt; STARTUP)<br>NOTE: The orapwd utility presents a security risk in that it receives a password from the command line. This password is visible in the process table of many systems. Administrators needs to be aware of this!<br><br>Is it possible to modify an external query in a report which contains it?<br>No.<br><br>Does a grouping done for objects in the layout editor affect the grouping done in the data model editor?<br>No.<br><br>How does one add users to a password file? (for DBA )<br>One can select from the SYS.V_$PWFILE_USERS view to see which users are listed in the password file. New users can be added to the password file by granting them SYSDBA or SYSOPER privileges, or by using the orapwd utility. GRANT SYSDBA TO scott;<br><br>If a break order is set on a column would it affect columns which are under the column?<br>No<br><br>Why are OPS$ accounts a security risk in a client/server environment? (for DBA )<br>If you allow people to log in with OPS$ accounts from Windows Workstations, you cannot be sure who they really are. With terminals, you can rely on operating system passwords, with Windows, you cannot.<br>If you set REMOTE_OS_AUTHENT=TRUE in your init.ora file, Oracle assumes that the remote OS has authenticated the user. If REMOTE_OS_AUTHENT is set to FALSE (recommended), remote users will be unable to connect without a password. IDENTIFIED EXTERNALLY will only be in effect from the local host. Also, if you are using "OPS$" as your prefix, you will be able to log on locally with or without a password, regardless of whether you have identified your ID with a password or defined it to be IDENTIFIED EXTERNALLY.<br><br>Do user parameters appear in the data modal editor in 2.5?<br>No<br><br>Can you pass data parameters to forms?<br>No<br><br>Is it possible to link two groups inside a cross products after the cross products group has been created?<br>no<br><br>What are the different modals of windows?<br>Modalless windows<br>Modal windows<br><br>What are modal windows?<br>Modal windows are usually used as dialogs, and have restricted functionality compared to modelless windows. On some platforms for example operators cannot resize, scroll or iconify a modal window.<br><br>What are the different default triggers created when Master Deletes Property is set to Non-isolated?<br><br>Master Deletes Property Resulting Triggers<br>----------------------------------------------------<br>Non-Isolated(the default) On-Check-Delete-Master<br>On-Clear-Details<br>On-Populate-Details<br><br>What are the different default triggers created when Master Deletes Property is set to isolated?<br><br>Master Deletes Property Resulting Triggers<br>---------------------------------------------------<br>Isolated On-Clear-Details<br>On-Populate-Details<br><br>What are the different default triggers created when Master Deletes Property is set to Cascade?<br><br>Master Deletes Property Resulting Triggers<br>---------------------------------------------------<br>Cascading On-Clear-Details<br>On-Populate-Details<br>Pre-delete<br><br>What is the diff. bet. setting up of parameters in reports 2.0 reports2.5?<br>LOVs can be attached to parameters in the reports 2.5 parameter form.<br><br>What are the difference between lov &amp; list item?<br>Lov is a property where as list item is an item. A list item can have only one column, lov can have one or more columns<br><br>What is the advantage of the library?<br>Libraries provide a convenient means of storing client-side program units and sharing them among multiple applications. Once you create a library, you can attach it to any other form, menu, or library modules. When you can call library program units from triggers menu items commands and user named routine, you write in the modules to which you have attach the library. When a library attaches another library, program units in the first library can reference program units in the attached library. Library support dynamic loading-that is library program units are loaded into an application only when needed. This can significantly reduce the run-time memory requirements of applications.<br><br>What is lexical reference? How can it be created?<br>Lexical reference is place_holder for text that can be embedded in a sql statements. A lexical reference can be created using &amp; before the column or parameter name.<br><br>What is system.coordination_operation?<br>It represents the coordination causing event that occur on the master block in master-detail relation.<br><br>What is synchronize?<br>It is a terminal screen with the internal state of the form. It updates the screen display to reflect the information that oracle forms has in its internal representation of the screen.<br><br>What use of command line parameter cmd file?<br>It is a command line argument that allows you to specify a file that contain a set of arguments for r20run.<br><br>What is a Text_io Package?<br>It allows you to read and write information to a file in the file system.<br><br>What is forms_DDL?<br>Issues dynamic Sql statements at run time, including server side pl/SQl and DDL<br><br>How is link tool operation different bet. reports 2 &amp; 2.5?<br>In Reports 2.0 the link tool has to be selected and then two fields to be linked are selected and the link is automatically created. In 2.5 the first field is selected and the link tool is then used to link the first field to the second field.<br><br>What are the different styles of activation of ole Objects?<br>In place activationExternal activation<br><br>How do you reference a Parameter?<br>In Pl/Sql, You can reference and set the values of form parameters using bind variables syntax. Ex. PARAMETER name = '' or :block.item = PARAMETER Parameter name<br><br>What is the difference between object embedding &amp; linking in Oracle forms?<br>In Oracle forms, Embedded objects become part of the form module, and linked objects are references from a form module to a linked source file.<br><br>Name of the functions used to get/set canvas properties?<br>Get_view_property, Set_view_property<br><br>What are the built-ins that are used for setting the LOV properties at runtime?<br>get_lov_property<br>set_lov_property<br><br>What are the built-ins used for processing rows?<br>Get_group_row_count(function)<br>Get_group_selection_count(function)<br>Get_group_selection(function)<br>Reset_group_selection(procedure)<br>Set_group_selection(procedure)<br>Unset_group_selection(procedure)<br><br>What are built-ins used for Processing rows?<br>GET_GROUP_ROW_COUNT(function)<br>GET_GROUP_SELECTION_COUNT(function)<br>GET_GROUP_SELECTION(function)<br>RESET_GROUP_SELECTION(procedure)<br>SET_GROUP_SELECTION(procedure)<br>UNSET_GROUP_SELECTION(procedure)<br><br>What are the built-in used for getting cell values?<br>Get_group_char_cell(function)<br>Get_groupcell(function)<br>Get_group_number_cell(function)<br><br>What are the built-ins used for Getting cell values?<br>GET_GROUP_CHAR_CELL (function)<br>GET_GROUPCELL(function)<br>GET_GROUP_NUMBET_CELL(function)<br><br>Atleast how many set of data must a data model have before a data model can be base on it?<br>Four<br><br>To execute row from being displayed that still use column in the row which property can be used?<br>Format trigger.<br><br>What are different types of modules available in oracle form?<br>Form module - a collection of objects and code routines Menu modules - a collection of menus and menu item commands that together make up an application menu library module - a collection of user named procedures, functions and packages that can be called from other modules in the application<br><br>What is the remove on exit property?<br>For a modelless window, it determines whether oracle forms hides the window automatically when the operators navigates to an item in the another window.<br><br>What is WHEN-Database-record trigger?<br>Fires when oracle forms first marks a record as an insert or an update. The trigger fires as soon as oracle forms determines through validation that the record should be processed by the next post or commit as an insert or update. c generally occurs only when the operators modifies the first item in the record, and after the operator attempts to navigate out of the item.<br><br>What is a difference between pre-select and pre-query?<br>Fires during the execute query and count query processing after oracle forms constructs the select statement to be issued, but before the statement is actually issued. The pre-query trigger fires just before oracle forms issues the select statement to the database after the operator as define the example records by entering the query criteria in enter query mode.Pre-query trigger fires before pre-select trigger.<br><br>What are built-ins associated with timers?<br>find_timercreate_timerdelete_timer<br><br>What are the built-ins used for finding object ID functions?<br>Find_group(function)<br>Find_column(function)<br><br>What are the built-ins used for finding Object ID function?<br>FIND_GROUP(function)<br>FIND_COLUMN(function)<br><br>Any attempt to navigate programmatically to disabled form in a call_form stack is allowed?<br>False<br><br>Use the Add_group_row procedure to add a row to a static record group 1. true or false?<br>False<br><br>What third party tools can be used with Oracle EBU/ RMAN? (for DBA)<br>The following Media Management Software Vendors have integrated their media management software packages with Oracle Recovery Manager and Oracle7 Enterprise Backup Utility. The Media Management Vendors will provide first line technical support for the integrated backup/recover solutions.<br>Veritas NetBackup<br>EMC Data Manager (EDM)<br>HP OMNIBack II<br>IBM's Tivoli Storage Manager - formerly ADSM<br>Legato Networker<br>ManageIT Backup and Recovery<br>Sterling Software's SAMS:Alexandria - formerly from Spectralogic<br>Sun Solstice Backup<br><br>Why and when should one tune? (for DBA)<br>One of the biggest responsibilities of a DBA is to ensure that the Oracle database is tuned properly. The Oracle RDBMS is highly tunable and allows the database to be monitored and adjusted to increase its performance. One should do performance tuning for the following reasons:<br>The speed of computing might be wasting valuable human time (users waiting for response); Enable your system to keep-up with the speed business is conducted; and Optimize hardware usage to save money (companies are spending millions on hardware). Although this FAQ is not overly concerned with hardware issues, one needs to remember than you cannot tune a Buick into a Ferrari.<br><br>How can a break order be created on a column in an existing group? What are the various sub events a mouse double click event involves?<br>By dragging the column outside the group.<br><br>What is the use of place holder column? What are the various sub events a mouse double click event involves?<br>A placeholder column is used to hold calculated values at a specified place rather than allowing is to appear in the actual row where it has to appear.<br><br>What is the use of hidden column? What are the various sub events a mouse double click event involves?<br>A hidden column is used to when a column has to embed into boilerplate text.<br><br>What database aspects should be monitored? (for DBA)<br>One should implement a monitoring system to constantly monitor the following aspects of a database. Writing custom scripts, implementing Oracle's Enterprise Manager, or buying a third-party monitoring product can achieve this. If an alarm is triggered, the system should automatically notify the DBA (e-mail, page, etc.) to take appropriate action.<br>Infrastructure availability:<br>. Is the database up and responding to requests<br>. Are the listeners up and responding to requests<br>. Are the Oracle Names and LDAP Servers up and responding to requests<br>. Are the Web Listeners up and responding to requests<br><br>Things that can cause service outages:<br>. Is the archive log destination filling up?<br>. Objects getting close to their max extents<br>. User and process limits reached<br><br>Things that can cause bad performance:<br>See question "What tuning indicators can one use?".<br><br>Where should the tuning effort be directed? (for DBA)<br>Consider the following areas for tuning. The order in which steps are listed needs to be maintained to prevent tuning side effects. For example, it is no good increasing the buffer cache if you can reduce I/O by rewriting a SQL statement. Database Design (if it's not too late):<br>Poor system performance usually results from a poor database design. One should generally normalize to the 3NF. Selective denormalization can provide valuable performance improvements. When designing, always keep the "data access path" in mind. Also look at proper data partitioning, data replication, aggregation tables for decision support systems, etc.<br>Application Tuning:<br>Experience showed that approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL. Also consider proper scheduling of batch tasks after peak working hours.<br>Memory Tuning:<br>Properly size your database buffers (shared pool, buffer cache, log buffer, etc) by looking at your buffer hit ratios. Pin large objects into memory to prevent frequent reloads.<br>Disk I/O Tuning:<br>Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation, etc<br>Eliminate Database Contention:<br>Study database locks, latches and wait events carefully and eliminate where possible. Tune the Operating System:<br>Monitor and tune operating system CPU, I/O and memory utilization. For more information, read the related Oracle FAQ dealing with your specific operating system.<br><br>What are the various sub events a mouse double click event involves? What are the various sub events a mouse double click event involves?<br>Double clicking the mouse consists of the mouse down, mouse up, mouse click, mouse down &amp; mouse up events.<br><br>What are the default parameter that appear at run time in the parameter screen? What are the various sub events a mouse double click event involves?<br>Destype and Desname.<br><br>What are the built-ins used for Creating and deleting groups?<br>CREATE-GROUP (function)<br>CREATE_GROUP_FROM_QUERY(function)<br>DELETE_GROUP(procedure)<br><br>What are different types of canvas views?<br>Content canvas views<br>Stacked canvas views<br>Horizontal toolbar<br>vertical toolbar.<br><br>What are the different types of Delete details we can establish in Master-Details?<br>Cascade<br>Isolate<br>Non-isolate<br><br>What is relation between the window and canvas views?<br>Canvas views are the back ground objects on which you place the interface items (Text items), check boxes, radio groups etc.,) and boilerplate objects (boxes, lines, images etc.,) that operators interact with us they run your form . Each canvas views displayed in a window.<br><br>What is a User_exit?<br>Calls the user exit named in the user_exit_string. Invokes a 3Gl program by name which has been properly linked into your current oracle forms executable.<br><br>How is it possible to select generate a select set for the query in the query property sheet?<br>By using the tables/columns button and then specifying the table and the column names.<br><br>How can values be passed bet. precompiler exits &amp; Oracle call interface?<br>By using the statement EXECIAFGET &amp; EXECIAFPUT.<br><br>How can a square be drawn in the layout editor of the report writer?<br>By using the rectangle tool while pressing the (Constraint) key.<br><br>How can a text file be attached to a report while creating in the report writer?<br>By using the link file property in the layout boiler plate property sheet.<br><br>How can I message to passed to the user from reports?<br>By using SRW.MESSAGE function.<br><br>Does one need to drop/ truncate objects before importing? (for DBA)<br>Before one import rows into already populated tables, one needs to truncate or drop these tables to get rid of the old data. If not, the new data will be appended to the existing tables. One must always DROP existing Sequences before re-importing. If the sequences are not dropped, they will generate numbers inconsistent with the rest of the database. Note: It is also advisable to drop indexes before importing to speed up the import process. Indexes can easily be recreated after the data was successfully imported.<br><br>How can a button be used in a report to give a drill down facility?<br>By setting the action associated with button to Execute pl/sql option and using the SRW.Run_report function.<br><br>Can one import/export between different versions of Oracle? (for DBA)<br>Different versions of the import utility is upwards compatible. This means that one can take an export file created from an old export version, and import it using a later version of the import utility. This is quite an effective way of upgrading a database from one release of Oracle to the next.<br>Oracle also ships some previous catexpX.sql scripts that can be executed as user SYS enabling older imp/exp versions to work (for backwards compatibility). For example, one can run $ORACLE_HOME/rdbms/admin/catexp7.sql on an Oracle 8 database to allow the Oracle 7.3 exp/imp utilities to run against an Oracle 8 database.<br><br>What are different types of images?<br>Boiler plate imagesImage Items<br><br>Can one export to multiple files?/ Can one beat the Unix 2 Gig limit? (for DBA)<br>From Oracle8i, the export utility supports multiple output files. This feature enables large exports to be divided into files whose sizes will not exceed any operating system limits (FILESIZE= parameter). When importing from multi-file export you must provide the same filenames in the same sequence in the FILE= parameter. Look at this example:<br>exp SCOTT/TIGER FILE=D:\F1.dmp,E:\F2.dmp FILESIZE=10m LOG=scott.log<br>Use the following technique if you use an Oracle version prior to 8i:<br>Create a compressed export on the fly. Depending on the type of data, you probably can export up to 10 gigabytes to a single file. This example uses gzip. It offers the best compression I know of, but you can also substitute it with zip, compress or whatever.<br># create a named pipe<br>mknod exp.pipe p<br># read the pipe - output to zip file in the background<br>gzip &lt;&gt; scott.exp.gz &amp;<br># feed the pipe<br>exp userid=scott/tiger file=exp.pipe ...<br><br>What is bind reference and how can it be created?<br>Bind reference are used to replace the single value in sql, pl/sql statements a bind reference can be created using a (:) before a column or a parameter name.<br><br>How can one improve Import/ Export performance? (for DBA)<br>EXPORT:<br>. Set the BUFFER parameter to a high value (e.g. 2M)<br>. Set the RECORDLENGTH parameter to a high value (e.g. 64K)<br>. Stop unnecessary applications to free-up resources for your job.<br>. If you run multiple export sessions, ensure they write to different physical disks.<br>. DO NOT export to an NFS mounted filesystem. It will take forever.<br><br>IMPORT:<br>. Create an indexfile so that you can create indexes AFTER you have imported data. Do this by setting INDEXFILE to a filename and then import. No data will be imported but a file containing index definitions will be created. You must edit this file afterwards and supply the passwords for the schemas on all CONNECT statements.<br>. Place the file to be imported on a separate physical disk from the oracle data files<br>. Increase DB_CACHE_SIZE (DB_BLOCK_BUFFERS prior to 9i) considerably in the init$SID.ora file<br>. Set the LOG_BUFFER to a big value and restart oracle.<br>. Stop redo log archiving if it is running (ALTER DATABASE NOARCHIVELOG;)<br>. Create a BIG tablespace with a BIG rollback segment inside. Set all other rollback segments offline (except the SYSTEM rollback segment of course). The rollback segment must be as big as your biggest table (I think?)<br>. Use COMMIT=N in the import parameter file if you can afford it<br>. Use ANALYZE=N in the import parameter file to avoid time consuming ANALYZE statements<br>. Remember to run the indexfile previously created<br><br>Give the sequence of execution of the various report triggers?<br>Before form , After form , Before report, Between page, After report.<br><br>What are the common Import/ Export problems? (for DBA )<br>ORA-00001: Unique constraint (...) violated - You are importing duplicate rows. Use IGNORE=NO to skip tables that already exist (imp will give an error if the object is re-created).<br>ORA-01555: Snapshot too old - Ask your users to STOP working while you are exporting or use parameter CONSISTENT=NO<br>ORA-01562: Failed to extend rollback segment - Create bigger rollback segments or set parameter COMMIT=Y while importing<br>IMP-00015: Statement failed ... object already exists... - Use the IGNORE=Y import parameter to ignore these errors, but be careful as you might end up with duplicate rows.<br><br>Why is it preferable to create a fewer no. of queries in the data model?<br>Because for each query, report has to open a separate cursor and has to rebind, execute and fetch data.<br><br><br>How does one see the uptime for a database? (for DBA )<br>Look at the following SQL query:<br>SELECT to_char (startup_time,'DD-MON-YYYY HH24: MI: SS') "DB Startup Time"<br>FROM sys.v_$instance;<br>Marco Bergman provided the following alternative solution:<br>SELECT to_char (logon_time,'Dy dd Mon HH24: MI: SS') "DB Startup Time"<br>FROM sys.v_$session<br>WHERE Sid=1 /* this is pmon */<br>/<br>Users still running on Oracle 7 can try one of the following queries:<br>Column STARTED format a18 head 'STARTUP TIME'<br>Select C.INSTANCE,<br>to_date (JUL.VALUE, 'J')<br>|| to_char (floor (SEC.VALUE/3600), '09')<br>|| ':'<br>-- || Substr (to_char (mod (SEC.VALUE/60, 60), '09'), 2, 2)<br>|| Substr (to_char (floor (mod (SEC.VALUE/60, 60)), '09'), 2, 2)<br>|| '.'<br>|| Substr (to_char (mod (SEC.VALUE, 60), '09'), 2, 2) STARTED<br>from SYS.V_$INSTANCE JUL,<br>SYS.V_$INSTANCE SEC,<br>SYS.V_$THREAD C<br>Where JUL.KEY like '%JULIAN%'<br>and SEC.KEY like '%SECOND%';<br>Select to_date (JUL.VALUE, 'J')<br>|| to_char (to_date (SEC.VALUE, 'SSSSS'), ' HH24:MI:SS') STARTED<br>from SYS.V_$INSTANCE JUL,<br>SYS.V_$INSTANCE SEC<br>where JUL.KEY like '%JULIAN%'<br>and SEC.KEY like '%SECOND%';<br>select to_char (to_date (JUL.VALUE, 'J') + (SEC.VALUE/86400), -Return a DATE<br>'DD-MON-YY HH24:MI:SS') STARTED<br>from V$INSTANCE JUL,<br>V$INSTANCE SEC<br>where JUL.KEY like '%JULIAN%'<br>and SEC.KEY like '%SECOND%';<br><br>Where are my TEMPFILES, I don't see them in V$DATAFILE or DBA_DATA_FILE? (for DBA)<br>Tempfiles, unlike normal datafiles, are not listed in v$datafile or dba_data_files. Instead query v$tempfile or dba_temp_files:<br>SELECT * FROM v$tempfile;<br>SELECT * FROM dba_temp_files;<br><br>How do I find used/free space in a TEMPORARY tablespace? (for DBA )<br>Unlike normal tablespaces, true temporary tablespace information is not listed in DBA_FREE_SPACE. Instead use the V$TEMP_SPACE_HEADER view:<br>SELECT tablespace_name, SUM (bytes used), SUM (bytes free)<br>FROM V$temp_space_header<br>GROUP BY tablespace_name;<br><br>What is a profile ?<br>Each database user is assigned a Profile that specifies limitations on various system resources available to the user.<br><br>How will you enforce security using stored procedures?<br>Don't grant user access directly to tables within the application. Instead grant the ability to access the procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.<br><br>How can one see who is using a temporary segment? (for DBA )<br>For every user using temporary space, there is an entry in SYS.V$_LOCK with type 'TS'.<br>All temporary segments are named 'ffff.bbbb' where 'ffff' is the file it is in and 'bbbb' is first block of the segment. If your temporary tablespace is set to TEMPORARY, all sorts are done in one large temporary segment. For usage stats, see SYS.V_$SORT_SEGMENT<br>From Oracle 8.0, one can just query SYS.v$sort_usage. Look at these examples:<br>select s.username, u."USER", u.tablespace, u.contents, u.extents, u.blocks<br>from sys.v_$session s, sys.v_$sort_usage u<br>where s.addr = u.session_addr<br>/<br>select s.osuser, s.process, s.username, s.serial#,<br>Sum (u.blocks)*vp.value/1024 sort_size<br>from sys.v_$session s, sys.v_$sort_usage u, sys.v_$parameter VP<br>where s.saddr = u.session_addr<br>and = 'db_block_size'<br>and s.osuser like '&amp;1'<br>group by s.osuser, s.process, s.username, s.serial#, vp.value<br>/<br><br>How does one get the view definition of fixed views/tables?<br>Query v$fixed_view_definition. Example: SELECT * FROM v$fixed_view_definition WHERE view_name='V$SESSION';<br><br>What are the dictionary tables used to monitor a database spaces ?<br>DBA_FREE_SPACE<br>DBA_SEGMENTS<br>DBA_DATA_FILES.<br><br>How can we specify the Archived log file name format and destination?<br>By setting the following values in init.ora file. LOG_ARCHIVE_FORMAT = arch %S/s/T/tarc (%S - Log sequence number and is zero left paded, %s - Log sequence number not padded. %T - Thread number lef-zero-paded and %t - Thread number not padded). The file name created is arch 0001 are if %S is used. LOG_ARCHIVE_DEST = path.<br><br>What is user Account in Oracle database?<br>An user account is not a physical structure in Database but it is having important relationship to the objects in the database and will be having certain privileges.<br><br>When will the data in the snapshot log be used?<br>We must be able to create a after row trigger on table (i.e., it should be not be already available) After giving table privileges. We cannot specify snapshot log name because oracle uses the name of the master table in the name of the database objects that support its snapshot log. The master table name should be less than or equal to 23 characters. (The table name created will be MLOGS_tablename, and trigger name will be TLOGS name).<br><br>What dynamic data replication?<br>Updating or Inserting records in remote database through database triggers. It may fail if remote database is having any problem.<br><br>What is Two-Phase Commit ?<br>Two-phase commit is mechanism that guarantees a distributed transaction either commits on all involved nodes or rolls back on all involved nodes to maintain data consistency across the global distributed database. It has two phase, a Prepare Phase and a Commit Phase.<br><br>How can you Enforce Referential Integrity in snapshots ?<br>Time the references to occur when master tables are not in use. Peform the reference the manually immdiately locking the master tables. We can join tables in snopshots by creating a complex snapshots that will based on the master tables.<br><br>What is a SQL * NET?<br>SQL *NET is ORACLE's mechanism for interfacing with the communication protocols used by the networks that facilitate distributed processing and distributed databases. It is used in Clint-Server and Server-Server communications.<br><br>What is a SNAPSHOT ?<br>Snapshots are read-only copies of a master table located on a remote node which is periodically refreshed to reflect changes made to the master table.<br><br>What is the mechanism provided by ORACLE for table replication ?<br>Snapshots and SNAPSHOT LOGs<br><br>What is snapshots?<br>Snapshot is an object used to dynamically replicate data between distribute database at specified time intervals. In ver 7.0 they are read only.<br><br>What are the various type of snapshots?<br>Simple and Complex.<br><br>Describe two phases of Two-phase commit ?<br>Prepare phase - The global coordinator (initiating node) ask a participants to prepare (to promise to commit or rollback the transaction, even if there is a failure) Commit - Phase - If all participants respond to the coordinator that they are prepared, the coordinator asks all nodes to commit the transaction, if all participants cannot prepare, the coordinator asks all nodes to roll back the transaction.<br><br>What is snapshot log ?<br>It is a table that maintains a record of modifications to the master table in a snapshot. It is stored in the same database as master table and is only available for simple snapshots. It should be created before creating snapshots.<br><br>What are the benefits of distributed options in databases?<br>Database on other servers can be updated and those transactions can be grouped together with others in a logical unit.<br>Database uses a two phase commit.<br><br>What are the options available to refresh snapshots ?<br>COMPLETE - Tables are completely regenerated using the snapshots query and the master tables every time the snapshot referenced.<br>FAST - If simple snapshot used then a snapshot log can be used to send the changes to the snapshot tables.<br>FORCE - Default value. If possible it performs a FAST refresh; Otherwise it will perform a complete refresh.<br><br>What is a SNAPSHOT LOG ?<br>A snapshot log is a table in the master database that is associated with the master table. ORACLE uses a snapshot log to track the rows that have been updated in the master table. Snapshot logs are used in updating the snapshots based on the master table.<br><br>What is Distributed database ?<br>A distributed database is a network of databases managed by multiple database servers that appears to a user as single logical database. The data of all databases in the distributed database can be simultaneously accessed and modified.<br><br>How can we reduce the network traffic?<br>- Replication of data in distributed environment.<br>- Using snapshots to replicate data.<br>- Using remote procedure calls.<br><br>Differentiate simple and complex, snapshots ?<br>- A simple snapshot is based on a query that does not contains GROUP BY clauses, CONNECT BY clauses, JOINs, sub-query or snashot of operations.<br>- A complex snapshots contain atleast any one of the above.<br><br>What are the Built-ins used for sending Parameters to forms?<br>You can pass parameter values to a form when an application executes the call_form, New_form, Open_form or Run_product.<br><br>Can you have more than one content canvas view attached with a window?<br>Yes. Each window you create must have atleast one content canvas view assigned to it. You can also create a window that has manipulated content canvas view. At run time only one of the content canvas views assign to a window is displayed at a time.<br><br>Is the After report trigger fired if the report execution fails?<br>Yes.<br><br>Does a Before form trigger fire when the parameter form is suppressed?<br>Yes.<br><br>What is SGA?<br>The System Global Area in an Oracle database is the area in memory to facilitate the transfer of information between users. It holds the most recently requested structural information between users. It holds the most recently requested structural information about the database. The structure is database buffers, dictionary cache, redo log buffer and shared pool area.<br><br>What is a shared pool?<br>The data dictionary cache is stored in an area in SGA called the shared pool. This will allow sharing of parsed SQL statements among concurrent users.<br><br>What is mean by Program Global Area (PGA)?<br>It is area in memory that is used by a single Oracle user process.<br><br>What is a data segment?<br>Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.<br><br>What are the factors causing the reparsing of SQL statements in SGA?<br>Due to insufficient shared pool size.<br>Monitor the ratio of the reloads takes place while executing SQL statements. If the ratio is greater than 1 then increase the SHARED_POOL_SIZE.<br><br>What are clusters?<br>Clusters are groups of one or more tables physically stores together to share common columns and are often used together.<br><br>What is cluster key?<br>The related columns of the tables in a cluster are called the cluster key.<br><br>Do a view contain data?<br>Views do not contain or store data.<br><br>What is user Account in Oracle database?<br>A user account is not a physical structure in database but it is having important relationship to the objects in the database and will be having certain privileges.<br><br>How will you enforce security using stored procedures?<br>Don't grant user access directly to tables within the application. Instead grant the ability to access the procedures that access the tables. When procedure executed it will execute the privilege of procedures owner. Users cannot access tables except via the procedure.<br><br>What are the dictionary tables used to monitor a database space?<br>DBA_FREE_SPACE<br>DBA_SEGMENTS<br>DBA_DATA_FILES.<br><br>Can a property clause itself be based on a property clause?<br>Yes<br><br>If a parameter is used in a query without being previously defined, what diff. exist between. report 2.0 and 2.5 when the query is applied?<br>While both reports 2.0 and 2.5 create the parameter, report 2.5 gives a message that a bind parameter has been created.<br><br>What are the sql clauses supported in the link property sheet?<br>Where start with having.<br><br>What is trigger associated with the timer?<br>When-timer-expired.<br><br>What are the trigger associated with image items?<br>When-image-activated fires when the operators double clicks on an image itemwhen-image-pressed fires when an operator clicks or double clicks on an image item<br><br>What are the different windows events activated at runtimes?<br>When_window_activated<br>When_window_closed<br>When_window_deactivated<br>When_window_resized<br>Within this triggers, you can examine the built in system variable system. event_window to determine the name of the window for which the trigger fired.<br><br>When do you use data parameter type?<br>When the value of a data parameter being passed to a called product is always the name of the record group defined in the current form. Data parameters are used to pass data to products invoked with the run_product built-in subprogram.<br><br>What is difference between open_form and call_form?<br>when one form invokes another form by executing open_form the first form remains displayed, and operators can navigate between the forms as desired. when one form invokes another form by executing call_form, the called form is modal with respect to the calling form. That is, any windows that belong to the calling form are disabled, and operators cannot navigate to them until they first exit the called form.<br><br>What is new_form built-in?<br>When one form invokes another form by executing new_form oracle form exits the first form and releases its memory before loading the new form calling new form completely replace the first with the second. If there are changes pending in the first form, the operator will be prompted to save them before the new form is loaded.<br><br>What is the "LOV of Validation" Property of an item? What is the use of it?<br>When LOV for Validation is set to True, Oracle Forms compares the current value of the text item to the values in the first column displayed in the LOV. Whenever the validation event occurs. If the value in the text item matches one of the values in the first column of the LOV, validation succeeds, the LOV is not displayed, and processing continues normally. If the value in the text item does not match one of the values in the first column of the LOV, Oracle Forms displays the LOV and uses the text item value as the search criteria to automatically reduce the list.<br><br>What is the diff. when Flex mode is mode on and when it is off?<br>When flex mode is on, reports automatically resizes the parent when the child is resized.<br><br>What is the diff. when confine mode is on and when it is off?<br>When confine mode is on, an object cannot be moved outside its parent in the layout.<br><br>What are visual attributes?<br>Visual attributes are the font, color, pattern proprieties that you set for form and menu objects that appear in your application interface.<br><br>Which of the two views should objects according to possession?<br>view by structure.<br><br>What are the two types of views available in the object navigator(specific to report 2.5)?<br>View by structure and view by type .<br><br>What are the vbx controls?<br>Vbx control provide a simple method of building and enhancing user interfaces. The controls can use to obtain user inputs and display program outputs.vbx control where originally develop as extensions for the ms visual basic environments and include such items as sliders, rides and knobs.<br><br>What is the use of transactional triggers?<br>Using transactional triggers we can control or modify the default functionality of the oracle forms.<br><br>How do you create a new session while open a new form?<br>Using open_form built-in setting the session option Ex. Open_form('Stocks ',active, session). when invoke the multiple forms with open form and call_form in the same application, state whether the following are true/False<br><br>What are the ways to monitor the performance of the report?<br>Use reports profile executable statement. Use SQL trace facility.<br><br>If two groups are not linked in the data model editor, What is the hierarchy between them?<br>Two group that is above are the left most rank higher than the group that is to right or below it.<br><br>An open form can not be execute the call_form procedure if you chain of called forms has been initiated by another open form?<br>True<br><br>Explain about horizontal, Vertical tool bar canvas views?<br>Tool bar canvas views are used to create tool bars for individual windows. Horizontal tool bars are display at the top of a window, just under its menu bar. Vertical Tool bars are displayed along the left side of a window<br><br>What is the purpose of the product order option in the column property sheet?<br>To specify the order of individual group evaluation in a cross products.<br><br>What is the use of image_zoom built-in?<br>To manipulate images in image items.<br><br>How do you reference a parameter indirectly?<br>To indirectly reference a parameter use the NAME IN, COPY 'built-ins to indirectly set and reference the parameters value' Example name_in ('capital parameter my param'), Copy ('SURESH','Parameter my_param')<br><br>What is a timer?<br>Timer is an "internal time clock" that you can programmatically create to perform an action each time the times.<br><br>What are the two phases of block coordination?<br>There are two phases of block coordination: the clear phase and the population phase. During, the clear phase, Oracle Forms navigates internally to the detail block and flushes the obsolete detail records. During the population phase, Oracle Forms issues a SELECT statement to repopulate the detail block with detail records associated with the new master record. These operations are accomplished through the execution of triggers.<br><br>What are Most Common types of Complex master-detail relationships?<br>There are three most common types of complex master-detail relationships:<br>master with dependent details<br>master with independent details<br>detail with two masters<br><br>What is a text list?<br>The text list style list item appears as a rectangular box which displays the fixed number of values. When the text list contains values that can not be displayed, a vertical scroll bar appears, allowing the operator to view and select undisplayed values.<br><br>What is term?<br>The term is terminal definition file that describes the terminal form which you are using r20run.<br><br>What is use of term?<br>The term file which key is correspond to which oracle report functions.<br><br>What is pop list?<br>The pop list style list item appears initially as a single field (similar to a text item field). When the operator selects the list icon, a list of available choices appears.<br><br>What is the maximum no of chars the parameter can store?<br>The maximum no of chars the parameter can store is only valid for char parameters, which can be upto 64K. No parameters default to 23Bytes and Date parameter default to 7Bytes.<br><br>What are the default extensions of the files created by library module?<br>The default file extensions indicate the library module type and storage format .pll - pl/sql library module binary<br><br>What are the Coordination Properties in a Master-Detail relationship?<br>The coordination properties are<br>Deferred<br>Auto-Query<br>These Properties determine when the population phase of block<br>coordination should occur.<br><br>How do you display console on a window ?<br>The console includes the status line and message line, and is displayed at the bottom of the window to which it is assigned.To specify that the console should be displayed, set the console window form property to the name of any window in the form. To include the console, set console window to Null.<br><br>What are the different Parameter types?<br>Text ParametersData Parameters<br><br>State any three mouse events system variables?<br>System.mouse_button_pressedSystem.mouse_button_shift<br><br>What are the types of calculated columns available?<br>Summary, Formula, Placeholder column.<br><br>Explain about stacked canvas views?<br>Stacked canvas view is displayed in a window on top of, or "stacked" on the content canvas view assigned to that same window. Stacked canvas views obscure some part of the underlying content canvas view, and or often shown and hidden programmatically.<br><br>How does one do off-line database backups? (for DBA )<br>Shut down the database from sqlplus or server manager. Backup all files to secondary storage (eg. tapes). Ensure that you backup all data files, all control files and all log files. When completed, restart your database.<br>Do the following queries to get a list of all files that needs to be backed up:<br>select name from sys.v_$datafile;<br>select member from sys.v_$logfile;<br>select name from sys.v_$controlfile;<br>Sometimes Oracle takes forever to shutdown with the "immediate" option. As workaround to this problem, shutdown using these commands:<br>alter system checkpoint;<br>shutdown abort<br>startup restrict<br>shutdown immediate<br>Note that if you database is in ARCHIVELOG mode, one can still use archived log files to roll forward from an off-line backup. If you cannot take your database down for a cold (off-line) backup at a convenient time, switch your database into ARCHIVELOG mode and perform hot (on-line) backups.<br><br>What is the difference between SHOW_EDITOR and EDIT_TEXTITEM?<br>Show editor is the generic built-in which accepts any editor name and takes some input string and returns modified output string. Whereas the edit_textitem built-in needs the input focus to be in the text item before the built-in is executed.<br><br>What are the built-ins that are used to Attach an LOV programmatically to an item?<br>set_item_property<br>get_item_property<br>(by setting the LOV_NAME property)<br><br>How does one do on-line database backups? (for DBA )<br>Each tablespace that needs to be backed-up must be switched into backup mode before copying the files out to secondary storage (tapes). Look at this simple example.<br>ALTER TABLESPACE xyz BEGIN BACKUP;<br>! cp xyfFile1 /backupDir/<br>ALTER TABLESPACE xyz END BACKUP;<br>It is better to backup tablespace for tablespace than to put all tablespaces in backup mode. Backing them up separately incurs less overhead. When done, remember to backup your control files. Look at this example:<br>ALTER SYSTEM SWITCH LOGFILE; -- Force log switch to update control file headers<br>ALTER DATABASE BACKUP CONTROLFILE TO '/backupDir/control.dbf';<br>NOTE: Do not run on-line backups during peak processing periods. Oracle will write complete database blocks instead of the normal deltas to redo log files while in backup mode. This will lead to excessive database archiving and even database freezes.<br><br>How does one backup a database using RMAN? (for DBA )<br>The biggest advantage of RMAN is that it only backup used space in the database. Rman doesn't put tablespaces in backup mode, saving on redo generation overhead. RMAN will re-read database blocks until it gets a consistent image of it. Look at this simple backup example.<br>rman target sys/*** nocatalog<br>run {<br>allocate channel t1 type disk;<br>backup<br>format '/app/oracle/db_backup/%d_t%t_s%s_p%p'<br>( database );<br>release channel t1;<br>}<br>Example RMAN restore:<br>rman target sys/*** nocatalog<br>run {<br>allocate channel t1 type disk;<br># set until time 'Aug 07 2000 :51';<br>restore tablespace users;<br>recover tablespace users;<br>release channel t1;<br>}<br>The examples above are extremely simplistic and only useful for illustrating basic concepts. By default Oracle uses the database controlfiles to store information about backups. Normally one would rather setup a RMAN catalog database to store RMAN metadata in. Read the Oracle Backup and Recovery Guide before implementing any RMAN backups.<br>Note: RMAN cannot write image copies directly to tape. One needs to use a third-party media manager that integrates with RMAN to backup directly to tape. Alternatively one can backup to disk and then manually copy the backups to tape.<br><br>What are the different file extensions that are created by oracle reports?<br>Rep file and Rdf file.<br><br>What is strip sources generate options?<br>Removes the source code from the library file and generates a library files that contains only pcode. The resulting file can be used for final deployment, but can not be subsequently edited in the designer.ex. f45gen module=old_lib.pll userid=scott/tiger strip_source YES output_file<br><br>How does one put a database into ARCHIVELOG mode? (for DBA )<br>The main reason for running in archivelog mode is that one can provide 24-hour availability and guarantee complete data recoverability. It is also necessary to enable ARCHIVELOG mode before one can start to use on-line database backups. To enable ARCHIVELOG mode, simply change your database startup command script, and bounce the database:<br>SQLPLUS&gt; connect sys as sysdba<br>SQLPLUS&gt; startup mount exclusive;<br>SQLPLUS&gt; alter database archivelog;<br>SQLPLUS&gt; archive log start;<br>SQLPLUS&gt; alter database open;<br>NOTE1: Remember to take a baseline database backup right after enabling archivelog mode. Without it one would not be able to recover. Also, implement an archivelog backup to prevent the archive log directory from filling-up.<br>NOTE2: ARCHIVELOG mode was introduced with Oracle V6, and is essential for database point-in-time recovery. Archiving can be used in combination with on-line and off-line database backups.<br>NOTE3: You may want to set the following INIT.ORA parameters when enabling ARCHIVELOG mode: log_archive_start=TRUE, log_archive_dest=... and log_archive_format=...<br>NOTE4: You can change the archive log destination of a database on-line with the ARCHIVE LOG START TO 'directory'; statement. This statement is often used to switch archiving between a set of directories.<br>NOTE5: When running Oracle Real Application Server (RAC), you need to shut down all nodes before changing the database to ARCHIVELOG mode.<br><br>What is the basic data structure that is required for creating an LOV?<br>Record Group.<br><br>How does one backup archived log files? (for DBA )<br>One can backup archived log files using RMAN or any operating system backup utility. Remember to delete files after backing them up to prevent the archive log directory from filling up. If the archive log directory becomes full, your database will hang! Look at this simple RMAN backup script:<br>RMAN&gt; run {<br>2&gt; allocate channel dev1 type disk;<br>3&gt; backup<br>4&gt; format '/app/oracle/arch_backup/log_t%t_s%s_p%p'<br>5&gt; (archivelog all delete input);<br>6&gt; release channel dev1;<br>7&gt; }<br><br>Does Oracle write to data files in begin/hot backup mode? (for DBA )<br>Oracle will stop updating file headers, but will continue to write data to the database files even if a tablespace is in backup mode.<br>In backup mode, Oracle will write out complete changed blocks to the redo log files. Normally only deltas (changes) are logged to the redo logs. This is done to enable reconstruction of a block if only half of it was backed up (split blocks). Because of this, one should notice increased log activity and archiving during on-line backups.<br><br>

    • 0

Most Popular Companies

Most Popular Job Functions