Skip to content

Archive for

Sep 26

ORA-23515: materialized views and/or their indices exist in the tablespace

When I am trying to drop the tablespace, I am getting below error

ORA-23515: materialized views and/or their indices exist in the tablespace

Solution :

Step 1: Find the materialized views and/or their indices

SQL> set head off
SQL> set newpage none
SQL> set pagesize 9999
SQL> spool drop_materialized_view.sql

SQL>select ‘drop materialized view ‘||owner||’.’||name||’ PRESERVE TABLE;’ from dba_registered_snapshots where name in (select table_name from dba_tables where tablespace_name =’XXXXXXX’);

SQL>spool off

Step 2:  Drop the materialized views and/or their indices

SQL>@drop_materialized_view.sql

Step 3: Drop the tablespace

SQL>drop tablespace XXXXXXX including contents and datafiles;

Tablespace dropped.

Sep 26

flashback database point in time recovery where is my relocated datafile available in 12c

Its new location

Documentaion says

“A flashback operation does not relocate a moved data file to its previous location. If you move a data file online from one location to another and later flash back the database to a point in time before the move, then the data file remains in the new location, but the contents of the data file are changed to the contents at the time specified in the flashback.”

Let see the example :

Step 1:

22:18:14 SQL> select current_scn from v$database;

CURRENT_SCN
———–
6270275

22:18:26 SQL> create table emp(empno number) tablespace users;

Table created.

22:18:55 SQL> insert into emp values(000001);

1 row created.

22:19:11 SQL> commit;

Commit complete.

22:19:14 SQL> select current_scn from v$database;

CURRENT_SCN
———–
6270313

Step 2:

22:19:51 SQL> select name from v$datafile;

NAME
——————————————————————————–
/premia/app/12c/oracle/oradata/ora12c/system01.dbf
/premia/app/12c/oracle/oradata/ora12c/sysaux01.dbf
/premia/app/12c/oracle/oradata/ora12c/undotbs01.dbf
/premia/app/12c/oracle/oradata/ora12c/users01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/tools01.dbf

Step 3:

22:19:59 SQL> alter database move datafile ‘/premia/app/12c/oracle/oradata/ora12c/users01.dbf’ to ‘/premia/app/12c/oracle/product/dbhome_1/dbs/users01.dbf’;

Database altered.

Step 4:

22:20:29 SQL> select name from v$datafile;

NAME
——————————————————————————–
/premia/app/12c/oracle/oradata/ora12c/system01.dbf
/premia/app/12c/oracle/oradata/ora12c/sysaux01.dbf
/premia/app/12c/oracle/oradata/ora12c/undotbs01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/users01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/tools01.dbf

Step 5:

22:20:39 SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
22:22:57 SQL> startup mount
ORACLE instance started.

Total System Global Area 5016387584 bytes
Fixed Size 5293992 bytes
Variable Size 1258293336 bytes
Database Buffers 3741319168 bytes
Redo Buffers 11481088 bytes
Database mounted.
22:23:09 SQL> !rman target /

Recovery Manager: Release 12.1.0.2.0 – Production on Sat Sep 26 22:23:23 2015

Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.

connected to target database: ORA12C (DBID=264834751, not open)

RMAN> FLASHBACK DATABASE TO SCN 6270275;

 

RMAN> alter database open resetlogs;

RMAN> select name from v$datafile;

NAME
——————————————————————————–

/premia/app/12c/oracle/oradata/ora12c/system01.dbf
/premia/app/12c/oracle/oradata/ora12c/sysaux01.dbf
/premia/app/12c/oracle/oradata/ora12c/undotbs01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/users01.dbf
/premia/app/12c/oracle/product/dbhome_1/dbs/tools01.dbf

 

Sep 7

APP-RG-09518 : An error occurred while creating a database link

When creating a database link in General Ledger FSG Transfer Program to copy FSGs from one instance to another the following error may be encountered:

APP-RG-09518 : An error occurred while creating a database link

Create a dblink like below

GL –> setup–> database links

Database name : SID

Description : null

connect string : (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=ipaddress)(PORT=1586))(CONNECT_DATA=(service_name=xxxxxxx)(instance_name=xxxxxx)))

domain name : aceins.com  (Ensure that there is no dash (-) in the domain name and the domain is not blank)

apps : apps username

apps : apps password

Still issues, please see the note

Support note : FSG: Transfer Program APP-RG-09518 : An Error Occurred While Creating A Database Link (Doc ID 1124483.1)