Page 8 of 10 FirstFirst ... 678910 LastLast
Results 141 to 160 of 194

Thread: Geeks - Tip of the Day

  1. #141
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Query to find locked objects

    select oracle_username, os_user_name,locked_mode,object_name,object_type
    from v$locked_object a, dba_objects b
    where a.object_id=b.object_id


  2. #142
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    How to enable archive log mode?

    To enable archive log mode

    1. Shut down the database

    2. Take full offline backup

    3.create pfile from spfile if you are spfile , Using the following command sql>create pfile='c:\pfile.ora' from spfile

    4. Then edit pfile or init.ora file and specify the archive log destination
    log_archive_start='true'
    log_archive_dest='f:\archive'
    log_archive_max_processes=4

    To specify multiple destinations
    log_archive_dest_1=’f:\archive’
    log_archive_dest_2=’e:\archive’

    5.creat spfile from pfile='c:\pfile.ora'

    6.create archive destination folders

    7.startup mount

    8.alter database archivelog;

    9.archive log list;

    10.alter database open;

    11.sho parameter log;

    12. Alter database backup controlfile to trace; - this is for safety purpose

    13. Sql>shutdown immediate;

    14.sql>startup.

    It is recommended that you take a full backup after you brought the database in archive log mode.

    Last edited by krishnaindia2007; 09-18-2008 at 10:36 PM.

  3. #143
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    To Disable Archive Log Mode

    1. Shut Down the database

    2. Comment Archive log destinations in parameter file
    # LOG_ARCHIVE_DEST_1=”location=/u02/ica/arc1”
    # LOG_ARCHIVE_DEST_2=”location=/u02/ica/arc2”

    3. Startup Mount

    4. ALTER DATABASE NOARCHIVELOG;

    5. Shutdown the database and take full offline backup.


  4. #144
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    How to export data based on a query?

    EXP file=f1.dmp log=f1.log tables=y query=\"WHERE DEPTNO=10\"


  5. #145
    Expert Member
    Join Date
    Apr 2007
    Answers
    500

    Re: Geeks - Tip of the Day

    Creation of linux user and Creation of Oracle directory

    1. Connect as root user

    For adding user to linux ,assigning dba group to username
    2 useradd -d /home/username -m -g dba -s /bin/sh username

    3 passwd username

    enter password for username
    reenter password for username

    4 connect as username in linux

    5 cd $HOME

    For read,write privileges to username

    6 chmod 777 username

    7 Add env variable to .bash_profile

    8 Run .bash_profile

    9 check connection of sqlplus

    For Creation of oracle directory

    1. connect as username

    2 mkdir LOGFILES

    3 chmod 755 LOGFILES

    4 chmod g+w LOGFILES

    5 for creating directory

    sqlplus > conn system/xxxxxxxx

    create or replace directory LOGDIR as '/home/username/LOGFILES'
    grant read,write on directory LOGDIR to scott

    sqlplus > conn scott/tiger

    test the simple script


    DECLARE
    output_file utl_file.file_type;
    begin
    output_file := utl_file.fopen ('LOGDIR','x.txt', 'w');
    utl_file.put_line (output_file, 'XXXXXXXXXXXX');
    utl_file.fclose(output_file);
    Exception
    when others then null;
    end;


    6 Test the x.txt file in LOGFILES.

    Last edited by susarlasireesha; 10-01-2008 at 02:42 AM.
    Sireesha

  6. #146
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    sequence related queries

    last number selected from sequence

    Code:
     sql> select sequence_name, last_number 
                 2 from user_sequences 
                 3 where sequence_name='smp_vdu_principals_sequence'; 
    sequence_name                    last_number
     ------------------------------ ----------- 
    smp_vdu_principals_sequence  21
    next number from sequence

    Code:
     
    sql> select sequence_name, (last_number + increment_by) next_value 
        2 from user_sequences 
        3 where sequence_name ='smp_vdp_region_id_seq'; 
    
    sequence_name           next_value 
    ------------------------------ ---------- 
    smp_vdp_region_id_seq    22



  7. #147
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Database Shutdown -I

    You must be connected as SYSDBA or SYSOPER to shutdown a database.

    Shutdown Modes

    I.Shutdown Normal
    1.No New Connections are allowed
    2.The server waits for all users to disconnect before completing the shutdown.
    3.Database and redo buffers are written to disk.
    4.The SGA memory allocation is released and background processes are terminated.
    5.The database is closed and dismounted.

    II.Shutdown Transactional
    1.No new connections are allowed
    2.No connected client can start a new transaction
    3.Clients are disconnected as soon as the current transaction ends.
    4.Showdown proceeds when all the transactions are finished.


  8. #148
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Database Shutdown -II


    III.Shutdown Immediate
    1.No New connections are allowed.
    2.Connected clients are disconnected and Sql Statements in process are not completed.
    3.Oracle rollback active transactions.
    4.Oracle closes and dismounts the database

    IV.Shutdown abort
    1.Current Sql statements are immediately terminated.
    2.Users are disconnected.
    3.Database and redo buffers are not written to disk.
    4.Uncommitted transactions are not rolled back.
    5.The instance is terminated without closing files.
    6.The database is not closed and dismounted.
    7.Database recovery by SMON must occur on the next startup.


  9. #149
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Starting Up a Database

    Database can be started up in various stages.

    I. Nomount
    This stage is used to
    1.Create a database
    2.To recreate a control file.

    It includes the following tasks
    1.Read spfile.ora or init.ora file.
    2.Allocate SGA
    3.Startup the background processes.
    4.Open a log file named ALERT_SID.LOG.

    II.Mount
    This stage is used to
    1.Rename database files
    2.Enable / Disable redo log archiving options
    3.To perform full database recovery.

    It includes the following tasks
    1.Database is mounted but not open.
    2.It is assosiated with instance.
    3.Locates and opens the control file specified in parameter file
    4.Reads control file to obtain the names and status of datafiles and redo log files.
    5.But it does not check to verify the existence of datafiles and redo log files.

    III.Open
    1.Any valid user can connect to database
    2.It opens datafiles and redo log files.


  10. #150
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Startup Command options

    I.STARTUP FORCE
    1.It aborts the current instance
    2.Starts a new normal instance

    II.STARTUP RESTRICT
    1.It restrict connection to users with the RESTRICTED SESSION privilege.
    2.Used to perform database maintenance activities.

    III.STARTUP RECOVER
    It begins media recovery when starts


  11. #151
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Dynamic performanc views - I

    Dynamic performance views are identified by the prefix v_$.
    Public synonyms for these views have the prefix v$.
    The v$ views provide a continually updated look at internal statistics

    Instance related views
    I.v$sga
    It contains summary information on system global area.

    Code:
     
    sql> select * 
        2 from v$sga; 
    Name                      Value 
    -------------------- ---------- 
    fixed size                 454584 
    variable size             218103808 
    database buffers       25165824 
    redo buffers              667648
    It is same as show sga in sql *plus with the exeption that it also show the total.

    Code:
     
    sql> show sga; 
    total system global area 244391864 bytes 
    fixed size                     454584 bytes 
    variable size                 218103808 bytes 
    database buffers           25165824 bytes 
    redo buffers                 667648 bytes


    Last edited by krishnaindia2007; 10-03-2008 at 11:48 PM.

  12. #152
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Dynamic performanc views - II

    II.v$sgastat
    It constains detailed information on the system global area or contains information on sga segment sizes
    Code:
     
    sql> select * 
        2 from v$sgastat; 
    pool          name             bytes 
    ----------- -------------------------- ---------- 
                     fixed_sga                          454584 
                     buffer_cache                     25165824 
                     log_buffer                         656384 
    shared pool errors                                9612 
    shared pool enqueue                            171860 
    shared pool kgk heap                            3756 
    shared pool kqr m po                            304168 
    shared pool kqr s po                             62212 
    .................... 
    Shared pool message pool freequeue       665792 
    shared pool ksxr pending messages que   841036 
    shared pool event statistics per sess     1913520 
    43 rows selected.
    Query to know freespace in sga
    Code:
     
    sql> select * from v$sgastat w
    here name = 'free memory'; 
    pool            name                      bytes 
    ----------- -------------------------- ---------- 
    shared pool free memory              151228400 
    large pool   free memory              8388608 
    java pool    free memory              33554432


    Last edited by krishnaindia2007; 10-03-2008 at 11:49 PM.

  13. #153
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Dynamic performanc views - III

    V$instance :- It displays information of the current instance.

    Code:
    SQL>  SELECT   instance_name,
      2                  version,
      3                  status,
      4                  instance_role
      5      FROM     v$instance;
    
    INSTANCE_NAME    VERSION           STATUS       INSTANCE_ROLE
    ---------------------------------------------------------------
    orcl            9.2.0.6.0          OPEN        PRIMARY_INSTANCE
    Instance_role can be used to determine if an instance is an active instance (primary instance) or a secondary instance (in a standby environment).

    Last edited by krishnaindia2007; 10-03-2008 at 11:53 PM.

  14. #154
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Dynamic performanc views - IV

    V$BGPROCESS :- This process describes the background processes.

    V$BH:- This is a parallel server view. This view gives the status and number of pings for every buffer in the SGA.


    V$BUFFER_POOL :- This view displays information about all buffer pools available for the instance.


  15. #155
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Control File Related Views

    V$CONTROL FILE :- This view lists the names of the control files.

    Code:
    SQL> SELECT name 
      2  FROM   v$controlfile;
    
    NAME
    -------------------------------------------
    E:\ORA9IDB\ORADATA\ORCL\CONTROL01.CTL
    E:\ORA9IDB\ORADATA\ORCL\CONTROL02.CTL
    E:\ORA9IDB\ORADATA\ORCL\CONTROL03.CTL
    V$CONTROLFILE_RECORD_SECTION :- This view displays information about the control file record sections.


  16. #156
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Choosing Between Having and Where

    Where puts condition on the table rows.
    Having puts condition on grouped results.

    The processing order is as follows.
    1. Select rows with WHERE
    2. Divide rows into sets with GROUP BY
    3. Calculate accurate values for each group.
    4. Eliminate unwanted group result rows with having.

    Any rows you can remove with where rather than having make your query more efficient.

    Ex:- Wrong Statement
    Code:
    SQL> SELECT job, count(*)
      2  FROM   emp
      3  GROUP BY job
      4  HAVING job<>'SALESMAN';
    
    JOB        COUNT(*)
    --------- ---------
    ANALYST           2
    CLERK             3
    MANAGER           4
    PRESIDENT         1
    Better Statement

    Code:
    SQL> SELECT job, count(*) 
      2  FROM   emp
      3  WHERE  job<>'SALESMAN'
      4  GROUP BY job;
    
    JOB        COUNT(*)
    --------- ---------
    ANALYST           2
    CLERK             3
    MANAGER           4
    PRESIDENT         1



  17. #157
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Indexes and Like operator

    you can take advantage of an index with like as long as you provide the first character in the pattern.

    Code:
     
    sql> select ename, job, sal 
       2 from emp 
       3 where ename like 'M&#37;'; 
    ename job sal 
    ---------- --------- --------- 
    martin salesman 1663.75 
    miller clerk 1730.3 
    
    execution plan 
    ---------------------------------------------------------- 
    0 select statement optimizer=choose 
    1 0 table access (by index rowid) of 'emp' 
    2 1  index (range scan) of 'ename_idx' (non-unique)
    Optimizer does not use index if you start pattern with wildcard.

    Code:
     
    sql> select ename, job, sal 
        2 from emp 
        3 where ename like '%M%'; 
    
    ename job sal 
    ---------- --------- --------- 
    martin salesman 1663.75 
    adams clerk 1464.1 
    james clerk 1264.45 
    miller clerk 1730.3 
    
    execution plan 
    --------------------------------------------- 
    0 select statement optimizer=choose 
    1 0 table access (full) of 'emp'


    Last edited by krishnaindia2007; 10-04-2008 at 04:45 AM.

  18. #158
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Indexes and Comparing columns in the same table

    Comparing the columns in the same table makes an index useless.

    Code:
     
    sql> select empno, ename, job, mgr 
       2 from emp 
       3 where empno = mgr; 
    
    empno ename job mgr 
    --------- ---------- --------- --------- 
    7944 krishna manager 7944 
    
    execution plan 
    ----------------------------------------------- 
    0 select statement optimizer=choose 
    1 0 table access (full) of 'emp' 
    It will consider index when empno column compared to a string constant.

    Code:
     
    sql> select empno, ename, job, mgr 
       2 from emp 
       3 where empno = 7944; 
    
    empno ename job mgr 
    --------- ---------- --------- --------- 
    7944 krishna manager 7944 
    
    execution plan 
    --------------------------------------------------------- 
    0 select statement optimizer=choose 
    1 0 table access (by index rowid) of 'emp' 
    2 1 index (unique scan) of 'empno_pk' (unique)


    Last edited by krishnaindia2007; 10-04-2008 at 04:56 AM.

  19. #159
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Indexes and between operator

    Between operator will consider indexes if there index on a column.
    Between is treated as a pair of comparison operators.
    Code:
     
    sql> select * 
       2 from emp 
       3 where empno between 7900 and 7999; 
    
    empno ename job mgr hiredate sal comm deptno 
    --------- ---------- --------- --------- --------- 
    7900 james clerk 7698 03-dec-81 1264.45 30 
    7902 ford analyst 7566 03-dec-81 3993 20 
    7934 miller clerk 7782 23-jan-82 1730.3 10 
    
    execution plan 
    ---------------------------------------------------------- 
    0 select statement optimizer=choose 
    1 0 table access (by index rowid) of 'emp' 
    2 1 index (range scan) of 'empno_pk' (unique)


    Last edited by krishnaindia2007; 10-04-2008 at 05:05 AM.

  20. #160
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    SPFILE and PFILE

    SPFILE stands for server parameter file.
    Initialisation parameter file init.ora is also called as pfile.

    These files hold setup parameters that defines attirbutes of instance.

    PFFILE is a text based file where are SPFILE is a binary file. We can't modify SPFILE using editors.

    The only way of modifying paramters in spfile is using ALTER SYSTE SET/RESET command.


Page 8 of 10 FirstFirst ... 678910 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
About us
Applying for a job can be a stressful and frustrating experience, especially for someone who has never done it before. Considering that you are competing for the position with a at least a dozen other applicants, it is imperative that you thoroughly prepare for the job interview, in order to stand a good chance of getting hired. That's where GeekInterview can help.
Interact