Description : How does one create and drop database users?
Last Answer : Look at these examples: CREATE USER scott IDENTIFIED BY tiger -- Assign password DEFAULT TABLESACE tools -- Assign space for table and index segments TEMPORARY TABLESPACE temp; -- Assign sort space ... give the user some space quota on its tablespaces: ALTER USER scott QUOTA UNLIMITED ON tools;
Description : What is the use of TABLES option in EXP command ?
Last Answer : List of tables should be exported.ze)
Description : Give the two types of tables involved in producing a star schema and the type of data they hold.
Last Answer : Fact tables and dimension tables. A fact table contains measurements while dimension tables will contain data that will help describe the fact tables.
Description : What are mutating tables
Last Answer : When a table is in state of transition it is said to be mutating. eg :: If a row has been deleted then the table is said to be mutating and no operations can be done on the table except select.
Description : What is the difference between deleting and truncating of tables
Last Answer : Deleting a table will not remove the rows from the table but entry is there in the database dictionary and it can be retrieved But truncating a table deletes it completely and it cannot be retrieved.
Description : What is in all those X$ tables? (for DBA
Last Answer : The following list attempts to describe some x$ tables. The list may not be complete or accurate, but represents an attempt to figure out what information they contain. One should generally not ... Fixed Table. It can predict the potential loss of decreasing the number of database buffers. The
Description : Can cursor variables be stored in PL/SQL tables.If yes how. If not why?
Last Answer : No, a cursor variable points a row which cannot be stored in a two- dimensional PL/SQL table.
Description : What are two virtual tables available during database trigger execution ?
Last Answer : The table columns are referred as OLD.column_name and NEW.column_name. For triggers related to INSERT only NEW.column_name values only available. For triggers related to UPDATE only OLD. ... values only available. For triggers related to DELETE only OLD.column_name values only available.
Description : Name the tables where characteristics of Package, procedure and functions are stored ?
Last Answer : User_objects, User_Source and User_error.
Description : How will you estimate the space required by a non- clustered tables?
Last Answer : Calculate the total header size Calculate the available dataspace per data block Calculate the combined column lengths of the average row Calculate the total average row size. Calculate the average number rows ... add 10 % additional space to calculate the initial extent size for a working table.
Description : What are the dictionary tables used to monitor a database space?
Last Answer : DBA_FREE_SPACE DBA_SEGMENTS DBA_DATA_FILES.
Description : What are the dictionary tables used to monitor a database spaces ?
Description : How does one get the view definition of fixed views/tables?
Last Answer : Query v$fixed_view_definition. Example: SELECT * FROM v$fixed_view_definition WHERE view_name='V$SESSION';
Description : Spurious tuples may occur due to i. Bad normalization ii. Theta joins iii. Updating tables from join a) i& ii b) ii & iii c) i& iii d) ii & iii
Last Answer : a) i& ii b) ii & iii
Description : Tables derived from the ERD
Last Answer : a) Are totally unnormalised b) Are always in 1NF c) Can be further denormalised d) May have multi-valued attributes (b) Are always in 1NF
Description : How do you move tables from one tablespace to another tablespace?
Last Answer : There are several methods to do this; 1) export the table, drop the table, create the table definition in the new tablespace, and then import the data (imp ignore=y). 2) Create ... table and rename the temporary table as the original: DROP TABLE real_table; RENAME temp_name TO real_table;
Description : Can you make collection of Statistics for tables automatically?
Last Answer : YES. Oracle databse has default, scheduled job "gather_stats_job" that analyses stats on a daily basis during the maintenance window time. There are two scheduled activities related to ... GATHER_STATS_JOB'); Oracle collects optimizer statistics for SQL via the default of autostats_target = auto.
Description : Name the seven types of Oracle tables
Last Answer : Heap Organized Tables, Index Organized Tables, Index Clustered Tables, Hash Clustered Tables, Nested Tables, Global Temporary Tables, Object Tables.
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.
Description : Who created all these users in my database?/ Can I drop this user? (for DBA
Last Answer : Oracle creates a number of default database users or schemas when a new database is created. Below are a few of them: SYS/CHANGE_ON_INSTALL or INTERNAL Oracle Data Dictionary/ Catalog Created ... there should be no problem altering these users to use a different default and temporary tablespace.
Description : Does one need to drop/ truncate objects before importing? (for DBA
Last Answer : 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. ... to speed up the import process. Indexes can easily be recreated after the data was successfully imported.
Description : How does one create a standby database? (for DBA)
Last Answer : While your production database is running, take an (image copy) backup and restore it on duplicate hardware. Note that an export will not work!!! On your standby database, issue ... the standby database, stop the recovery process and activate it: ALTER DATABASE ACTIVATE STANDBY DATABASE;
Description : Name a tablespace automatically created when you create a database.
Last Answer : The SYSTEM tablespace.
Description : What command would you use to create a backup control file?
Last Answer : Alter database backup control file to trace.
Description : Which parameter specified in the DEFAULT STORAGE clause of CREATE TABLESPACE cannot be altered after creating the tablespace?
Last Answer : All the default storage parameters defined for the tablespace can be changed using the ALTER TABLESPACE command. When objects are created their INITIAL and MINEXTENS values cannot be changed.
Description : What utility is used to create a physical backup?
Last Answer : Either rman or alter tablespace begin backup will do..
Description : The following describes means to create a OEM V1.x (very old!!!) repository on WindowsNT:
Last Answer : Create a tablespace that would hold the repository data. A size between 200- 250 MB would be ideal. Let us call it Dummy_Space. . Create an Oracle user who would own this repository. Assign ... would trigger the creation of the repository. From now on, Enterprise manager is ready to accept jobs.
Description : How does one create a repository? (for DBA
Last Answer : For OEM v2 and above, start the Oracle Enterprise Manager Configuration Assistant (emca on Unix) to create and configure the management server and repository. Remember to setup a backup for the repository database after creating it.
Description : What are the built-in routines is available in forms 4.0 to create and manipulate a parameter list?
Last Answer : Add_parameter Create_Parameter_list Delete_parameter Destroy_parameter_list Get_parameter_attr Get_parameter_list set_parameter_attr
Description : Can we create two blocks with the same name in form 3.0 ?
Last Answer : No
Description : How will you create multiple rollback segments in a database ?
Last Answer : Create a database which implicitly creates a SYSTEM Rollback Segment in a SYSTEM tablespace. Create a Second Rollback Segment name R0 in the SYSTEM tablespace. Make new rollback segment ... ) for rollback segments. Deactivate Rollback Segment R0 and activate the newly created rollback segments.
Description : How does one create a RMAN recovery catalog? (for DBA
Last Answer : Start by creating a database schema (usually called rman). Assign an appropriate tablespace to it and grant it the recovery_catalog_owner role. Look at this example: sqlplus sys SQL>create user ... . Look at this example: rman catalog rman/rman target backdba/backdba RMAN> register database;
Description : Why is it preferable to create a fewer no. of queries in the data model?
Last Answer : Because for each query, report has to open a separate cursor and has to rebind, execute and fetch data.
Description : How does one create a password file? (for DBA
Last Answer : 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 ... visible in the process table of many systems. Administrators needs to be aware of this!
Description : How do you create a new session while open a new form?
Last Answer : Using open_form built-in setting the session option Ex. Open_form('Stocks ',active,session). when invoke the mulitiple forms with open form and call_form in the same application, state whether the following are true/False
Description : How does one create a new database? (for DBA)
Last Answer : One can create and modify Oracle databases using the Oracle "dbca" (Database Configuration Assistant) utility. The dbca utility is located in the $ORACLE_HOME/bin directory. The Oracle Universal ... ='/u02/oradata/'; ALTER SYSTEM SET DB_CREATE_ONLINE_LOG_DEST_2='/u03/oradata/'; CREATE DATABASE;
Description : A Column is having many repeated values which type of index you should create on this column, if you have to?
Last Answer : For example, assume there is a motor vehicle database with numerous low-cardinality columns such as car_color, car_make, car_model, and car_year. Each column contains less than 100 distinct values by themselves, and a b tree index would be fairly useless in a database of 20 million vehicles.
Description : On which columns you should create Indexes?
Last Answer : In general, you should create an index on a column in any of the following situations: ▪ The column is queried frequently ▪ A referential integrity constraint exists on the column ▪ A UNIQUE key ... ORDER BY clauses ▪ On columns that have few of the same values or unique values in the table
Description : Which default Database roles are created when you create a Database?
Last Answer : CONNECT , RESOURCE and DBA are three default roles. The DBA_ROLES data dictionary view can be used to list all roles of a database and the authentication used for each role. The following query lists all the roles in ... ------------ -------- CONNECT NO RESOURCE NO DBA NO SECURITY_ADMIN YES
Description : What is the minimum number of instances you need to have in order to create a RAC?
Last Answer : 1. You can create a RAC with just one server.
Description : Which among the following definitions are correct: a) Object - Any entity that has state and behavior is known as an object. b) Class - Collection of objects is called class. It is logical entity. c) ... re-usability and is used to achieve run time polymorphism. d) All of the above e) None of These
Last Answer : d) All of the above
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\"
Description : How can you gather statistics on a table?
Last Answer : The ANALYZE command.
Description : How would you determine who has added a row to a table?
Last Answer : Turn on fine grain auditing for the table.
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.
Description : What type of index should you use on a fact table?
Last Answer : A Bitmap index.
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.
Description : Give two examples of how you might determine the structure of the table DEPT.
Last Answer : Use the describe command or use the dbms_metadata.get_ddl package.
Description : How many columns can table have?
Last Answer : The number of columns in a table can range from 1 to 254.
Description : Deleting the Duplicate rows in the table
Last Answer : We can delete the duplicate rows in the table by using the Rowid