Geeks - Tip of the Day

krishnaindia2007 · #**121** (**permalink**) 07-04-2008

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

krishnaindia2007 · #**122** (**permalink**) 07-04-2008

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;

krishnaindia2007 · #**123** (**permalink**) 07-07-2008

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

krishnaindia2007 · #**124** (**permalink**) 07-07-2008

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'

krishnaindia2007 · #**125** (**permalink**) 07-09-2008

Triggers cannot be created on SYS-owned objects.

krishnaindia2007 · #**126** (**permalink**) 07-26-2008

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.

krishnaindia2007 · #**127** (**permalink**) 08-09-2008

Roles predefined by Oracle are
CONNECT
RESOURCE
DBA
EXP_FULL_DATABASE
IMP_FULL_DATABASE

krishnaindia2007 · #**128** (**permalink**) 08-19-2008

To know default tablespace, temporaray tablespace associated with an user

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

krishnaindia2007 · #**129** (**permalink**) 08-20-2008

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.

krishnaindia2007 · #**130** (**permalink**) 08-21-2008

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.

krishnaindia2007 · #**131** (**permalink**) 08-23-2008

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

krishnaindia2007 · #**132** (**permalink**) 08-25-2008

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)

krishnaindia2007 · #**133** (**permalink**) 08-28-2008

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

krishnaindia2007 · #**134** (**permalink**) 08-29-2008

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;

krishnaindia2007 · #**135** (**permalink**) 09-02-2008

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

krishnaindia2007 · #**136** (**permalink**) 09-03-2008

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.

krishnaindia2007 · #**137** (**permalink**) 09-03-2008

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.

krishnaindia2007 · #**138** (**permalink**) 09-04-2008

UNLIMITED TABLESPACE cannot be granted to a role.

krishnaindia2007 · #**139** (**permalink**) 09-05-2008

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

krishnaindia2007 · #**140** (**permalink**) 09-09-2008

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Hi Geeks! Another Freak here to join you :)	itcoolgirl	Get Together	1	12-12-2007 03:17 AM
Hey Geeks!!!	maverick_dude	Get Together	1	12-12-2007 03:12 AM
Geeks talk has 5000+ threads.	debasisdas	Site News & Announcements	0	07-31-2007 10:37 AM
GeekInterview Pager - communicate better with Geeks community	admin	Site News & Announcements	1	03-01-2007 06:32 AM
Welcome to Geeks Talk	admin	Site News & Announcements	0	05-13-2006 02:10 PM

Geeks - Tip of the Day

Geeks - Tip of the Day

Similar Threads