Synonyms

Tested on an Oracle 11 database.

You can use a synonym to create an alias for an object e.g. a table or view:

 

SQL> create synonym a for b

  2  /

 

Synonym created.

 

SQL> create public synonym x for y

  2  /

 

Synonym created.

SQL>
  

If the object does not exist, you do not get an error until you try to access it:

SQL> desc a

ERROR:

ORA-04043: object "SYSTEM"."B" does not exist

 

SQL> desc x

ERROR:

ORA-04043: object "SYSTEM"."Y" does not exist

 

SQL>

And you can even create a synonym for an object which belongs to a user who does not exist:

SQL> create synonym c for d.e;

 

Synonym created.

 

SQL> select count(*) from dba_users

  2  where username = 'D';

 

  COUNT(*)

----------

         0

 

1 row selected.

 

SQL> desc c

ERROR:

ORA-04043: object "D"."E" does not exist

 

SQL>

DBMS_RANDOM (Part 1)

This is Oracle's built-in random number generator. It produces random numbers >= 0 and < 1. You can run a rudimentary test on it by calling it several times and checking the spread of the numbers returned:
          

SQL> create table dbms_random_test_tab
  2  (col1 number)
  3  /

Table created.

SQL> begin
  2  for a in 1..1000 loop
  3  insert into dbms_random_test_tab
  4  select floor(10*sys.dbms_random.value)+1 from dual;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select col1, count(*)
  2  from dbms_random_test_tab
  3  group by col1
  4  order by col1
  5  /

      COL1   COUNT(*)
---------- ----------
         1        103
         2         99
         3         80
         4        120
         5        104
         6         94
         7         87
         8        109
         9        111
        10         93

10 rows selected.

SQL>

If you run it again, a different set of numbers is returned:

SQL> delete dbms_random_test_tab
  2  /

1000 rows deleted.

SQL> begin
  2  for a in 1..1000 loop
  3  insert into dbms_random_test_tab
  4  select floor(10*sys.dbms_random.value)+1 from dual;
  5  end loop;
  6  end;
  7  /

PL/SQL procedure successfully completed.

SQL> select col1, count(*)
  2  from dbms_random_test_tab
  3  group by col1
  4  order by col1
  5  /

      COL1   COUNT(*)
---------- ----------
         1         85
         2         90
         3        105
         4        113
         5         96
         6        103
         7        114
         8         99
         9        115
        10         80

10 rows selected.

SQL>

log_archive_dest

You can see where your database’s archived redo goes as follows:

SQL> l

  1  select value from v$parameter

  2* where name = 'log_archive_dest'

SQL> /

VALUE

------------------------------

/old_dir/arch

SQL>

And you can see them on disk too:

SOLARIS > /old_dir > ls -1 *arc

arch8190_TEST11_1_705064488.arc

arch8191_TEST11_1_705064488.arc

arch8192_TEST11_1_705064488.arc

arch8193_TEST11_1_705064488.arc

arch8194_TEST11_1_705064488.arc

SOLARIS > /old_dir >

You can send archived redo to a new location like this:

SQL> alter system set log_archive_dest =

  2  '/new_dir/arch'

  3  scope = both;

System altered.

SQL>

When you have done this you should always test that the archiving is working:

SQL> alter system switch logfile;

System altered.

SQL> /

System altered.

SQL> /

System altered.

SQL>

And check in the operating system that the files are arriving:

SOLARIS > /new_dir > ls -1 *arc

arch8195_TEST11_1_705064488.arc

arch8196_TEST11_1_705064488.arc

arch8197_TEST11_1_705064488.arc

SOLARIS > /new_dir >

Drop User Privilege

I ran this example on Oracle 9 to demonstrate the DROP USER privilege. First I created two users, A and X:

SQL> conn / as sysdba

Connected.

SQL> grant create session to a identified by b;

 

Grant succeeded.

 

SQL> grant create session to x identified by y;

 

Grant succeeded.

SQL>

... then I logged on as user A and tried to drop user X. This failed as user A did not have the DROP USER privilege:

SQL> conn a/b

Connected.

SQL> drop user x;

drop user x

*

ERROR at line 1:

ORA-01031: insufficient privileges

SQL>

I granted the DROP USER privilege to user A and this allowed it to drop user X:

SQL> conn / as sysdba

Connected.

SQL> grant drop user to a;

 

Grant succeeded.

 

SQL> conn a/b

Connected.

SQL> drop user x;

 

User dropped.

 

SQL>