Few Questions

[samirshah]

Hi guys,

I have following 3 questions for you experts.

1. I have taken Full Database export with COMPRESS=Y option. Now I want to import back in the same database.

Do I have to drop all schemas first before import?

2. I have taken Full Database export of X database with COMPRESS=Y option. Now I want to import it on Y database.

Do I have to drop all schemas of Y database first before
import?

What if table structure of Y is different than that of X?

3. How can I increase the size of my Redo log files. I have three groups with two members each.

4. Is there any IS_NUMBER function in Oracle. If not, what is the workaround to check if the supplied constant is a NUMBER?

Thanks in Adv.

Sam

[Shestakov]

4. Is there any IS_NUMBER function in Oracle. If not, what is the workaround to check if the supplied constant is a NUMBER?

------------------------------------------------
listing
------------------------------------------------
create or replace
function is_digit(p_str varchar2) return boolean is
n number;
begin
n := to_number(p_str);
return true;
exception when VALUE_ERROR then return false;
when OTHERS then
raise_application_error(-20100, 'Undefined convertion error in IS_DIGIT');
end;
/

create or replace
function is_digit_char(p_str varchar2) return varchar2 is
n number;
begin
n := to_number(p_str);
return 'true';
exception when VALUE_ERROR then return 'false';
when OTHERS then
raise_application_error(-20100, 'Undefined convertion error in IS_DIGIT');
end;
/

3. About REDOLOG:

1 step :
create new redo log group with new filesizes

ALTER DATABASE ADD LOGFILE GROUP 5 ('..../new_g_log1.log', '..../new_g_log1.log') SIZE XXXXK;

ALTER DATABASE ADD LOGFILE GROUP 6 ('..../new_g_log1.log', '..../new_g_log1.log') SIZE XXXXK;

2. drop old redo log groups

[samdba]

Originally posted by Shestakov
4. Is there any IS_NUMBER function in Oracle. If not, what is the workaround to check if the supplied constant is a NUMBER?

------------------------------------------------
listing
------------------------------------------------
create or replace
function is_digit(p_str varchar2) return boolean is
n number;
begin
n := to_number(p_str);
return true;
exception when VALUE_ERROR then return false;
when OTHERS then
raise_application_error(-20100, 'Undefined convertion error in IS_DIGIT');
end;
/

create or replace
function is_digit_char(p_str varchar2) return varchar2 is
n number;
begin
n := to_number(p_str);
return 'true';
exception when VALUE_ERROR then return 'false';
when OTHERS then
raise_application_error(-20100, 'Undefined convertion error in IS_DIGIT');
end;
/

3. About REDOLOG:

1 step :
create new redo log group with new filesizes

ALTER DATABASE ADD LOGFILE GROUP 5 ('..../new_g_log1.log', '..../new_g_log1.log') SIZE XXXXK;

ALTER DATABASE ADD LOGFILE GROUP 6 ('..../new_g_log1.log', '..../new_g_log1.log') SIZE XXXXK;

2. drop old redo log groups

Hi Shestakov,

Can I then create new log file names with old file name?

Thanks,

Sam

[marist89]

Sure, as long as they have been deleted from the OS.

[samdba]

Originally posted by marist89
Sure, as long as they have been deleted from the OS.

Jeff,

But first, I have to add new redo group, members and then drop the old one so how do I create them later with old names?

[marist89]

Why not drop a group, delete the file, create a group?

SQL> @c:\scripts\watch_logfile

    GROUP# MEMBER                                           MB STATUS
---------- ---------------------------------------- ---------- ----------------
         1 E:\ORACLE\ORADATA\NT817\REDO01.LOG                1 INACTIVE
         2 E:\ORACLE\ORADATA\NT817\REDO02.LOG                1 INACTIVE
         3 E:\ORACLE\ORADATA\NT817\REDO03.LOG                1 CURRENT

SQL> alter database drop logfile group1;
alter database drop logfile group1
                            *
ERROR at line 1:
ORA-02236: invalid file name


SQL> alter database drop logfile group 1;

Database altered.

SQL> host del e:\oracle\oradata\nt817\redo01.log

SQL> alter database add logfile group 1 ('e:\oracle\oradata\nt817\redo01.log')
  2  size 4M;

Database altered.

SQL> @c:\scripts\watch_logfile

    GROUP# MEMBER                                           MB STATUS
---------- ---------------------------------------- ---------- ----------------
         1 E:\ORACLE\ORADATA\NT817\REDO01.LOG                4 UNUSED
         2 E:\ORACLE\ORADATA\NT817\REDO02.LOG                1 INACTIVE
         3 E:\ORACLE\ORADATA\NT817\REDO03.LOG                1 CURRENT

SQL>