R12 Check Writer XML process failed.

R12 Check Writer XML process failed

Issue:


When attempting to run the Check Writer XML process, the following error occurs.

stat_low = 86
stat_high = 0
emsg:was terminated by signal 6
/test/appltest/apps/apps_st/appl/pay/12.0.0/bin/PYUGEN
Program exited with status -1


Cause:


This issue is generally encountered after cloning. Main reason for this is wrong value of the DBC_FILE parameter in View "PAY_ACTION_PARAMETERS".


Use the following SQL Query to find the Value:

select length(parameter_value), Length(trim(parameter_value)), parameter_value from pay_action_parameters where parameter_name = 'DBC_FILE';



Solution:



1. Run the following query to confirm the extra space(s) in the DBC file parameter value

Query:    

select length(parameter_value), Length(trim(parameter_value)), parameter_value from pay_action_parameters where parameter_name = 'DBC_FILE';



2. Modify the DBC file parameter value in PAY_ACTION_PARAMETERS.

Query:

UPDATE "APPS"."PAY_ACTION_PARAMETERS" SET PARAMETER_VALUE = '<Correct DBC Path>/FileName.dbc';



3. Retest the issue.



4. Confirm the result.

R12 Login Page issue: Java.Lang.OutOfMemoryError: Java Heap Space Error

 R12 Login Page issue: Java.Lang.OutOfMemoryError: Java Heap Space Error

Issue:

We were facing issue in our R12 (12.1.3) E-business suite environment, where all of sudden users were not able to get the login page but existing users were able to use forms/navigate between forms, but not able to use JSP pages. After investigation, we came to know the root cause of the issue.

Log File Location: $INST_TOP/logs/ora/10.1.3/opmn/oacore_default_group_1/oacorestd.err


14/08/12 12:21:51 java.lang.OutOfMemoryError: Java heap space

14/08/12 12:23:05 java.lang.OutOfMemoryError: GC overhead limit exceeded

Solution:

Reset the Java Heap Size in your R12 application server.

Step 1: Identify the maximum Heap Size you can set in your server.

applprod@server:~$ java -mx4096m -version

Invalid maximum heap size: -Xmx4096m

The specified size exceeds the maximum representable size.

Could not create the Java virtual machine.

applprod@server:~$ java -mx3048m -version

java version "1.6.0_10"

Java(TM) SE Runtime Environment (build 1.6.0_10-b33)

Java HotSpot(TM) Server VM (build 11.0-b15, mixed mode)

From this output, we can say maximum 3 GB Java Heap Size can be set in our server.

Step 2: Edit opmn.xml file.

Location : $INST_TOP/ora/10.1.3/opmn/conf/

Edit opmn.xml file for the following parameters (Xms & Xmx)

Search for string Xms or Xmx or module-id=”OC4J”

This search should lead you to below location

‘<‘process-type id=”oacore” module-id=”OC4J” status=”enabled” working-dir=”$ORACLE_HOME/j2ee/home”‘>’
‘<‘category id=”start-parameters”‘>’
‘<‘data id=”java-options” value=”-server -verbose:gc -Xmx512M -Xms128M ……]

The default value for Maximum (-Xmx) and Minimum (-Xms) heap sizes are 512M and 128M respectively.

Again here you have options, you can set both Xms and Xmx has the same value as Xmx if you feel all your sessoins require higher memory or set a lower value for Xms and the maximum value for Xmx. Dont forget to change the values under ‘<‘category id=”stop-parameters”‘>’

opmn.xml also contains jvm configurations for other components – oafm & forms.

Step 3: Edit oc4j.properties file.

Location : ($INST_TOP/ora/10.1.3/j2ee/oacore/config)

This step is optional since we had already made changes in opmn.xml but there is no harm in making the change here. This step will come handy for troubleshooting specific components of Oracle viz., configurator, iSupplier or any other option which heavily utilizes/consumes CPU/memory.

Search for string Xms or Xmx or wrapper.

Option 1: If you find any of the above parameters change the values corresponding to the value you had  mentioned in opmn.xml or even more than that, as long as you dont exceed the maximum heap size limit.

Option 2: If you DO NOT find any of the above parameters, then make an entry like this, under the heading “Java Object Cache Configuration Parameters”

wrapper.bin.parameters=-Xms[Value]M -Xmx[Value]M -XX:NewSize=256M -XX:MaxNewSize=256M

Step 4: Edit Applications Context file

vi $CONTEXT_FILE

Location: $INST_TOP/appl/admin/SID_hostname.xml

search for string s_oacore_jvm_start_options

Change Xms and Xmx value. Repeat the same step for parameter s_oacore_jvm_stop_options.

Create tar file

Create tar file

tar -zvcf file.tar.gz file1 file2 dir1 dir2
eg :-  tar -zcvf CLN_12_1_3_AR.tar.gz CLN_12_1_3_AR


>> Details of tar :-
tar -tvf backup.tar.gz

>> UNTAR in current location
tar -xvf <file>.tar.gz

>> UNTAR in one particular directory
tar -xvf <file>.tar.gz -C /Oracle/test2
eg :-  tar -xvf test_bkp-31Dec13.tar.gz -C /Oracle/test_bkp2

Responsibilities not showing for user

Responsibilities not showing for user

We added one responsibility to a user and wasn't not showing up.

Solution:

Run program 'Workflow Directory Services User/Role Validation' with following parameters.

Note:
Optionally, we can also run 'Synchronize WF LOCAL tables' after this request completes.

11g Background Processes

11g Background Processes


New background processes in 11g. As per Oracle documentation there are 56 new background processes added in 11g release 1

Some Important background Process

MMAN - this process is responsible for ASMM in 10g and AMM in 11g  which manages memory allocation to SGA and PGA

RCBG - this background process is responsible for processing data into server result cache

DIAG - In 11g we have a single location for all the trace files, alert log and other diagnostic files. DIAG is the process which performs  diagnostic dumps and executes oradebug commands

DIA0 – responsible for hang detection and deadlock resoultion

DBRM – Database resource manager is responsible for setting plans to users and all other database resource management activities

EMNC – Event Monitor Coordinator will coordinate with event management and notification activity

FBDA – Flashback Data Archiver process is responsible for all flashback related actions in 11g database

GEN0 - General task execution process which performs required tasks

SMCo – Space management coordinator executes various space management tasks like space reclaiming, allocation etc. It uses slave processes Wnnn whenever required

VKTM – Virtual keeper of time is  responsible for keeping track of the wall-clock time and used as a reference-time counter

Change priority of concurrent request in R12

Change priority of concurrent request in R12

Select "View Details". Change the Priority from default "50". Change it to "10" for high priority. [1 for highest and 99 for lowest].

How to change Priority of concurrent request in User level?

Change the value of profile option "Concurrent Request Priority" in User level to make him or her a high or low priority user.

Alternatively you can change the priority of a "Concurrent Request" in define concurrent request screen.

GRANT REVOKE PRIVILEGES

GRANT REVOKE PRIVILEGES 

This Oracle tutorial explains how to grant and revoke privileges in Oracle with syntax and examples.

DESCRIPTION

You can GRANT and REVOKE privileges on various database objects in Oracle. We'll first look at how to grant and revoke privileges on tables and then how to grant and revoke privileges on functions and procedures in Oracle.

GRANT PRIVILEGES ON TABLE

You can grant users various privileges to tables. These privileges can be any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.
Syntax

The syntax for granting privileges on a table in Oracle is:

GRANT privileges ON object TO user;

privileges can be any of the following values:

Privilege	Description
SELECT	Ability to perform SELECT statements on the table.
INSERT	Ability to perform INSERT statements on the table.
UPDATE	Ability to perform UPDATE statements on the table.
DELETE	Ability to perform DELETE statements on the table.
REFERENCES	Ability to create a constraint that refers to the table.
ALTER	Ability to perform ALTER TABLE statements to change the table definition.
INDEX	Ability to create an index on the table with the create index statement.
ALL	All privileges on table.

Object is the name of the database object that you are granting privileges for. In the case of granting privileges on a table, this would be the table name.

user is the name of the user that will be granted these privileges.
Example

Let's look at some examples of how to grant privileges on tables in Oracle.

For example, if you wanted to grant SELECT, INSERT, UPDATE, and DELETE privileges on a table called suppliers to a user name smithj, you would run the following GRANT statement:

GRANT SELECT, INSERT, UPDATE, DELETE ON suppliers TO smithj;

You can also use the ALL keyword to indicate that you wish ALL permissions to be granted for a user named smithj. For example: 

GRANT ALL ON suppliers TO smithj;

If you wanted to grant only SELECT access on your table to all users, you could grant the privileges to the public keyword. For example:

GRANT SELECT ON suppliers TO public;

REVOKE PRIVILEGES ON TABLE

Once you have granted privileges, you may need to revoke some or all of these privileges. To do this, you can run a revoke command. You can revoke any combination of SELECT, INSERT, UPDATE, DELETE, REFERENCES, ALTER, INDEX, or ALL.
Syntax

The syntax for revoking privileges on a table in Oracle is:

REVOKE privileges ON object FROM user;

privileges can be any of the following values:

Privilege	Description
SELECT	Ability to perform SELECT statements on the table.
INSERT	Ability to perform INSERT statements on the table.
UPDATE	Ability to perform UPDATE statements on the table.
DELETE	Ability to perform DELETE statements on the table.
REFERENCES	Ability to create a constraint that refers to the table.
ALTER	Ability to perform ALTER TABLE statements to change the table definition.
INDEX	Ability to create an index on the table with the create index statement.
ALL	All privileges on table.

Object is the name of the database object that you are revoking privileges for. In the case of revoking privileges on a table, this would be the table name.

user is the name of the user that will have these privileges revoked.
 
Example
Let's look at some examples of how to revoke privileges on tables in Oracle.

For example, if you wanted to revoke DELETE privileges on a table called suppliers from a user named anderson, you would run the following REVOKE statement:

REVOKE DELETE ON suppliers FROM anderson;

If you wanted to revoke ALL privileges on a table for a user named anderson, you could use the ALL keyword as follows:

REVOKE ALL ON suppliers FROM anderson;

If you had granted ALL privileges to public (all users) on the suppliers table and you wanted to revoke these privileges, you could run the following REVOKE statement:

REVOKE ALL ON suppliers FROM public;

GRANT PRIVILEGES ON FUNCTIONS/PROCEDURES

When dealing with functions and procedures, you can grant users the ability to EXECUTE these functions and procedures.
Syntax

The syntax for granting EXECUTE privileges on a function/procedure in Oracle is:

GRANT EXECUTE ON object TO user;

EXECUTE means the following:

Privilege : EXECUTE
Description : Ability to compile the function/procedure.
Ability to execute the function/procedure directly.
 

Object is the name of the database object that you are granting privileges for. In the case of granting EXECUTE privileges on a function or procedure, this would be the function name or the procedure name.

user is the name of the user that will be granted the EXECUTE privileges.
Example

Let's look at some examples of how to grant EXECUTE privileges on a function or procedure in Oracle.

For example, if you had a function called Find_Value and you wanted to grant EXECUTE access to the user named smithj, you would run the following GRANT statement:

GRANT EXECUTE ON Find_Value TO smithj;

If you wanted to grant ALL users the ability to EXECUTE this function, you would run the following GRANT statement:

GRANT EXECUTE ON Find_Value TO public;

REVOKE PRIVILEGES ON FUNCTIONS/PROCEDURES

Once you have granted EXECUTE privileges on a function or procedure, you may need to REVOKE these privileges from a user. To do this, you can execute a REVOKE command.
Syntax

The syntax for the revoking privileges on a function or procedure in Oracle is:

REVOKE EXECUTE ON object FROM user;

Privilege : EXECUTE
Description : 
EXECUTE Ability to compile the function/procedure.
Ability to execute the function/procedure directly.
 
Object is the name of the database object that you are revoking privileges for. In the case of revoking EXECUTE privileges on a function or procedure, this would be the function name or the procedure name.

user is the name of the user that will be revoked the EXECUTE privileges.
Example
Let's look at some examples of how to revoke EXECUTE privileges on a function or procedure in Oracle.

If you wanted to revoke EXECUTE privileges on a function called Find_Value from a user named anderson, you would run the following REVOKE statement:

REVOKE execute ON Find_Value FROM anderson;

If you had granted EXECUTE privileges to public (all users) on the function called Find_Value and you wanted to revoke these EXECUTE privileges, you could run the following REVOKE statement:

REVOKE EXECUTE ON Find_Value FROM public;

Startup and Shutdown stages

Startup and Shutdown stages

Before starting the instance we need to set instance name to be opened at os prompt
$export ORACLE_SID=<sid>

Sid = instance name

SQL> startup
ORACLE instance started.

Total System Global Area  251658240 bytes
Fixed Size                   788368 bytes
Variable Size             145750128 bytes
Database Buffers          104857600 bytes
Redo Buffers                 262144 bytes
Database mounted.
Database opened.

When Oracle is trying to open your database, it goes through three distinct stages, and each of these is listed in the startup output listed previously. These stages are:
The Startup (nomount) Stage
During the nomount stage, Oracle first opens and reads the initialization parameter file (init.ora) to see how the database is configured
* Startup (nomount)
There are some types of Oracle recovery operations that require the database to be in nomount stage.
SQL> startup nomount

The Mount Stage

When the startup command enters the mount stage, it opens and reads the control fil
SQL> startup mount
If you have already started the database instance with the startup nomount command, you might change it from the nomount to mount startup stage using the alter database command:
SQL> alter database mount;
The Open Oracle startup Stage
To open the database, you can just use the startup command as seen in this example
SQL> startup
If the database is mounted, you can open it with the alter database open command as seen in this example:
SQL> alter database open;

Shutting Down a Database
To shut down a database and instance, you must first connect as SYSOPER or SYSDBA.

·         Shutting Down with the NORMAL Clause
·         Shutting Down with the IMMEDIATE Clause
·         Shutting Down with the TRANSACTIONAL Clause
·         Shutting Down with the ABORT Clause


Shutting Down with the NORMAL Clause
To shut down a database in normal situations, use the SHUTDOWN command with the NORMAL clause:

SHUTDOWN NORMAL
The NORMAL clause is optional, because this is the default shutdown method if no clause is provided.
Normal database shutdown proceeds with the following conditions:
·         No new connections are allowed after the statement is issued.
·         Before the database is shut down, the database waits for all currently connected users to disconnect from the database.
The next startup of the database will not require any instance recovery procedures.


Shutting Down with the IMMEDIATE Clause
Immediate database shutdown proceeds with the following conditions:
·         No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
·         Any uncommitted transactions are rolled back. (If long uncommitted transactions exist, this method of shutdown might not complete quickly, despite its name.)
·         Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly rolls back active transactions and disconnects all connected users.
The next startup of the database will not require any instance recovery procedures.
Shutting Down with the TRANSACTIONAL Clause
When you want to perform a planned shutdown of an instance while allowing active transactions to complete first, use the SHUTDOWN command with the TRANSACTIONAL clause:

SHUTDOWN TRANSACTIONAL
Transactional database shutdown proceeds with the following conditions:
·         No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
·         After all transactions have completed, any client still connected to the instance is disconnected.
·         At this point, the instance shuts down just as it would when a SHUTDOWN IMMEDIATE statement is submitted.
The next startup of the database will not require any instance recovery procedures.
Shutting Down with the ABORT Clause
You can shut down a database instantaneously by aborting the database instance.
When you must do a database shutdown by aborting transactions and user connections, issue the SHUTDOWN command with the ABORT clause:
SHUTDOWN ABORT
An aborted database shutdown proceeds with the following conditions:
·         No new connections are allowed, nor are new transactions allowed to be started, after the statement is issued.
·         Current client SQL statements being processed by Oracle Database are immediately terminated.
·         Uncommitted transactions are not rolled back.
·         Oracle Database does not wait for users currently connected to the database to disconnect. The database implicitly disconnects all connected users

Basic Linux Commands

Basic Linux  Commands

>> To check the present working directory

 [root@database ~]# pwd
/root

>> To show the contents of a directory (folder)

[root@database ~]# ls
3145.zip  args1    database_notes  Documents   ifcfg-eth1_March25  linux_image.iso  names         phonenubers  uln_migrate             uln_register.tar
args      BegPerl  Desktop         hello2.plx  index.html.1        mbox             oradiag_root  scripts   

>> To see more details including the permission regarding the contents of a directory (folder)

[[root@database ~]# ls -l
total 3511620
-rw-r--r--  1 root   root       422670 Dec 30 10:29 3145.zip
-rwxr--r--  1 root   root          105 Apr  8 21:45 args
-rwxr--r--  1 root   root           32 Apr  8 21:51 args1
drwxr-xr-x 17 root   root         4096 Dec 30 10:31 BegPerl
-rw-r--r--  1 root   root         5022 Jan  5 09:55 database_notes
drwxr-xr-x  3 root   root         4096 Mar 25 04:45 Desktop
drwx------  3 root   root         4096 Mar 30 21:33 Documents

>> To see all contents including hidden files of a directory (folder)

[root@database ~]# ls -a
.              .bashrc         .eggcups         .gstreamer-0.10     mbox                 scripts                 uln_register.tar             .xauthgXJKsS
..             BegPerl         .elinks          .gtkrc-1.2-gnome2   .metacity            .sqldeveloper           .vboxclient-clipboard.pid    .xauthixsRh6


>> To see tree structure of nested directories

[root@database ~]# ls -R /opt
/opt:
ORCLfmap
sqldeveloper
sun
VBoxGuestAdditions-4.2.6

/opt/ORCLfmap:
prot1_32

/opt/ORCLfmap/prot1_32:
bin
etc
log

/opt/ORCLfmap/prot1_32/bin:
Fmputl

>> To see a file starting from f

[root@database ~]# ls f*

>> To see a file have a middle string as disk

[root@database ~]# ls /bin/*disk*

>> To see a file whose length is 3 charaters

[root@database ~]# ls ???

>> To see a file which starts with single char & ends up with any number of character

[root@database ~]# ls ?edh*

>> To create a file
[root@database ~]# cat > file1

>>  To see file content
[root@database ~]# cat file1


>> To append a file

[root@database ~]  cat >> file1

[root@database ~] cat file1 file2 >> file3 #redirecting output to file3

[root@database ~] cat file3

>>  To create a file using touch command

[root@database ~]# touch f1 f2 f3 f4

[root@database ~]# ls

>>  Creating a single directory

[root@database ~]# mkdir dir

>> Creating multiple directories

[root@database ~]# mkdir dir1 dir2 dir3 dir4

[root@database ~]# ls

anaconda-ks.cfg  dir   dir2  dir4  f2  f4     file2  install.log     
labmanual
Desktop          dir1  dir3  f1    f3  file1  file3  install.log.syslog

>>  To create nested directories

[root@database ~]# mkdir -p d1/d2/d3/d4

>> To see the tree structure

[root@database ~]# ls -R d1
d1:
d2

d1/d2:
d3

d1/d2/d3:
d4

d1/d2/d3/d4:

>> To change a directory

[root@database ~]# cd dir1

[root@database ~]# cd ..

[root@database ~]# cd ../..

[root@database ~]# cd -
/root

[root@database ~]# pwd
/root

[root@database ~]# cd

[root@database ~]# pwd
/root

[root@database ~]#

13. To remove files

[root@database ~]# rm file1

rm: remove regular file `file1'? y

14. To remove an empty directory

[root@database ~]# rmdir dir1

[root@database ~]# ls
anaconda-ks.cfg  Desktop  dir2  dir4  f2  f4     file3        install.log.syslog
d1               dir      dir3  f1    f3  file2  install.log  labmanual

15. To remove a directory
[root@database ~]# rm -rf dir

[root@database ~]# ls
anaconda-ks.cfg  Desktop  dir3  f1  f3  file2  install.log         labmanual
d1               dir2     dir4  f2  f4  file3  install.log.syslog

To copy files

[root@database ~]# cp anaconda-ks.cfg file1

To copy folders

[root@database ~]# cp -r dir2 Desktop

To rename  directories and files

[root@database ~]# mv dir3 d4


[root@database ~]# ls
anaconda-ks.cfg  d4       dir2  f1  f3  file1  file3        install.log.syslog
d1               Desktop  dir4  f2  f4  file2  install.log  labmanual

To move directories and files

[root@database ~]# mv dir2 /opt

[root@database ~]# ls
anaconda-ks.cfg  d4       dir4  f2  f4     file2  install.log         labmanual
d1               Desktop  f1    f3  file1  file3  install.log.syslog


[root@database ~]# cd /opt

[root@database ~]#  ls
dir2

To search a word from single or multiple file’s

[root@database ~]# grep tom  /etc/passwd /etc/group /etc/gshadow

/etc/passwd:tom:x:500:500::/home/tom:/bin/bash
/etc/group:tom:x:500:
/etc/gshadow:tom:!::

[root@database ~]# cat /etc/passwd | grep tom

To see the type of file

[root@database ~]# file *

To view the date

[root@database ~]# date

[root@database ~]# date -s "07/15/2008 00:06:00 "
 mm/dd/yyyy hh:mm:ss
Tue Jul 15 00:06:00 EDT 2008

[root@database ~]# cal

[root@database ~]# cal 12 2008

[root@database ~]# man mkdir

[root@database ~]# man cal

To see the content screen wise

[root@database ~]# ls -l /bin  | less


Visual Interface (VI)


Commands to Go into Insert mode

To open a file use vi

e.g.
# vi test.txt

i   -  inserts the text at current cursor position
I   -  inserts the text at  beginning of line
a   -  appends the text after current cursor position
A   -  appends the text at end of line
o   -  inserts a line below current cursor position
O   -  inserts a line above current cursor position
r   -  replace a single char at current cursor position

Commands at execute mode

:q -  quit without saving
:q!   -  quit forcefully without saving
:w   -  save
:wq   -  save & quit
:wq! -  save & quit  forcefully
:x -  save & quit
:sh -  Provides temporary shell
:se nu -  Setting line numbers
:se nonu -  Removing line numbers
:84   -  Press enter goes to line 84

>> To move the cursor, press the h,j,k,l keys as indicated.   
             ^
             k              Hint:  The h key is at the left and moves left.
       < h       l >               The l key is at the right and moves right.
             j                     The j key looks like a down arrow
             v

w forward   word to word
b     back side word to word

>> Command's at command mode

dd   -  Deletes a line
2dd   -  Deletes 2 lines
yy   -  Copy a line
2yy   -  Copies 2 lines
p -  put  (deleted or copied text)
u -  Undo (can undo 1000 times)
Ctrl+r -  Redo
G - Moves cursor to last line of file
5G - Moves cursor to 5th line of file
Shift+ZZ -  save & quit
/ -  locate word


>> To find and replace words
:1,$s///gc

e.g.
:1,$s/world/universe/gc

1-- To start the search at from 1st line
$ -> End of File
s -> substitute
g -> global
c -> confirmation

>> To power off machine

# poweroff

After Clone Concurrent Managers not starting

After Clone Concurrent Managers not starting

After cloning R12 instance, concurrent managers were not coming up. Actual and Target value of Concurrent managers are different.

Actual 0 and Target 1

Solution:


1.  Stop all middle tier services including the concurrent managers.
2.  Stop the database.
3.  Start the database.
4.  Connect SQLPLUS as APPS user and run the following :

EXEC FND_CONC_CLONE.SETUP_CLEAN;
COMMIT;
EXIT;

5.  Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers and webtiers to repopulate the required system tables.


6.  Connect to SQLPLUS as APPS user and run the following statement :

select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

If the above SQL does not returning any value then please do the following:

Go to $FND_TOP/patch/115/sql

Connect SQLPLUS as APPS user and run the following script :

SQL> @afdcm037.sql;

This script will create libraries for FNDSM and create managers for preexisting nodes.

Check again that FNDSM entries now exist:

select CONCURRENT_QUEUE_NAME from FND_CONCURRENT_QUEUES where CONCURRENT_QUEUE_NAME like 'FNDSM%';

7.  Go to cd $FND_TOP/bin
$ adrelink.sh force=y "fnd FNDLIBR"
$ adrelink.sh force=y "fnd FNDSM"
$ adrelink.sh force=y "fnd FNDFS"
$ adrelink.sh force=y "fnd FNDCRM"

8.  Run the CMCLEAN.SQL script from the referenced note below (don't forget to commit).

9.  Start the middle tier services including your concurrent manager.

User add and adding Privileges

User add and adding Privileges

SQL> create tablespace test datafile '/Oracle/ora/TEST/Data/proddata/test.dbf'size 1000m

SQL> create user test identified by test

SQL> alter user test default tablespace test

SQL> alter user test quota unlimited on test

SQL> grant connect to test

SQL> grant create session to test

SQL> grant create table to test

 
(INCASE IF GIVING GRANT ON ANY PACKAGE OR PROCEDURE)

SQL> grant execute, debug on <PACKAGE / PROCEDURE NAME > to test

SQL> grant select on dba_source to test

SQL> grant select on dba_objects to test

SQL> grant select any table to test


FOR TESTING OF CREATE DROP

SQL> conn test/test
Connected.

SQL> create table testing (col1 varchar2(5));
Table created.

SQL> alter table testing add (col2 varchar2(5));
Table altered.

SQL> drop table testing;
Table dropped.