Monday, January 28, 2013

Oracle Questions and commands


Oracle, SQL, Interview Questions, Question Paper,
Oracle Questions

      "h What is SQL*Plus and where does it come from?
      "h How does one use the SQL*Plus utility?
      "h What commands can be executed from SQL*Plus?
      "h What are the basic SQL*Plus commands?
      "h What is AFIEDT.BUF?
      "h How does one restore session state in SQL*Plus?
      "h What is the difference between @ and @@?
      "h What is the difference between & and &&?
      "h What is the difference between ! and HOST?
      "h What is the difference between ? and HELP?
      "h How does one enable the SQL*Plus HELP facility?
      "h How can one disable SQL*Plus prompting?
      "h How can one trap errors in SQL*Plus?
      "h How does one trace SQL statement execution?
      "h How can one prevent SQL*Plus connection warning messages?
      "h How can uses be prevented from executing devious commands?
      "h How can one disable SQL*Plus formatting?
      "h Can one send operating system parameters to SQL*Plus?
      "h Can one copy tables with LONG columns from one database to another?
      "h Where can one get more info about SQL*Plus?
      What is SQL*Plus and where does it come from?
      SQL*Plus is a command line SQL and PL/SQL language interface and reporting
      tool that ships with the Oracle Database Client and Server. It can be used
      interactively or driven from scripts. SQL*Plus is frequently used by DBAs
      and Developers to interact with the Oracle database.
      SQL*Plus's predecessor was called UFI (User Friendly Interface). UFI was
      included in the first releases of Oracle, its interface was extremely
      primitive and anything but user friendly.
      How does one use the SQL*Plus utility?
      Start using SQL*Plus by executing the "sqlplus" command-line utility.
      Valid options are:
      userid/password@db -- Connection details
      /nolog -- Do not login to Oracle. You will need to do it yourself.
      -s or -silent -- start sqlplus in silent mode. Not recommended for
      beginners!
      @myscript -- Start executing script called "myscript"
      Look at this simple example:
      sqlplus /nolog
      SQL> connect scott/tiger
      SQL> select * from tab;
      SQL> disconnect
      SQL> exit
      What commands can be executed from SQL*Plus?
      One can enter three kinds of commands from the SQL*Plus command prompt:

      1. SQL*Plus commands - SQL*Plus commands are used to set options for 
      SQL*Plus, format reports, edit files, edit the command buffer, and so on.
      SQL*Plus commands do not interact with the database. These commands do not
      have to be terminated with a semicolon (;), as is the case with SQL
      commands. The rest of this page is dedicated to SQL*Plus commands, eg.
      SHOW USER
      2. SQL commands - for more information see the Oracle SQL FAQ. Eg: 
      SELECT * FROM TAB;

      3. PL/SQL blocks - for more information see the Oracle PLSQL FAQ. Eg:
      BEGIN
      DBMS_OUTPUT.PUT_LINE('Hello World!');
      END;
      /
      What are the basic SQL*Plus commands?
      The following SQL*Plus commands are available:
      ACCEPT Get input from the user
      DEFINE Declare a variable (short: DEF)
      DESCRIBE Lists the attributes of tables and other objects (short: DESC)
      EDIT Places you in an editor so you can edit a SQL command (short: ED)
      EXIT or QUIT Disconnect from the database and terminate SQL*Plus
      GET Retrieves a SQL file and places it into the SQL buffer
      HOST Issue an operating system command (short: !)
      LIST Displays the last command executed/ command in the SQL buffer (short:
      L)
      PROMPT Display a text string on the screen. Eg prompt Hello World!!!
      RUN List and Run the command stored in the SQL buffer (short: /)
      SAVE Saves command in the SQL buffer to a file. Eg "save x" will create a
      script file called x.sql
      SET Modify the SQL*Plus environment eg. SET PAGESIZE 23
      SHOW Show environment settings (short: SHO). Eg SHOW ALL, SHO PAGESIZE
      etc.
      SPOOL Send output to a file. Eg "spool x" will save STDOUT to a file
      called x.lst
      START Run a SQL script file (short: @)
      How does one restore session state in SQL*Plus?
      Look at the following example (Oracle8): 
      SQL> STORE SET filename REPLACE
      SQL> (do whatever you like)
      SQL> @filename
      What is AFIEDT.BUF?
      AFIEDT.BUF is the SQL*Plus default edit save file. When you issue the
      command "ed" or "edit" without arguments, the last SQL or PL/SQL command
      will be saved to a file called AFIEDT.BUF and opened in the default
      editor.
      In the prehistoric days when SQL*Plus was called UFI, the file name was
      "ufiedt.buf", short for UFI editing buffer. When new features were added
      to UFI, it was the initially named Advanced UFI and the filename was
      changed to "aufiedt.buf" and then to "afiedt.buf". They presumably needed
      to keep the name short for compatibility with some of the odd operating
      systems that Oracle supported in those days.
      The name "Advanced UFI" was never used officially, as the name was changed
      to SQL*Plus before this version was released.
      You can overwrite the default edit save file name like this:
      SET EDITFILE "afiedt.buf"
      What is the difference between @ and @@?
      The @ (at symbol) is equivalent to the START command and is used to run
      SQL*Plus command scripts.
      A single @ symbol runs the script in your current directory, or one
      specified with a full or relative path, or one that is found in you
      SQLPATH or ORACLE_PATH.
      @@ will start a sqlplus script that is in the same directory as the script
      that called it (relative to the directory of the current script). This is
      normally used for nested command files.

      What is the difference between & and &&?
      "&" is used to create a temporary substitution variable and will prompt
      you for a value every time it is referenced.
      "&&" is used to create a permanent substitution variable as with the
      DEFINE command and the OLD_VALUE or NEW_VALUE clauses of a COLUMN
      statement. Once you have entered a value it will use that value every time
      the variable is referenced.
      Eg: SQL> SELECT * FROM TAB WHERE TNAME LIKE '%&TABLE_NAME.%';
      What is the difference between ! and HOST?
      Both "!" and "HOST" will execute operating system commands as child
      processes of SQL*Plus. The difference is that "HOST" will perform variable
      substitution (& and && symbols), whereas "!" will not. (Note: use "$"
      under MVS, VMS, and Windows environments, not "!")
      What is the difference between ? and HELP?
      There is no difference. Both "?" and HELP will read the SYSTEM.HELP table
      (if available) and shows help text on the screen.
      To use the help facility, type HELP followed by the command you need to
      learn more about. For example, to get help on the SELECT statement, type:
      HELP SELECT 
      How does one enable the SQL*Plus HELP facility?
      To enable HELP for SQl*Plus, run the supplied SQL and Loader scritps to
      create the Help table and to populate it. Look at this Unix example:
      cd $ORACLE_HOME/sqlplus/admin/help
      sqlplus system/manager @helptbl
      sqlplus system/manager @helpindx
      sqlldr system/manager control=plushelp.ctl
      sqlldr system/manager control=sqlhelp.ctl
      sqlldr system/manager control=plshelp.ctl
      If the HELP command is not supported on your operating system, you can
      access the help table with a simple script like this:
      HELP.SQL:
      select info
      from system.help
      where upper(topic)=upper('&1')
      /
      How can one disable SQL*Plus prompting?
      If you run a script that contains "&" symbols SQL*Plus thinks that you
      want to prompt the user for a value. To turn this off:
      SET ESCAPE ON
      SET ESCAPE "\"
      SELECT 'You \& me' FROM DUAL;
      or
      SET DEFINE ?
      SELECT 'You & me' FROM DUAL;
      Note: You can disable substitution variable prompting altogether by
      issuing the SET DEFINE OFF commmand.

      How can one trap errors in SQL*Plus?
      Use the "WHENEVER OSERROR ..." to trap operating system errors and the
      "WHENEVER SQLERROR ..." command to trap SQL and PL/SQL errors. Eg:
      SQL> WHENEVER OSERROR EXIT 9
      SQL> WHENEVER SQLERROR EXIT SQL.SQLCODE

      How does one trace SQL statement execution?
      1. Run the PLUSTRCE.SQL script from the SYS database user. This script is
      located the in $ORACLE_HOME/sqlplus/admin.
      2. Create a PLAN_TABLE using the UTLXPLAN.SQL script. This script is in
      $ORACLE_HOME/rdbms/admin.
      3. Use the "SET AUTOTRACE ON" command to trace SQL execution. This will
      print the result of your query, an explain plan and high level trace
      information. Look at this example:
      SQL> set autotrace on
      SQL> select * from dual;

      D
      -
      X
      Execution Plan
      ----------------------------------------------------------
      0 SELECT STATEMENT Optimizer=CHOOSE
      1 0 TABLE ACCESS (FULL) OF 'DUAL'

      Statistics
      ----------------------------------------------------------
      0 recursive calls
      2 db block gets
      1 consistent gets
      0 physical reads
      0 redo size
      181 bytes sent via SQL*Net to client
      256 bytes received via SQL*Net from client
      3 SQL*Net roundtrips to/from client
      0 sorts (memory)
      0 sorts (disk)
      1 rows processed
      How can one prevent SQL*Plus connection warning messages?
      When I go to SQl*Plus, I get the following errors:
      Error accessing PRODUCT_USER_PROFILE
      Warning: Product user profile information not loaded!
      You may need to run PUPBLD.SQL as SYSTEM
      This messages will stop appearing when you create the PRODUCT_USER_PROFILE
      table in the SYSTEM schema. This is performed by the PUPBLD.SQL script.
      Go to the $ORACLE_HOME/sqlplus/admin directory, connect as SYSTEM and run
      @PUPBLD from the sqlprompt.
      How can users be prevented from executing devious commands?
      Yes, command authorization is verified against the
      SYSTEM.PRODUCT_USER_PROFILE table. This table is created by the PUPBLD.SQL
      script. Note that this table is not used when someone signs on as user
      SYSTEM.
      Eg. to disable all users whose names starts with OPS$ from executing the
      CONNECT command:
      SQL> INSERT INTO SYSTEM.PRODUCT_USER_PROFILE VALUES ('SQL*Plus', 'OPS$%',
      'CONNECT', NULL, NULL, 'DISABLED', NULL, NULL);
      How can one disable SQL*Plus formatting?
      Issue the following SET commands to disable all SQL*Plus formatting:
      SET ECHO OFF
      SET NEWPAGE 0
      SET SPACE 0
      SET PAGESIZE 0
      SET FEEDBACK OFF
      SET HEADING OFF
      SET TRIMSPOOL ON
      These settings can also be entered on one line, eg.:
      SET ECHO OFF NEWPAGE 0 SPACE 0 PAGESIZE 0 FEED OFF HEAD OFF TRIMSPOOL ON
      Can one send operating system parameters to SQL*Plus?
      One can pass operating system variables to sqlplus using this syntax:
      sqlplus username/password @cmdfile.sql var1 var2 var3
      Parameter var1 will be mapped to SQL*Plus variable &1, var2 to &2, etc.
      Look at this example:
      sqlplus scott/tiger @x.sql '"test parameter"' dual
      Where x.sql consists of:
      select '&1' from &2;
      exit 5;
 
http://educationexampoint.blogspot.in

0 comments:

Post a Comment