"ORA-01031: insufficient privileges"

The ORA-01031: “insufficient privileges” error occurs when you attempt to execute a program or function for which you have not been granted the appropriate privileges.

For the DBA, the ORA-01031 can happen if the target OS executables do not have read and execute permissions (e.g. (770) in UNIX/Linux), and ensure that the oracle user is a member of the dba group (e.g. /etc/group). 

There are similar permission in the Windows registry.
In windows just have to assign the ORA-DBA group to the corresponding OS user who is getting the error,

Oracle on Solaris 10 : Fixing the 'ORA-27102: out of memory' Error
Symptom:
As part of a database tuning effort you increase the SGA/PGA sizes; and Oracle greets with an
ORA-27102: out of memory error message.
The system had enough free memory to serve the needs of Oracle.
SQL> startup
ORA-27102: out of memory
SVR4 Error: 22: Invalid argument
Diagnosis
$ oerr ORA 27102
27102, 00000, "out of memory"
// \*Cause: Out of memory
// \*Action: Consult the trace file for details

Not so helpful. Let's look the alert log for some clues.

% tail -2 alert.log
WARNING: EINVAL creating segment of size 0x000000028a006000
fix shm parameters in /etc/system or equivalent
Oracle is trying to create a 10G shared memory segment (depends on SGA/PGA sizes), but operating system (Solaris in this example) responded with an invalid argument (EINVAL) error message. There is a little hint about setting shm parameters in /etc/system.
Prior to Solaris 10, shmsys:shminfo_shmmax parameter has to be set in /etc/system with maximum memory segment value that can be created. 8M is the default value on Solaris 9 and prior versions; where as 1/4th of the physical memory is the default on Solaris 10 and later. On a Solaris 10 (or later) system, it can be verified as shown below:

% prtconf | grep Mem
Memory size: 32760 Megabytes

% id -p
uid=59008(oracle) gid=10001(dba) projid=3(default)

% prctl -n project.max-shm-memory -i project 3
project: 3: default
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
        privileged      7.84GB      -   deny                                 -
        system          16.0EB    max   deny                                 -
Now it is clear that the system is using the default value of 8G in this scenario, where as the application (Oracle) is trying to create a memory segment (10G) larger than 8G. Hence the failure.
So, the solution is to configure the system with a value large enough for the shared segment being created, so Oracle succeeds in starting up the database instance.
On Solaris 9 and prior releases, it can be done by adding the following line to /etc/system, followed by a reboot for the system to pick up the new value.

set shminfo_shmmax = 0x000000028a006000

However shminfo_shmmax parameter was obsoleted with the release of Solaris 10; and Sun doesn't recommend setting this parameter in /etc/system even though it works as expected.
On Solaris 10 and later, this value can be changed dynamically on a per project basis with the help of resource control facilities . This is how we do it on Solaris 10 and later:

% prctl -n project.max-shm-memory -r -v 10G -i project 3

% prctl -n project.max-shm-memory -i project 3
project: 3: default
NAME    PRIVILEGE       VALUE    FLAG   ACTION                       RECIPIENT
project.max-shm-memory
        privileged      10.0GB      -   deny                                 -
        system          16.0EB    max   deny                                 -
Note that changes made with the prctl command on a running system are temporary, and will be lost when the system is rebooted. To make the changes permanent, create a project with projadd command and associate it with the user account as shown below:

% projadd -p 3  -c 'eBS benchmark' -U oracle -G dba  -K 'project.max-shm-memory=(privileged,10G,deny)' OASB
% usermod -K project=OASB oracleFinally make sure the project is created with projects -l or

cat /etc/project commands.
% projects -l
...
...
OASB
        projid : 3
        comment: "eBS benchmark"
        users  : oracle
        groups : dba
        attribs: project.max-shm-memory=(privileged,10737418240,deny)

% cat /etc/project
...
...
OASB:3:eBS benchmark:oracle:dba:project.max-shm-memory=(privileged,10737418240,deny)
With these changes, Oracle would start the database up normally.

SQL> startup
ORACLE instance started.
Total System Global Area 1.0905E+10 bytes
Fixed Size                  1316080 bytes
Variable Size            4429966096 bytes
Database Buffers         6442450944 bytes
Redo Buffers               31457280 bytes
Database mounted.
Database opened.

SPM: SMB space usage (810418176) exceeds 10.000000% of SYSAUX size (6492782592).

SQL Plan baselines are strored in the tablespace SYSAUX and by default, at most 10 percent of the tablespace can be used for this purpose. You can get the current value throught the data dictionary view DBA_SQL_MANAGEMENT_CONFIG.

SQL> select parameter value 
from dba_sql_management_config 
where parameter_name='SPACE_BUDGET_PERCENT';


PARAMETER_VALUE
---------------
         10

When this default value (default threshold) is exceeded, a warning message gets written in the alert log of the database, it is not any error but just a warning so don't bothered about it. If it occurs intermittently, you can change(increase) its default value using the procedure CONFIG in the package DBMS_SPM.

Supported values for its threshold is between 1 percent to 50 percent. Below example illustrate how to change it to 20 percent.

If PARAMETER_VALUE is set to NULL then default value is restored, i.e. 10 percent.

SQL> dbms_spm.configure(
parameter_name => 'space_budget_percent',
parameter_value => 20
);

After successful execution of procedure restart your oracle instance and now you will notice that there would be no more such entry in your alert log file.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 5361582080 bytes
Fixed Size                  2185112 bytes
Variable Size            1728055400 bytes
Database Buffers         3623878656 bytes
Redo Buffers                7462912 bytes
Database mounted.

SQL> alter database open;
Database altered.

Hope it wold help someone....!!

Database object and it's corresponsing datafiles, owner, tablespace

Here you could find which database object is located in which datafile of it's tablespace and it's owner, object name, object type and it's respective size:

SQL> select t.ts#, t.name as Tablespace_name, d.name as Datafile, s.owner, s.segment_name, s.segment_type, s.bytes/1024/1024 as "MB"
from v$tablespace t, v$datafile d, dba_segments s
where t.ts#=d.ts#
and t.name=s.tablespace_name
and s.owner='SCOTT';

TS# TS_NAME DATAFILE                                  OWNER  SEGMENT_NAME SEGMENT_TYPE Size(MB)
--- ------ ------------------------------------------ ------ ------------ ------------ ----------
4 USERS    E:\APP\RAJKUMAR\ORADATA\ORCL\USERS01.DBF SCOTT  DEPT         TABLE        .0625
4 USERS    E:\APP\RAJKUMARN\ORADATA\ORCL\USERS01.DBF SCOTT  EMP          TABLE        .0625
4 USERS    E:\APP\RAJKUMAR\ORADATA\ORCL\USERS01.DBF SCOTT  PK_EMP       INDEX        .0625
4 USERS    E:\APP\RAJKUMAR\ORADATA\ORCL\USERS01.DBF SCOTT  PK_DEPT      INDEX        .0625
4 USERS    E:\APP\RAJKUMAR\ORADATA\ORCL\USERS01.DBF SCOTT  SALGRADE     TABLE        .0625

SQL>