Note: we cannot rename user.
How
to rename a table?
syntax:
sql>RENAME
<OLDNAME> TO <NEWNAME>;
SQL>CONN
SCOTT/TIGER;
SQL>SELECT
* FROM TAB;
SQL>RENAME
DEPT TO DEPARTMENT;
RENAMED
How
to rename a tablespace?
syntax:
sql>alter
tablespace <oldtablespacename> rename to <newtablespacename>;
Note:
we cannot rename the SYSTEM,SYSAUX tablespaces.
This
feature is available from 10g onwards.
sql>connect
/as sysdba
sql>show
user
SYS
sql>select
* from v$tablespace;
5
tablespaces it is showing
sql>alter
tablespace userdata rename to test;
tablespace
altered
sql>select
* from v$tablespace;
It
is renamed.
sql>alter
tablespace test to userdata;
will
try to alter system tablespace
---------------------------------
sql>alter
tablespace system rename to systm;
cannot
rename system tablespace.
How
to rename a datafile or how to move datafile from current location
to
new location?
steps:
step1:
make the desired tablespace offline.
sql>alter
tablespace userdata offline;
step2:
GO to OS level, make a copy of the existing datafile to the
new name and location.
]$ls
userdata.dbf
]$cp
userdata.dbf user.dbf or specify the path where u want to create.
step3:
GO to database level
sql>alter
tablespace userdata rename datafile '/home/prd/userdata.dbf'
to '/home/prd/user.dbf';
step4:
Make the tablespace online.
sql>alter tablespace userdata online;
Practical Demo
sql>show
user;
SYS
sql>select
file_name,tablespace_name from dba_data_files;
sql>col
FILE_NAME for a 25
sql>select
file_name,tablespace_name from dba_data_files;
This
will show what are the available tablespaces and the location.
sql>seleect
tablespace_name ,status from dba_tablespaces;
This
will show whether it is online or offline.
In
offline mode we cannot access anything.
sql>alter
tablespace userdata offline;
tablespace
alterred
sql>select
file_name,tablespace_name from dba_data_files;
now
userdata is showing offline.
go
to os level in prd folder
]$cd
prd
prd]$ls
prd]$cp
userdata.dbf user.dbf
prd]$cd
]$exit
sql>show
user
SYS
sql>select
file_name,tablespace_name from dba_data_files;
still
name is same
sql>alter
tablespace userdata rename datafile '/home/prd/userdata.dbf'
to '/home/prd/user.dbf';
tablespace
altered
sql>select
file_name,tablespace_name from dba_data_files;
Now
the datafile is modified to user.dbf
sql>seleect
tablespace_name ,status from dba_tablespaces;
tablespace
is still offline
sql>alter
tablespace userdata online;
go
ot os level
prd]$rm
userdata.dbf
prd]$cd
$exit
sql>startup
force; (bounce the database server)
sql>select
name from v$datafile;
IInd
method to rename a datafile:
---------------------------------
step1:
select name from v$datafile;
user.dbf
--------
step2:
shutdown the database preperly
sql>shut immediate;
step3:
go to os level make a copy of existing datafile to a new name
and location
]$ls
user.dbf
]$cp
user.dbf userdata.dbf
step4:
connect database as sysdba adn start the
database in mount stage
and then execute these commands
sql>startup mount;
sql>alter database rename
file '/home/prd/user.dbf' to
'/home/prd/userdata.dbf';
sql>alter
database open;
sql>select
name from v$datafile;
It will
display all database files.
userdata.dbf
Practical Demo
sql>select
name from v$datafile;
it
is showing 5 the last one is user.dbf
sql>shut
immediate;
sql>host
go
tp prd directory
prd]$ls
cp
user.dbf userdata.dbf
prd]$cd
$exit
sql>show
user;
SYS
sql>startup
mount;
sql>select
name from v$datafile;
sql>alter
database rename '/home/prd/user.dbf' to '/home/prd/userdata.dbf';
How to rename a logfile?
Follow
II way to rename a file.
sql>show
user
SYS
sql>select
member from v$logfile;
Member
-----------
/home/prd/redo1.log
/home/prd/redo2.log
sql>select
group#,member from v$logfile;
sql>shut
immediate;
sql>host
go
to prd folder
prd]$cp
redo1.lg redo1a.log (note: extension must be .log)
prd]$exit
sql>show
user
SYS
sql>startup
mount;
sql>select
member from v$logfile;
old
info.
sql>alter
database rename
file '/home/prd/redo1.log' to
'/home/prd/redo1a.log';
database
altered
sql>select
member from v$logfile;
member
------
/home/prd/redo1a.log
/home/prd/redo2.log
sql>alter
database open;
sql>select
name from v$logfile;
memeber
----------
/home/prd/redo1a.log
/home/prd/redo2.log
How
to rename a database name?
Database
name is there in pfile and controlfile.
sql>show
parameter db_name;
PRD
sql>select
name from v$database;
PRD
sql>alter
database backup controlfile to trace;
Note:
taking xerox copy of the control file.so text information will
come so that we can edit and modify.
oracle will take snapshot and keep it in
udump directory.
How do we know the udump location?
sql>show parameter user_dump_dest;
user related dump file is stored here.
/home/prd/udump
sql>shut immediate;
sql>exit
]$
go
to udump location
]$cd /home/prd/udump
udump]$ls
-lrt
latest
file will display at an end
---------.trc
]$vi ------.trc
CREATE CONTROLFILE REUSE "PRD"
NORESETLOGS NOARCHIVELOG
OR ARCHIVELOG
--------
----------
CHARACTERSET US7ASCII;
REPLACE REUSE WITH SET KEYWORD
REPLACE DATABASENAME "PRD" WITH
NEW DATABASENAME
IN NORESETLOGS REMOVE NO
:wq (save and exit)
---------------------------------------------------------------------------------------
]$cd /home/prd <enter>
prd]$rm
*.ctl
note: while recreateing control file old
control file is existing
It will throw an error. so we have to
remove existing control file.
prd]$cd $ORACLE_HOME/dbs
prd]$ vi initprd.ora
here prd is the sidname or
the instance name
instance name and database name may be
same or different
db_name=TEST
:wq (save and exit)
]$cd
]$sqlplus
'/as sysdba'
sql>startup
nomount;
sql>@/home/prd/udump/------.trc;
control file is recreated
sql>alter
database open resetlogs;
sql>select
name from v$database;
It will display new name
Practical Demo
sql>
select name from v$database;
name
-------
PRD
sql>show
parameter db_anme;
prd
sql>alter
database backup controlfile to trace;
database altered
sql>
show parameter user_dump_dest;
/home/prd/udump
go
to udump folder and check it
udump]$
if
it is not generated execute again.
sql>shut
immediate;
sql>exit
]$cd
/home/prd/udump
udump]$ls
-lrt
select the last one
udump]$vi
prd_ora_5991.trc
dgg for removing the top portion
dd
will command remove one by one
go to insert mode
:wq
]$cd
]$cd
$ORACLE_HOME/dbs
dbs]$vi
initprd.ora
change db_name=TEST
:wq
Before
removing control file take a backup
prd]$cp
*.ctl ~ (taking backup into the /home
directory)
prd]$rm
*.ctl
prd]$ls
no
control files
]$sqlplus
'/as sysdba'
sql>startup
nomount;
sql>@/home/prd/udump/prd_ora_5991.trc;
control
file created
login
as oracle user
#su
- oracle
go
to prd folder
prd]$ls
control1.ctl
will exist
sql>alter
database open resetlogs;
database
altered
sql>select
name from v$database;
TEST
No comments:
Post a Comment