Start/Stop Application Services Without Concurrent

./adstrtal.sh -nodbchk

./adstpall.sh -nodbchk

Find the size of the SCHEMA/USER?

select sum(bytes/1024/1024)"size" from dba_segments where owner='&owner';

Find the log file for active workflow mailer and workflow agent listener Container

select fl.meaning,fcp.process_status_code,
decode(fcq.concurrent_queue_name,’WFMLRSVC’,’maile r container’,’WFALSNRSVC’,’listener container’,fcq.concurrent_queue_name),
fcp.concurrent_process_id,os_process_id, fcp.logfile_name
from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl
where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code=’A’
and fl.lookup_type=’CP_PROCESS_STATUS_CODE’ and
fl.lookup_code=fcp.process_status_code
and concurrent_queue_name in(‘WFMLRSVC’,’WFALSNRSVC’)
order by fcp.logfile_name;

Start/Stop Individual Components in Opmn

./opmnctl startproc ias-component=coreapplication_obis1

./opmnctl stopproc ias-component=coreapplication_obis1

Rolling back an Autoconfig session

Each execution of Autoconfig creates a rollback script in case you need to revert to previous configuration settings.

Tier                                       Directory
Application    $APPL_TOP/admin/<CONTEXT_NAME>/out/<MMDDhhmm>
Database       $ORACLE_HOME/appsutil/out/<CONTEXT_NAME>/<MMDDhhmm>


To roll back an autoconfig session, run: restore.sh script

Database User Create/Alter

Create User:

SQL> CREATE USER <username>
IDENTIFIED BY <password>
DEFAULT TABLESPACE <tablespace name>
TEMPORARY TABLESPACE <tablespace name>
QUOTA <quota amount> ON <tablespace name>;

You then need to grant appropriate roles to the user for example, session, connect, resource:

SQL> grant connect,resource to <username>;

Change Password:

SQL> alter user <username> identified by <newpassword>;

Unlock User:

SQL> alter user <username> account unlock;

Add tablespace / datafile

Increase the size of the datafile:

SQL> alter database datafile '/ebiz/oracle/test/db/apps_st/data/system04.dbf' autoextend on maxsize 1G;

SQL> alter database datafile '/ebiz/oracle/test/db/apps_st/data/system04.dbf' resize 3G;

Add an additional datafile:

SQL> alter tablespace system add datafile '/ebiz/oracle/test/db/apps_st/data/system05.dbf' size 2G;

Find Users connected to the Database

To find how many users are on the database issue the following:

SQL> SELECT username FROM v$session;

Show what users are running:

SQL> SELECT a.sid
, a.serial#
, b.sql_text
FROM v$session a
, v$sqlarea b
WHERE a.sql_address=b.address AND a.username = '<username>';

Find Database Users with DBA Privilege

 If you wish to know which users have been granted the dba role then you need to query the dba_role_privs in the SYS schema.


SQL> desc dba_role_privs
Name         Null?    Type
------------ -------- ------------
GRANTEE               VARCHAR2(30)
GRANTED_ROLE NOT NULL VARCHAR2(30)
ADMIN_OPTION          VARCHAR2(3)
DEFAULT_ROLE          VARCHAR2(3)

To find a list of all users with DBA privilege execute the following code:

SQL> select * from dba_role_privs where granted_role='DBA';
GRANTEE   GRANTED_ROLE ADM DEF
--------- ------------ --- ---
SYS       DBA          YES YES
SYSTEM    DBA          YES YES

Find Tablespace size including Autoextend Values

Using this below sql query, you can find the tablespace size including autoextend values which is enabled for datafiles.

WITH my_ddf AS
    (
        SELECT file_id, tablespace_name, file_name,
               DECODE (autoextensible,
                       'YES', GREATEST (BYTES, maxbytes),
                       BYTES
                      ) mysize,
              DECODE (autoextensible,
                      'YES', CASE
                         WHEN (maxbytes > BYTES)
                            THEN (maxbytes - BYTES)
                         ELSE 0
                      END,
                      0
                     ) growth
         FROM dba_data_files)
SELECT   my_ddf.tablespace_name,
         ROUND (SUM (my_ddf.mysize) / (1024 * 1024)) totsize,
         ROUND (SUM (growth) / (1024 * 1024)) growth,
         ROUND ((SUM (NVL (freebytes, 0))) / (1024 * 1024)) dfs,
         ROUND ((SUM (NVL (freebytes, 0)) + SUM (growth)) / (1024 * 1024)
               ) totfree,
         ROUND (  (SUM (NVL (freebytes, 0)) + SUM (growth))
                 / SUM (my_ddf.mysize)
                 * 100
               ) perc
    FROM my_ddf, (SELECT   file_id, SUM (BYTES) freebytes
                      FROM dba_free_space
                  GROUP BY file_id) dfs
   WHERE my_ddf.file_id = dfs.file_id(+)
         AND my_ddf.tablespace_name NOT LIKE '%UNDOTB%'
GROUP BY my_ddf.tablespace_name
ORDER BY 6 DESC