Posts Tagged ‘tablespace’

apps-fnd-01564 oracle error 1653 in summit others

December 22nd, 2019, posted in Oracle Queries
Share

CAUSE :

Typically occurs when writing a new record to oracle database, but there is not enough space to write it. The data file in oracle tablespace called APPS_TS_TX_DATA should be set to AUTOEXTEND. If it is not, Oracle will not auto-increase the size of the datafile, and will raise the above error.

apps-fnd-01564 oracle error 1653 in summit others,apps-fnd-01564,oracle error,imam dba,dba imam,immam dba,dba immam,

SOLUTION :

If all the reports end in COMPLETE – NORMAL, means its work.

If not than follow below steps.

Run a Concurrent Manager Recovery :

1. Please stop Concurrent managers by adcmctl.sh.

2. If not stopped, please abort managers by adcmctl.sh.

adcmctl.sh abort apps/****

3. Please kill if a process remains.

4. relink all (ADADMIN)

5. Please retry Concurrent Manager Recovery.

Navigate :
Oracle Applications Manager > Concurrent Managers OR Concurrent Requests > Site Map > Diagnostics and Repair > Concurrent Manager Recovery.

OR

SOLUTION :

WARNING: This procedure should be performed by your ORACLE Database Administrator.
The SYSTEM user should have required privileges to perform this task.

Enable Autoextend on the APPS_TS_TX_DATA tablespace by executing the following Oracle PL/SQL command:

SQL > ALTER DATABASE DATAFILE '/u01/ORACLE/ORADATA/APPS_TS_TX_DATA.ORA' AUTOEXTEND ON NEXT 3072K

 

Share
read more no comments

Renaming Data Files in 11g database

September 22nd, 2019, posted in Oracle Queries
Share

Renaming datafile (.dbf) rename in 11g

 

Rename Datafiles in Single Tablespace (Database Open Mode) :

Caution : Backup your tablespace Before you will do some change in your tablespace.

Please find below steps to rename datafile.

We can use the alter tablespace rename datafile command,
but the tablespace most be offline and you must re-name at OS level command the data file while the tablespace is offline:

Step: 1

[ora@oracle ~]$ sqlplus / as sysdba

SQL> ALTER TABLESPACE SYSAUX OFFLINE NORMAL;
Tablespace altered.

SQL> !



Step: 2

Rename undo07.dbf to sysaux08.dbf

[ora@oracle ~]$ cd /u01/db/apps_st/data/

[ora@oracle data]$ ls -lrt|grep undo07.dbf
-rw-r----- 1 oraqa dba 3221233664 Aug 9 11:32 undo07.dbf

[ora@oracle data]$ chmod 755 undo07.dbf

[ora@oracle data]$ mv undo07.dbf sysaux08.dbf

[ora@oracle data]$ ls -lrt|grep undo07.dbf

[ora@oracle data]$ ls -lrt|grep sysaux08.dbf
-rwxr-xr-x 1 oraqa dba 3221233664 Aug 9 11:32 sysaux08.dbf


Step: 3

[ora@oracle data]$ sqlplus / as sysdba

SQL> ALTER TABLESPACE SYSAUX RENAME DATAFILE '/u01/db/apps_st/data/undo07.dbf' to '/u01/db/apps_st/data/sysaux08.dbf';

Tablespace altered.



Step: 4

SQL> ALTER TABLESPACE SYSAUX ONLINE;

Tablespace altered.


Steps: 5

Verify tablespace status.

SQL> SELECT status
FROM dba_tablespaces
WHERE tablespace_name = 'SYSAUX';

STATUS
---------
ONLINE

Verify datafile name.

SQL> select TABLESPACE_NAME,FILE_NAME from dba_data_files where TABLESPACE_NAME = 'SYSAUX';

TABLESPACE_NAME-------------FILE_NAME
--------------------------------------------------------------------------------
SYSAUX /u01/db/apps_st/data/sysaux08.dbf

 

Rename datafile undo07.dbf to sysaux08.dbf completed.

Share
read more no comments

Oracle : What is tablespace and types of tablespace

January 13th, 2013, posted in Oracle Queries
Share
Tablespace :
The tablespace is a logical storage units and database divided into one or more logical storage units. The tablespace is not visible in the data file system.
Syntax :
Create tablespace “tablespace name”;
Example :
SQL> Create tablespace test datafile
2 ‘C:/ORACLE/PRODUCT/10.2.0/oradata/orcl/test.dbf’
3 size 10M;
We can see all tablespace through this command:
SQL> desc dba_tablespaces;
Output :

oracle Create tablespace


SQL> Select Tablespace_Name,Contents From dba_tablespaces;

Output :

oracle Create tablespace

Types of tablespace in Oracle:

  • System Tablespace
  • Sysaux Tablespace
  • undo Tablespace
  • Temporary Tablespace
  • User/Normal Tablespace
  • Example Tablespace

1. System Tablespace:
The system tablespace is create by default. The system tablespace is a permanent tablespace. The system tablespace owner is sys and within all data dictionary. The all data dictionary tables is store in the datafiles. The system tablespace is always online when the database is open.

2. Sysaux Tablespace:
The sysaux tablespace is a permanent tablespace. The sysaux tablespace is a new feature of the Oracle 10g. The sysaux tablespace is a system tablespace and auxiliary tablespace. The sysaux tablespace is a optional database components.

3. Undo Tablespace:
 The undo tablespace is show always from undotbs1 name in the database. The undo tablespace is contains the undo data after any updating, deleting of database object. The undo tablespace is used for rolling back transaction.

Create Undo Tablespace:

SQL> Create undo tablespace undo01 datafile

2 ‘C:ORACLEPRODUCT10.2.0ORADATAORCLundo01.dbf’

3 size 100M;

4. Temporary Tablespace:
 The temporary tablespace show always from temp name in the database. The temporary tablespace is store in tempfile. All temporary operation are perform in temporary tables. The temporary tablespace is used for the shorting temporary tables.

For example:

SQL> Select * from employee order by salary desc;

Create Temporary Tablespace:

SQL> Create temporary tablespace temp3 tempfile

2 ‘C:ORACLEPRODUCT10.2.0ORADATAORCLTEMP03.dbf’

3 size 10M;

5. User/Normal Tablespace:
 The user and normal tablespace is a permanent tablespace. All user tablespace information store/show in the user tablespace.

Create User Tablespace:

SQL> Create user tablespace user02 userfile

2 ‘C:ORACLEPRODUCT10.2.0ORADATAORCLuser02.dbf’

3 size 10M;

6. Example Tablespace:
We will create database through the Database Configuration Assistant when we will select sample schemas option. This sample schemas is create a example tablespace. The sample schemas provide a comman platform for examples.

*********************************************************************************************************************
Note : Please not do make backups before using these queries and also confirm them yourself or by aother means as
 well.

*********************************************************************************************************************

Share
read more no comments