scripts-2016

To list all group

[root@ora ~]# adduser oracle
[root@ora ~]# groups oracle
oracle : oracle

[root@ora ~]# passwd oracle
Changing password for user oracle.
New password:
BAD PASSWORD: The password contains the user name in some form
Retype new password:
passwd: all authentication tokens updated successfully.
[root@ora ~]#
[root@ora ~]# groupadd oinstall
[root@ora ~]# cat /etc/group


[root@ora ~]# usermod -a -G oinstall oracle
[root@ora ~]# groups oracle
oracle : oracle oinstall

To change the primary group a user is assigned to, run the usermod command,
[root@ora ~]# usermod -g oinstall oracle
[root@ora ~]#groups oracle
oracle : oinstall

[oracle@sujeet ~]$ cat /etc/group |grep oinstall
oinstall:x:1001:

[oracle@sujeet ~]$ groups oracle
oracle : oinstall dba

[oracle@sujeet ~]$ id -Gn oracle
oinstall dba

To list all users

[oracle@sujeet ~]$ cat /etc/passwd

root:x:0:0:root:/root:/bin/bash
oracle:x:1001:1001::/home/oracle:/bin/bash

[oracle@sujeet ~]$  cat /etc/redhat-release

Red Hat Enterprise Linux Server release 7.2 (Maipo)

[oracle@sujeet ~]$ cat /etc/hosts
127.0.0.1            localhost.localdomain localhost
127.0.0.1            oracle.sujeet.com  sujeet
192.168.1.420      oracle.sujeet.com  sujeet


[oracle@sujeet ~]$ df -h

Filesystem             Size  Used  Avail  Use%  Mounted on

[oracle@sujeet ~]$ free -g
              total        used        free      shared  buff/cache   available
Mem:              3           0           0           0           2           1
Swap:             3           0           3

[oracle@sujeet ~]$ grep MemTotal /proc/meminfo
MemTotal:        3882708 kB

[oracle@sujeet ~]$ grep SwapTotal /proc/meminfo
SwapTotal:       4063228 kB


[oracle@sujeet ~]$ sqlplus / as sysdba

SQL> select sum(bytes / (1024*1024*1024)) "DB Size in GB" from dba_data_files;

DB Size in GB
-------------
     20.2456

SQL> select member from v$logfile;

MEMBER
--------------------------------------------------------------------------------
/oracle/oradata/Airtel/redo03.log

SQL> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oracle/oradata/Airtel/control01.ctl

SQL> select name from v$database;

NAME
---------
Airtel


SQL> select name from V$datafile;

NAME
------------------------------------------------------------------------------                             --
/oracle/oradata/Airtel/system01.dbf


SQL> show parameter target

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------                             -
archive_lag_target                   integer     0
db_big_table_cache_percent_target    string      0
db_flashback_retention_target        integer     1440
fast_start_io_target                 integer     0
fast_start_mttr_target               integer     0
memory_max_target                    big integer 1520M
memory_target                        big integer 1520M
parallel_servers_target              integer     32
pga_aggregate_target                 big integer 0
sga_target                           big integer 0


SQL> show parameter background_dump_dest

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------                             -
background_dump_dest                 string      /oracle/product/12.1.0/                             d
                                                 bhome_1/rdbms/log

Unlock User & reset User password

[oracle@sujeet ~]$ sqlplus / as sysdba

Here user name= sonu
Password= sonu123


SQL> alter user sonu account unlock;

User altered.

SQL> alter user sonu identified by sonu123;

User altered.

Table-spaces having less than 20% free space

SQL> select tablespace_name,(round(sum(Free_Space_MB),2)) "Free_Space_MB",(round(sum(Total_Space_MB),2))  "Total_MB",(round(100*sum(Free_Space_MB)/sum(Total_Space_MB),2)) "% Free" from
(select a.tablespace_name,a.file_name,(((a.maxbytes-a.bytes) + nvl(b.Free_Space_B,0))/1024/1024) Free_Space_MB,(maxbytes/1024/1024)  Total_Space_MB
from (select * from dba_data_files where autoextensible='YES' and increment_by != 0 and maxbytes >= bytes) a left outer join
(select FILE_ID,(sum(bytes)) Free_Space_B from dba_free_space group by FILE_ID) b
on a.file_id=b.file_id
UNION
select a.tablespace_name,a.file_name,(nvl(b.Free_Space_B,0)/1024/1024) Free_Space_MB,(bytes/1024/1024) Total_Space_MB
from (select * from dba_data_files where autoextensible='NO' or (autoextensible='YES' and maxbytes < bytes )) a left outer join  (select FILE_ID,(sum(bytes)) Free_Space_B from dba_free_space group by FILE_ID) b
on a.file_id=b.file_id)
where tablespace_name in (select tablespace_name from dba_tablespaces where contents not in ('UNDO','TEMPORARY'))
group by tablespace_name having (round(100*sum(Free_Space_MB)/sum(Total_Space_MB),2)) > 80;

Add Data_File in 11gR2

1. Check table space status if less than 5 & 10% than add data_file.
If totally depend on your project transaction.

2. User upper scripts to check table space status.(login with sqlplus sys as / sysdba)

[ora@sujeet 11.2.0]$ sqlplus sys as / sysdba

3. Use below statement with your datafile path.
If you dont now how find datafile path user below statement.

SQL> select name from V$datafile;
output> you got datafile path here after that modified below statement.

SQL> alter database datafile '/u01/db/data/apps_ts_tools01.dbf' RESIZE 500M;

Database altered.

SQL> alter database datafile '/u01/db/data/ctxd01.dbf' RESIZE 1024M;

Database altered.

4. After resize datafile again check table-space status.

SQL> select tablespace_name,(round(sum(Free_Space_MB),2)) "Free_Space_MB",(round(sum(Total_Space_MB),2))  "Total_MB",(round(100*sum(Free_Space_MB)/sum(Total_Space_MB),2)) "% Free" from
(select a.tablespace_name,a.file_name,(((a.maxbytes-a.bytes) + nvl(b.Free_Space_B,0))/1024/1024) Free_Space_MB,(maxbytes/1024/1024)  Total_Space_MB
from (select * from dba_data_files where autoextensible='YES' and increment_by != 0 and maxbytes >= bytes) a left outer join
(select FILE_ID,(sum(bytes)) Free_Space_B from dba_free_space group by FILE_ID) b
on a.file_id=b.file_id
UNION
select a.tablespace_name,a.file_name,(nvl(b.Free_Space_B,0)/1024/1024) Free_Space_MB,(bytes/1024/1024) Total_Space_MB
from (select * from dba_data_files where autoextensible='NO' or (autoextensible='YES' and maxbytes < bytes )) a left outer join  (select FILE_ID,(sum(bytes)) Free_Space_B from dba_free_space group by FILE_ID) b
on a.file_id=b.file_id)
where tablespace_name in (select tablespace_name from dba_tablespaces where contents not in ('UNDO','TEMPORARY'))
group by tablespace_name having (round(100*sum(Free_Space_MB)/sum(Total_Space_MB),2)) < 20;

How to find & Kill Inactive session?

Login with sys as / sysdba

SQL> select COUNT(*) from v$session where status='INACTIVE';

  COUNT(*)
----------
        88

SQL> select * from v$session where status='INACTIVE';

SQL> select 'alter system kill session '''||sid||','||serial#||''' immediate;'from v$session where status='INACTIVE';

SQL>spool on

SQL> spool /u01/homes/ora/session1.sql

SQL>select 'alter system kill session '''||sid||','||serial#||''' immediate;' from v$session where status='INACTIVE';

SQL>spool off

SQL> @/u01/homes/ora/session1.sql