Suppose DBA wants that in the database certain user will not be able structural change. That is no DDL operation can be performed by the user. In that case DBA can achieve his goal simply by making a trigger on the schema.
Suppose we want user faruk will not be able to perform any DDL. Then create trigger as below.
SQL> conn faruk/faruk
Connected.
SQL> create table before_trigger(a number);
Table created.
SQL>conn system/a
Connected.
SQL> CREATE OR REPLACE
2 TRIGGER BEFORE_DDL_FARUK
3 BEFORE DDL
4 ON FARUK.SCHEMA
5 BEGIN
6 RAISE_APPLICATION_ERROR(-30900,'DDL Operation is not Permitted.' );
7 END;
8 /
Trigger created.
SQL> conn faruk/faruk
Connected.
SQL> create table after_trigger(a number);
create table after_trigger(a number)
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-21000: error number argument to raise_application_error of -30900 is out of
range
ORA-06512: at line 2
Showing posts with label Database Administration. Show all posts
Showing posts with label Database Administration. Show all posts
Thursday, September 4, 2008
Wednesday, September 3, 2008
Create Read only user for a Schema
One thing you need to remember before read this post is there is no easy or shortcut way to make a read only user of another schema. Like grant select on username to another_username- there is no such single command like this. However you may have several alternatives to make read only user for a schema.
I will demonstrate the procedure with examples to make a read only user for a schema. In the example I will make devels user which will have read only permission on prod schema.
Let's start by creating PROD user.
SQL> CREATE USER PROD IDENTIFIED BY P;
User created.
SQL> GRANT DBA TO PROD;
Grant succeeded.
SQL> CONN PROD/P;
Connected.
SQL> CREATE TABLE PROD_TAB1 ( A NUMBER PRIMARY KEY, B NUMBER);
Table created.
SQL> INSERT INTO PROD_TAB1 VALUES(1,2);
1 row created.
SQL> CREATE TABLE PROD_TAB2(DATE_COL DATE);
Table created.
SQL> CREATE OR REPLACE TRIGGER PROD_TAB2_T AFTER INSERT ON PROD_TAB1
BEGIN
INSERT INTO PROD_TAB2 VALUES(SYSDATE);
END;
/
Trigger created.
SQL>CREATE VIEW A AS SELECT * FROM PROD_TAB2;
View created.
Method 1: Granting Privilege Manually
Step 1: Create devels user
SQL> CREATE USER DEVELS IDENTIFIED BY D;
User created.
Step 2: Grant only select session and create synonym privilege to devels user.
SQL> GRANT CREATE SESSION ,CREATE SYNONYM TO DEVELS;
Grant succeeded.
Step 3:Make script to grant select privilege.
$vi /oradata2/script.sql
SET PAGESIZE 0
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL /oradata2/select_only_to_prod.sql
@@/oradata2/select_only_script.sql
SPOOL OFF
This script will run the /oradata2/select_only_script.sql and generate a output script /oradata2/select_only_to_prod.sql which need to be run in fact.
Step 4:
Prepare the /oradata2/select_only_script.sql script which will work as input for /oradata2/script.sql file.
$vi /oradata2/select_only_script.sql
Step 5:
Now execute the /oradata2/script.sql which will in fact generate scipt /oradata2/select_only_to_prod.sql.
SQL> @/oradata2/script.sql
Step 6:
Execute the output script select_only_to_prod.sql which will be used to grant read only permission of devels user to prod schema.
SQL> @/oradata2/select_only_to_prod.sql
Step 7:
Log on devels user and create synonym so that the devels user can access prod's table without any dot(.). Like to access prod_tab2 of prod schema he need to write prod.prod_tab2. But after creating synonym he simply can use prod_tab2 to access devels table and views.
To create synonym do the following,
SQL>CONN DEVELS/D;
SQL>host vi /oradata2/script_synonym.sql
SQL>host vi /oradata2/synonym_script.sql
SELECT 'CREATE SYNONYM ' ||TABLE_NAME|| ' FOR PROD.' ||TABLE_NAME||';' FROM ALL_TABLES WHERE OWNER='PROD';
SELECT 'CREATE SYNONYM ' ||VIEW_NAME|| ' FOR PROD.' ||VIEW_NAME||';' FROM ALL_VIEWS WHERE OWNER='PROD';
SQL>@/oradata2/script_synonym.sql
SQL>@/oradata2/synonym_to_prod.sql
Step 8: At this stage you have completed your job. Log on as devels schema and see,
SQL> select * from prod_tab1;
1 2
SQL> show user
USER is "DEVELS"
Only select privilege is there. So DML will throw error. Like,
SQL> insert into prod_tab1 values(4,3);
insert into prod_tab1 values(4,3)
*
ERROR at line 1:
ORA-01031: insufficient privileges
Method 2: Writing PL/SQL Code
This is script for table :
This is the script for grant select permission for views.
To create synonym on prod schema,
Log on as devels and execute the following procedure.
Method 3: Writing a Trigger
After granting select permission in either of two ways above you can avoid creating synonym by simply creating a trigger.
Create a log on trigger that eventually set current_schema to prod just after log in DEVELS user.
create or replace trigger log_on_after_devels
after logon ON DEVELS.SCHEMA
BEGIN
EXECUTE IMMEDIATE 'alter session set CURRENT_SCHEMA = prod';
END;
/
Related Documents
Drop User in Oracle
ORA-01940: Cannot drop a user that is currently connected
Create user in oracle
A user can do work in his schema with only Create Session Privilege.
I will demonstrate the procedure with examples to make a read only user for a schema. In the example I will make devels user which will have read only permission on prod schema.
Let's start by creating PROD user.
SQL> CREATE USER PROD IDENTIFIED BY P;
User created.
SQL> GRANT DBA TO PROD;
Grant succeeded.
SQL> CONN PROD/P;
Connected.
SQL> CREATE TABLE PROD_TAB1 ( A NUMBER PRIMARY KEY, B NUMBER);
Table created.
SQL> INSERT INTO PROD_TAB1 VALUES(1,2);
1 row created.
SQL> CREATE TABLE PROD_TAB2(DATE_COL DATE);
Table created.
SQL> CREATE OR REPLACE TRIGGER PROD_TAB2_T AFTER INSERT ON PROD_TAB1
BEGIN
INSERT INTO PROD_TAB2 VALUES(SYSDATE);
END;
/
Trigger created.
SQL>CREATE VIEW A AS SELECT * FROM PROD_TAB2;
View created.
Method 1: Granting Privilege Manually
Step 1: Create devels user
SQL> CREATE USER DEVELS IDENTIFIED BY D;
User created.
Step 2: Grant only select session and create synonym privilege to devels user.
SQL> GRANT CREATE SESSION ,CREATE SYNONYM TO DEVELS;
Grant succeeded.
Step 3:Make script to grant select privilege.
$vi /oradata2/script.sql
SET PAGESIZE 0
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL /oradata2/select_only_to_prod.sql
@@/oradata2/select_only_script.sql
SPOOL OFF
This script will run the /oradata2/select_only_script.sql and generate a output script /oradata2/select_only_to_prod.sql which need to be run in fact.
Step 4:
Prepare the /oradata2/select_only_script.sql script which will work as input for /oradata2/script.sql file.
$vi /oradata2/select_only_script.sql
SELECT 'GRANT SELECT ON PROD.' ||TABLE_NAME || ' TO DEVELS;' FROM DBA_TABLES WHERE OWNER='PROD';
SELECT 'GRANT SELECT ON PROD.' ||VIEW_NAME || ' TO DEVELS;' FROM DBA_VIEWS WHERE OWNER='PROD';
Step 5:
Now execute the /oradata2/script.sql which will in fact generate scipt /oradata2/select_only_to_prod.sql.
SQL> @/oradata2/script.sql
GRANT SELECT ON PROD.PROD_TAB1 TO DEVELS;
GRANT SELECT ON PROD.PROD_TAB2 TO DEVELS;
Step 6:
Execute the output script select_only_to_prod.sql which will be used to grant read only permission of devels user to prod schema.
SQL> @/oradata2/select_only_to_prod.sql
Step 7:
Log on devels user and create synonym so that the devels user can access prod's table without any dot(.). Like to access prod_tab2 of prod schema he need to write prod.prod_tab2. But after creating synonym he simply can use prod_tab2 to access devels table and views.
To create synonym do the following,
SQL>CONN DEVELS/D;
SQL>host vi /oradata2/script_synonym.sql
SET PAGESIZE 0
SET LINESIZE 200
SET HEADING OFF
SET FEEDBACK OFF
SET ECHO OFF
SPOOL /oradata2/synonym_to_prod.sql
@@/oradata2/synonym_script.sql
SPOOL OFF
SQL>host vi /oradata2/synonym_script.sql
SELECT 'CREATE SYNONYM ' ||TABLE_NAME|| ' FOR PROD.' ||TABLE_NAME||';' FROM ALL_TABLES WHERE OWNER='PROD';
SELECT 'CREATE SYNONYM ' ||VIEW_NAME|| ' FOR PROD.' ||VIEW_NAME||';' FROM ALL_VIEWS WHERE OWNER='PROD';
SQL>@/oradata2/script_synonym.sql
SQL>@/oradata2/synonym_to_prod.sql
Step 8: At this stage you have completed your job. Log on as devels schema and see,
SQL> select * from prod_tab1;
1 2
SQL> show user
USER is "DEVELS"
Only select privilege is there. So DML will throw error. Like,
SQL> insert into prod_tab1 values(4,3);
insert into prod_tab1 values(4,3)
*
ERROR at line 1:
ORA-01031: insufficient privileges
Method 2: Writing PL/SQL Code
This is script for table :
set serveroutput on
DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT table_name FROM dba_tables where owner='PROD';
BEGIN
dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON PROD.'||tables.table_name||' TO devels';
execute immediate sql_txt;
END LOOP;
END;
/
This is the script for grant select permission for views.
DECLARE
sql_txt VARCHAR2(300);
CURSOR tables_cur IS
SELECT view_name FROM dba_views where owner='PROD';
BEGIN dbms_output.enable(10000000);
FOR tables IN tables_cur LOOP
sql_txt:='GRANT SELECT ON PROD.'||tables.view_name||' TO devels';
--dbms_output.put_line(sql_txt);
execute immediate sql_txt;
END LOOP;
END;
/
To create synonym on prod schema,
Log on as devels and execute the following procedure.
SQL>CONN DEVELS/D
SQL>
DECLARE
sql_txt VARCHAR2(300);
CURSOR syn_cur IS
SELECT table_name name FROM all_tables where owner='PROD'
UNION SELECT VIEW_NAME name from all_views where owner='PROD' ;
BEGIN dbms_output.enable(10000000);
FOR syn IN syn_cur LOOP
sql_txt:='CREATE SYNONYM '||syn.name|| ' FOR PROD.'||syn.name ;
dbms_output.put_line(sql_txt);
execute immediate sql_txt;
END LOOP;
END;
/
Method 3: Writing a Trigger
After granting select permission in either of two ways above you can avoid creating synonym by simply creating a trigger.
Create a log on trigger that eventually set current_schema to prod just after log in DEVELS user.
create or replace trigger log_on_after_devels
after logon ON DEVELS.SCHEMA
BEGIN
EXECUTE IMMEDIATE 'alter session set CURRENT_SCHEMA = prod';
END;
/
Related Documents
Drop User in Oracle
ORA-01940: Cannot drop a user that is currently connected
Create user in oracle
A user can do work in his schema with only Create Session Privilege.
Wednesday, June 11, 2008
Moving a Table to a New Segment or Tablespace
•The ALTER TABLE...MOVE statement enables you to relocate data of a non-partitioned table or of a partition of a partitioned table into a new segment, and optionally into a different tablespace.
•This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE.
•It is good to remember that The ALTER TABLE...MOVE statement does not permit DML against the table while the statement is executing. If you want to leave the table available for DML while moving it it the you have to use DBMS_REDEFINITION package to move online.
•Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.
•You cannot move a table containing a LONG or LONG RAW column.
•You cannot MOVE an entire partitioned table. You must move individual partitions or subpartitions.
Example:
---------------
SQL> select TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARJU' and segment_name='TEST';
TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
USER_TBS 11 1283
Here HEADER_BLOCK indicates the ID of the block containing the segment header. And HEADER_FILE is the data file id. If I move the table to a new segment then header block number will be change. Lets have a look at it.
SQL> alter table test move;
Table altered.
SQL> select TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARJU' and segment_name='TEST';
TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
USER_TBS 11 1459
So HEADER_BLOCK change to 1459 from 1283.
Now I want to move the table test from USER_TBS tablespace to tablespace TBS_AFTER_BACKUP. To see so we have to append TABLESPACE keyword like,
SQL> alter table test move tablespace TBS_AFTER_BACKUP;
Table altered.
SQL> select TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARJU' and segment_name='TEST';
TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
TBS_AFTER_BACKUP 6 11
•This statement also lets you modify any of the storage attributes of the table or partition, including those which cannot be modified using ALTER TABLE.
•It is good to remember that The ALTER TABLE...MOVE statement does not permit DML against the table while the statement is executing. If you want to leave the table available for DML while moving it it the you have to use DBMS_REDEFINITION package to move online.
•Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.
•You cannot move a table containing a LONG or LONG RAW column.
•You cannot MOVE an entire partitioned table. You must move individual partitions or subpartitions.
Example:
---------------
SQL> select TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARJU' and segment_name='TEST';
TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
USER_TBS 11 1283
Here HEADER_BLOCK indicates the ID of the block containing the segment header. And HEADER_FILE is the data file id. If I move the table to a new segment then header block number will be change. Lets have a look at it.
SQL> alter table test move;
Table altered.
SQL> select TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARJU' and segment_name='TEST';
TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
USER_TBS 11 1459
So HEADER_BLOCK change to 1459 from 1283.
Now I want to move the table test from USER_TBS tablespace to tablespace TBS_AFTER_BACKUP. To see so we have to append TABLESPACE keyword like,
SQL> alter table test move tablespace TBS_AFTER_BACKUP;
Table altered.
SQL> select TABLESPACE_NAME,HEADER_FILE,HEADER_BLOCK from dba_segments where owner='ARJU' and segment_name='TEST';
TABLESPACE_NAME HEADER_FILE HEADER_BLOCK
------------------------------ ----------- ------------
TBS_AFTER_BACKUP 6 11
Wednesday, May 28, 2008
How to Export data to a flat file
Whenever you want to move data from oracle to other software products like SQL SERVER or MYSQL or any other database software then it is needed at first to move data to a flat file. Flat file is an OS file like a text file. Also, if you don't have oracle net then for moving data from lower verion to upper version you can also use this method - first save data to a flat file and then using SQL*Loader or external table transfer data into database.
To illustrate the system I have create table test_spool and insert data into it.
A)SQL> create table test_spool( a number, b varchar2(10),c varchar2(30));
Table created.
SQL> insert into test_spool values(1,'Oracle','Bangladesh , India and USA');
1 row created.
SQL> select * from test_spool;
A B C
---------- ---------- ------------------------------
1 Oracle Bangladesh , India and USA
Now Set the following environmental variables of SQL*Plus.
B)SQL> SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF
SET COLSEP " "
I used COLSEP in order to separate column inside flat file.
Now spool it and run the query.
C)SQL> spool output_to_flat_file.txt
SQL> select * from test_spool;
1 Oracle Bangladesh , India and USA
SQL> spool off
See the contents of the file now.
D)SQL> !cat output_to_flat_file.txt
SQL> select * from test_spool;
1 Oracle Bangladesh , India and USA
SQL> spool off
Use it as you like.
In order to save the query to an html file you can use as follows.
SET HEADING ON
SET MARKUP HTML ON SPOOL OFF
SPOOL /oradata2/a.html
SELECT * FROM LOGIN WHERE ROWNUM<5;
SPOOL OFF
To save it in excel file paste this output to an excel file.
To illustrate the system I have create table test_spool and insert data into it.
A)SQL> create table test_spool( a number, b varchar2(10),c varchar2(30));
Table created.
SQL> insert into test_spool values(1,'Oracle','Bangladesh , India and USA');
1 row created.
SQL> select * from test_spool;
A B C
---------- ---------- ------------------------------
1 Oracle Bangladesh , India and USA
Now Set the following environmental variables of SQL*Plus.
B)SQL> SET SPACE 0
SET LINESIZE 80
SET PAGESIZE 0
SET ECHO OFF
SET FEEDBACK OFF
SET VERIFY OFF
SET HEADING OFF
SET MARKUP HTML OFF SPOOL OFF
SET COLSEP " "
I used COLSEP in order to separate column inside flat file.
Now spool it and run the query.
C)SQL> spool output_to_flat_file.txt
SQL> select * from test_spool;
1 Oracle Bangladesh , India and USA
SQL> spool off
See the contents of the file now.
D)SQL> !cat output_to_flat_file.txt
SQL> select * from test_spool;
1 Oracle Bangladesh , India and USA
SQL> spool off
Use it as you like.
In order to save the query to an html file you can use as follows.
SET HEADING ON
SET MARKUP HTML ON SPOOL OFF
SPOOL /oradata2/a.html
SELECT * FROM LOGIN WHERE ROWNUM<5;
SPOOL OFF
To save it in excel file paste this output to an excel file.
What is the overall database size
An oracle database consists of data files, redo log files, control files, temporary files. Whenever you say the size of the database this actually means the summation of these files.
The biggest portion of a database's size comes from the datafiles. To find out how many megabytes are allocated to ALL datafiles:
select sum(bytes)/1024/1024 "Meg" from dba_data_files;
To get the size of all TEMP files:
select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;
To get the size of the on-line redo-logs:
select sum(bytes)/1024/1024 "Meg" from sys.v_$log;
To get the size of the control files use,
SQL> select sum(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024) "MEG" from v$controlfile;
So to get the total size of the database just sum these.
select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v$controlfile) d;
Related Documents
The biggest portion of a database's size comes from the datafiles. To find out how many megabytes are allocated to ALL datafiles:
select sum(bytes)/1024/1024 "Meg" from dba_data_files;
To get the size of all TEMP files:
select nvl(sum(bytes),0)/1024/1024 "Meg" from dba_temp_files;
To get the size of the on-line redo-logs:
select sum(bytes)/1024/1024 "Meg" from sys.v_$log;
To get the size of the control files use,
SQL> select sum(BLOCK_SIZE*FILE_SIZE_BLKS/1024/1024) "MEG" from v$controlfile;
So to get the total size of the database just sum these.
select a.data_size+b.temp_size+c.redo_size+d.controlfile_size "total_size in MB"
from ( select sum(bytes)/1024/1024 data_size
from dba_data_files ) a,
( select nvl(sum(bytes),0)/1024/1024 temp_size
from dba_temp_files ) b,
( select sum(bytes)/1024/1024 redo_size
from sys.v_$log ) c,
( select sum(BLOCK_SIZE*FILE_SIZE_BLKS)/1024/1024 controlfile_size
from v$controlfile) d;
Related Documents
Maximum Oracle Database Size.
Saturday, April 12, 2008
How to Create and Use OMF
OMF indicates Oracle Managed Files. With the use of Oracle-managed files the administration of an Oracle Database can be simplified. Oracle-managed files eliminate the need for you, the DBA, to directly manage the operating system files comprising an Oracle Database. You specify operations in terms of database objects rather than filenames.
Enable the Creation of OMFs
The following initialization parameters allow the database server to use the Oracle-managed files feature.
1)DB_CREATE_FILE_DEST: Defines the location of the default file system directory where the database creates datafiles or tempfiles when no file specification is given in the creation operation. It is also used as the default file system directory for redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.
2)DB_CREATE_ONLINE_LOG_DEST_n:Defines the location of the default file system directory for redo log files and control file creation when no file specification is given in the creation operation. You can use this initialization parameter multiple times, where n specifies a multiplexed copy of the redo log or control file. You can specify up to five multiplexed copies.
3)DB_RECOVERY_FILE_DEST:Defines the location of the default file system directory where the database creates RMAN backups when no format option is used, archived logs when no other local destination is configured, and flashback logs. Also used as the default file system directory for redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.
Both of these initialization parameters are dynamic, and can be set using the ALTER SYSTEM or ALTER SESSION statement.
An Example of using OMF :
1)Setting the parameter for the session:
SQL> alter session set db_create_file_dest='/oradata';
Session altered.
2)Create Tablespace using OMF:
SQL> create tablespace omf_tbs;
Tablespace created.
3)Check the data file Location:
SQL> select file_name from dba_data_files where tablespace_name='OMF_TBS';
FILE_NAME
--------------------------------------------------------------------------------
/oradata/ARJUT/datafile/o1_mf_omf_tbs_4049w4op_.dbf
Here ARJUT is the Database Name.
The dafault location for datafile is Your settings for parameter/Database Name/datafile/Unique Name.dbf
Enable the Creation of OMFs
The following initialization parameters allow the database server to use the Oracle-managed files feature.
1)DB_CREATE_FILE_DEST: Defines the location of the default file system directory where the database creates datafiles or tempfiles when no file specification is given in the creation operation. It is also used as the default file system directory for redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.
2)DB_CREATE_ONLINE_LOG_DEST_n:Defines the location of the default file system directory for redo log files and control file creation when no file specification is given in the creation operation. You can use this initialization parameter multiple times, where n specifies a multiplexed copy of the redo log or control file. You can specify up to five multiplexed copies.
3)DB_RECOVERY_FILE_DEST:Defines the location of the default file system directory where the database creates RMAN backups when no format option is used, archived logs when no other local destination is configured, and flashback logs. Also used as the default file system directory for redo log and control files if DB_CREATE_ONLINE_LOG_DEST_n is not specified.
Both of these initialization parameters are dynamic, and can be set using the ALTER SYSTEM or ALTER SESSION statement.
An Example of using OMF :
1)Setting the parameter for the session:
SQL> alter session set db_create_file_dest='/oradata';
Session altered.
2)Create Tablespace using OMF:
SQL> create tablespace omf_tbs;
Tablespace created.
3)Check the data file Location:
SQL> select file_name from dba_data_files where tablespace_name='OMF_TBS';
FILE_NAME
--------------------------------------------------------------------------------
/oradata/ARJUT/datafile/o1_mf_omf_tbs_4049w4op_.dbf
Here ARJUT is the Database Name.
The dafault location for datafile is Your settings for parameter/Database Name/datafile/Unique Name.dbf
Wednesday, April 9, 2008
How to Change Database Name and DBID?
Prior to introduction of DBNEWID utility it was possible to change the name of the database by manually creating a new control file but it was not possible to give new dbid to the database.
The DBID is an internal, unique identifier for a database. RMAN distinguishes databases by DBID, so you could not register a seed database and a manually copied database together in the same RMAN repository.
DBNEWID solves this. With DBNEWID utility you can change either database name or database id or both.
However, changing DBID is a serious procedure. When you change DBID previous backups, archived redo logs become invalid.
Procedure of changing DBID and Database Name:
1)Take a recoverable full database backup.
2)Mount the database.
3)With sysdba privilege, invoke nid
i) To change only DBID just invoke nid target=username/pass
ii) To change both DBID and DBNAME invoke nid target=username/pass DBNAME=new_database_name
iii)To change only DBNAME invoke nid target=username/pass DBNAME=new_database_name SETNAME=y
i)Change only DBID:
To change only DBID just enter the following command,
SQL>host nid target=arju/a
Where arju is a user having sysdba system priviege. And password of arju is a.
ii)Change both DBID and DBNAME:
Related Documents
The DBID is an internal, unique identifier for a database. RMAN distinguishes databases by DBID, so you could not register a seed database and a manually copied database together in the same RMAN repository.
DBNEWID solves this. With DBNEWID utility you can change either database name or database id or both.
However, changing DBID is a serious procedure. When you change DBID previous backups, archived redo logs become invalid.
Procedure of changing DBID and Database Name:
1)Take a recoverable full database backup.
2)Mount the database.
3)With sysdba privilege, invoke nid
i) To change only DBID just invoke nid target=username/pass
ii) To change both DBID and DBNAME invoke nid target=username/pass DBNAME=new_database_name
iii)To change only DBNAME invoke nid target=username/pass DBNAME=new_database_name SETNAME=y
i)Change only DBID:
To change only DBID just enter the following command,
SQL>host nid target=arju/a
Where arju is a user having sysdba system priviege. And password of arju is a.
ii)Change both DBID and DBNAME:
To change the database name in addition to DBID enter the following command.
SQL>host nid=arju/a DBNAME=arjut
which changes the DBID to a new DBID (You can't set DBID though as your wish) and change the database name to arjut.
In this case the follow operations are performed is below.
1)The DBNEWID utility performs validations in the headers of the datafiles and
control files before attempting I/O to the files.
2)If validation is successful, then DBNEWID prompts you to confirm the operation (unless you specify a log file, in which case it does not prompt)
3)Then changes the DBID and the DBNAME for each datafile, including offline normal and read-only datafiles,
4)Shuts down the database, and then exits.
iii)Change only Database Name:
In the following example I will try to demonstrate to change the Database name.
1)SQL> select dbid, name from v$database;
DBID NAME
---------- ---------
246608360 ARJU
2)SQL> shutdown imemdiate;
startup mount;
3)SQL> host nid target=arju/a DBNAME=ARJUT setname=Y
DBNEWID: Release 10.2.0.1.0 - Production on Wed Apr 9 16:21:33 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to database ARJU (DBID=246608360)
Connected to server version 10.2.0
Control Files in database:
/oradata/Arju/arju/control01.ctl
/oradata/Arju/arju/control02.ctl
/oradata/Arju/arju/control03.ctl
Change database name of database ARJU to ARJUT? (Y/[N]) => y
Instance shut down
Database name changed to ARJUT.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
4)SQL> !export ORACLE_SID=ARJUT
5)SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 113246248 bytes
Database Buffers 46137344 bytes
Redo Buffers 6369280 bytes
6)SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string arju
SQL> alter system set db_name=ARJUT scope=spfile;
System altered.
7)SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
8)SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 113246248 bytes
Database Buffers 46137344 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
9)SQL> select name,dbid from v$database;
NAME DBID
--------- ----------
ARJUT 246608360
SQL>host nid=arju/a DBNAME=arjut
which changes the DBID to a new DBID (You can't set DBID though as your wish) and change the database name to arjut.
In this case the follow operations are performed is below.
1)The DBNEWID utility performs validations in the headers of the datafiles and
control files before attempting I/O to the files.
2)If validation is successful, then DBNEWID prompts you to confirm the operation (unless you specify a log file, in which case it does not prompt)
3)Then changes the DBID and the DBNAME for each datafile, including offline normal and read-only datafiles,
4)Shuts down the database, and then exits.
iii)Change only Database Name:
In the following example I will try to demonstrate to change the Database name.
1)SQL> select dbid, name from v$database;
DBID NAME
---------- ---------
246608360 ARJU
2)SQL> shutdown imemdiate;
startup mount;
3)SQL> host nid target=arju/a DBNAME=ARJUT setname=Y
DBNEWID: Release 10.2.0.1.0 - Production on Wed Apr 9 16:21:33 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to database ARJU (DBID=246608360)
Connected to server version 10.2.0
Control Files in database:
/oradata/Arju/arju/control01.ctl
/oradata/Arju/arju/control02.ctl
/oradata/Arju/arju/control03.ctl
Change database name of database ARJU to ARJUT? (Y/[N]) => y
Instance shut down
Database name changed to ARJUT.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.
4)SQL> !export ORACLE_SID=ARJUT
5)SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup nomount
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 113246248 bytes
Database Buffers 46137344 bytes
Redo Buffers 6369280 bytes
6)SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string arju
SQL> alter system set db_name=ARJUT scope=spfile;
System altered.
7)SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
8)SQL> startup
ORACLE instance started.
Total System Global Area 167772160 bytes
Fixed Size 2019288 bytes
Variable Size 113246248 bytes
Database Buffers 46137344 bytes
Redo Buffers 6369280 bytes
Database mounted.
Database opened.
9)SQL> select name,dbid from v$database;
NAME DBID
--------- ----------
ARJUT 246608360
Related Documents
How to Discover find DBID
Subscribe to:
Posts (Atom)