Query to find database name and username with using Oracle package.

This query will tell you the name of the database that you use and the username that you use to query the database currently.
SQL>select dbms_standard.database_name DB_NAME,
dbms_standard.login_user USERNAME from dual;

With Regards
Vijay Kaushik

File transfers for database cloning

Hi,
There are lot of ways in which files are copied from one database to the other during database cloning or transporting tablespaces. File transfers can be done using oracle package called "DBMS_FILE_TRANSFER".

Let us assume we need to transfer a file x.dbf from database SOURCE_DB to database DEST_DB.
Do the following :

AT SOURCE_DB:
==============
1)Create directory source_dir as '/u01/dbdata';

2)Create database link remote_db connect to system identified by manager
using 'remmote_db';

Make sure to add this entry in TNSNAMES.ORA file.
Check if the database link works by
select count(1) from dba_tables@remote_db;

AT DEST_DB:
============
create directory dest_dir as '/u02/dbdata';
Now back to SOURCE_DB:
exec dbms_file_transfer.put_file('SOURCE_DIR','x.dbf','DEST_DIR','x.dbf','REMOTE_DB'); Now the file is copied from SOURCE_DB to DEST_DB.

This package also has get_file and copy_file (for local copy). try this and it is faster too.

regards,
Vijay kaushik

Find objects that are locked

Use this script to find the objects that are locked
To Find the tables that are locked .
SQL>select a.sid,a.serial#,c.object_name
from V$session a, V$locked_object b, user_objects c
where a.sid=b.session_id and b.object_id=c.object_id;

OUTPUT:
--------
SID SERIAL# OBJECT_NAME
---- ------- ------------
7 36 emp
9 58 dept
2 rows selected.
Now to release the lock
SQL>alter system kill session '7,36';
System Altered.
SQL>alter system kill session '9,58';
System Altered.
But before killing the sessions make sure that does not affect your Business.

With Regards
V.Vijay Kaushik

rename a datafile without using OS Commands

SQL> select file_name,bytes/1024/1024,status from dba_data_files where tablespace_name='VIJAY_TS';
FILE_NAME BYTES/1024/1024 STATUS
----------- -------------- -----
C:\ORACLE\DBSQL\DATA\VIJAY_TS.DBF 4.1015625 AVAILABLE C:\ORACLE\DBSQL\DATA\VIJAY_TS1.DBF 2 AVAILABLE
2 rows selected.
Elapsed: 00:00:00.10
SQL> alter database datafile 'C:\ORACLE\DBSQL\DATA\VIJAY_TS.DBF' offline;
Database altered.
Elapsed: 00:00:00.42
SQL> select file#,name,status from V$datafile where upper(name) like '%VIJAY%';
FILE#
----------
NAME
-----------------------------------------------------------------------------------------STATUS
-------
7 C:\ORACLE\DBSQL\DATA\VIJAY_TS.DBF RECOVER
9 C:\ORACLE\DBSQL\DATA\VIJAY_TS1.DBF ONLINE

2 rows selected.
Elapsed: 00:00:00.08
SQL> show user
USER is "VIJAY"
SQL> create directory file_tr as 'C:\oracle\dbsql\data';
Directory created.
Elapsed: 00:00:00.05
SQL> exec dbms_file_transfer.COPY_FILE('FILE_TR','VIJAY_TS.DBF','FILE_TR','VIJAY_TS001.DBF')
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.38
SQL> alter tablespace vijay_ts rename datafile 'C:\ORACLE\DBSQL\DATA\VIJAY_TS.DBF' to 'C:\ORACLE\DBSQL\DATA\VIJAY_TS001.DBF';
Tablespace altered.
Elapsed: 00:00:00.30
SQL> alter database datafile 'C:\ORACLE\DBSQL\DATA\VIJAY_TS001.DBF' online;
alter database datafile 'C:\ORACLE\DBSQL\DATA\VIJAY_TS001.DBF' online*ERROR at line 1:ORA-01113: file 7 needs media recovery
ORA-01110: data file 7: 'C:\ORACLE\DBSQL\DATA\VIJAY_TS001.DBF'
Elapsed: 00:00:00.37
SQL> recover datafile 'C:\ORACLE\DBSQL\DATA\VIJAY_TS001.DBF';
Media recovery complete.

SQL> alter database datafile 'C:\ORACLE\DBSQL\DATA\VIJAY_TS001.DBF' online;
Database altered.
Elapsed: 00:00:00.50
SQL> select file#,name,status from V$datafile where upper(name) like '%VIJAY%';
FILE#
----------
NAME ------------------------------------------------------------------------------------------------------------------------------------------------------
STATUS
-------
7 C:\ORACLE\DBSQL\DATA\VIJAY_TS001.DBF ONLINE
9 C:\ORACLE\DBSQL\DATA\VIJAY_TS1.DBF ONLINE

2 rows selected.
Elapsed: 00:00:00.08
SQL> select file_name,bytes/1024/1024,status
from dba_data_files where tablespace_name='VIJAY_TS';
FILE_NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
BYTES/1024/1024
---------------
STATUS
---------
C:\ORACLE\DBSQL\DATA\VIJAY_TS001.DBF 4.1015625 AVAILABLE C:\ORACLE\DBSQL\DATA\VIJAY_TS1.DBF 2 AVAILABLE

2 rows selected.
Elapsed: 00:00:00.06