Brainscape's Knowledge GenomeTM

Browse over 1 million classes created by top students, professors, publishers, and experts.


See full index

Interview > Oracle > Flashcards

Oracle Flashcards

1
Q

What is pipeline view?

A

A view that get data from other views.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
2
Q

What is materialised View?

A

Materialised view (MV) is similar to a view but the data is actually stored on disk. Often used for summary and Pre-joined tables.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
3
Q

What are the tools for Oracle?

A

TOAD, PUTTY Linux/Unix, Oracle forms, Oracles reports.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
4
Q

How to get date and time?

A

Sysdate

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
5
Q

What is a snapshot?

A

snapshot is just like a print screen, read only.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
6
Q

Difference between ref cursor and nomral cursor?

A

Normal Cursor is static and can be associated with only a single query where as ref cursor is dynamic and can be associated with multiple queries.

Refence Curso is basically used to pass result set to a procedure or function. Can be Strong Type or Weak Type.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
7
Q

What is the difference between Strong and Weak Types?

A

?

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
8
Q

What is Explain Plan?

A

Just like SQL Server Excuation plan.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
9
Q

Which is faster Truncate or Delete?

A

Truncate will be faster because delete will have to update logs and do overhead operations and Truncate doesn not.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
10
Q

Where to find database errors?

A

Alert Log

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
11
Q

What is DDL?

A

Data Definition Language (DDL) statements are used to define the database structure or schema.

Some examples:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the records are removed
COMMENT - add comments to the data dictionary
RENAME - rename an object

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
12
Q

What is DML?

A

Data Manipulation Language (DML) statements are used for managing data within schema objects.

Some examples:
SELECT - retrieve data 
INSERT - insert data into a table
UPDATE - updates existing data 
DELETE - deletes records 
MERGE - UPSERT operation (insert or update)
CALL - call a PL/SQL or Java subprogram
EXPLAIN PLAN - explain access path to data
LOCK TABLE - control concurrency
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
13
Q

Why used an Index?

A

Faster Access to the data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
14
Q

What is Fact Table?

A

A fact table contains measurements.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
15
Q

What is a Dimension Table?

A

A dimenension table will contain data that will help describe the fact tables.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
16
Q

List some contraints.

A

Primary or Unique Key
Referential Integrity
Not Null Contraint
Value contraint - to check a column value against a specific set of values.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
17
Q

What type of index shoould you use for a fact table?

A

A bitmap index.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
18
Q

How to drop and recreate a table children records?

A

Drop the foreign key constraits first. Drop and recreated, then add constraits back.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
19
Q

How to get the timezone?

A

Use dbtimezone.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
20
Q

What is the use of Global_Names?

A

To ensure the use of consistent conventions for database and links in the a networked environment.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
21
Q

How would you encrypt PL/SQL?

A

Use WRAP.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
22
Q

What is a Package?

A

Package is the collections of functions, procedures, variables which can be logically grouped together.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
23
Q

What is function?

A

Function must return a value. can be called inside a query.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
24
Q

What is a procedure?

A

procedure may or may not return a value.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
25
Q

Explain the use of table functions?

A

Table functions are designed to return a set of rows through PL/SQL logic but are intended to be used as a normal table or view in a SQL statement. They are also used to pipeline information in an ETL process.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
26
Q

Name three advisory statistics you can collect?

A

fer Cache Advice, Segment Level Statistics, & Timed Statistics

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
27
Q

When a user process fails, what background process cleans up after it?

A

PMON

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
28
Q

What background process refreshes materialized views?

A

Job Queue process (CJQ)

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
29
Q

What does coalescing a tablespace do?

A

Coalesce simple takes contigous free extents and makes them into a single bigger free extend. Defrag?

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
30
Q

Difference between Temp and permanent Tablespace?

A

Temp get cleared once the transaction is done where Permanent retains the data.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
31
Q

Two methods determine DDL changes?

A

You could use Logminer or Streams

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
32
Q

How to rebuild an index?

A

ALTER INDEX Index_Name REBUILD.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
33
Q

Explain partitionary.

A

A Table partition is also a table segment and by using this techique we can enhance performance on a table.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
34
Q

You just compiled a PL/SQL packages and you get Errors, how to view the errors?

A

show errors.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
35
Q

Difference between SQL*Loader and IMPORT utilities?

A

SQL*Loader loads external data which is in OS files to oracle database tables while IMPORT utility imports data only which is experoted by EXPORT utility of Oracle database.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
36
Q

name two files used for network connection to a database?

A

TNSNames.ORA and SQLNET.ORA

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
37
Q

What is mutating table error?

A

happens with TRIGGERS while trying to update a row it is using.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
38
Q

Describe %RowType and %Type in PL/SQL?

A

%RowType allows you to associate a variable with an entire table row. The %Type associates a variable with a single column type.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
39
Q

Developer’s Packages?

A 
DBMS_SQL
DBMS_PIPE
DBMS_Transaction
DBMS_Lock
DBMS_ALERT
DBMS_OUTPUT
DBMS_Job
DBMS_Utility
DBMS_DDL
How well did you know this?
1
Not at all
2
3
4
5
Perfectly
40
Q

Describe the use of PL/SQL Tables.

A

PL/SQL tables are scalar arrays that be referenced by a binary integer. They can be used to hold values for use in later queires or calculations. In oracle 8 they will be able to be of the %RowType designation or RECORD

Similar to Table variable is SQL Server.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
41
Q

When is a DECLARE statement needed?

A

The DECLARE statement is used in PL/SQL anonymous block such as with stand alone, non-stored PL/SQL procedures. It must come first in a PL/SQL stand Alone file if it is used.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
42
Q

What is an Anonymous block?

A

An anonymous block is an unnamed sequence of actions. Since they are unnamed, anonymous blocks cannot be referenced by other program units.

In contrast to anonymous blocks, stored/ named code blocks include Packages, Procedures, and Functions.

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
43
Q

What are SQLCODE and SQLERRM and why are the important for PPL/SQL developers?

A

SQLCODE Returns the ballue of the error number for the last error encountered. The SQLERRM returns the actual error message for the last error encountered.

Simary to SQL Server Try and CATCH

How well did you know this?
1
Not at all
2
3
4
5
Perfectly
44
Q

How to find an OPEN Cursor?

A

Use the %ISOPEN cursor status variable.

45
Q

Howto get Debugging Output?

A

use the DBMS_Output package. Or SHOW ERROR comman.

46
Q

Types of Triggers?

A

12 Types.

BEFORE, AFTER, ROW, TABLE, INSERT, UPDATE, DELETE, ALL

47
Q

What is the fastest query mehtod for a table?

A

Fetch by Rowid. In SQL Server, you would SELECT primary key using clustered index.

48
Q

Explain the use of TKPROF?

A

A tuning tool for SQL Statements. Similar to SQL Server Excuation Plan where CPU, index, etc information is displayed.

49
Q

provide one method for transferring a table from one schema to another?

A

CREATE TABLE AS SELECT or COPY

50
Q

What is Oracle table?

A

A table is the basic unit of data storage in an Oracle database. The tables of a database hold all of the user accessible data. Table data is stored in rows and columns.

51
Q

What are Clusters?

A

Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

52
Q

What is an Index?

A

An Index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

53
Q

What are the advantages of views?

A

■Provide an additional level of table security, by restricting access to a predetermined set of rows and columns of a table.
■Hide data complexity.
■Simplify commands for the user.
■Present the data in a different perspective from that of the base table
■Store complex queries.

54
Q

What are the various types of queries?

A 
The types of queries are :■Normal Queries
■Sub Queries
■Co-related queries
■Nested queries
■Compound queries
55
Q

What is the difference between clustered and a non-clustered index?

A

A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.
A Nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk.

56
Q

What is a Tablespace?

A

A database is divided into Logical Storage Unit called tablespace. A tablespace is used to grouped related logical structures together.

57
Q

Why use materialized view instead of a table?

A

Materialized views are basically used to increase query performance since it contains results of a query. They should be used for reporting instead of a table for a faster execution.

58
Q

What does ROLLBACK do?

A

ROLLBACK retracts any of the changes resulting from the SQL statements in the transaction.

59
Q

Compare and contrast TRUNCATE and DELETE for a table?

A

Both the truncate and delete command have the desired outcome of getting rid of all the rows in a table. The difference between the two is that the truncate command is a DDL operation and just moves the high water mark and produces a now rollback. The delete command, on the other hand, is a DML operation, which will produce a rollback and thus take longer to complete.

60
Q

what is null value?

A

Null Value is neither zero nor it is a blank space. It is some unknown value which occupies 4 bytes of space of memory in SQL.

61
Q

Define transaction?

A

A transaction is a sequence of SQL statements that Oracle Database treats as a single unit.

62
Q

what is the difference between sql&oracle?

A

SQL is Stuctured Query Language.Oracle is a Database.SQL is used to write queries against Oracle DB.

63
Q

What are different Oracle database objects?

A 
■TABLES
■VIEWS
■INDEXES
■SYNONYMS
■SEQUENCES
■TABLESPACES
64
Q

What is hash cluster?

A

Hash Clusters cluster table data in a manner similar to normal, index clusters.

A row is stored in a hash cluster based on the result of applying a hash function to the row’s cluster key value. All rows with the same hash key value are stores together on disk.

65
Q

What is a User_exit?

A

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.

66
Q

What is schema?

A

A schema is collection of database objects of a user.

67
Q

What are Roles?

A

Roles are named groups of related privileges that are granted to users or other roles.

68
Q

What are the dictionary tables used to monitor a database spaces ?

A

■DBA_FREE_SPACE
■DBA_SEGMENTS
■DBA_DATA_FILES

69
Q

What is a SNAPSHOT?

A

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.

70
Q

What is a database instance?

A

A database instance is a set of memory structure and background processes that access a set of database files. The processes can be shared by all of the users.

71
Q

at are parameters?

A

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.

72
Q

What are the different file extensions that are created by oracle reports?

A

Rep file and Rdf file.

73
Q

What are clusters?

A

Clusters are groups of one or more tables physically stores together to share common columns and are often used together.

74
Q

What is difference between SUBSTR and INSTR?

A

INSTR function search string for sub-string and returns an integer indicating the position of the character in string that is the first character of this occurrence.SUBSTR function return a portion of string, beginning at character position, substring_length characters long.SUBSTR calculates lengths using characters as defined by the input character set.

75
Q

Define a view?

A

A view is a virtual table which is based on the one or more physical tables and views.

76
Q

What is the difference between a view and a synonym?

A

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.

77
Q

What is the usage of SAVEPOINTS?

A

SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.

78
Q

What are ORACLE PRECOMPILERS?

A

A precompiler is a tool that allows programmers to embed SQL statements in high-level source programs like C, C++, COBOL, etc.The precompiler accepts the source program as input, translates the embedded SQL statements into standard Oracle runtime library calls, and generates a modified source program that one can compile, link, and execute in the usual way.

79
Q

When do you use WHERE clause and when do you use HAVING clause?

A

The WHERE condition lets you restrict the rows selected to those that satisfy one or more conditions.Use the HAVING clause to restrict the groups of returned rows to those groups for which the specified condition is TRUE.

80
Q

What are the differences between stored procedures and triggers?

A

A stored procedures are compiled collection of programs or SQL statements that live in the database. A stored procedure can access and modify data present in many tables. Also a stored procedure is not associated with any particular database object. But triggers are event-driven special procedures which are attached to a specific database object.

81
Q

.

What must be installed with ODBC on the client in order for it to work with Oracle?

A

SQLNET and PROTOCOL (for example: TCPIP adapter) layers of the transport programs.

82
Q

what is trigger?

A

Triggers are special kind of stored procedures that get executed automatically when an INSERT, UPDATE or DELETE operation takes place on a table.

83
Q

Explain the difference between a data block, an extent and a segment?

A

A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.

84
Q

What is bind reference and how can it be created?

A

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.

85
Q

How many LONG columns are allowed in a table? Is it possible to use LONG columns in WHERE clause or ORDER BY?

A

Only one LONG column is allowed. It is not possible to use LONG column in WHERE or ORDER BY clause.

86
Q

What are various types of joins?

A

■Equi joins
■Cartesian joins
■Self join
■Outer join

87
Q

What is the maximum number of triggers, can apply to a single table?

A

12 triggers

88
Q

What command would you use to create a backup control file?

A

Alter database backup control file to trace.

89
Q

What is an Oracle index?

A

An index is an optional structure associated with a table to have direct access to rows, which can be created to increase the performance of data retrieval. Index can be created on one or more columns of a table.

90
Q

What are the different index configurations a table can have?

A

■A table can have one of the following index configurations
■No indexes
■A clustered index
■A clustered index and many nonclustered indexes
■A nonclustered index
■Many nonclustered indexes.

91
Q

What is difference between UNIQUE constraint and PRIMARY KEY constraint?

A

A column defined as UNIQUE can contain Nulls while a column defined as PRIMARY KEY can’t contain Nulls. A table can have only one primary keys.

92
Q

What is BCP? When does it used?

A

BulkCopy is a tool used to copy huge amount of data from tables and views. BCP does not copy the structures same as source to destination.

93
Q

How to know which index a table is using?

A

SELECT table_name,index_name FROM user_constraints.

94
Q

What is SYSTEM tablespace and when is it created?

A

Every Oracle database contains a tablespace named SYSTEM, which is automatically created when the database is created. The SYSTEM tablespace always contains the data dictionary tables for the entire database.

95
Q

Which date function returns number value?

A

months_between

96
Q

what is integrity constrains?

A

Integrity constraints are used to ensure accuracy and consistency of data in a relational database.

97
Q

What is the difference between Explicit and Implicit Cursors?

A

An Implicit cursor is one created “automatically” for you by Oracle when you execute a query. It is simpler to code
An Explicit cursor is one you create yourself. It takes more code, but gives more control - for example, you can just open-fetch-close if you only want the first record and don’t care if there are others DBA_DATA_FILES.

98
Q

what are primary keys?

A

Primary key are used to uniquely identify each row of the table. A table can have only one primary key.

99
Q

What are the components of physical database structure of Oracle database?

A

Oracle database is comprised of three types of files. One or more data files, two are more redo log files, and one or more control files.

100
Q

What is DECODE function used for?

A

DECODE is used to decode a CHAR or VARCHAR2 or NUMBER into any of several different character strings or numbers based on value. That is DECODE does a value-by-value substitution.

101
Q

What is the default return value of a function?

A

The default return value from a function is int. In other words, unless explicitly specified the default return value by compiler would be integer value from function.

102
Q

What is the difference between oracle,sql and sql server?

A

■Oracle is based on RDBMS.
■SQL is Structured Query Language.
■SQL Server is another tool for RDBMS provided by MicroSoft.

103
Q

How you will avoid your query from using indexes?

A

By changing the order of the columns that are used in the index, in the Where condition, or by concatenating the columns with some constant values.

104
Q

What is a cluster key?

A

The related columns of the tables are called the cluster key. The cluster key is indexed using a cluster index and its value is stored only once for multiple tables in the cluster.

105
Q

What is SGA?

A

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.

106
Q

What is a data segment?

A

Data segment are the physical areas within a database block in which the data associated with tables and clusters are stored.

107
Q

What is ROWID?

A

ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, row number are the components of ROWID.

108
Q

What is the usage of SAVEPOINTS?

A

SAVEPOINTS are used to subdivide a transaction into smaller parts. It enables rolling back part of a transaction. Maximum of five save points are allowed.

Interview (23 decks)

Brainscape helps you reach your goals faster, through stronger study habits.
© 2024 Bold Learning Solutions. Terms and Conditions