Give two examples of how you might determine the structure of the table DEPT.

Last Answer : FUNCTION name (argument list .....) Return datatype is local variable declarations Begin executable statements Exception execution handlers End;

Give the structure of the procedure ?

Description : Give the structure of the procedure ?

Last Answer : PROCEDURE name (parameter list.....) is local variable declarations BEGIN Executable statements. Exception. exception handlers end;

What view would you use to determine free space in a tablespace?

Description : What view would you use to determine free space in a tablespae?

Last Answer : DBA_FREE_SPACE

How would you determine what sessions are connected and what resources they are waiting for?

Description : How would you determine what sessions are connected and what resources they are waiting for?

Last Answer : Use of V$SESSION and V$SESSION_WAIT

How would you determine the time zone under which a database was operating?

Description : How would you determine the time zone under which a database was operating?

Last Answer : select DBTIMEZONE from dual;

Is it right to pay for the doctor to give you a clean health to drive when the licensing dept asks for it?

Description : Is it right to pay for the doctor to give you a clean health to drive when the licensing dept asks for it?

Last Answer : Do you have a medical condition that would preclude driving such as epilepsy?

Say two examples of DML, two of DCL and two of DDL

Description : Say two examples of DML, two of DCL and two of DDL

Last Answer : Say two examples of DML, two of DCL and two of DDL

Can one export a subset of a table? (for DBA

Description : Can one export a subset of a table? (for DBA

Last Answer : From Oracle8i one can use the QUERY= export parameter to selectively unload a subset of the data from a table. Look at this example: exp scott/tiger tables=emp query=\"where deptno=10\"

How can you gather statistics on a table?

Description : How can you gather statistics on a table?

Last Answer : The ANALYZE command.

Explain the use of table functions.

Description : Explain the use of table functions.

Last Answer : 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.

A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?

Description : A table is classified as a parent table and you want to drop and re-create it. How would you do this without affecting the children tables?

Last Answer : Disable the foreign key constraint to the parent, drop the table, re- create the table, enable the foreign key constraint.

What type of index should you use on a fact table?

Description : What type of index should you use on a fact table?

Last Answer : A Bitmap index.

Compare and contrast TRUNCATE and DELETE for a table.

Description : Compare and contrast TRUNCATE and DELETE for a table.

Last Answer : 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 ... other hand, is a DML operation, which will produce a rollback and thus take longer to complete.

How many columns can table have?

Description : How many columns can table have?

Last Answer : The number of columns in a table can range from 1 to 254.

Deleting the Duplicate rows in the table

Description : Deleting the Duplicate rows in the table

Last Answer : We can delete the duplicate rows in the table by using the Rowid

Table Space,Data Files,Parameter File, Control Files

Description : Table Space,Data Files,Parameter File, Control Files

Last Answer : Table Space :: The table space is useful for storing the data in the database.When a database is created two table spaces are created. a) System Table space :: This data file stores all the tables ... They contain the Db name, name and location of dbs, data files ,redo log files and time stamp.

Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

Description : Suppose a customer table is having different columns like customer no, payments.What will be the query to select top three max payments?

Last Answer : SELECT customer_no, payments from customer C1 WHERE 3

Find out nth highest salary from emp table?

Description : Find out nth highest salary from emp table?

Last Answer : SELECT DISTINCT (a.sal) FROM EMP A WHERE &N = (SELECT COUNT (DISTINCT (b.sal)) FROM EMP B WHERE a.sal

maxvalue.sql Select the Nth Highest value from a table?

Description : maxvalue.sql Select the Nth Highest value from a table?

Last Answer : select level, max('col_name') from my_table where level = '&n' connect by prior ('col_name') > 'col_name') group by level; Example: Given a table called emp with the following columns: -- id number -- name ... , max(sal) from emp -- where level=2 -- connect by prior sal > sal -- group by level

What are the data types allowed in a table ?

Description : What are the data types allowed in a table ?

Last Answer : CHAR,VARCHAR2,NUMBER,DATE,RAW,LONG and LONG RAW.

If a View on a single base table is manipulated will the changes be reflected on the base table ?

Description : If a View on a single base table is manipulated will the changes be reflected on the base table ?

Last Answer : If changes are made to the tables which are base tables of a view will the changes be reference on the view.

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

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

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

What is the fastest way of accessing a row in a table ?

Description : What is the fastest way of accessing a row in a table ?

Last Answer : Using ROWID.CONSTRAINTS

How do you find the numbert of rows in a Table ?

Description : How do you find the numbert of rows in a Table ?

Last Answer : A bad answer is count them (SELECT COUNT(*) FROM table_name) A good answer is :- 'By generating SQL to ANALYZE TABLE table_name COUNT STATISTICS by querying Oracle System Catalogues (e.g ... utility which Oracle released which makes it unnecessary to do ANALYZE TABLE for each Table individually.

How will you delete duplicating rows from a base table?

Description : How will you delete duplicating rows from a base table?

Last Answer : delete from table_name where rowid not in (select max(rowid) from table group by duplicate_values_field_name); or delete duplicate_values_field_name dv from table_name ta where rowid

minvalue.sql Select the Nth lowest value from a table?

Description : minvalue.sql Select the Nth lowest value from a table?

Last Answer : select level, min('col_name') from my_table where level = '&n' connect by prior ('col_name') < 'col_name') group by level; Example: Given a table called emp with the following columns: -- id number -- name ... , min(sal) from emp -- where level=2 -- connect by prior sal < sal -- group by level

Write the order of precedence for validation of a column in a table ?

Description : Write the order of precedence for validation of a column in a table ?

Last Answer : I. done using Database triggers. ii. done using Integarity Constraints.?

How many types of database triggers can be specified on a table ? What are they ?

Description : How many types of database triggers can be specified on a table ? What are they ?

Last Answer : Insert Update Delete Before Row o.k. o.k. o.k. After Row o.k. o.k. o.k. Before Statement o.k. o.k. o.k. After Statement o.k. o.k. o.k. If FOR EACH ROW clause is ... for each Row affected by the statement. If WHEN clause is specified, the trigger fires according to the returned Boolean value.

Where can one find I/O statistics per table? (for DBA

Description : Where can one find I/O statistics per table? (for DBA

Last Answer : The UTLESTAT report shows I/O per tablespace but one cannot see what tables in the tablespace has the most I/O. The $ORACLE_HOME/rdbms/admin/catio.sql script creates a sample_io procedure and ... For more details, look at the header comments in the $ORACLE_HOME/rdbms/admin/catio.sql script.

Committed block sometimes refer to a BASE TABLE ?

Description : Committed block sometimes refer to a BASE TABLE ?

Last Answer : False

How does Space allocation table place within a block ?

Description : How does Space allocation table place within a block ?

Last Answer : Each block contains entries as follows Fixed block header Variable block header Row Header,row date (multiple rows may exists) PCTEREE (% of free space for row updation in future)

How will you swap objects into a different table space for an existing database ?

Description : How will you swap objects into a different table space for an existing database ?

Last Answer : Export the user Perform import using the command imp system/manager file=export.dmp indexfile=newrite.sql. This will create all definitions into newfile.sql. Drop necessary objects. Run the script newfile.sql after altering the tablespaces. Import from the backup for the necessary objects.

What is the mechanism provided by ORACLE for table replication ?

Description : What is the mechanism provided by ORACLE for table replication ?

Last Answer : Snapshots and SNAPSHOT LOGs

Where can one find the high water mark for a table? (for DBA)

Description : Where can one find the high water mark for a table? (for DBA)

Last Answer : There is no single system table, which contains the high water mark (HWM) for a table. A table's HWM can be calculated using the results from the following SQL statements: SELECT BLOCKS FROM ... : You can also use the DBMS_SPACE package and calculate the HWM = TOTAL_BLOCKS - UNUSED_BLOCKS - 1.

What is Table ?

Description : What is Table ?

Last Answer : 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.

What is Oracle table?

Description : What is Oracle table?

Last Answer : 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.

What is a Table?

Description : What is a Table?

Last Answer : A Table is the primary unit of physical storage in a database. Usually a database contains more than one table.

What is an Index Organized Table?

Description : What is an Index Organized Table?

Last Answer : An index-organized table (IOT) is a type of table that stores data in a B*Tree index structure. Normal relational tables, called heap-organized tables, store rows in any order (unsorted). In contrast ... all the columns of the table (an exception to this rule - is being called the overflow area).

Can you Redefine a table Online?

Description : Can you Redefine a table Online?

Last Answer : Yes you can. In any database system, it is occasionally necessary to modify the logical or physical structure of a table to: ▪ Improve the performance of queries or DML ▪ Accommodate application ... space used by the table being redefined. More space may be required if new columns are added.

A table is having few rows, should you create indexes on this table

Description : A table is having few rows, should you create indexes on this table

Last Answer : Small tables do not require indexes; if a query is taking too long, then the table might have grown from small to large. You can create an index on any column; however, if the ... , creating an index on the column does not increase performance and the index takes up resources unnecessarily.

How do you collect statistics for a table, schema and Database?

Description : How do you collect statistics for a table, schema and Database?

Last Answer : Using DBMS_STATS package to gather Oracle dictionary statistics.

How often you should collect statistics for a table?

Description : How often you should collect statistics for a table?

Last Answer : Analyse if it's necessary! - Refresh STALE statistics before the batch processes run but only for tables involved in batch run, - Don't do it if you don't have to. - Oracle ... has default, scheduled job "gather_stats_job" that analyses stats on a daily basis during the maintenance window time.

Will a user be able to modify a table with SELECT only privilege?

Description : Will a user be able to modify a table with SELECT only privilege?

Last Answer : He won’t be able to UPDATE/INSERT into that table, but for some reason, he will still be able to lock a certain table.

In which view can you find information about every view and table of oracle dictionary

Description : In which view can you find information about every view and table of oracle dictionary

Last Answer : DICT or DICTIONARY

What is the basic structure of PL/SQL ?

Description : What is the basic structure of PL/SQL ?

Last Answer : PL/SQL uses block structure as its basic structure. Anonymous blocks or nested blocks can be used in PL/SQL.

Where in the Oracle directory tree structure are audit traces placed?

Description : Where in the Oracle directory tree structure are audit traces placed?

Last Answer : In unix $ORACLE_HOME/rdbms/audit, in Windows the event viewer

rohanb31 · Answer 1 · 2022-01-02T11:38:49+0000

Use the describe command or use the dbms_metadata.get_ddl
package.

Give two examples of how you might determine the structure of the table DEPT.

Please log in or register to add a comment.

Please log in or register to answer this question.

1 Answer

Answer :

Please log in or register to add a comment.

Related questions