Friday, December 20, 2013

Changing archivelog destination in STANBY side

If you did not set log_archive_dest_* or db_recovery_file_dest in stanby database`s parameter file during creation of stanbdy, primary`s archivelogs will be transferred to default location of standby database.
This is $ORACLE_HOME/dbs (in linux/unix).

SQL> select status,instance_name,database_role from v$database,v$instance;

STATUS     INSTANCE_NAME    DATABASE_ROLE
------------ ---------------- ----------------
OPEN     STND      PHYSICAL STANDBY

Disconnect recovery managed

SQL> alter database recover managed standby database cancel;

Database altered.

Check current location of archivelogs

[oracle@fcdbdb_stby trace]$ ls -lrt /u01/app/oracle/product/11.2.0.3/db_1/dbs/
total 133444
-rw-r--r--. 1 oracle oinstall     2851 May 15  2009 init.ora
-rw-r-----. 1 oracle oinstall       24 Dec 19 14:43 lkSTND
-rw-r--r--. 1 oracle oinstall     1520 Dec 19 14:55 initSTND.ora
-rw-r-----. 1 oracle oinstall     1536 Dec 19 14:56 orapwSTND
-rw-r-----. 1 oracle oinstall  1141248 Dec 19 15:02 arch1_11_834576721.dbf
-rw-r-----. 1 oracle oinstall  1459200 Dec 19 15:02 arch1_12_834576721.dbf
-rw-r-----. 1 oracle oinstall    93696 Dec 19 15:04 arch1_13_834576721.dbf
-rw-r-----. 1 oracle oinstall    15872 Dec 19 15:04 arch1_14_834576721.dbf
-rw-r-----. 1 oracle oinstall  4966912 Dec 19 16:15 arch1_15_834576721.dbf
-rw-r-----. 1 oracle oinstall    83456 Dec 19 16:17 arch1_16_834576721.dbf
-rw-r-----. 1 oracle oinstall 40787968 Dec 19 22:00 arch1_17_834576721.dbf
-rw-r-----. 1 oracle oinstall 37803008 Dec 20 02:26 arch1_18_834576721.dbf
-rw-r-----. 1 oracle oinstall 38546432 Dec 20 11:01 arch1_19_834576721.dbf
-rw-r-----. 1 oracle oinstall  1888768 Dec 20 11:37 arch1_20_834576721.dbf
-rw-r-----. 1 oracle oinstall    66560 Dec 20 11:38 arch1_21_834576721.dbf

Check db_recovery_file_desc`s value

SQL> show parameters db_rec

NAME     TYPE VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest     string
db_recovery_file_dest_size     big integer 4122M
db_recycle_cache_size     big integer 0

Set db_recovery_file_dest

SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area' scope=both;

System altered.


Connect recovery managed

SQL> alter database recover managed standby database disconnect from session;

Database altered.


On Primary side enforce archivelog

SQL> alter system switch logfile;

System altered.

And check in Standby side :

[oracle@fcdbdb_stby trace]$ ls -lrt /u01/app/oracle/fast_recovery_area/STND/archivelog/2013_12_20/
total 12240
-rw-r-----. 1 oracle oinstall    51200 Dec 20 11:40 o1_mf_1_22_9c7x32kp_.arc

As you see, staring 22th archivelog file transferred to new destination.

You can do it using dgmgrl utiliy.



Wednesday, December 18, 2013

What is ID: oracle.install.commons.util.exception.DefaultErrorAdvisor:37 and how to fix it

Today while installing Oracle 11g r2 (11.2.0.3) on OEL 6 I faced below error.

Looking up something in the OraInstall Log.

[oracle@fcdbdb database]$ cd /tmp/OraInstall2013-12-18_04-47-35PM
[oracle@fcdbdb OraInstall2013-12-18_04-47-35PM]$ ls -ltr
total 36
-rwxr-x---. 1 oracle oinstall 5578 Dec 18 16:47 command_output_15591
drwxr-x---. 2 oracle oinstall 4096 Dec 18 16:47 images
drwxr-x---. 6 oracle oinstall 4096 Dec 18 16:47 jdk
drwxr-x---. 3 oracle oinstall 4096 Dec 18 16:47 diagnostics
drwxr-x---. 3 oracle oinstall 4096 Dec 18 16:47 srvm
drwxr-x---. 8 oracle oinstall 4096 Dec 18 16:47 oui
drwxrwx---. 5 oracle oinstall 4096 Dec 18 16:47 ext
-rw-------. 1 oracle oinstall    0 Dec 18 16:47 oraInstall2013-12-18_04-47-35PM.out
-rw-------. 1 oracle oinstall 1269 Dec 18 16:47 oraInstall2013-12-18_04-47-35PM.err

Here it is oraInstall2013-12-18_04-47-35PM.err

[oracle@fcdbdb OraInstall2013-12-18_04-47-35PM]$ more oraInstall2013-12-18_04-47-35PM.err
---# Begin Stacktrace #---------------------------
ID: oracle.install.commons.util.exception.DefaultErrorAdvisor:37
oracle.cluster.verification.VerificationException: fcdbdb: fcdbdb
at oracle.cluster.verification.ClusterVerification.<init>(ClusterVerification.java:231)
at oracle.cluster.verification.ClusterVerification.getInstance(ClusterVerification.java:333)
at oracle.install.driver.oui.OUISetupDriver.load(OUISetupDriver.java:419)
at oracle.install.ivw.db.driver.DBSetupDriver.load(DBSetupDriver.java:190)
at oracle.install.commons.base.driver.common.Installer.run(Installer.java:299)
at oracle.install.ivw.common.util.OracleInstaller.run(OracleInstaller.java:106)
at oracle.install.ivw.db.driver.DBInstaller.run(DBInstaller.java:136)
at oracle.install.commons.util.Application.startup(Application.java:891)
at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:165)
at oracle.install.commons.flow.FlowApplication.startup(FlowApplication.java:182)
at oracle.install.commons.base.driver.common.Installer.startup(Installer.java:348)
at oracle.install.ivw.db.driver.DBInstaller.startup(DBInstaller.java:124)
at oracle.install.ivw.db.driver.DBInstaller.main(DBInstaller.java:155)

---# End Stacktrace #-----------------------------

The cause of error most likely in that that dns does not return ip the server for the name.
Let`s check.

more /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4

edit hostname

vi /etc/hosts

127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
XXXXXXXXX fcdbdb

now, re-run runInstaller again.

Tuesday, December 17, 2013

Moving & renaming datafile

Sometimes we need to rename or move data files.

My db version: 11.2.0.3

One of the easy way is below:

1. Identify which data file you will move. In my case I will move datafile which belong FCATDEV121 tablespace.

select a.tablespace_name, a.status, file_name from dba_tablespaces a, dba_data_files b
where a.tablespace_name=b.tablespace_name
order by 1;

TABLESPACE_NAME       STATUS FILE_NAME
==========================================================================================================================
SYSAUX       ONLINE /u01/app/oracle/oradata/FCDBT/sysaux01.dbf
SYSTEM       ONLINE /u01/app/oracle/oradata/FCDBT/system01.dbf
UNDOTBS1       ONLINE /u01/app/oracle/oradata/FCDBT/undotbs01.dbf
USERS       ONLINE /u01/app/oracle/oradata/FCDBT/users01.dbf
FCATDEV121       ONLINE /home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCATDEV121_FNO-5_01orokb8



2. Take tablespace to offline mode, copy using OS command (cp, mv), rename datafile and take tablespace to online mode

SQL> ALTER TABLESPACE FCAT_DEV21 offline;

Tablespace altered.



SQL> host
[oracle@fc-db-tst1 FCDBT_EXPTPS]$ mv /home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf  /u01/app/oracle/oradata/FCDBT/FCAT_DEV21_01.dbf
[oracle@fc-db-tst1 FCDBT_EXPTPS]$ exit
exit

SQL> ALTER TABLESPACE FCAT_DEV21 RENAME DATAFILE '/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf'  TO '/u01/app/oracle/oradata/FCDBT/FCAT_DEV21_01.dbf';

Tablespace altered.

SQL> ALTER TABLESPACE FCAT_DEV21 online;


3. Check

SQL> select a.tablespace_name, a.status, file_name from dba_tablespaces a, dba_data_files b
where a.tablespace_name=b.tablespace_name
order by 1;  2    3

TABLESPACE_NAME       STATUS FILE_NAME
------------------------------ --------- --------------------------------------------------------------FCAT_DEV21       ONLINE /u01/app/oracle/oradata/FCDBT/FCAT_DEV21_01.dbf
SYSAUX       ONLINE /u01/app/oracle/oradata/FCDBT/sysaux01.dbf
SYSTEM       ONLINE /u01/app/oracle/oradata/FCDBT/system01.dbf
UNDOTBS1       ONLINE /u01/app/oracle/oradata/FCDBT/undotbs01.dbf
USERS       ONLINE /u01/app/oracle/oradata/FCDBT/users01.dbf

Starting 12.c we can move datafiles in online mode!

ALTER DATABASE MOVE DATAFILE
'/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf'
TO '/u01/app/oracle/oradata/FCDBT/FCAT_DEV21_01.dbf';

Moving production databases across platforms

Today I requested moving 3 databases under Aix to Linux. I am going to demonstrate only one database in this tutorial.

So, let`s start. Selected SID name is FCDBT.

Source

-bash-4.2$ uname -a
AIX localhost 1 6 00F697214C00


Target

[oracle@fc-db-tst1 FCDBT_EXPTPS]$ uname -a
Linux fc-db-tst1 2.6.39-400.17.1.el6uek.x86_64 #1 SMP Fri Feb 22 18:16:18 PST 2013 x86_64 x86_64 x86_64 GNU/Linux


1. create same name DB on target.

ex: dbca

Note: nls_language should be same as source

SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               AL32UTF8
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.3.0

20 rows selected.


AL32UTF8


Source:

SQL> set linesize 300
SQL> SELECT A.platform_id, A.platform_name, B.endian_format
FROM   v$database A, v$transportable_platform B
WHERE  B.platform_id (+) = A.platform_id;  2    3

PLATFORM_ID PLATFORM_NAME                                                                                         ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------------- --------------
          6 AIX-Based Systems (64-bit)                                                                            Big

SQL>


Target:

SQL> set linesize 300
SQL> SELECT A.platform_id, A.platform_name, B.endian_format
FROM   v$database A, v$transportable_platform B
WHERE  B.platform_id (+) = A.platform_id;  2    3

PLATFORM_ID PLATFORM_NAME  ENDIAN_FORMAT
----------- ----------------------------------------------------------------------------------------------------- --------------
13 Linux x86 64-bit  Little

SQL>


2. select users and identify tablespaces to be transported

select username, account_status, default_tablespace from dba_users
--where .....
order by created;

USERNAME                       ACCOUNT_STATUS                   DEFAULT_TABLESPACE
------------------------------ -------------------------------- ------------------------------
FCDBAPPDEV121                  OPEN                             FCATDEV121
FCDBADMINDEV121                OPEN                             FCATDEV121
TMPDBA                         OPEN                             SYSTEM
FCDBAPP_DEV21                  OPEN                             FCAT_DEV21
FCDBADMIN_DEV21                OPEN                             FCAT_DEV21
FCDBB001_DEV21                 OPEN                             FCAT_DEV21



SQL> SELECT   tablespace_name, segment_type, COUNT(*),
           SUM (bytes) / 1024 / 1024 mb
FROM     dba_segments
WHERE    owner in ('FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21') 
GROUP BY tablespace_name, segment_type
ORDER BY 1, 2 DESC;  

TABLESPACE_NAME                SEGMENT_TYPE         COUNT(*)         MB
------------------------------ ------------------ ---------- ----------
FCATDEV121                     TABLE                     209     57.375
FCATDEV121                     LOBSEGMENT                 24     44.375
FCATDEV121                     LOBINDEX                   24        1.5
FCATDEV121                     INDEX                     253    37.4375
FCAT_DEV21                     TABLE                     230    80.5625
FCAT_DEV21                     LOBSEGMENT                 33    131.375
FCAT_DEV21                     LOBINDEX                   33     2.4375
FCAT_DEV21                     INDEX                     266      40.25
TBS_OFFLINE_DEV21              TABLE                       9      16.75
TBS_OFFLINE_DEV21              LOBSEGMENT                 12    88.6875
TBS_OFFLINE_DEV21              LOBINDEX                   12        .75

11 rows selected.





3.create OS dir (in this case I will create same dircetories on both host)


-bash-4.2$ pwd
/home/oracle
-bash-4.2$ mkdir FCDBT_EXPTPS

-bash-4.2$ cd FCDBT_EXPTPS




Ran the following on the source database to verify there were no self-containment problems:


SQL> BEGIN
SYS.dbms_tts.transport_set_check('FCATDEV121,FCAT_DEV21,TBS_OFFLINE_DEV21', incl_constraints=>TRUE, full_check=>FALSE);
END;
/  2    3    4  

PL/SQL procedure successfully completed.

SQL> SELECT * FROM SYS.transport_set_violations;

no rows selected



SQL> SELECT   B.data_type, COUNT(*)
FROM     dba_tables A, dba_tab_columns B
WHERE    A.owner  in ('FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21') 
AND      B.owner = A.owner
AND      B.table_name = A.table_name
GR  2    3    4    5    6  OUP BY B.data_type
ORDER BY B.data_type;  7  

DATA_TYPE                                                                                                    COUNT(*)
---------------------------------------------------------------------------------------------------------- ----------
BLOB                                                                                                              135
CHAR                                                                                                             1240
CLOB                                                                                                                4
DATE                                                                                                              774
FLOAT                                                                                                               2
LONG                                                                                                                8
LONG RAW                                                                                                           10
NUMBER                                                                                                           1505
TIMESTAMP(1)                                                                                                       19
TIMESTAMP(6)                                                                                                       34
VARCHAR2                                                                                                         6691

DATA_TYPE                                                                                                    COUNT(*)
---------------------------------------------------------------------------------------------------------- ----------
XMLTYPE                                                                                                             4

12 rows selected.

SQL> 


SQL> SELECT B.owner, B.table_name
FROM   dba_xml_tables A, all_all_tables B
WHERE  B.owner = A.owner
AND    B.table_name = A.table_name
AND    B.tablespace_name IN ('FCATDEV121','FCAT_DEV21','TBS_OFFLINE_DEV21');SQL>   2    3    4    5  

no rows selected

SQL> 


SQL> SELECT   owner, COUNT(*)
FROM     dba_segments
WHERE    tablespace_name IN ('FCATDEV121','FCAT_DEV21','TBS_OFFLINE_DEV21')
GROUP BY owner;  2    3    4  

OWNER                            COUNT(*)
------------------------------ ----------
FCDBADMIN_DEV21                       585
FCDBADMINDEV121                       510
FCDBB001_DEV21                         10

SQL> 





Next we verify that this schema did not yet exist on the target database:

[oracle@fc-db-tst1 admin]$ export ORACLE_SID=FCDBT
[oracle@fc-db-tst1 admin]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Tue Dec 17 16:29:19 2013

Copyright (c) 1982, 2011, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> 
SQL> select name from v$database;

NAME
---------
FCDBT

SQL> SELECT username
FROM   dba_users
WHERE  username in ( 'FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21');

no rows selected

SQL> 





Now we have to create missing schemas on the target database and grant necessary system privileges:

SQL> CREATE USER FCDBAPPDEV121 IDENTIFIED BY FCDBAPPDEV121;
SQL> CREATE USER FCDBADMINDEV121 IDENTIFIED BY FCDBADMINDEV121;
SQL> CREATE USER TMPDBA IDENTIFIED BY TMPDBA;
SQL> CREATE USER FCDBAPP_DEV21 IDENTIFIED BY FCDBAPP_DEV21;
SQL> CREATE USER FCDBADMIN_DEV21 IDENTIFIED BY FCDBADMIN_DEV21;
SQL> CREATE USER FCDBB001_DEV21 IDENTIFIED BY FCDBB001_DEV21;




SQL> GRANT connect, resource, create library TO FCDBAPPDEV121;
SQL> GRANT connect, resource, create library TO FCDBADMINDEV121;
SQL> GRANT connect, resource, create library TO TMPDBA;
SQL> GRANT connect, resource, create library TO FCDBAPP_DEV21;
SQL> GRANT connect, resource, create library TO FCDBADMIN_DEV21;
SQL> GRANT connect, resource, create library TO FCDBB001_DEV21;


Check exist tablespaces on TARGET

SQL> SELECT tablespace_name
FROM   dba_tablespaces
WHERE  tablespace_name IN ('FCATDEV121','FCAT_DEV21','TBS_OFFLINE_DEV21');
  2    3  
no rows selected



ON SOURCE db make tablespace READ ONLY


Make Tablespaces Read-only in Source Database

SQL> select name from v$database;

NAME
---------
FCDBT

SQL>

SQL> ALTER TABLESPACE FCATDEV121 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE FCAT_DEV21 READ ONLY;

Tablespace altered.

SQL> ALTER TABLESPACE TBS_OFFLINE_DEV21 READ ONLY;

Tablespace altered.

SQL> 


Extract Metadata from Source Database


exp "'/ as sysdba'" file=FCDBT_tabsp.dmp transport_tablespace=y tablespaces=FCATDEV121,FCAT_DEV21,TBS_OFFLINE_DEV21



Copy Files to Target Server and Convert if Necessary


-bash-4.2$ echo $ORACLE_SID 
FCDBT
-bash-4.2$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Tue Dec 17 16:46:04 2013

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

connected to target database: FCDBT (DBID=1987414639)

RMAN> CONVERT TABLESPACE FCATDEV121,FCAT_DEV21,TBS_OFFLINE_DEV21
TO PLATFORM 'Linux x86 64-bit'
FORMAT='/home/oracle/FCDBT_EXPTPS/%U';2> 3> 

Starting conversion at source at 17-DEC-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=154 device type=DISK
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00005 name=/u01/app/oracle/oradata/FCDBT/datafile/o1_mf_fcatdev1_94vkgg0m_.dbf
converted datafile=/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCATDEV121_FNO-5_01orokb8
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00011 name=/u01/app/oracle/oradata/FCDBT/datafile/o1_mf_fcat_dev_94vkl66p_.dbf
converted datafile=/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile file number=00014 name=/u01/app/oracle/oradata/FCDBT/datafile/o1_mf_tbs_offl_94vkn2n1_.dbf
converted datafile=/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-TBS_OFFLINE_DEV21_FNO-14_03orokbm
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished conversion at source at 17-DEC-13

RMAN> 




now copy



-bash-4.2$ scp -r /home/oracle/FCDBT_EXPTPS/* 192.168.10.168:/home/oracle/FCDBT_EXPTPS
oracle@192.168.10.168's password: 
FCDBT_tabsp.dmp                                                                                                                                              100% 7852KB   7.7MB/s   00:00    
data_D-FCDBT_I-1987414639_TS-FCATDEV121_FNO-5_01orokb8                                                                                                       100%  500MB  45.5MB/s   00:11    
data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf                                                                                                      100%  500MB  45.5MB/s   00:11    
data_D-FCDBT_I-1987414639_TS-TBS_OFFLINE_DEV21_FNO-14_03orokbm                                                                                               100%  160MB  53.3MB/s   00:03    
-bash-4.2$ 


change back read write source db`s tablespace




ALTER TABLESPACE FCATDEV121 READ WRITE;
ALTER TABLESPACE FCAT_DEV21 READ WRITE;
ALTER TABLESPACE TBS_OFFLINE_DEV21 READ WRITE;



Import Metadata into Target Database



imp "'/ as sysdba'" file=FCDBT_tabsp.dmp transport_tablespace=y \
datafiles=/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCATDEV121_FNO-5_01orokb8, \
/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-FCAT_DEV21_FNO-11_02orokbf, \
/home/oracle/FCDBT_EXPTPS/data_D-FCDBT_I-1987414639_TS-TBS_OFFLINE_DEV21_FNO-14_03orokbm



Checking on target database


SELECT   owner, COUNT(*)
FROM     dba_segments
WHERE    tablespace_name IN ('FCATDEV121','FCAT_DEV21','TBS_OFFLINE_DEV21')
GROUP BY owner; 




SELECT   B.data_type, COUNT(*)
FROM     dba_tables A, dba_tab_columns B
WHERE    A.owner  in ('FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21') 
AND      B.owner = A.owner
AND      B.table_name = A.table_name
GROUP BY B.data_type
ORDER BY B.data_type;


SELECT   tablespace_name, segment_type, COUNT(*),
           SUM (bytes) / 1024 / 1024 mb
FROM     dba_segments
WHERE    owner in ('FCDBAPPDEV121','FCDBADMINDEV121','TMPDBA','FCDBAPP_DEV21','FCDBADMIN_DEV21','FCDBB001_DEV21') 
GROUP BY tablespace_name, segment_type
ORDER BY 1, 2 DESC;  



SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME       STATUS
------------------------------ ---------
SYSTEM       ONLINE
SYSAUX       ONLINE
UNDOTBS1       ONLINE
TEMP       ONLINE
USERS       ONLINE
FCATDEV121       READ ONLY
FCAT_DEV21       READ ONLY
TBS_OFFLINE_DEV21       READ ONLY

8 rows selected.

SQL> 


Alter status to Read Write


SQL> ALTER TABLESPACE FCATDEV121 READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE FCAT_DEV21 READ WRITE;

Tablespace altered.

SQL> ALTER TABLESPACE TBS_OFFLINE_DEV21 READ WRITE;

Tablespace altered.

SQL> select tablespace_name, status from dba_tablespaces;

TABLESPACE_NAME       STATUS
------------------------------ ---------
SYSTEM       ONLINE
SYSAUX       ONLINE
UNDOTBS1       ONLINE
TEMP       ONLINE
USERS       ONLINE
FCATDEV121       ONLINE
FCAT_DEV21       ONLINE
TBS_OFFLINE_DEV21       ONLINE

8 rows selected.

SQL> 

That is all. :)

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...