Node id does not exist for the current application server id

Issue:

After completing cloning procedure, you generally may across the following issue. "Node id does not exist for the current application server id" or

May be we created a disaster database for testing purpose due to this also you will get node id error.

There is a mismatch between server_id in the  fnd_nodes table and appl_server_id in the dbc file and hence are getting this  "node id does not exist" error.

When trying to open Oracle Forms, all forms error out with the below message:

“Node id does not exist for the current application server id”.

Solution:

Do a select on the server_id in the fnd_nodes table.  Here is an example of what it could look like:

server_id=EA99227ED75CFE4EE030149077C4515496138833635529486962342698410274

Do a search on APPL_SERVER_ID in the .dbc file.  It could show something like:

APPL_SERVER_ID=EA99220924EF823FE030149077C450C096138833635529486962342698410274

compare both the id if any mismatch or if both same also you can run the below script it will solve the issue.

Run adgendbc.sh to config the apps tier dbc file with the correct id from the database instance.

cd $INST_TOP/admin/install/
sh adgendbc.sh

Completes with:

Updating Server Security Authentication
AUTHENTICATION SECURE executed
successfully –
/u01/PROD/inst/apps/xxx/appl/fnd/12.0.0/secure/PROD.dbc
adgendbc.sh
exiting with status 0
ERRORCODE = 0 ERRORCODE_END

solution steps:

1.First stop the application.
2.Run adgendbc.sh script to configure apps tier dbc file with the correct id from the database instance.
3.Start the application and test your issue.

Best Regards.

How to delete expired or obsolete archvielog files.

RMAN provided two commands for deleting archive log.

1.delete expired archivelog
2.backup archivelog all delete input

These command can delete archive logs based on the input provided. There were two things that RMAN made sure every time it attempted to delete an archive log. The first is that specified archive log is backed up to all the locations specified under log_archived_dest_n parameters. The value under these parameters can be flash recovery area or non flash recovery area. Second is whether the archive logs to be deleted are obsolete or not. This in turn depends on your backup retention policy. An archive redo log is considered obsolete if it is not required in any recovery scenario.

Using RMAN we can remove the old archvielogs or old backup file using report obsolete or delete obsolete by setting rman rman retention policy
RMAN> show all;
using target database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name DEV are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE RMAN OUTPUT TO KEEP FOR 7 DAYS; # defaultCONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'E:\APP\USER\PRODUCT\12.1.0\DBHOME_1\DATA
BASE\SNCFDEV.ORA'; # default
RMAN>
Above Configuration show  RMAN  retention policy is 1, based on this retention policy only report obsolete or delete obsolete will work.

Manually we can remove the expired archivelog using below 2 options:

1.delete .. expired archivelog;
2.backup archivelog all delete input;

1. delete .. expired archivelog;

By using this command we can remove the old archivelog manually.

Using OS command remove the old archivelog files from the archivelog log location.
Move to archivelog location then issue (to find the achivelog location , connect sqlplus / as sysdba )
execute below command to find the archive log.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     46
Next log sequence to archive   48
Current log sequence           48
SQL> 
Archvielog location using Flash revoerey area,

SQL> show parameter recover
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      E:\app\user\fast_recovery_area
db_recovery_file_dest_size           big integer 6930M
db_unrecoverable_scn_tracking        boolean     TRUE
recovery_parallelism                 integer     0
SQL>

ARchivelog location is "E:\app\user\fast_recovery_area\dev\ARCHIVELOG".
move to this location and issue command below with 2 options.
Recommended options is move the old archivelog file to some other directory wait for 2 days then remove that files. or directly reomove like below
rm -rf  *.arc or
rm -rf  (specified archivelog files)

Then issue the command connecting RMAN.

C:\Users\user>rman target /
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Oct 11 17:19:15 2017
Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.
connected to target database: DEV (DBID=4027486540)
RMAN>
RMAN> list expired archivelog all;
using target database control file instead of recovery catalog
specification does not match any archived log in the repository
RMAN>
Before crosscheck its shows like this.
RMAN> crosscheck archivelog all;

It will crosscheck the archivelog files with RMAN repository deleted files status changed to expired in RMAN repository.

RMAN> list expired archivelog all;
List of Archived Log Copies for database with db_unique_name DEV
=====================================================================
Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
39      1    48      X 11-OCT-17
        Name: E:\APP\USER\FAST_RECOVERY_AREA\DEV\ARCHIVELOG\2017_10_11\O1_MF_1_
8_DXW2ZZFW_.ARC
RMAN>

Then issue below command to remove from the RMAN repository.

RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=129 device type=DISK
List of Archived Log Copies for database with db_unique_name DEV
=====================================================================
Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
39      1    48      X 11-OCT-17
        Name: E:\APP\USER\FAST_RECOVERY_AREA\DEV\ARCHIVELOG\2017_10_11\O1_MF_1_4
8_DXW2ZZFW_.ARC
Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=E:\APP\USER\FAST_RECOVERY_AREA\DEV\ARCHIVELOG\2017_10_11\
O1_MF_1_48_DXW2ZZFW_.ARC RECID=39 STAMP=957116888
Deleted 1 EXPIRED objects
RMAN>
RMAN> delete noprompt expired archivelog all;

this command will remove the files without prompting the Yes or no.

2.Backup archivelog all delete input:

Above command will remove the all archivelog file after taking the backup of all archvielog available in current RMAN repository.

We have options also

It will take backup 2 days before archivelog file and then remove it.
RMAN> backup archivelog until time 'sysdate-2' delete input;

Crosscheck commands:

To crosscheck all backups use:
RMAN> CROSSCHECK BACKUP;

To list any expired backups detected by the CROSSCHECK command use:
RMAN> LIST EXPIRED BACKUP;

To delete any expired backups detected by the CROSSCHECK command use:
RMAN> DELETE EXPIRED BACKUP;

To crosscheck all archive logs use:
RMAN> CROSSCHECK ARCHIVELOG ALL;

To list all expired archive logs detected by the CROSSCHECK command use:
RMAN> LIST EXPIRED ARCHIVELOG ALL;

To delete all expired archive logs detected by the CROSSCHECK command use:
RMAN> DELETE EXPIRED ARCHIVELOG ALL;

To crosscheck all datafile image copies use:
RMAN> CROSSCHECK DATAFILECOPY ALL;

To list expired datafile copies use:
RMAN> LIST EXPIRED DATAFILECOPY ALL;

To delete expired datafile copies use:
RMAN> DELETE EXPIRED DATAFILECOPY ALL;

To crosscheck all backups of the USERS tablespace use:
RMAN> CROSSCHECK BACKUP OF TABLESPACE USERS;

To list expired backups of the USERS tablespace:
RMAN> LIST EXPIRED BACKUP OF TABLESPACE USERS;

To delete expired backups of the USERS tablespace:
RMAN> DELETE EXPIRED BACKUP OF TABLESPACE USERS;

Best Regards.

Fatal: Could not find Unzip. At this time only Native UnZip 5.X is supported(RC-20200: )

while cloning oracle application database using cold backup iam getting below error:

  0% completed       RC-20200: Fatal: Could not find Unzip. At this time only Native UnZip 5.X is supported.
Please make sure you have UnZip 5.X in your path and try again...

ERROR while running Apply..

This error due to different unzip version of source and target,

First check the unzip version on source and target,

Cause:
Source:
unzip -version
caution:  both -n and -o specified; ignoring -o
UnZip 5.52 of 28 February 2005, by Info-ZIP.  Maintained by C. Spieler.  Send
bug reports using http://www.info-zip.org/zip-bug.html; see README for details.

Target:
unzip -version
caution:  both -n and -o specified; ignoring -o
UnZip 6.0 of 28 February 2005, by Info-ZIP.  Maintained by C. Spieler.  Send
bug reports using http://www.info-zip.org/zip-bug.html; see README for details.

Solution:
Copy the unzip file from source to target or local system
/usr/bin/unzip
/data/db/tech_st/11.2.0/bin/unzip

MyOracle Support offers a solution with a patch:
RC-20200: Fatal: Could not find Unzip. At this time only Native UnZip 5.X is supported [ID 1410514.1]

Check the unzip version now on target:

unzip -version
caution:  both -n and -o specified; ignoring -o
UnZip 5.52 of 28 February 2005, by Info-ZIP.  Maintained by C. Spieler.  Send
bug reports using http://www.info-zip.org/zip-bug.html; see README for details.

After solving the unzip version you proceed the cloning steps (if you proceed you might get error)

[AutoConfig Error Report]
The following report lists errors AutoConfig encountered during each
phase of its execution.  Errors are grouped by directory and phase.
The report format is:
          

  [INSTANTIATE PHASE]
  AutoConfig could not successfully instantiate the following files:
    Directory: /data/db/tech_st/11.2.0/appsutil/install/PRODAR_ebsdbprd
      adcrdb.sh               INSTE8
   
  AutoConfig is exiting with status 1
  
  RC-50014: Fatal: Execution of AutoConfig was failed
Raised by oracle.apps.ad.clone.ApplyDBTechStack

Cause:

/data/db/tech_st/11.2.0/appsutil/template/adcrdb.sh  file in deleted from the first failed cloning process becuase of this you getting above error,

Solution:

Copy the specfic file "/data/db/tech_st/11.2.0/appsutil/template/adcrdb.sh" from the target database or copy the template folder from the target system.

Run again the cloning steps:

perl adcfgclone.pl dbTier

cloning completed successful.

Best Regards.

Changing archivelog location:

Choose the FRA for archivelog location,

While creating database if we enabled archivelog without create FRA oracle placed archive logs in a default directory of $ORACLE_HOME/dbs

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            $ORACLE_HOME/dbs/archive
Oldest online log sequence     45
Next log sequence to archive   46
Current log sequence           46
SQL>

Suppose if we created FRA and then choose FRA for archive log location,

For example so many facing issues on arechivelog location because while executing command like below,

alter system set  log_archive_dest_1='LOCATION=DB_RECOVERY_FILE_DEST'

SQL> sho parameter log_archive_dest_1

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      LOCATION=DB_RECOVERY_FILE_DEST
log_archive_dest_10                  string
SQL>

It will not take FRA for archivelog location, it will use old location.

if we set location "LOCATION=USE_DB_RECOVERY_FILE_DEST" then only it will choose FRA for archivelog location.

alter system set  log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST'

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     42
Next log sequence to archive   44
Current log sequence           44


SQL> select destination from V$ARCHIVE_DEST;

SQL> show parameter recovery

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string      /data/DEV/fast_recovery_area
db_recovery_file_dest_size           big integer 6930M
recovery_parallelism                 integer     0
SQL>


If you want to change the different archivelog location:


SQL> alter system set log_archive_dest_1='LOCATION=/u20/oracle/data1';

System altered.

Best Regards

Recover SYS account password!!!!

While installing/creating oracle database , we will provide the schema password for all user is same like "oracle",, due to some reasons we forgot to noted down the password given for schema users while installing oracle DB.

After that iam connecting to sys schema to startup the database its says login denied(password incorrect),due to some reason iam not possible to use the option 
"connect / as sysdba".

ERROR:
 sqlplus / as sysdba
 SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 30 15:39:05 2017
 Copyright (c) 1982, 2005, Oracle.  All rights reserved.
 ERROR:
 ORA-01031: insufficient privileges

we have lost/forgot newly created oracle database sys account password,To recover sys account password we have several options to recover sys user password.

We can recover sys account password using following options:

Use a another SYSDBA user account
Use orapwd utlity
Rebuild the schema in a new database

Normally you can reset the schema user password:

To recover a password in Oracle, simply connect under command line mode on the server: 

#sqlplus /nolog      
SQL>conn / as sysdba      
SQL>alter user Username identified by PASSWORD;

But iam not able to use this option,due to some issues.so iam orapwd utility to reset the sys account password.

ORAPWD utility:

Using this orapwd utility we can reset the sys account password.

Password file should be under <orahome>\database\PWD<SID>.ora. 
Delete it and run the Oracle password utility from the command prompt:

ORAPWD file=<oracle_home>\database\PWD<SID>.ora password="password" entries="however many"

The <password> is your new sys password. After you log in as sys you can change it and create new passwords for system. 

Now we can able to connect sys account using newly created password.
Another options to reset the sys account password,

Optionally if you have another sysdba account reset the sys account password using this account.

Rebuild the schema to new database:

Create a new database with known sys password then export/import the schema to new database.

Best Regards.

Issue on Opening ORACLE EBS FORMS:

While openings oracle forms on internet explorer we will get some issue like java version or compatablity or trusted site issues.

If you got below error while opening oracle ebs forms on internet explorer.

If you got above error on opening oracle forms first please check the correct  java version installed on your pc,for example if you are using EBS 12.1.3 java  jdk6 version is ok , but if you upgraded java version JDK7 please check the corresponding patch applied on EBS.

If you installed correct version or related version of java on your machine, Please add the EBS url to trusted site on internet explorer setting, Then try to check this steps,

After you got this forms error , Go to internet explorer setting,

click Setting - select Compatability view setting

you will get above page with you EBS url domain name (like "xxx.com") then click add the url and click close.
Else if not chosen anything on the website name , please enter the website name ("xxx.com"hostname of the server running application) then click add and close

Then reload the browser or automatically it will reload after added the url in compatablity mode.

Now if you open the EBS form you will get below popup options to choose select "run this time" options,

If its again popup came please choose the option "run this time".Now form will be opened.

 ISSUE RESOLVED

Best Regards

SQL Developer backspace/delete not working

We regularly working on sql developer without  any issues, suddenly we got an issue with sql developer backupsace button and delete button is now working,.

We restarted the sql developer and tried its same issue.

Becuase of this issue  no need to unistall / reinstall sqldeveloper,

To resolve the problem you have to go to:

Sql  Developer (old version) 

 Tools -> Preferences -> Accelerators -> Load Preset -> Default -> OK

Sql Developer(New version 3.2):

Open sqldeveloper -> Tools - > Preferences - > Shortcut Keys -> More Actions -> Load keyboard schema -> Default - > OK.

Now its working well.

Best Regards.

Find DB tier details from R 12 application login.

How to find the oracle R12 application database running  hostname, port number,service name from oracle application front end .

If R12 oracle application installed on multi-node we will find the database server details from application front end ..

Iam have crossed this situation, we having only access to oracle application and have access to database apps user only but we dnt know the database tier details someone is supporting for database.

while executing scripts given wrong password for apps user, now apps user account is locked.

We need to unlock the apps user account for that we need to connect to database system user.

To find the database tier details , login to application "http://.......com:8007/" sysadmin/password.

In Home page - click on "About this page" available at left side bottom - Click on "Page Context" tab in about oracle application page.

Here you will get the database tier details,

hostname :
port number:1527.
SID   :DEV

Now we connected to the database using this details and unlocked the apps user account. FYI. If system/sys account password is Default as "manager" then only you can connect to database. Best Regards.

Unlock R12 APPS user account...

Unlocking APPS user account:

Check the status of the user account using sql query ,connect system/sys account and check the account status.

SQL> select username,account_status from dba_users where username='APPS';

USERNAME                  ACCOUNT_STATUS
------------------------------ --------------------------------
APPS                           LOCKED

Account status locked,

• Using below query you can check the "FALIED LOGIN ATTEMPTS" 
• You have given wrong password more than 10 times so account locked,

SQL> select RESOURCE_NAME,RESOURCE_TYPE,LIMIT from dba_profiles where profile='DEFAULT';  
                                                                             
RESOURCE_NAME                    RESOURCE LIMIT
-------------------------------- -------- ----------------------------------------
COMPOSITE_LIMIT                  KERNEL   UNLIMITED
SESSIONS_PER_USER                KERNEL   UNLIMITED
CPU_PER_SESSION                  KERNEL   UNLIMITED
CPU_PER_CALL                     KERNEL   UNLIMITED
LOGICAL_READS_PER_SESSION        KERNEL   UNLIMITED
LOGICAL_READS_PER_CALL           KERNEL   UNLIMITED
IDLE_TIME                        KERNEL   UNLIMITED
CONNECT_TIME                     KERNEL   UNLIMITED
PRIVATE_SGA                      KERNEL   UNLIMITED
FAILED_LOGIN_ATTEMPTS            PASSWORD 10
PASSWORD_LIFE_TIME               PASSWORD UNLIMITED

RESOURCE_NAME                    RESOURCE LIMIT
-------------------------------- -------- ----------------------------------------
PASSWORD_REUSE_TIME              PASSWORD UNLIMITED
PASSWORD_REUSE_MAX               PASSWORD UNLIMITED
PASSWORD_VERIFY_FUNCTION         PASSWORD NULL
PASSWORD_LOCK_TIME               PASSWORD UNLIMITED
PASSWORD_GRACE_TIME              PASSWORD UNLIMITED

16 rows selected.

To unlock the APPS user account,

[oradev@erp2 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.7.0 - Production on Thu Aug 10 15:35:10 2017

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

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>alter user apps account unlock;

user altered.

SQL> select username,account_status from dba_users where username='APPS';

USERNAME                       ACCOUNT_STATUS

------------------------------ --------------------------------

APPS                           OPEN

We can follow below solutions also:

You can change the "FALIED LOGIN ATTEMPTS" to unlimited, to create a new profile with unlimited attempt.and assingn the profile to apps users

SQL> CREATE PROFILE APPS_DEFAULT LIMIT

 COMPOSITE_LIMIT UNLIMITED

 SESSIONS_PER_USER UNLIMITED

 CPU_PER_SESSION UNLIMITED

 CPU_PER_CALL UNLIMITED

 LOGICAL_READS_PER_SESSION UNLIMITED

 LOGICAL_READS_PER_CALL UNLIMITED

 IDLE_TIME UNLIMITED

 CONNECT_TIME UNLIMITED

 PRIVATE_SGA UNLIMITED

 FAILED_LOGIN_ATTEMPTS UNLIMITED

 PASSWORD_LIFE_TIME UNLIMITED

 PASSWORD_REUSE_TIME UNLIMITED

 PASSWORD_REUSE_MAX UNLIMITED

 PASSWORD_VERIFY_FUNCTION NULL

 PASSWORD_LOCK_TIME UNLIMITED

 PASSWORD_GRACE_TIME UNLIMITED;

Profile created.

Assign the newly created profile to the user as default profile.

SQL> ALTER USER appusr PROFILE appusr_default;

Unlock the user account:

SQL> alter user apps account unlock;

user altered.

Best Regards.

CREATING TABLESPACE

Creating and Increase the size of tablespace by adding datafiles.(AUTOEXTEND is disabled):

Normally ,we can the increase the tablespace size by adding the datafile to the tablesapce.

Create test tablespace with auntoextend is off,

SQL> create tablespace test datafile 'E:\APP\USER\ORADATA\DEV\test01.DBF' size 1
0M;

Tablespace created.

Auto extend is off:

SQL> select tablespace_name,autoextensible from dba_data_files;

TABLESPACE_NAME                AUT
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
UNDOTBS1                       YES
USERS                          YES
TEST                           NO

Query the created tablespace :

SQL> select * from dba_data_files where tablespace_name ='TEST';

FILE_NAME
--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
E:\APP\USER\ORADATA\DEV\TEST01.DBF
        10 TEST                             10485760       1280 AVAILABLE
          10 NO           0          0            0    9437184        1152
ONLINE

Adding the datafile by increasing the tablespace,

SQL> alter tablespace test add datafile 'E:\APP\USER\ORADATA\DEV\TEST02.DBF' siz
e 20M;

Tablespace altered.


SQL> select * from dba_data_files where tablespace_name ='TEST';

FILE_NAME
--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
E:\APP\USER\ORADATA\DEV\TEST01.DBF
        10 TEST                             10485760       1280 AVAILABLE
          10 NO           0          0            0    9437184        1152
ONLINE


FILE_NAME
--------------------------------------------------------------------------------

   FILE_ID TABLESPACE_NAME                     BYTES     BLOCKS STATUS
---------- ------------------------------ ---------- ---------- ---------
RELATIVE_FNO AUT   MAXBYTES  MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS
------------ --- ---------- ---------- ------------ ---------- -----------
ONLINE_
-------
E:\APP\USER\ORADATA\DEV\TEST02.DBF
        11 TEST                             20971520       2560 AVAILABLE
          11 NO           0          0            0   19922944        2432
ONLINE

Check the free size of the tablespace

SQL> select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
  2  from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space
  3         from dba_free_space
  4         group by tablespace_name) a,
  5        (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
  6         from dba_data_files
  7         group by tablespace_name) b
  8  where a.tablespace_name(+)=b.tablespace_name;

TABLESPACE_NAME                    SIZEMB     FREEMB
------------------------------ ---------- ----------
SYSAUX                                870      45.25
UNDOTBS1                              725     697.31
USERS                                   5       3.63
TEST                                   30         28
SYSTEM                                780        9.5

Adding the datafile to the tablespace.

SQL> alter tablespace test add datafile 'E:\APP\USER\ORADATA\DEV\TEST03.DBF' siz
e 40M;

Tablespace altered.

Now check the size after adding the datafile to tablespace:

SQL> select b.tablespace_name, tbs_size SizeMb, a.free_space FreeMb
  2  from  (select tablespace_name, round(sum(bytes)/1024/1024 ,2) as free_space

  3         from dba_free_space
  4         group by tablespace_name) a,
  5        (select tablespace_name, sum(bytes)/1024/1024 as tbs_size
  6         from dba_data_files
  7         group by tablespace_name) b
  8  where a.tablespace_name(+)=b.tablespace_name;

TABLESPACE_NAME                    SIZEMB     FREEMB
------------------------------ ---------- ----------
SYSAUX                                870      45.25
UNDOTBS1                              725     697.31
USERS                                   5       3.63
TEST                                   70         67
SYSTEM                                780        9.5


SQL> select tablespace_name,autoextensible from dba_data_files;

TABLESPACE_NAME                AUT
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
UNDOTBS1                       YES
USERS                          YES
TEST                           NO
TEST                           NO
TEST                           NO

7 rows selected.

Best Regards.

How to enable tablespace autoextend on

Enabling and Disabling Automatic Extension for a Datafile:

You can create datafiles or alter existing datafiles so that they automatically increase in size when more space is needed in the database. The file size increases in specified increments up to a specified maximum.

Setting your datafiles to extend automatically provides these advantages:

• Reduces the need for immediate intervention when a tablespace runs out of space
• Ensures applications will not halt or be suspended because of failures to allocate extents

To determine whether a datafile is auto-extensible, query the DBA_DATA_FILES view and examine the AUTOEXTENSIBLE column.

SQL> select tablespace_name ,autoextensible from dba_data_files;

TABLESPACE_NAME                AUT
------------------------------ ---
SYSTEM                         YES
SYSAUX                         YES
UNDOTBS1                       YES
USERS                          YES

You can specify automatic file extension by specifying an AUTOEXTEND ON clause when you create datafiles using the following SQL statements:

• CREATE DATABASE
• ALTER DATABASE
• CREATE TABLESPACE
• ALTER TABLESPACE

You can enable or disable automatic file extension for existing datafiles, or manually resize a datafile, using the ALTER DATABASE statement. For a bigfile tablespace, you are able to perform these operations using the ALTER TABLESPACE statement.

The following example enables automatic extension for a datafile added to the users tablespace:

ALTER TABLESPACE users
    ADD DATAFILE '/u02/oracle/rbdb1/users03.dbf' SIZE 10M
      AUTOEXTEND ON
      NEXT 512K
      MAXSIZE 250M;

The value of NEXT is the minimum size of the increments added to the file when it extends. The value of MAXSIZE is the maximum size to which the file can automatically extend.

The next example disables the automatic extension for the datafile.

ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf' 
    AUTOEXTEND OFF;

Manually Resizing a Datafile

You can manually increase or decrease the size of a datafile using the ALTER DATABASE statement. This enables you to add more space to your database without adding more datafiles. This is beneficial if you are concerned about reaching the maximum number of datafiles allowed in your database.

For a bigfile tablespace you can use the ALTER TABLESPACE statement to resize a datafile. You are not allowed to add a datafile to a bigfile tablespace.

Manually reducing the sizes of datafiles enables you to reclaim unused space in the database. This is useful for correcting errors in estimates of space requirements.

In the next example, assume that the datafile /u02/oracle/rbdb1/users03.dbf has extended up to 250M. However, because its tablespace now stores smaller objects, the datafile can be reduced in size.

The following statement decreases the size of datafile /u02/oracle/rbdb1/users03.dbf:
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/users03.dbf'
   RESIZE 250M;

Best Regards.

CREATE TABLESPACE WITH AUTO Extend ON

Create a permanent tablespace.

Syntax:

   CREATE [UNDO] TABLESPACE tablespace_name
      DATAFILE Datafile_Options Storage_Options;

The Autoextend Maxsize clause will default to UNLIMITED if no value is specified.

Storage_Options:

    DEFAULT [COMPRESS|NOCOMPRESS] STORAGE storage_clause
    MINIMUM EXTENT int {K|M}
    BLOCKSIZE int K
    LOGGING | NOLOGGING
    FORCE LOGGING
    ONLINE | OFFLINE
    PERMANENT | TEMPORARY
    EXTENT MANAGEMENT {DICTIONARY |
       LOCAL {AUTOALLOCATE | UNIFORM [SIZE int K | M]} }
    SEGMENT SPACE MANAGEMENT {MANUAL | AUTO}

Examples

-- With Autoextend:

CREATE TABLESPACE ts_mydemo DATAFILE
'/data/ts_mydemo01.dbf' SIZE 50M,
'/data/ts_mydemo02.dbf' SIZE 64M
logging
autoextend on
next 32m maxsize 2048m
extent management local;

-- With specified datafile sizes:

CREATE TABLESPACE ts_myapp DATAFILE
'/data/ts_myapp01.dbf' SIZE 200M,
'/data/ts_myapp02.dbf' SIZE 500M
logging
autoextend off
extent management local;

-- Undo tablespace

CREATE UNDO TABLESPACE ts_undo01 DATAFILE
'/data/ts_undo01.dbf SIZE 50000M REUSE
autoextend on

RETENTION NOGUARANTEE;

Retention guarantee can also be enabled for UNDO tablespaces with the option RETENTION GUARANTEE, this should be used with caution - it can easily cause updates to fail due to a lack of space in the undo tablespace.

Best Regards.