Today I asked our SysAdmin about a password of one of the servers, but unfortunately he wasn’t able to remember (and didn’t noted it in somewhere). So I decided to crack it using the following steps. It’s documented and is written in the Linux manual

http://www.redhat.com/docs/manuals/enterprise/RHEL-4-Manual/step-guide/s1-q-and-a-root-passwd.html

Today, I’ve seen a nice example at www.psoug.org on how to prevent users from using additional tools to connect to production database. This is the AFTER LOGON trigger create ON DATABASE as follows:

CONNECT / AS SYSDBA;
 
CREATE OR REPLACE TRIGGER block_tools_from_prod
  AFTER LOGON ON DATABASE
DECLARE
  v_prog sys.v_$session.program%TYPE;
BEGIN
  SELECT program INTO v_prog
    FROM sys.v_$session
  WHERE  audsid = USERENV('SESSIONID')
    AND  audsid != 0  -- Don't Check SYS Connections
    AND  ROWNUM = 1;  -- Parallel processes will have the same AUDSID's
 
  IF UPPER(v_prog) LIKE '%TOAD%' OR UPPER(v_prog) LIKE '%T.O.A.D%' OR -- Toad
     UPPER(v_prog) LIKE '%SQLNAV%' OR     -- SQL Navigator
     UPPER(v_prog) LIKE '%PLSQLDEV%' OR -- PLSQL Developer
     UPPER(v_prog) LIKE '%BUSOBJ%' OR   -- Business Objects
     UPPER(v_prog) LIKE '%EXCEL%'       -- MS-Excel plug-in
  THEN
     RAISE_APPLICATION_ERROR(-20000, 'Development tools are not allowed here.');
  END IF;
END;
/
SHOW ERRORS

http://www.psoug.org/snippet/Block_TOAD_and_other_tools_516.htm

To hold data only in your session or transaction life, you can create temporary table which data will be deleted after completion of session or transaction. There’re some specific features of temporary tables. In this post I show some of them on examples

* Each session has its own data

SQL> CREATE USER user1 IDENTIFIED BY test;

User created.

SQL> CREATE USER user2 IDENTIFIED BY test;

User created.

SQL> grant connect, resource, create public synonym to user1;

Grant succeeded.

SQL> conn user1/test
Connected.
SQL> CREATE GLOBAL TEMPORARY TABLE tbl_temp (id number) on commit delete rows;

Table created.

SQL> grant select, insert, update, delete on tbl_temp to user2;

Grant succeeded.

SQL> CREATE PUBLIC SYNONYM tbl_temp FOR tbl_temp;

Synonym created.

SQL> INSERT INTO tbl_temp VALUES(1);

1 row created.

SQL> SELECT * FROM tbl_temp;

        ID
———-
         1

 

From the second session connect with USER2 and insert some data to temporary table

SQL> conn user2/test
Connected.
SQL> select * from tbl_temp;

no rows selected

SQL> insert into tbl_temp values(333);

1 row created.

SQL> select * from tbl_temp;

        ID
———-
       333

SQL>

 

Now switch to the first session and select data from tbl_temp

SQL> SELECT * FROM tbl_temp;

        ID
———-
         1

SQL>
As you see, each session has its own data for the same temporary table

 

* If temporary table is truncated from one session, only data of that session is truncated. Meanwhile, other users will be able to see their data in their own temporary table
Now, TRUNCATE tbl_temp table from the first session and query it from another session:

SQL> truncate table tbl_temp;

Table truncated.

SQL> SELECT * FROM tbl_temp;

no rows selected

SQL>

//Query the table from the second session:

SQL> select * from tbl_temp;

        ID
———-
       333

 

* If you issue command COMMIT, you’ll lose all your data because you’ve create the table using ON COMMIT DELETE ROWS functionality

SQL> commit;

Commit complete.

SQL> select * from tbl_temp;

no rows selected

SQL>

* Indexes created on temporary tables are also temporary. Let’s see it from the following example

SQL> show user
USER is “SYS”
SQL> CREATE GLOBAL TEMPORARY TABLE tbl_temp (id number) on commit preserve rows;
Table created.

SQL> create index idx_tbl_temp on tbl_temp (id);

Index created.

SQL> select num_rows, temporary from dba_indexes where index_name=’IDX_TBL_TEMP’;

  NUM_ROWS T
———- –
         0 Y
        
SQL>

SQL> BEGIN
  2  FOR i IN 1..100 LOOP
  3  INSERT INTO tbl_temp VALUES(i);
  4  END LOOP;
  5  END;
  6  /

PL/SQL procedure successfully completed.

SQL> select count(1) from tbl_temp;

  COUNT(1)
———-
       100

SQL> analyze index idx_tbl_temp compute statistics;

Index analyzed.

SQL> select num_rows from dba_indexes where index_name=’IDX_TBL_TEMP’;

  NUM_ROWS
———-
       100
       
SQL> disc
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Pr
oduction
With the Partitioning, OLAP and Data Mining options
SQL> conn / as sysdba
Connected.
SQL> select num_rows from dba_indexes where index_name=’IDX_TBL_TEMP’;

  NUM_ROWS
———-
       100
        
SQL> select count(1) from tbl_temp;

  COUNT(1)
———-
         0

SQL> analyze index idx_tbl_temp compute statistics;

Index analyzed.

SQL> select num_rows, temporary from dba_indexes where index_name=’IDX_TBL_TEMP’;

  NUM_ROWS T
———- –
         0 Y

Exclusive Interview with Syed Jaffar Hussain

 

He’s Senior Oracle DBA with over 16 years of IT experience which also includes above 8 years of production Oracle DBA exposure. Presently the only Oracle 10g Certified Master in Saudi Arabia with Oracle RAC Expert, Oracle 8i, 9i and 10g OCP DBA and Oracle ACE credentials.Past one year and so, he has been largely working with Oracle RAC environemnts that inlcudes successful 8 node production, 6 node development RAC setups on HPUX Itaninum with EMC DMX SAN Storage. He’s one of the regular contributor at Oracle forums, oracle freelist group and occasionally writes up at his blog. He’s currently engaged in writing an Oracle 11g RAC book

 

Exclusive Interview with Tim Hall

Tim Hall is an Oracle Certified Professional (OCP) DBA (7, 8, 8i, 9i, 10g, 11g), Oracle Certified Associate (OCA) PL/SQL Developer, Oracle ACE Director and was chosen as Oracle ACE of the Year 2006 by Oracle Magazine Editors Choice Awards. He has been involved in DBA, design and development work with Oracle databases since graduating from university in 1994.

 He has gained a wide knowledge of the Oracle software stack and has worked as a consultant for several multi-national companies on projects ranging from real-time control systems to OLTP web applications.

 Since 2000 I’ve published over 350 articles on his website (www.oracle-base.com) covering a wide range of Oracle features. He has also published two books, “Oracle Job Scheduling” and “Oracle PL/SQL Tuning“.

 

Exclusive Interview with Laurent Schneider

Laurent Schneider is one of the most respected authors in Oracle technology, with many years of experience as a Systems Engineer and Database Engineer.

Laurent achieved the highest level of DBA certification in 2004, being the first Oracle Certified Master in Switzerland and recipient of the prestigious Oracle Technology Network ACE trophy.  
Laurent has over a decade of expertise in development – specializing in data warehousing and database modeling as well as database administration. He currently works for a successful Swiss bank as application architect/developer.

In his spare time, Laurent enjoys studying Chinese Chess strategy and has won the Swiss Championship. He lives on the sunny side of the Uetliberg with his wife Bertille, his daughter Dora and his son Loïc.

He is the author of the book “Advanced Oracle SQL Programming“

Tim Hall is an Oracle Certified Professional (OCP) DBA (7, 8, 8i, 9i, 10g, 11g), Oracle Certified Associate (OCA) PL/SQL Developer, Oracle ACE Director and was chosen as Oracle ACE of the Year 2006 by Oracle Magazine Editors Choice Awards. He has been involved in DBA, design and development work with Oracle databases since graduating from university in 1994.

 He has gained a wide knowledge of the Oracle software stack and has worked as a consultant for several multi-national companies on projects ranging from real-time control systems to OLTP web applications.

 Since 2000 I’ve published over 350 articles on his website (www.oracle-base.com) covering a wide range of Oracle features. He has also published two books, “Oracle Job Scheduling” and “Oracle PL/SQL Tuning“.

 Here is our interview:

 

 

Could u please provide answer to the following questions as follows:

1.      Brief information about yourself and your family

 I’m a DBA/Developer and have been working with Oracle for approximately 15 years.

 

 2. Your education

I did a PhD in Molecular Biology before getting a job in IT.

 

3. Your experience with Oracle. When you started first? Has it been interest of your side or just a coincidence?

I got my first job with Oracle completely by accident. I had no knowledge of the company or their products. I didn’t even really know what an RDBMS was.

 

4. What was the motive behind to prefer Oracle? Who you have been influenced by?

Oracle is what I’ve always done. I’ve worked with other database products (SQL Server, mySQL, DB2, MS Access), but nowhere near as much as I’ve worked with Oracle, so I guess I’m a little biased.

 

5. What would your preference of profession if not Oracle?

If I knew I would do it.

 

6. What motivates you in your job?

I’m interested in technology. I like to play around with things. Sometimes the day job can get a little mundane, but the technology always keeps you on your toes.

 

7. Do you give lectures on Oracle?

Over the last years I’ve been speaking at conferences all over the world and I’ve been doing some 2 day PL/SQL workshops in Europe for Oracle University as part of their Celebrity Series.

 

8. Have you authored any book in Oracle?

I’ve written 2 books (Oracle Job Scheduling & Oracle PL/SQL Tuning).

  

9. Do you manage with your time as to read books on Oracle? What is the name of the book that you read recently?

I mostly read when I’m traveling. Over the last couple of years I’ve read the Vampire Chronicles series by Anne Rice and the Discworld series by Terry Prachett.

 

 10.  What do you think on OTN forums?

 I don’t visit the OTN forums too often. I have my own forum so that keeps me fairly busy.

 

11.  Do you refer to the documentation? And how often does it happen?

I refer to the documentation constantly. When I have a question, it’s the first place I go. When I answer a question, I check my answer against the documentation before i post it.

 

12.  What is the biggest mistake you have ever made during your DBA career?

The longer you work with a product, the easier it is to start thinking you know it inside out. Then one day someone who knows nothing about it asks you a questions and you are lost for an answer. The biggest mistake is when you start thinking you know it all. I think we all go through a period where we think we are bulletproof. With age and experience you get  a bit more humble and sit back and smile at the new kids on the block who are making all the waves.

 

13.  What was your greatest achivement as an Oracle DBA?

 My greatest achievement is the fact I am still a techie after 15 years. Many really good technical people lose their desire and move out of the technical roles into management. Being a techie takes a lot of work. It would be far easier to move into a less technical role, but that’s not what I’m about.

 

14.  What is your priority to manage the challenges you face?

 Whenever I try to plan anything, life throws a spanner in the works. So now I never plan anything big in my life. I let things happen to me. The challenge is to keep an open mind so you don’t talk yourself out of opportunities.

 

15. How would you describe the essence of your success? According to your definition of success, how successful have you been so far?

 It’s impossible to be objective about your own achievements. I don’t feel successful or unsuccessful. I am just me, doing what I do.

 

16. What are your best skills which make you differ from others?

I guess I am quite driven when I’m interested in something. I tend to throw myself into things. I’m not obsessed by money or possessions. I just want to be involved.

 

17. What’s your major weakness?

I find it hard to motivate myself to do things I’m not interested in. It takes a lot of mental effort to motivate me to cut the grass in my garden.

 

18.  Have you ever lost your spirit? If so, what has been the reason and how have you overcome it?

If I’m interested in something I never question my motives. If something goes wrong it is almost fun as it leads me down a new path.
19.  What is the next success you would like to attain and your efforts to this end?

I have no plans or desires. I just want to carry on doing what I want to do, when I want to do it.

 

20.  How do you balance your daily life with your career?

I have never considered trying to balance anything. Things just fall into place if you let them.

 

21.  Please describe your one day summary of activities?

At the moment I am traveling a lot, so there is never a normal day. I might be teaching, speaking at a conference, traveling, or just at home playing with the computer.

When I’m doing regular work, my day is like every other DBA/Developer in the world.
 

22.  How many hours do you work and sleep in a day?

Every day is different. I’m on the computer from the time I get up until the time I go to bed, unless something forces me not to be.

 

23.  Where and how do you spend your daily, weekly and annual holidays?

 When I’m not in front of the computer I visit my family and do some exercise (Swimming, Yoga, Karate, Gym)

 

24.  Do you think about Oracle during vacations?

If possible I’m online during holidays. I always check my mails and answer questions on my forum if I can.

 

25.  Do you have time or motivation to go in for any sports? If yes, which kind of sport do you go in for?

I like swimming, Yoga, Karate and I go to the gym most weeks. Sometimes it’s only possible to solve problems if you can distance yourself from them. Exercise distracts your conscious mind so your subconscious can do the work for you.

 

26.  What’s the best funny event you have ever faced in your life?

I find almost everything amusing. My typical response to most situations is to make fun of them and laugh.

 

27.  Do you keep pets? If so, the name please? 

I like animals in their natural surroundings. I don’t really agree with the concept of pets. I don’t have a problem with others having pets. Each to their own. 

 

28.  What’s your favorite meal and non-alcoholic drink?

If it’s bad for me I like to eat it. If it’s good for me, I probably don’t like it.

 

29.  What foreign languages do you know?

I only speak English. Most countries where English is the first language are pretty rubbish at learning other languages. It’s the curse of speaking such a widely available language.

 

30.  What’s your average typing speed?

I have no idea. I type quite fast, but if I think about it I will slow down to a crawl.

 

31.  Have you ever get involved in politics?

I have the unenviable trait of always seeing both side to the story. I would make a terrible politician.

 

32.  What are your hobbies? 

Playing with technology.

 

33. How do you spend your free time?

Playing with technology.

 

34.  What’s your biggest ambition?

I never think about that sort of thing. I just do stuff.

 

35.  What would be your advice to the beginners in Oracle?

Read the manuals. Always check everything people say to you. Things change and most people aren’t too good at adapting to change.

 

36.  Would you like your children to follow in your footsteps or take a different path in life?

I have no children. If I ever have any I want them to do what they want to do, not what other people expect them to do.

 

37.  Do you have any followers of you?

I guess lots of people know who I am because of my website and the Oracle ACE Director program, but I’m just a regular guy. Followers implies there is something to follow.

 

38.  What is your vision on the future of Oracle?

 
Oracle will continue to expand and every year my skills will be a smaller and smaller subset of the total product set. That’s the way it should be.

 

39.  Could you please take a photo in your office near to your desktop?

No way. My home office is a mess. I’m overly neat at work, but at home I’m a slob.

Here, I’ve collected all my “Step by Step Guides” which help you to understand basic concepts very easily

Install VMware – Step by step

Installing CentOS on VMware – Step by Step 

Step by Step Installing Oracle Database 10g Release 2 on Linux (CentOS) and AUTOMATE the installation using Linux Shell Script

Create Database Manually – Step by Step instruction

This question is one of the most famous questions in OTN forum So I decided to show the way to do it

SQL> select name from v$datafile;

NAME
——————————————————————————–

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST_TBS\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST_TBS\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST_TBS\SYSAUX01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST_TBS\USERS01.DBF

SQL> alter tablespace users offline;

Tablespace altered.

SQL> host
Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.
C:\Documents and Settings\Administrator>move c:\oracle\product\10.2.0\oradata\test_tbs\USERS01.DBF c:\oradata

C:\Documents and Settings\Administrator>exit

SQL> alter database rename file ‘C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST_TBS\USERS
01.DBF’ to ‘c:\oradata\users01.dbf’;

Database altered.

SQL> alter tablespace users online;

Tablespace altered.

SQL> select name from v$datafile;

NAME
——————————————————————————–

C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST_TBS\SYSTEM01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST_TBS\UNDOTBS01.DBF
C:\ORACLE\PRODUCT\10.2.0\ORADATA\TEST_TBS\SYSAUX01.DBF
C:\ORADATA\USERS01.DBF

SQL>

If you want to know EVERYTHING related to DUAL table in Oracle, please refer to Tom Kyte’s discussion

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1562813956388