Msc IT Last Year Paper Fully Solved

M. Sc. (Sem. I) (IT) Examination

January/February 2009

P-I03 DBMS & Database Administration

TIME: 3 Hours MAX. MARKS: 70

Q.1	Fill in the blanks		5
	(1)	Privileges are removed from a user with REVOKE command.
	(2)	ALERT.LOG file will gives the oracle instance status information.
	(3)	Control File and Redo Log file can be mirrored by oracle.
	(4)	SYS DBA role gives complete authority in oracle database.
	(5)	ROWID is an internal physical address for every row in every nonclustered table in the database.
Q.2	Answer briefly :		15
	(1)	Name three types of files that make up the oracle database.
	Ans	Control File, Redo Log File and Database File
	(2).	What is auditing used for ?
	Ans	Auditing is monitoring of selected user database actions. It is used to Investigate suspicious database activity. Gather Information about specific database activity.
	(3).	What is oracle SID?
	Ans	The Oracle System Identifier (SID) identifies the Oracle instance on the machine. It is usually set up as an ORACLE_SID symbol is used to name of the Oracle background processes and to identify the SGA area in memory.
	(4).	Name four types of segments.
	Ans	Table (Data Segment), Index, Cluster, Rollback Segment, Temporary Segment, Index Organized Table, Table Partition, Nested Table.
	(5).	What is mirrored-online redo log?
	Ans.	In an attempt to preserve the data within the online redo logs in much the same way as control files, Oracle introduces redo log groups. They enable redo logs to be mirrored across multiple disks. SQL> ALTER DATABASE ARCHIVELOG; SQL> ALTER DATABASE OPEN; Instead of having individual redo logs, each of which contains a distinct series of transactions, the redo logs are broken down into groups and members.
	(6).	Name different kinds of tuning at database level.
	Ans	Application Tuning. Database Tuning. I/O Tuning Memory Tuning Contention Tuning Operating System Tuning
	(7).	Which command is issued to force the checkpoint ?
	Ans	SQL> ALTER DATABASE SWITCH LOGFILE; SQL> ALTER DATABASE CHECKPOINT;
	(8).	What parameter will format the name of archived redo logs?
	Ans	SHOW PARAMETER LOG_ARCHIVE_START SQL> ALTER SYSTEM SET LOG_ARCHIVE_START = TRUE;
	(9).	List various shutdown methods.
	Ans	Shutdown Normal (Default) Shutdown Transaction Shutdown Immediate Shutdown Abort
	(10).	What is log switch ?
	à	LGWR writes new copy of changed data from Redo Log buffer to Online Redo Log files. When Online Redo log File is Full LGWR begins writing into next redo log file. Moving from One Redo log file to next Redo log file for Writing is called Log Switch. It is Automatically occurs when Online Redo log file is Full. You can do it Force fully using following Command : SQL> Alter database switch logfile;
	(11).	What is UTLBSTAT and UTLESTAT?
	Ans.	Oracle provides tools that enable you to examine in detail what the Oracle RDBMS was doing during a specific period of time. They are the begin statistics utility (utlbstat) and the end statistics utility (utlestat). These scripts enable you to take a snapshot of how the instance was performing during an interval of time. They use the Oracle dynamic performance (V$) tables to gather information.
	(12).	What is tablespace?
	Ans	A tablespace is the name given to a group of one or more database files. When objects are created, you can specify in which tablespace they will occupy storage. This gives you control over where and how much storage is used. You can specify the amount of storage that users are allowed to use in each tablespace in the database. To Create a Tablespace : SQL> CREATE TABLESPACE T1 DATAFILE F:\DB1.DBS’ SIZE 10M;
	(13).	What is an Oracle Instance ?
	Ans	Oracle Instance is Combination of Memory Structures (SGA) and Background Processes. SGA : Shared Pool, Redo Log Buffer, Database Buffer Cache, Large Pool, Java Pool Background Processes : DBWn, LGWR, PMON,SMON, ARCHn, Checkpoint.
	(14).	Name the Three stage of Oracle Instance Startup?
	Ans.	STARTUP Nomount, Mount, Open, Force, Recover, Restrict
	(15).	What is profile ?
	Ans	The database profile is Oracle's attempt to enable the DBA to exercise some method of resource management upon the database. A profile is "a named set of resource limits." To Create a Profile SQL > CREATE PROFILE BOSS LIMIT ( FAILED_LOGIN_ATTEMPTS 3 PASSWORD_LOCK_TIME 2 IDLE_TIME 600 CONNECT_TIME 500 SESSIONS_PER_USER 5 );
Q.4	Answer in one word or one sentence :		10.
	(1).	Which tool is commonly used to create queries and execute them against SQL Server databases?
		Query Analyzer
	(2)	What are the three types of Transact-SQL statements that SQL Server supports?
		DDL, DCL, and DML
	(3).	What is Transact-SQL?
		Transact-SQL is a language that contains the commands used to administer instances of SQL Server; to create and manage all objects in an instance of SQL Server; and to insert, retrieve, modify, and delete data in SQL Server tables. Transact-SQL is an extension of the language defined in the SQL standards published by ISO and ANSI.
	(4).	What methods can you use to create a SQL Server database object?
		SQL Server provides several methods that you can use to create a database: the Transact-SQL CREATE DATABASE statement, the console tree in Enterprise Manager, and the Create Database wizard (which you can access through Enterprise Manager).
	(5).	Which statement should you use to delete all rows in a table without having the action logged?
		The TRUNCATE TABLE statement
	(6).	Which three types of cursor implementations does SQL Server support?
		Transact-SQL server cursors, API server cursors, and client cursors
	(7)	Name three common database tasks accomplished with triggers
		Maintaining running totals and other computed values; creating audit records; invoking external actions; and implementing complex data integrity
	(8)	What is the default sort order for an index key?
		An index key is sorted in ascending order unless you specify descending order (the DESC keyword).
	(9).	Name a SQL Server tool you can use to monitor current SQL Server activity.
		The Current Activity node of Enterprise Manager and SQL Profiler are two SQL Server tools that monitor current activity.
	(10).	What are several keywords that you can use in a select list?
		DISTINCT, TOP n, and AS