Oracle how to find the max number of sessions ever reached in database

To find the number max number of sessions reached run the command below, note that this view will hold the information up to the  last restart of the database:


SQL> select * from v$resource_limit;

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL LIMIT_VALU
------------------------------ ------------------- --------------- ---------- ----------
processes                                       37              47        150        150
sessions                                        45              58        248        248
enqueue_locks                                   28             297       3080       3080
enqueue_resources                               19              50       1308  UNLIMITED
ges_procs                                        0               0          0          0
ges_ress                                         0               0          0  UNLIMITED
ges_locks                                        0               0          0  UNLIMITED
ges_cache_ress                                   0               0          0  UNLIMITED
ges_reg_msgs                                     0               0          0  UNLIMITED
ges_big_msgs                                     0               0          0  UNLIMITED
ges_rsv_msgs                                     0               0          0          0
gcs_resources                                    0               0          0          0
gcs_shadows                                      0               0          0          0
dml_locks                                        0               0       1088  UNLIMITED
temporary_table_locks                            0               3  UNLIMITED  UNLIMITED
transactions                                     2               2        272  UNLIMITED
branches                                         0               0        272  UNLIMITED
cmtcallbk                                        2               3        272  UNLIMITED
max_rollback_segments                           11              11        272      65535
sort_segment_locks                               0               8  UNLIMITED  UNLIMITED
k2q_locks                                        0               0        496  UNLIMITED
max_shared_servers                               1               1  UNLIMITED  UNLIMITED
parallel_max_servers                             0               0         40       3600

How to remove the sparcing in the oracle temp files

Created a script to remove the sparcing out of the temp datafiles of oracle. Please note that this script will use the existing location of the temp file so make sure that it have enough space:

$ vi removeS.sh
#!/bin/sh

# Script to expand the datafile to the maxsize
# Created by : Jeet
# Dated : 06-MARCH-2011

COMD_1="H"
TS_NAME=""
TAR=".tar"
count=0
ANOTHER_FILE="Y"

#Function to bring tablespace offline
tsOffline()
{
  echo 'connect / as sysdba'  >> ./tmp.file
  echo 'alter tablespace ' $TS_NAME ' tempfile offline;' >> ./tmp.file
  echo 'exit' >> ./tmp.file
  sqlplus -s /nolog < ./tmp.file
  rm ./tmp.file
}

tsOnline()
{
  echo 'connect / as sysdba'  >> ./tmp.file
  echo 'alter tablespace ' $TS_NAME ' tempfile online;' >> ./tmp.file
  echo 'exit' >> ./tmp.file
  sqlplus -s /nolog < ./tmp.file
  rm ./tmp.file
}

initInput()
{

while [[ $ANOTHER_FILE = "Y"  ||  $ANOTHER_FILE = "y" ]]
  do
    
    read -p "Enter file location........................................: " FILE_LOCATION
    FILE_ARR[$count]=$FILE_LOCATION
    count=`expr $count + 1`
    read -p "Do you want to enter another filename ? ( Y/y/N/n ) .......: " ANOTHER_FILE

done

  echo ""
  echo ""
  echo "**********************************************************"
  echo "Verify if the file name(s) is/are correct"
  echo "**********************************************************"
  echo "Entered file name(s): "

for i in ${FILE_ARR[@]}
do
  echo $i

done
  echo ""
  echo ""
  echo "if you want to re-enter tablespace name and file name then press any other character other than (Y/y/N/n)"
  read -p "Do you want to expand this file ( Y/y/N/n or any other Char ): " COMD_1
}

generateFileName()
{

for FILE_L in ${FILE_ARR[@]}
do

  # generate the file name
  F_NAME=`ls -tr $FILE_L | awk -F/ '{ print $NF }'`
  echo " "
  echo " "
  echo "File under operation is: $F_NAME"
  echo "Location of the file is: " $FILE_L
  #Change get the directory and work on that

  F_DIR=`dirname $FILE_L`
  cd $F_DIR

  # Start tablespace offline
  # Bringing tablespace offline
  tsOffline

  # Modification to the file
  fileMods

  # Tablespace online
  # Bringing tablespace online
  tsOnline
    
  echo "Operation on Done on File: " $FILE_L
done

}

fileMods()
{
  # Get the original size
  du -h $F_NAME

  # Start Tar
  TEMP_NAME=$F_NAME$TAR
  tar cvf $TEMP_NAME $F_NAME

  # Remove sparce file
  rm $F_NAME

  # Un Tar
  tar xvf $TEMP_NAME

  #Remove the tar file
  rm $TEMP_NAME

  # Get the new siz
  du -h $F_NAME
}

# Show all tablespace names
showTS()
{
  echo 'connect / as sysdba'  >> ./tmp.file
  echo 'select distinct tablespace_name "Temporary TableSpace Name" from dba_temp_files order by tablespace_name;' >> ./tmp.file
  echo 'exit' >> ./tmp.file
  sqlplus -s /nolog < ./tmp.file
  rm ./tmp.file
}

# show all files under the tablespace
showTSFileNames()
{
  A="select file_name from dba_temp_files where tablespace_name =upper('"
  B="') order by file_name;"
  C=$A$TS_NAME$B
  echo 'connect / as sysdba' >> ./tmp.file
  echo $C >> ./tmp.file
  echo 'exit' >> ./tmp.file
  sqlplus -s /nolog < ./tmp.file
  rm ./tmp.file
}

# use input for the tablespace name
getUserTSInput()
{
  read -p 'Enter the name of the tablespace on which you want to work: ' TS_NAME
}


preCheck ()
{
  rm ./tmp.file
}


echo "=================================================="
echo "Utility is used to expand the file to its max size"
echo "=================================================="



while [[ $COMD_1 != "Y"  &&  $COMD_1 != "y"  &&  $COMD_1 != "N"  &&  $COMD_1 != "n" ]]
  do

  # Do a precheck if the temp file already exists then delete it else do nothing
  preCheck

  # Show the current tablespaces
  showTS

  # get user input to find the tablespace on which user want to work
  getUserTSInput

  # show all files under the tablespace
  showTSFileNames

  # as user to enter initial inputs
  initInput

  if [ $COMD_1 = "Y" ] || [ $COMD_1 = "y" ]
    then
      # Get the inputs from user
      generateFileName
      echo " "
      echo "Sparce files are converted to non sparce files"
      echo " "
    else
      if [ $COMD_1 = "N" ] || [ $COMD_1 = "n" ]
        then
          echo "Nothing is done try again later"
      else
        echo "Enter either Y/N "
    fi
  fi
done

Oracle move table from one tablespace to another

There could be a scenario when you will be required to move the table from one tablespace to another. Note that when you will be moving the table then you would link to move the indexes associated with this table as well.

Make sure that no one is using the table.

So below are the steps by which you can move the table and its index in one got to another tablespace:

SQL>  desc hdhillon.t;
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 OWNER                         NOT NULL VARCHAR2(30)
 OBJECT_NAME                   NOT NULL VARCHAR2(30)
 SUBOBJECT_NAME                         VARCHAR2(30)
 OBJECT_ID                     NOT NULL NUMBER
 DATA_OBJECT_ID                         NUMBER
 OBJECT_TYPE                            VARCHAR2(19)
 CREATED                       NOT NULL DATE
 LAST_DDL_TIME                 NOT NULL DATE
 TIMESTAMP                              VARCHAR2(19)
 STATUS                                 VARCHAR2(7)
 TEMPORARY                              VARCHAR2(1)
 GENERATED                              VARCHAR2(1)
 SECONDARY                              VARCHAR2(1)
 NAMESPACE                     NOT NULL NUMBER
 EDITION_NAME                           VARCHAR2(30)
SQL> select count (*) from hdhillon.t;
  COUNT(*)
----------
   1390900
SQL> select owner, index_name, INDEX_TYPE, table_name from dba_indexes where table_name ='T';
OWNER                          INDEX_NAME                     INDEX_TYPE                  TABLE_NAME
------------------------------ ------------------------------ --------------------------- ------------------------------
HDHILLON                       T_OWNER_INDEX                  NORMAL                      T
SQL> select table_name, owner, tablespace_name from dba_tables where table_name ='T';
TABLE_NAME                     OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T                              HDHILLON                       MICHAEL
SQL> select index_name, owner, tablespace_name from dba_indexes where index_name ='T_OWNER_INDEX';
INDEX_NAME                     OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T_OWNER_INDEX                  HDHILLON                       MICHAEL
SQL>
SQL> set lines 270 pages 1000
SQL> col sql format a90
SQL> select decode( segment_type, 'TABLE', 1, 2 ) order_col2,
  2        'alter ' || segment_type || ' ' || segment_name ||
  3        decode( segment_type, 'TABLE', ' move ', ' rebuild ' ) ||
  4        chr(10) ||
  5        ' tablespace &new_tablespace_name ' || chr(10) ||
  6        ' storage ( initial ' || initial_extent || ' next ' ||
  7          next_extent || chr(10) ||
  8        ' minextents ' || min_extents || ' maxextents ' ||
  9          max_extents || chr(10) ||');' sql
 10  from user_segments,
 11            (select table_name, index_name from user_indexes where table_name =upper('&&table_name'))
 12  where segment_type in ( 'TABLE', 'INDEX' )
 13  and segment_name in ( select table_name from user_indexes where table_name =upper('&&table_name'))
 14  or segment_name in ( select index_name from user_indexes where table_name =upper('&&table_name'))
 15  order by order_col2;
Enter value for new_tablespace_name: users
old   5:       ' tablespace &new_tablespace_name ' || chr(10) ||
new   5:       ' tablespace users ' || chr(10) ||
old  11:               (select table_name, index_name from user_indexes where table_name =upper('&&table_name'))
new  11:               (select table_name, index_name from user_indexes where table_name =upper('t'))
old  13: and segment_name in ( select table_name from user_indexes where table_name =upper('&&table_name'))
new  13: and segment_name in ( select table_name from user_indexes where table_name =upper('t'))
old  14: or segment_name in ( select index_name from user_indexes where table_name =upper('&&table_name'))
new  14: or segment_name in ( select index_name from user_indexes where table_name =upper('t'))
ORDER_COL2 SQL
---------- ------------------------------------------------------------------------------------------
         1 alter TABLE T move
            tablespace users
            storage ( initial 65536 next 1048576
            minextents 1 maxextents 2147483645
           );
         2 alter INDEX T_OWNER_INDEX rebuild
            tablespace users
            storage ( initial 65536 next 1048576
            minextents 1 maxextents 2147483645
           );
SQL>
SQL> alter TABLE T move
  2              tablespace users
  3              storage ( initial 65536 next 1048576
  4              minextents 1 maxextents 2147483645);
Table altered.
SQL> alter INDEX T_OWNER_INDEX rebuild
  2              tablespace users
  3              storage ( initial 65536 next 1048576
  4              minextents 1 maxextents 2147483645);
Index altered.
SQL> select table_name, owner, tablespace_name from dba_tables where table_name ='T';
TABLE_NAME                     OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T                              HDHILLON                       USERS
SQL> select index_name, owner, tablespace_name from dba_indexes where index_name ='T_OWNER_INDEX';
INDEX_NAME                     OWNER                          TABLESPACE_NAME
------------------------------ ------------------------------ ------------------------------
T_OWNER_INDEX                  HDHILLON                       USERS

Change the size of temp file

How to change the size of temp file.

SQL>  select FILE_NAME,  MAXBYTES/1024/1024 MB from dba_temp_files;
FILE_NAME                                                                          MB
------------------------------------------------------------ ----------
/u01/app/oracle/oradata/new_location/temp01.dbf                    3072
SQL>
SQL> alter database tempfile '/u01/app/oracle/oradata/new_location/temp01.dbf' autoextend on maxsize 4G;
Database altered.
SQL> select FILE_NAME,  MAXBYTES/1024/1024 MB from dba_temp_files;
FILE_NAME                                                                           MB
------------------------------------------------------------ ----------
/u01/app/oracle/oradata/new_location/temp01.dbf                    4096
SQL>