Page 7 of 10 FirstFirst ... 56789 ... LastLast
Results 121 to 140 of 194

Thread: Geeks - Tip of the Day

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

    Re: Geeks - Tip of the Day

    How dates are stored internally?

    Each date value in oracle takes seven bytes of space.
    The first two bytes represent the century and year respectively.
    The 3rd and 4th bytes represent the month and the day of that month respectively.
    The last three bytes represent the hour, minute, and second.

    Date value do not have any format . When you query date value it will display the value in default date format that is specified by the initialization parameter NLS_DATE_FORMAT.

    To following statement will display your current session date settings
    Code:
    SQL> SELECT value 
         2   FROM   nls_session_parameters
         3   WHERE  parameter  = 'NLS_DATE_FORMAT';
    
    VALUE
    -------------------
    DD-MON-YY
    
    SQL> SELECT ename, hiredate 
      2  FROM   emp
      3  WHERE  ename = 'SMITH';
    
    ENAME      HIREDATE
    ---------- ---------
    SMITH      17-DEC-80  -- Output is in NLS_DATE_FORMAT
    SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YY';

    Session altered.

    Code:
    SQL>  SELECT ename, hiredate 
      2   FROM   emp
      3   WHERE  ename = 'SMITH';
    
    ENAME      HIREDATE
    ---------- --------
    SMITH      17/12/80


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

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

    Re: Geeks - Tip of the Day

    How to prevent oracle from using an index?

    We can do it in four different ways.
    1. adding an expression to index
    Code:
     
     sql> select x 
        2 from test_data 
        3 where x+0 = 256;
    2. using function on indexed column

    Code:
     
    sql> select x 
       2 from test_data 
       3 where to_number(x) = 250;
    3. specifying no_index hint
    Code:
     
    sql> select /*+ no_index(test_data) */ x 
       2 from test_data 
       3 where to_number(x) = 250;
    4. specifying the full hint
    Code:
     
     sql> select /*+ full(test_data) */ x 
        2 from test_data 
        3 where to_number(x) = 250;


    Last edited by krishnaindia2007; 07-04-2008 at 10:13 PM.

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

    Re: Geeks - Tip of the Day

    DDL Statements – Auto Commit

    Auto commit or implicit commit will takes place before and after every DDL statement. DDL will always commit, even if it is unsuccessful (Run time errors). But it will not commit a transaction in case of syntax errors or semantic errors..

    Example
    Code:
    SQL> CREATE TABLE TEST_DATA (X NUMBER);
    Table created.
    
    SQL> INSERT INTO TEST_DATA VALUES(1);
    1 row created.
    
    SQL> INSERT INTO TEST_DATA VALUES(2);
    1 row created.
    
    
    SQL> CREATE TABLE TEST_NEW (XX NUMBEER);
    CREATE TABLE TEST_NEW (XX NUMBEER)
                              *
    ERROR at line 1:
    ORA-00902: invalid datatype
    -- It is runtime  error. Auto commit will takes place.
    
    SQL> ROLLBACK;
    Rollback complete.
    
    SQL> SELECT * FROM TEST_DATA;
    
            X
    ---------
            1
            2
    Example for syntax error

    Code:
    SQL> TRUNCATE TABLE TEST_DATA;
    Table truncated.
    
    SQL> INSERT INTO TEST_DATA VALUES(1);
    1 row created.
    SQL> INSERT INTO TEST_DATA VALUES(2);
    1 row created.
    
    SQL> CREATE TABLETEST_NEW (XX NUMBER);
    CREATE TABLETEST_NEW (XX NUMBER)
           *
    ERROR at line 1:
    ORA-00901: invalid CREATE command
    
    -- It is syntax error. It will not commit transaction.
    SQL> ROLLBACK;
    Rollback complete.
    
    SQL> SELECT * FROM TEST_DATA;
    no rows selected



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

    Re: Geeks - Tip of the Day

    Composite Index - When It Will Be Used?

    An index that is created on multiple columns of a table is called composite index.

    If you create a composite index on three columns for example a,b,c of a table

    composite index will be used for abc, ab, a combination of columns in where clause .

    Composite index will not be used for bc,b and c combinations of columns in where clause.

    Here is the example
    Code:
     
    sql> create table test_data ( a number, b number, c number); 
    table created. 
    --- to insert 500 records 
    sql> declare 
    2 i number; 
    3 begin 
    4 for i in 1..500 
    5 loop 
    6 insert into test_data values(i,i,i); 
    7 end loop; 
    8 commit; 
    9 end; 
    10 / 
    pl/sql procedure successfully completed. 
    Sql> create index test_data_idx on test_data(a,b,c); 
    index created.
    Ex1:- ab combination will use index
    Code:
     
    sql> set autotrace on explain; 
    sql> select * from test_data 
    2 where a = 100 
    3 and b = 100; 
    execution plan 
    ---------------------------------------------------------- 
    0 select statement optimizer=choose 
    1 0 index (range scan) of 'test_data_idx' (non-unique)
    Ex2:- bc combination in where clause will not use index
    Code:
     
    sql> select * from test_data 
    2 where b = 100 
    3 and c = 100; 
    execution plan 
    ---------------------------------------------------------- 
    0 select statement optimizer=choose 
    1 0 table access (full) of 'test_data'


    Last edited by krishnaindia2007; 07-07-2008 at 10:35 PM.

  5. #125
    Expert Member
    Join Date
    Sep 2007
    Answers
    697

    Re: Geeks - Tip of the Day

    Triggers cannot be created on SYS-owned objects.


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

    Re: Geeks - Tip of the Day

    A hint is code embedded into a SQL statement suggesting to Oracle how it should be processed.The comment containing hints must follow the SELECT, UPDATE, INSERT, MERGE, or DELETE keyword and only one hint is allowed in a statement block.


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

    Re: Geeks - Tip of the Day

    Roles predefined by Oracle are
    CONNECT
    RESOURCE
    DBA
    EXP_FULL_DATABASE
    IMP_FULL_DATABASE


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

    Re: Geeks - Tip of the Day

    To know default tablespace, temporaray tablespace associated with an user

    select
    temporary_tablespace,
    default_tablespace
    from dba_users
    where username='username';


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

    Re: Geeks - Tip of the Day

    What is a schema?

    A schema is collection of database objects of user.
    It has same name as that of user.
    It is owned by database user.
    Objects of same schema can be in different tablespaces and a tablespace can contain objects from different schema.


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

    Re: Geeks - Tip of the Day

    What is a datablock?

    ORACLE database’s data is stored in data blocks.
    One data block corresponds to a specific number of bytes of physical database space on disk.
    A data block size is specified for each ORACLE database when the database is created.
    The standard block size is specified by the initialization parameter DB_BLOCK_SIZE and is specified in INIT.ORA file .
    ORACLE database’s data block can be of size 2K, 4K, 8k, 16K or 32K.
    The default block size for oracle 10g is 8K.
    A small block size is useful if you’re working with small rows and you’re doing a lot of index lookups.
    Larger block sizes are useful in report applications when you’re doing large table scans.


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

    Re: Geeks - Tip of the Day

    PRODUCT_COMPONENT_VERSION VIEW

    This view shows you at a glance the version numbers of all the major components of your Oracle database.

    Code:
    SQL> SELECT * FROM PRODUCT_COMPONENT_VERSION;
    PRODUCT                                  VERSION              STATUS
    ---------------------------------------- -------------------- --------------
    NLSRTL                                   9.2.0.6.0            Production
    Oracle9i Enterprise Edition              9.2.0.6.0            Production
    PL/SQL                                   9.2.0.6.0            Production
    TNS for 32-bit Windows:                  9.2.0.6.0            Production



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

    Re: Geeks - Tip of the Day

    DIFFERENCE BETWEEN STATIC AND DYNAMIC VIEWS

    Static views don’t change continuously while the database operates. All views starts with DBA_, ALL_ and USER_ are static views.
    Ex:- DBA_TABLES, USER_OBJECTS, ALL_SOURCE


    Dynamic view are updated continuously while the database is running.
    All dynamic views have prefix V_$.
    Oracle creates synonyms for these dynamic views whose prefix is simply V$.
    EX:- V$CONTROLFILE, V$DATABASE, V$INSTANCE
    ( These three are synonyms for V_$DATABASE, V_$INSTANCE, V_$CONTROLFILE)


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

    Re: Geeks - Tip of the Day

    If the backup is inconsistent, you need to use archived redo logs to make the database consistent.


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

    Re: Geeks - Tip of the Day

    When you drop a user, all the objects in the user's schema will be dropped permanently without using recycle bin. if you aren’t sure whether you will need a user’s objects later, but you want to deny access, simply leave the user and the user’s schema intact, use the following command:

    SQL> REVOKE CREATE SESSION FROM username;


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

    Re: Geeks - Tip of the Day

    You can restrict user login attempts using profiles.

    Profiles are a named set of password and resource limits

    To create a new profile first connect as sysdba and create a new profile as folllows.

    Create profile password_test
    limit
    failed_login_attempts 3;

    If the user fails to log in within three attempts, the user’s accounts will be locked for a specified period or until the dba manually unlocks them.

    Create a new user and assign the profile password_test to new user.

    Create user test_user identified by test_user

    grant create session to test_user

    alter user test_user profile password_test

    You can check the status of test_user account using the following statement.

    Code:
     
    sql> select username, account_status 
       2 from dba_users 
       3 where username = 'test_user'; 
    
    username account_status 
    ------------------------------ ----------------- 
    ssapllive open
    Give wrong password for three times and observe the account status.

    Code:
     
    sql> select username, account_status 
        2 from dba_users 
        3 where username = 'test_user'; 
    
    username account_status 
    ------------------------------ ------------------------ 
    test_user locked(timed)
    Use the following command to unlock the account.

    Alter user test_user account unlock

    Last edited by krishnaindia2007; 09-01-2008 at 11:46 PM.

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

    Re: Geeks - Tip of the Day

    A privilege is the right to execute a particular type of SQL statement or to access a database object owned by another user.

    Oracle privileges are of two types

    1. System privileges :- Enable user to perform particular task in the database

    2.Object privileges:- Enable user to access or manipulate particular object in the database

    Use grant command to add a privilege to user and revoke command to delete a privilege from user.

    There are no cascading effects when a system privilege is revoked. But revoking object privilege has cascading effect.

    i.e. If user X grant a system privilege with admin option to user y
    User y grant a system privilege to user z.
    User x revoke the system privilege from user y
    still user z has the privilege that user y granted him

    Where as in case of object privileges
    If x user revoke the privilege from user y then the user z also losses the privilges granted by y.


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

    Re: Geeks - Tip of the Day

    You can’t grant object privileges on some schema objects, such as clusters, indexes, triggers, and database links. You control the use of these types of objects with a system privilege instead.


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

    Re: Geeks - Tip of the Day

    UNLIMITED TABLESPACE cannot be granted to a role.


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

    Re: Geeks - Tip of the Day

    There is no CREATE INDEX privilege. CREATE TABLE includes the CREATE INDEX and the ANALYZE commands


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

    Re: Geeks - Tip of the Day

    A control file records the physical structure of the database.
    It contains
    Name and location of datafiles and redo log files
    Data base name
    Time stamp of database creation.

    Use the following query to find the size of control file
    select sum(record_size) from v$controlfile_record_section


Page 7 of 10 FirstFirst ... 56789 ... 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