CREATE DATABASE LINK

There are two main ways which are easy to create database links between databases instances.

Format;

create database link [link_name] connect to [user_name] identified by [password]
using [database_connection_string / allias];


1-) If there is an description about database connection inside tnsnames.ora file about the database which will be connected, it is easy by just writing allias names of it in code.
For instance,

create database link test_hamdi to connect hamdi identified by "hamditest18"  using 'TESTDB';

In tnsnames.ora,connection string will be defined as written here;

TESTDB=
   (DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=10.1.1.111)
      (PORT=1532)
    )
    (CONNECT_DATA=
      (SID=TEST)
    )



2-)If there is not any description about database connection string inside tns.names.ora file,it is also possible to write directly appropriate string inside linking sql code just like below;

create database link test_hamdi to connect hamdi identified by "hamditest18" using '(DESCRIPTION=
    (ADDRESS=
      (PROTOCOL=TCP)
      (HOST=10.1.1.111)
      (PORT=1532)
    )
    (CONNECT_DATA=
      (SID=TEST)
    )
  )'


It is assumed that strings above are connection information for TESTDB database.


WARNING: Be sure that you can ping to each databases from tellnet which database link will be created

ROLLBACK TO SAVEPOINT Sample With Oracle9i,Oracle11g Databases Cases

Let's see the reaction of ROLLBACK TO SAVEPOINT a with a scenerio of two different databases below. It is explained in here with sql coding.

Sql Code:
>create table product
(pcode number(2),
pname varchar2(10));

>insert into product values(1,'pencil');

>insert into product values(2,'pencil');

>savepoint a;

>update product set pcode=10 where pcode=1;

>savepoint b;

>delete from product where pcode=2;

>commit;

>delete from product where pcode=10;


>rollback to savepoint a;


>select * from product;

Oracle 9i result;
This is different.

Oracle 11g result;

 No sql statement rollbacked.
 The rollback generates an error.

GRANT FUNCTION/PROCEDURE TO USER / PUBLIC

If you want to grant a user on a function/procedure, it can be used just like here;

grant execute on  [ function name ] / [ procedure name ] to [ user_name] ;

It is also possible to grant all users to execute function/procedure by this code below;

grant execute on [ function name ] / [ procedure name ] to public;

AS OF TIMESTAMP

This is a very useful sql code to retrieve older data which is deleted.

sys.sample_table is a custom table created by sys user.

select *

from sys.sample_table as of timestamp (systimestamp - interval '10' minute);

select *

from sys.sample_table as of timestamp (systimestamp - interval '10' day);

select *

from sys.sample_table as of timestamp (sysdate -1);     -- 1 day ago 

select *

from sys.sample_table as of timestamp (sysdate - 1/24);   --1 hour ago

select *

from sys.sample_table as of timestamp (sysdate -1/(24*60));     -- 1 minute ago

select *

from sys.sample_table as of timestamp (sysdate - 1/(24*60*60));  --1 second ago

It is also good to see result by testing with these sql below and how it works;

select sysdate , (sysdate + 1)  from dual;  -- 1 day later

select sysdate, (sysdate + 1/24) from dual; --1 hour later

select sysdate, (sysdate + 1/(24*60)) from dual; --1 minute later

select sysdate, (sysdate + 1/(24*60*60)) from dual; --1 second later

There are also two different errors prompted while using 

1-)There is one day difference between these two select statements. What causes these two different errors?

select * 

from sys.sample_table as of timestamp (systimestamp - interval '5' day);

ORA - 01466

select *

from from sys.sample_table as of timestamp (systimestamp - interval '6' day);

ORA - 08180

HOW TO MOVE A TABLE FROM ONE TABLESPACE TO ANOTHER

Let's explain this with an scenerio sample.Here are the steps,

1-)Create a table with sys dba privileges.
2-)Create an index on this table.
3-)Add some constraints on table.
4-)Check which tablespace this table and index belongs to.
5-)Move table from one tablespace from one to another
6-)Insert data to table after moving tablespace and investigate reaction of select statement.



1-)

,

2-)

3-) It is also possible to do these constraints while creating table

4-)

 Retrieved data;

5-)

6-)The first two insert statements before changing table's tablespace

Retrieved data;

The last  insert statement after changing table's tablespace from SYSTEM to XXXT.

Error after changing only table's tablespace;

As seen on scenerio above, If there is an index created on table,it is a must to change that indexes' tablespace too. Index remained at its last block. So need to change it;

                                                                   Just leave yourself on way and walk,the way will appear.

HOW TO RESIZE OR ADD TABLESPACE SIZE ?

How to resize an existing tablespace datafile;

alter database

datafile  '<datafile_path/data_file_name>'

resize 150M;

For example;

alter database

datafile '/ora_data/PROD/proddata/xxxt06.dbf'

resize 150M;

How to add a new datafile on partition for an existing tablespace;

alter tablespace

<tablespace_name>

add datafile '<datafile_path/data_file_name>'

size 200M

autoextend on;

For example;

alter tablespace 

xxxt

add datafile '/ora_data/PROD/proddata/xxxt07.dbf'

size 500M

autoextend off;

I have also added  'autoextend on/off' option which is useful.It will manage automatically extending datafile's size.

To see also tablespace autospace management;

select tablespace_name,segment_space_management

from dba_tablespace;

WHICH TABLESPACE BELONGS TO WHICH DATAFILE?

It is possible to see in two ways;

1-)select *

    from dba_data_files;

2-)select df.name,ta.name

    from v$datafile df,v$tablespace ta

    where df.ts#=ta.ts#; 

Data dictionary stuffs.

GET_HOST_NAME,GET_HOST_ADDRESS

These sql codes below provides us to see our instance's database's host name and host address.

1-)Press F5 to see script output.

2-)Other possible use of utl_inaddr.get_host_name and utl_address.get_host_address

CASCADE

drop user USER_NAME cascade;

It also drops the objects created by user,if you do not write cascade it will prompt  ORA-01922 error.