Solaris: Calculate Memory Consumed by project

# ps -e -o pid,user,project,rss,vsz,zone,comm >/tmp/3-9309294501_ps.out

# ipcs -mAZ >/tmp/3-9309294501_ipcs.out

# echo "::walk proc p| ::print -t proc_t p_cred |::print struct cred cr_projid|::grep ".==0t101" |::eval '<p=K'" |mdb -k > /tmp/3-9309294501_mdb_ora_procs.out

# echo "::walk proc p| ::print -t proc_t p_cred |::print struct cred cr_projid|::grep ".==0t101" |::eval '<p=K' |::pmap" | mdb -k 

# echo "::cat /tmp/3-9309294501_mdb_ora_procs.out|::pmap; ::cat /tmp/3-9309294501_mdb_ora_procs.out |::ps" | mdb -k >/tmp/3-9309294501_pmap.out


# cat 3-9309294501_pmap.out |egrep -v '(^S|^R|^Z)'| awk '{print $3}' | grep -v SIZE | sed -e 's/k//g' | awk '$1 < 1500000 {x=x+$1} END {print x/1024/1024}'

# prstat -J -c 1 1
# prstat -a -s rss -c 1 1

Oracle Fix Missing Datafile

To fix the missing user tablespace,
1. Create a new tablespace
2. Drop the old tablespace
3. Change the new tablespace name to old tablespace name
4. Change affected user default tablespace to new one.

Example:
1.  Create a new tablespace
create tablespace USERS datafile '/data6/GOWEEK/USERSPACE.dbf' size 200M autoextend on next 500k maxsize 500m;

2. Drop the old tablespace    
DROP TABLESPACE USERSPACE INCLUDING CONTENTS AND DATAFILES;       

3. Change new table space name  
ALTER TABLESPACE USERS RENAME TO USERSPACE;

4. Use below script to change user default table space, you may need to change the statement

SQL> select 'ALTER USER '||username||' DEFAULT TABLESPACE USERSPACE;'  FROM DBA_USERS WHERE DEFAULT_TABLESPACE like '%_$%' and username not in('SYS','SYSTEM');

Reference:

select * from dba_data_files;

select * from dba_users;

select *  from database_properties where property_name like 'DEFAULT%TABLESPACE';

Oracle: Change datafile names

1. Using below statement to generate script to change datafile names in database

SQL> select 'alter database rename file ''' || file_name || '''' || ' to ' || '''' || SUBSTR(file_name, 1,19) || 'pfr' || SUBSTR(file_name, 24) || ''';' from dba_data_files where file_name like '%KEYWORD%' order by file_name;

and export the result into renDBfiles.sql

2. Using below statement to generate shell script to rename data files at system level

SQL> select 'mv ' || file_name || ' ' || SUBSTR(file_name, 1,19) || 'pfr' || SUBSTR(file_name, 24) from dba_data_files where file_name like '%KEYWORD%' order by file_name;


and export the result into renSysfiles.sh

3. Shutdown Database
SQL> shutdown immediate;

4. Rename data files at system level
$ ./renSysfiles.sh

5. Rename the data files at mount stage (database is not open)
SQL> startup mount;

6. Rename date files at database level
SQL> @renDBfiles.sql

7. Startup database
SQL> startup