Oracle: How to shrink table

How to shrink oracle tables


Alter table table_name enable row movement;
Alter table table_name shrink space;

To find out size of any table

Select segment_name,bytes/1024/1024 size_in_MB
from user_segments
where segment_name = 'your_table_name'

kesava temple, Belur, Karnataka

Panch Mahal

Great Temples in India

Must see...

Tanjore Temle

Oracle: How to move data files from c:\ to d:\

Some times you may want to move your oracle data files from c:\ to another drive due to lack of space. Here is how you do it. In my example, my database is SUPPDB and it was created on default c:\



Connect as sys

Alter database backup controlfile to trace;

Go to udump folder (under admin\database_name\udump),open the latest *.trc files
and you will find 2 sets of sql's to recreate the control files.

Use option 1, if you have all your online redo log files

use option 2, if you don't have all your online redo log files.

Stop all services (like dbconsole ) accessing oracle database

Shutdown;
--wait for shutdown to complete.This way you ensure your online log files are up to date.Do not issue shutdown immediate or shutdown abort)

Now, move all your database files (like SYSTEM01.DBF,UNDOTBS01.DBF,SYSAUX01.DBF,USERS01.DBF,REDO01.LOG tablspace files )to the destination directory. In my example, I simply copied the files from c:\ to d:\...

Change the path for the above files in the create controlfile statement as shown
below.

Connect as sys

STARTUP NOMOUNT;

CREATE CONTROLFILE REUSE DATABASE "SUPPDB" NORESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 'D:\Oracle\SUPPDB\Oradata\REDO01.LOG' SIZE 50M,
GROUP 2 'D:\Oracle\SUPPDB\Oradata\REDO02.LOG' SIZE 50M,
GROUP 3 'D:\Oracle\SUPPDB\Oradata\REDO03.LOG' SIZE 50M
DATAFILE
'D:\Oracle\SUPPDB\Oradata\SYSTEM01.DBF',
'D:\Oracle\SUPPDB\Oradata\UNDOTBS01.DBF',
'D:\Oracle\SUPPDB\Oradata\SYSAUX01.DBF',
'D:\Oracle\SUPPDB\Oradata\USERS01.DBF',
'D:\ORACLE\SUPPDB\ORADATA\apple_DATA.DBF',
'D:\ORACLE\SUPPDB\ORADATA\apple_INDX.DBF',
'D:\ORACLE\SUPPDB\ORADATA\orange_DATA.DBF',
'D:\ORACLE\SUPPDB\ORADATA\orange_INDX.DBF'
CHARACTER SET WE8MSWIN1252;

RECOVER DATABASE;
--It's ok to get an ora message saying no recovery needed, its not an error message.
ALTER DATABASE OPEN;
--Create a new TEMPFILE
ALTER TABLESPACE TEMP ADD TEMPFILE 'D:\Oracle\SUPPDB\Oradata\TEMP01.DBF'
SIZE 271581184 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

If you choose option 2

Connect as sys

STARTUP NOMOUNT;

CREATE CONTROLFILE REUSE DATABASE "SUPPDB" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 584
LOGFILE
GROUP 1 'D:\Oracle\SUPPDB\Oradata\REDO01.LOG' SIZE 50M,
GROUP 2 'D:\Oracle\SUPPDB\Oradata\REDO02.LOG' SIZE 50M,
GROUP 3 'D:\Oracle\SUPPDB\Oradata\REDO03.LOG' SIZE 50M
DATAFILE
'D:\Oracle\SUPPDB\Oradata\SYSTEM01.DBF',
'D:\Oracle\SUPPDB\Oradata\UNDOTBS01.DBF',
'D:\Oracle\SUPPDB\Oradata\SYSAUX01.DBF',
'D:\Oracle\SUPPDB\Oradata\USERS01.DBF',
'D:\ORACLE\SUPPDB\ORADATA\apple_DATA.DBF',
'D:\ORACLE\SUPPDB\ORADATA\apple_INDX.DBF',
'D:\ORACLE\SUPPDB\ORADATA\orange_DATA.DBF',
'D:\ORACLE\SUPPDB\ORADATA\orange_INDX.DBF'
CHARACTER SET WE8MSWIN1252;

RECOVER DATABASE USING BACKUP CONTROLFILE;
--At this point, I was prompted to enter my archive log files which doesn't exist.
--I pointed to my redo02.log file and it solved the problem. You may have to try all --the 3 Redo log files one by one

ALTER DATABASE OPEN RESETLOGS;

ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\ORADATA\SUPPDB\TEMP01.DBF'
SIZE 271581184 REUSE AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;

Oracle: How to find number of redo log switch

Following query outputs redo log switch/day broken down to each hour. If you see too many switches at any point of time, think about increasing redo log size

select to_char(first_time,'YYYY-MM-DD') day,
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'00',1,0)),'99')
"00",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'01',1,0)),'99')
"01",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'02',1,0)),'99')
"02",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'03',1,0)),'99')
"03",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'04',1,0)),'99')
"04",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'05',1,0)),'99')
"05",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'06',1,0)),'99')
"06",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'07',1,0)),'99')
"07",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'08',1,0)),'99')
"08",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'09',1,0)),'99')
"09",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'10',1,0)),'99')
"10",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'11',1,0)),'99')
"11",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'12',1,0)),'99')
"12",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'13',1,0)),'99')
"13",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'14',1,0)),'99')
"14",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'15',1,0)),'99')
"15",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'16',1,0)),'99')
"16",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'17',1,0)),'99')
"17",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'18',1,0)),'99')
"18",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'19',1,0)),'99')
"19",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'20',1,0)),'99')
"20",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'21',1,0)),'99')
"21",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'22',1,0)),'99')
"22",
to_char(sum(decode(substr(to_char(first_time,'HH24'),1,2),'23',1,0)),'99')
"23"
from v$log_history
group by to_char(first_time,'YYYY-MM-DD')

Oracle: How to increase redo log size

Below is list of sql's to increase the size of redo log files. In order to increase the size, you need to create new redo log files, check point the old one and drop the old one. Replace the path to match your redo location.

In this example, I am creating 3 more redolog files with group number 4,5 and 6 and dropping the old ones.

Log in as sys

Select * from v$log --- Get max group no

Max log files you can have in your database can be got from

Select * from v$controlfile_record_section
where type ='REDO LOG'

Alter database add logfile group 4 'c:\oracle\oradata\orcldb\redo04.log'
size 250M reuse;
Alter database add logfile group 5 'c:\oracle\oradata\orcldb\redo05.log'
size 250M reuse;
Alter database add logfile group 6 'g:\oracle\oradata\orcldb\redo06.log'
size 250M reuse;

Now switch log file 3 times to make these new files online.

Alter system switch logfile;
Alter system switch logfile;
Alter system switch logfile;

force checkpoint to write all contents to data files.

Alter system checkpoint;

Drop the old log files.
Alter database drop logfile group 1;
Alter database drop logfile group 2;
Alter database drop logfile group 3;