Oracle DB data size monitoring - tips for troubleshooting

To check Datafile size of a given tablespace;
select NAME,BYTES/1024/1024/1024 from v$datafile;
SELECT FILE_NAME,BYTES/1024/1024/1024 FROM DBA_DATA_FILES where TABLESPACE_NAME = 'USERS';
select sum(bytes)/1024/1024/1024 "Gig" from dba_data_files where TABLESPACE_NAME = 'TEST_OIM';
select * from DBA_FREE_SPACE where TABLESPACE_NAME = 'TEST_OIM';

Alter tablespace datafiles
ALTER DATABASE DATAFILE '+DATA/oimtest/datafile/test_oim.278.779629303' RESIZE 5000m;

To check size availability in Storage Disks(ASM)
select GROUP_NUMBER,NAME,TOTAL_MB,FREE_MB,HOT_USED_MB,COLD_USED_MB,USABLE_FILE_MB from v$asm_diskgroup;

To check actual sizes of objects in a schema:
select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024 from dba_Segments where TABLESPACE_NAME = 'TEST_OIM' and BYTES/1024/1024> 1000 order by bytes desc;
select sum(BYTES/1024/1024) from dba_Segments where TABLESPACE_NAME = 'TEST_OIM';
select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024 from dba_Segments where TABLESPACE_NAME = 'TEST_OIM' and BYTES/1024/1024> 1000 order by bytes desc;

To check ownerships of LOB objects:
SELECT owner, table_name, column_name FROM dba_lobs WHERE segment_name = 'SYS_LOB0000095949C00007$$';

DBA_TABLES definition

https://netfiles.uiuc.edu/jstrode/www/oradd/DBA_TABLES.html

Oracle Database Monitoring Tips

http://www.rocket99.com/techref/oracle8406.html

If netca wouldn't like the OID schema

Last week, I was trying to setup EUS on 11.1.0.7 DB, with a fresh installed OID 11g.

Following the instructions in the EUS Admin Guide document, I run NETCA and came up with the following error:

=======
The directory has not been configured for this usage. It does not contain the required Oracle Schema, or the Oracle Schema version is not correct.

Select how you wan to proceed:
- I want to continue without using a directory service
- I want to verify directory service information and try again.

=======


I have searched for this in Oracle docs and apparently in OID 9, this wizard lets you to create the required schema, hence the documentation: http://download.oracle.com/docs/html/B10263_01/install.htm#1160409

For OID 11g however, this problem was related to out of the box configuration of OID in where "anonymous bind" is turned off by default

and guess what, Netca (and also netmgr) requires "anonymous bind" to be enabled.

The attribute is orclanonymousbindsflag, and default value is 2, changing it to 1 resolves this problem..

cn=OIDINSTANCENAME,cn=osdldapd,cn=subconfigsubentry


I misleadedly looked for this solution everywhere on the OID documentation, the solution was in one of the db documentations which is:
http://download.oracle.com/docs/cd/E11882_01/network.112/e10836/naming.htm#BABDHGIF

Killing connected user sessions from an Oracle DB

Killing connected user sessions from an Oracle DB
select sid,serial# from v$session;

(
Use following in a RAC environment:
select sid,serial# from gv$session;
)
Output is something like this:
----- -----
xxx yyy


then for each row run:
alter system kill session 'xxx,yyy' immediate;

ORA-01102 Cannot mount database in Exclusive mode

1. Verify that there is not a "sgadef.dbf" file in the directory
"ORACLE_HOME/dbs".

% ls $ORACLE_HOME/dbs/sgadef.dbf

If this file does exist, remove it.

% rm $ORACLE_HOME/dbs/sgadef.dbf

2. Verify that there are no background processes owned by "oracle"

% ps -ef | grep ora_ | grep $ORACLE_SID

If background processes exist, remove them by using the Unix
command "kill". For example:

% kill -9

3. Verify that no shared memory segments and semaphores that are owned
by "oracle" still exist

% ipcs -b

If there are shared memory segments and semaphores owned by "oracle",
remove the shared memory segments

% ipcrm -m

and remove the semaphores

% ipcrm -s

NOTE: The example shown above assumes that you only have one
database on this machine. If you have more than one
database, you will need to shutdown all other databases
before proceeding with Step 4.

4. Verify that the "$ORACLE_HOME/dbs/lk" file does not exist

5. Startup the instance