How to restore damaged files in a primary storagepools

Traditionally we use copy storagepool tapes to restore the damaged files in a primary storagepools. Initially we need to find which files are damaged in the primary storagepool and also find which copy storagepool tapes are needed from offsite in order to fix the damaged files. This procedure is a time consuming process as there is lot of physical activity and dependancy involved in it. To overcome this issue, starting from IBM TSM V7.1.1 we can automatically recover damaged files from a replication server if replication is enabled. When this feature is enabled, the system detects any damaged files on a source replication server and replaces them with undamaged files from a target replication server.

With Tivoli Storage Manager Version 7.1.1 and above versions, you can use node replication processing to recover damaged files. You can use the replication process to recover damaged files from that server. You can specify that the replication process is followed by an additional process that detects damaged files on the source server and replaces them with undamaged files from the target server.

You can also enable this feature for specific client nodes. With REGISTER NODE command or with the UPDATE NODE command you can specify whether data from damaged files is recovered automatically during the replication process. You can also specify a parameter on the REPLICATE NODE command for a single replication instance, you can start a process that replicates the node and recovers damaged files. Alternatively, you can start a replication process for the sole purpose of recovering damaged files.


After the node replication process is completed, a recovery process can be started on the target replication server. Files are recovered only if all the following conditions are met:
  • Tivoli Storage Manager, Version 7.1.1 or later, is installed on the source and target replication servers.
  • The REPLRECOVERDAMAGED system parameter is set to ON. The system parameter can be set by using the SET REPLRECOVERDAMAGED command.
  • The source server includes at least one file that is marked as damaged in the node that is being replicated.
  • The node data was replicated before the damage occurred.

Steps to restore or recover damaged files in the primary storagepools

1) First check the setting for recovering damaged files from a target replication server is turned on by issuing QUERY STATUS command
query status
For the above output, Recovery of Damaged Files parameter value should be ON. If the setting for recovering damaged files is OFF, turn it on by issuing the SET REPLRECOVERDAMAGED command and specifying ON
set replrecoverdamaged on
If the REPLRECOVERDAMAGED system parameter is set to OFF, and you change the setting to ON, an automatic scan of the Tivoli Storage Manager system is started. You must wait for the process to be completed successfully before you can initiate the recovery of damaged files by using the REPLICATE NODE command.

2) Now we can use REPLICATE NODE command with RECOVERDAMAGED parameter to recover the damaged files. When trying to recover the damaged files, you can use RECOVERDAMAGED parameter to run the full node replication process for a node or node-group and then recover the damaged files or you can only recover the damaged files without initiating a full node replication process. 
RECOVERDAMAGED parameter

For example, to run the full node replication process and recover damaged files for client nodes in the PROD group, issue the following command:
replicate node PROD recoverdamaged=yes

For example, to recover damaged files for client nodes in the PROD group without running the full node replication process, issue the following command:
replicate node PROD recoverdamaged=only

Note that the RECOVERDAMAGED parameter of the REPLICATE NODE command overrides any value that you specify for the RECOVERDAMAGED parameter at the node level. 

How to redistribute data and reclaim TSM DB space manually by using DB2 commands

If you did not redistribute the DB directories when you added them to the TSM server or if you used RECLAIMSTORAGE=NO when running EXTEND DBSPACE command, then you need to run the following DB2 commands to manually redistribute data and reclaim space from the old DB directories. 

The redistribution process, also known as rebalancing, only works with DB2 version 9.7 or later table spaces. Please note that the rebalancing process uses considerable resources and you need to follow the below tips before you start the procedure.
  • Run the process when the server is not handling a heavy workload.
  • To redistribute data to new directories, the storage paths must be able to host the directories and data. Make sure that sufficient disk space is available for the operation.
  • The time that is required to redistribute data and reclaim space might vary. File system layout, the ratio of new paths to existing storage paths, server hardware, and concurrent operations are all factors in determining the time requirement. Start the process with one small and one medium-sized table space and then try a larger table space. Use your results as a reference to estimate the time that is needed to process remaining table spaces.
  • Do not interrupt the process. If you try to stop it, for example, by halting the process that is completing the work, you must stop and restart the DB2 server. When the server is restarted, it will go into crash recovery mode, which takes several minutes, after which the process resumes.
  • For the best performance, rebalance a table space and then reduce the size for that table space. While the size for the first table space is being reduced, you can start rebalancing on the second table space to save some time.

Steps to redistribute or rebalance the TSM DB directories

Use the following steps to redistribute data and then reclaim space for each table space. 

1) Open the DB2 command line processor and issue the following command:
db2 connect to tsmdb1
2) List DB2 table spaces by issuing the following command. To display details about each table space, including the total size of a table space and how many bytes are used in each file system where the table space is located, include show detail.
db2 list tablespaces show detail
You only need to redistribute data on Database Managed Space (DMS) table spaces. The following example output shows where the table space type is identified:
Tablespaces for Current Database---------------------------------------------- Tablespace ID            = 0 Name                     = SYSCATSPACE Type                     = Database managed space  <---DMS table space. Contents                 = All permanent data. Regular table space. State                    = 0x0000 Detailed explanation: Normal
Also Read: Use this DB2 command to extend TSM DB space when the TSM server is down or unable to startup

3) Use the list that you obtained in Step 2 to identify each DMS table space. For each DMS table space, issue the following command to start redistribution of data for the first DMS space:
db2 alter tablespace tablespace_name rebalance 
For example : db2 alter tablespace SYSCATSPACE rebalance

4) Monitor the data redistribution progress by issuing the following command:
db2list utilities show detail

If the rebalance process is running, the command output shows Type = REBALANCE, and also indicates how many extents are moved and how many remain to be moved. The following example output shows where these details are displayed
ID = 6219Type = REBALANCE                   <--- Data is being redistributed.Database Name = AX4Partition Number = 0Description = Tablespace ID: 37Start Time = 04/27/2009 21:37:37.932471State = ExecutingInvocation Type = UserThrottling:Priority = UnthrottledProgress Monitoring:Estimated Percentage Complete = 15Total Work = 22366 extents         <--- Total extents to be moved.Completed Work = 3318 extents      <--- Total extents moved.Start Time = 01/03/2017 21:37
The value in the Completed Work field should increase as the redistribution progresses. The db2diag log also records status about the process, including start and complete time and what percentage of the process is complete at a certain time.

5) After the redistribution process is completed, reduce the size for each table space. During and after the operation, table spaces have a much larger total size because directories are added. Issue the following command
db2 alter tablespace tablespace_name reduce max
For Example: db2 alter tablespace SYSCATSPACE reduce max

How to add extra space to TSM DB for immediate use ?

The Tivoli Storage Manager server can use all of the space that is available to the drives or file systems where the database directories are located. To ensure that database space is always available, monitor the space in use by the server and the file systems where the directories are located. Use the QUERY DBSPACE command to display the number of free pages in the table space and the free space that is available to the database. If the number of free pages is low and there is plenty of free space available, the database allocates more space. However, if free space in drives or file systems is low, it might not be possible to expand the database and you need to add more directories or filesystems to increase the TSM DB space. You can increase the Tivoli Storage Manager database size upto 4 TB only. 

If you want to increase space for the database, you can create new directories and add them by two methods. 
  • By using EXTEND DBSPACE command when the server is online
  • By using DSMSERV EXTEND DBSPACE utility when the server is offline
By default these commands will allow the data to be redistributed across the new database directories and storage space in the old directories is reclaimed. This action makes the new directories available for use immediately and parallel I/O performance is improved. You should make sure that sufficient disk space is available for the operation and the new directories are empty. The process of redistributing data and reclaiming space uses considerable resources. 


However, if you do not want to redistribute data at the same time that you add directories, you can set the RECLAIMSTORAGE parameter in the EXTEND DBSPACE command to No. You can perform the tasks to redistribute data and reclaim space after the database size is increased, but the steps must be done manually.

Steps to add extra space to TSM DB when the server is up and running

To add the extra space to TSM DB when the server is online, use EXTEND DBSPACE command. The redistribution process as part of an operation to extend database space uses considerable system resources, so ensure that you plan for the process when the server is not handling a heavy workload. 
extend dbspace

Do not interrupt the redistribution process. If you try to stop it, for example, by halting the process that is completing the work, you must stop and restart the DB2 server. When the server is restarted, it will go into crash recovery mode, which takes several minutes, after which the redistribution process resumes.

1) Create one or more directories for the database on separate drives or file systems. How you spread the database directories across available disk storage has a strong effect on performance. Make all directories that are used for the database the same size to ensure parallelism. For most disk systems, performance is best if one database directory is on one LUN, which has one logical volume. Aim for a ratio of one database directory, array, or LUN for each inventory expiration process.

2) Make sure that there is no heavy background processes are running such as reclamation and expiration etc.

3) Then issue the EXTEND DBSPACE command to add the directory or directories to the database. The directories must be accessible to the user ID of the database manager. By default, data is redistributed across all database directories and space is reclaimed. For example
extend dbspace /tsmdb07/tsmdb08
To increase the size of the database without redistributing data and reclaiming space, issue the following command:
extend dbspace /tsmdb07,/tsmdb08 reclaim=no

The time that is needed to complete redistribution of data and reclaiming of space is variable, depending on the size of your database. Make sure that you plan adequately.

Also Read: How to increase or decrease TSM DB, active log and archive log size ?

4) You can Halt and restart the server or you might not need to restart the server to fully use the new directories. If the existing database directories are nearly full when a new directory is added, the server might encounter an out of space condition reported in the db2diag.log. This condition should be corrected by halting and restarting the server.

Steps to add extra space to TSM DB when the server is offline

Use DSMSERV EXTEND DBSPACE utility to add the extra space to the TSM DB when the server is offline. However, this utility performs the same function as the EXTEND DBSPACE command. 
dsmserv extend dbspace

1) Create one or more directories for the database on separate drives or file systems by following best practices (discussed above) to get optimal performance. The directories must be accessible to the user ID of the database manager. 

2) Go to the TSM server configuration files directory (/opt/tivoli/tsm/server/bin) and issue the DSMSERV EXTEND DBSPACE command to increase the TSM DB size. You can also specify whether data is redistributed across newly created database directories and space is reclaimed from the old storage paths when you add space to the database. This parameter is optional and the default value is Yes. But it is recommended to leave it as default to get optimal DB performance. For example on AIX and Windows
dsmserv extend dbspace /tsm_db/stg1 
dsmserv extend dbspace D: 

3) Restart the TSM server and check the DB size by issuing QUERY DBSPACE command.

How to increase TSM DB backup speed ?

We often notice that TSM DB backup runs very slowly even when TSM server is in idle state or having few background processes. There is a chance that this slow DB backup is due to the Transmission Control Protocol (TCP) loopback problems. To overcome this problem, now you can use shared memory setting to reduce processor load and improve throughput to increase the DB backup performance.

How to update the TSM server to use SHARED MEMORY for DB backup

We can manually configure a Tivoli Storage Manager server or use the instance configuration wizard, to use shared memory with DB2 backups. This option is only supported from TSM V7.1.0, configure the TSM server instance with shared memory to resolve slow database backup problems which can occur because of Transmission Control Protocol (TCP) loopback problems.

Also Read: TSM Storage Pool Concepts (V7 Revised)

In the following 2 step procedure, we must update the database backup node configuration for TSM server to enable shared memory. To update the server configuration files, we need to halt the TSM server. Make sure to cancel all the background processes and cancel the client sessions if any before halting the TSM server.

Next, we need to edit the below configuration files to update the above settings to enable SHARED MEMORY for DB backup operations. In AIX  modify  /usr/tivoli/tsm/client/api/bin64/dsm.sys file, In
HP-UX, Linux & Oracle Solaris  modify /opt/tivoli/tsm/client/api/bin64/dsm.sys and in Windows operating system modify d:\tsmserver1\tsmdbmgr.opt files with the below steps.

Also Read: How to increase TSM restore performance

Step 1 - Ensure that the server options file, dsmserv.opt, contains the following lines:
COMMMethod SHAREdmem
SHMPort         1510

Step 2- On UNIX and LINUX systems, modify the stanza for the database backup node in the client API system options file, dsm.sys.

Remove the following lines from the stanza:
COMMMethod TCPip
TCPServeraddress 127.0.0.1
TCPPort 1500 

Add the following lines to the stanza:
COMMMethod SHAREdmem
SHMPort 1510

On windows operating systems,  modify the stanza for the database backup node in the client API system options file, tsmdbmgr.opt.

Remove the following lines from the tsmdbmgr.opt file:
COMMMethod TCPip
TCPServeraddress 127.0.0.1
TCPPort 1500 

Add the following lines to the tsmdbmgr.opt file:
COMMMethod SHAREdmem
SHMPort 1510

Core Java Study guide and interview questions - Part 1

This is the 1st part of the 500+ Core JAVA interview questions and answers. These questions can also be used as a quick study guide for preparing for an interview or for any Core JAVA concepts revision. These questions cover all the topics in core JAVA from basics to advanced concepts.

1. What is a JVM?
JVM is Java Virtual Machine which is a run time environment for the compiled java class files.

2. Does Java support multiple inheritance?
Java doesn't support multiple inheritance.

3. What is the most important feature of Java?
Java is a platform independent language.

4. What is difference between Path and Classpath?
Path and Classpath are operating system level environment variales. Path is used define where the system can find the executables(.exe) files and classpath is used to specify the location .class files.

5. What are instance variables?
Instance variables are those which are defined at the class level. Instance variables need not be initialized before using them as they are automatically initialized to their default values.

6. What is a pointer and does Java support pointers?
Pointer is a reference handle to a memory location. Improper handling of pointers leads to memory leaks and reliability issues hence Java doesn't support the usage of pointers.

7. What is the return type of the main() method?
Main() method doesn't return anything hence declared void.

8. What are local variables?
Local variables are those which are declared within a block of code like methods. Local variables should be initialized before accessing them.

9. Is Java a pure object oriented language?
Java uses primitive data types and hence is not a pure object oriented language.

10. Is JVM platform independent?
JVM's are not platform independent. JVM's are platform specific run time implementation provided by the vendor.
Java Standard Edition

11. Can a main() method be overloaded?
Yes. You can have any number of main() methods with different method signature and implementation in the class.

12. What is the base class of all classes?
java.lang.Object

13. What do you mean by platform independence?
Platform independence means that we can write and compile the java code in one platform (eg Windows) and can execute the class in any other supported platform eg (Linux,Solaris,etc).

14. Are arrays primitive data types?
In Java, Arrays are objects.

15. What is the difference between a JDK and a JVM?
JDK is Java Development Kit which is for development purpose and it includes execution environment also. But JVM is purely a run time environment and hence you will not be able to compile your source files using a JVM.

16. Does the order of public and static declaration matter in main() method?
No. It doesn't matter but void should always come before main().

17. What is the impact of declaring a method as final?
A method declared as final can't be overridden. A sub-class can't have the same method signature with a different implementation.

18. Can a class be declared as protected?
A class can't be declared as protected. only methods can be declared as protected.

19. How to define a constant variable in Java?
The variable should be declared as static and final. So only one copy of the variable exists for all instances of the class and the value can't be changed also.
static final int PI = 2.14; is an example for constant.

20. Which package is imported by default?
java.lang package is imported by default even without a package declaration.

21. What is the argument of main() method?
main() method accepts an array of String object as argument.

22. Can a source file contain more than one class declaration?
Yes a single source file can contain any number of Class declarations but only one of the class can be declared as public.

23. What is the access scope of a protected method?
A protected method can be accessed by the classes within the same package or by the sub classes of the class in any package.

24. Can a class declared as private be accessed outside it's package?
Not possible.

25. Why is the main() method declared static?
main() method is called by the JVM even before the instantiation of the class hence it is declared as static.

26. What is the purpose of declaring a variable as final?
A final variable's value can't be changed. final variables should be initialized before using them.

27. What is a package?
Package is a collection of related classes and interfaces. package declaration should be first statement in a java class.

28. Can a main() method be declared final?
Yes. Any inheriting class will not be able to have it's own default main() method.

29. I don't want my class to be inherited by any other class. What should i do?
You should declared your class as final. But you can't define your class as final, if it is an abstract class. A class declared as final can't be extended by any other class.

30. Should a main() method be compulsorily declared in all java classes?
No not required. main() method should be defined only if the source class is a java application.

31. When will you define a method as static?
When a method needs to be accessed even before the creation of the object of the class then we should declare the method as static.

32. What is the importance of static variable?
static variables are class level variables where all objects of the class refer to the same variable. If one object changes the value then the change gets reflected in all the objects.

33. How is final different from finally and finalize()?
final is a modifier which can be applied to a class or a method or a variable. final class can't be inherited,final method can't be overridden and final variable can't be changed.

finally is an exception handling code section which gets executed whether an exception is raised or not by the try block code segment.
finalize() is a method of Object class which will be executed by the JVM just before garbage collecting object to give a final chance for resource releasing activity.

34. I want to print "Hello" even before main() is executed. How will you achieve that?
Print the statement inside a static block of code. Static blocks get executed when the class gets loaded into the memory and even before the creation of an object. Hence it will be executed before the main() method. And it will be executed only once.

35. Can we declare a static variable inside a method?
Static variables are class level variables and they can't be declared inside a method. If declared, the class will not compile.

36. Can a abstract class be defined without any abstract methods?
Yes it's possible. This is basically to avoid instance creation of the class.

37. Can you give few examples of final classes defined in Java API?
java.lang.String, java.lang.Math are final classes.

38. Can a abstract class be declared final?
Not possible. An abstract class without being inherited is of no use and hence will result in compile time error.

39. Can you create an object of an abstract class?
Not possible. Abstract classes can't be instantiated.

40. What are the restriction imposed on a static method or a static block of code?
A static method should not refer to instance variables without creating an instance and cannot use "this" operator to refer the instance.

41. Can a Class extend more than one Class?
Not possible. A Class can extend only one class but can implement any number of Interfaces.

42. Class C implements Interface I containing method m1 and m2 declarations. Class C has provided implementation for method m2. Can i create an object of Class C?
No not possible. Class C should provide implementation for all the methods in the Interface I. Since Class C didn't provide implementation for m1 method, it has to be declared as abstract. Abstract classes can't be instantiated.

43. Can a class be defined inside an Interface?
Yes it's possible.

44. Can an Interface extend another Interface?
Yes an Interface can inherit another Interface, for that matter an Interface can extend more than one Interface.

45. What is use of a abstract variable?
Variables can't be declared as abstract. only classes and methods can be declared as abstract.

46. Can an Interface be defined inside a class?
Yes it's possible.

47. What is an Abstract Class and what is it's purpose?
A Class which doesn't provide complete implementation is defined as an abstract class. Abstract classes enforce abstraction.

48. Can an Interface be final?
Not possible. Doing so so will result in compilation error.

49. Can an Interface implement another Interface?
Interfaces doesn't provide implementation hence a interface cannot implement another interface.

50. Why does Java not support operator overloading?
Operator overloading makes the code very difficult to read and maintain. To maintain code simplicity, Java doesn't support operator overloading.

Next : 1000+ core JAVA quick study material & interview Questions and Answers

SQL frequently used commands and interview question and answers

Below are the continuous post for the frequently used commands and interview questions for SQL basic and intermediate level SQL Developers, Administrators, and Testers. These questions are about SELECT commands and some of the important and frequently  used commands.


21) What are the privileges that can be granted on a table by a user to others?
Insert, update, delete, select, references, index, execute, alter, all.

22) Which function is used to find the largest integer less than or equal to a specific value?
FLOOR.

23) Which is the subset of SQL commands used to manipulate Oracle Database structures, including tables?
Data Definition Language (DDL).

24) What is the use of DESC in SQL?
DESC has two purposes. It is used to describe a schema as well as to retrieve rows from table in descending order.
Explanation : The query SELECT * FROM EMP ORDER BY ENAME DESC will display the output sorted on ENAME in descending order.

25) What command is used to create a table by copying the structure of another table?
CREATE TABLE .. AS SELECTcommand
Explanation: To copy only the structure, the WHERE clause of the SELECT command should contain a FALSE statement as in the following.
CREATE TABLE NEWTABLE AS SELECT * FROM EXISTINGTABLE WHERE 1=2;
If the WHERE condition is true, then all the rows or rows satisfying the condition will be copied to the new table.

26) What is the output of the following query SELECT TRUNC(1234.5678,-2) FROM DUAL;?
1200.

27) What are the wildcards used for pattern matching.?
_ for single character substitution
% for multi-character substitution.

28) What's an SQL injection?
SQL Injection is when form data contains an SQL escape sequence and injects a new SQL query to be run.

29) What is difference between TRUNCATE & DELETE?
TRUNCATE commits after deleting entire table i.e., cannot be rolled back. Database triggers do not fire on TRUNCATE.
DELETE allows the filtered deletion. Deleted records can be rolled back or committed. Database triggers fire on DELETE.

30) What is a join? Explain the different types of joins?
Join is a query, which retrieves related columns or rows from multiple tables.
Self Join - Joining the table with itself.
Equi Join - Joining two tables by equating two common columns. Non-Equi Join - Joining two tables by equating two common columns.
Outer Join - Joining two tables in such a way that query can also retrieve rows that do not have corresponding join value in the other table.

31) What is the sub-query?
Sub-query is a query whose return values are used in filtering conditions of the main query.

32) What is correlated sub-query?
Correlated sub-query is a sub-query, which has reference to the main query.

33) Explain CONNECT BY PRIOR?
Retrieves rows in hierarchical order eg. select empno, ename from emp where.

34) Difference between SUBSTR and INSTR?
INSTR (String1, String2 (n, (m)),
INSTR returns the position of the m-th occurrence of the string 2 in string1. The search begins from nth position of string1.
SUBSTR (String1 n, m)
SUBSTR returns a character string of size m in string1, starting from n-th position of string1.

35) Explain UNION, MINUS, UNION ALL and INTERSECT?
INTERSECT - returns all distinct rows selected by both queries.
MINUS - returns all distinct rows selected by the first query but not by the second.
UNION - returns all distinct rows selected by either query
UNION ALL - returns all rows selected by either query,including all duplicates.

36) What is ROWID?
ROWID is a pseudo column attached to each row of a table. It is 18 characters long, blockno, rownumber are the components of ROWID.

37) What is the fastest way of accessing a row in a table?
Using ROWID. CONSTRAINTS

38) What is an integrity constraint?
Integrity constraint is a rule that restricts values to a column in a table.

39) What is referential integrity constraint?
Maintaining data integrity through a set of rules that restrict the values of one or more columns of the tables based on the values of primary key or unique key of the referenced table.

SQL interview questions on Commands, Operations, editing database and tables

Below are the interview questions for SQL basic and intermediate level SQL Developers, Administrators, and Testers. These questions are about Commands, Operations, creating and editing database and tables. 

1) How to fetch data from a Database Table?
Using SELECT Statement, we can fetch data from a Database Table
Syntax:
SELECT column1, column2, columnN FROM table_name;
Or
SELECT * FROM table_name;

2) Explain about IN Operator?
The IN operator implements comparison to a list of values, that is, it tests whether a value matches any value in a list of values. IN comparisons have the following general format:
value-1 [NOT] IN ( value-2 [, value-3] ... )
This comparison tests if value-1 matches value-2 or matches value-3, and so on. It is equivalent to the following logical predicate:
value-1 = value-2 [ OR value-1 = value-3 ] ...

3) Explain about FROM Clause in SQL?
The FROM clause always follows the SELECT clause. It lists the tables accessed by the query. For example,
SELECT * FROM s
When the From List contains multiple tables, commas separate the table names. For example,
SELECT sp.*, city FROM sp, s WHERE sp.sno=s.sno
When the From List has multiple tables, they must be joined together.

4) What is the parameter substitution symbol used with INSERT INTO command?
The parameter substitution symbol used with INSERT INTO command is &.

5) What are the various uses of database triggers?
Database triggers can be used to enforce business rules, to maintain derived values and perform value-based auditing.

6) What is a event handler in sql?
An event handler is a routine that is written to respond to a particular event.

7) What are two methods of retrieving SQL?
The two methods of retrieving SQL are
1-Select
2-using Cursor.


8) What is a synonym? How is it used?
A synonym is used to reference a table or view by another name. The other name can then be written in the application code pointing to test tables in the development stage and to production entities when the code is migrated. The synonym is linked to the AUTHID that created it.

9) What is referential integrity?
Referential integrity refers to the consistency that must be maintained between primary and foreign keys, i.e. every foreign key value must have a corresponding primary key value.

10) Explain the EXPLAIN statement?
The explain statement provides information about the optimizer's choice of access path of the SQL.

11) How is the SUBSTR keyword used in SQL?
SUBSTR is used for string manipulation with column name, first position and string length used as arguments. E.g. SUBSTR (NAME, 1 3) refers to the first three characters in the column NAME.

12) What is the difference between group by and order by?
Group by controls the presentation of the rows, order by controls the presentation of the columns for the results of the SELECT statement.

13) What is a subselect? Is it different from a nested select?
A subselect is a select which works in conjunction with another select. A nested select is a kind of subselect where the inner select passes to the where criteria for the outer select.

14) What is the use of CASCADE CONSTRAINTS?
When this clause is used with the DROP command, a parent table can be dropped even when a child table exists.

15) How do you prevent output from coming to the screen?
The SET option TERMOUT controls output to the screen. Setting TERMOUT OFF turns off screen output. This option can be shortened to TERM.

16) Can Primary key is a Foreign Key on the same table?
Yes, Primary key is a Foreign Key on the same table.

17) How do you execute a host operating system command from within SQL?
By use of the exclamation point “!” (in UNIX and some other OS) or the HOST (HO) command.

18) What is a Cartesian product?
A Cartesian product is the result of an unrestricted join of two or more tables. The result set of a three table Cartesian product will have x * y * z number of rows where x, y, z correspond to the number of rows in each table involved in the join.

19) How can variables be passed to a SQL routine?
By use of the & symbol. For passing in variables the numbers 1-8 can be used (&1, &2,...,&8) to pass the values after the command into the SQL PLUS session. To be prompted for a specific variable, place the ampersanded variable in the code itself: “select * from dba_tables where owner=&owner_name;” . Use of double ampersands tells SQLPLUS to resubstitute the value for each subsequent use of the variable, a single ampersand will cause a reprompt for the value unless an ACCEPT statement is used to get the value from the user.

20) What command is used to get back the privileges offered by the GRANT command?
Revoke command is used to get back the privileges offered by the GRANT command.

21) What are the advantages of procedures?
Advantages of procedures:
  • Loaded once and used many times.
  • Performance better coz all SQL statements are sent in one go from the application to the database.
  • Security (no object privileges are given directly).
  • Invoker's rights possible.
  • Data integrity, productivity.

22) What is Parsing?
Parsing checks syntax, checks privileges, and allocating Private SQL Area.

23) What is Cursor?
Name or handle to a private SQL area where Oracle parses and fetches query results.

24) Is SQL supports Conditional and Loop Statements?
No Basically SQL is a Command based Language, not a procedural  language, but it has Operators and built-in Functions.