Re-Create TEMPORARY tablespace in Oracle 11g

In this article, I will explain you the step by step guide to create new TEMP tablespace and drop existing temporary tablespace.

While doing this activity, existing temporary tablespace may have existing live sessions, due to same oracle won’t let us to drop existing temporary tablespace. Resulting, we need to kill existing session before dropping temporary tablespace.

Following query will give you tablespace name and datafile name along with path of that data file.

SQL> select FILE_NAME,TABLESPACE_NAME from dba_temp_files;

Following query will create temp tablespace named: ‘TEMP_NEW’ with 500 MB size along with auto-extend and maxsize unlimited.

SQL> CREATE TEMPORARY TABLESPACE TEMP_NEW TEMPFILE '/DATA/database/ifsprod/temp_01.dbf' SIZE 500m autoextend on next 10m maxsize unlimited;

or

SQL> CREATE TEMPORARY TABLESPACE TEMP_NEW;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP_NEW;

Database altered.

SQL> DROP TABLESPACE TEMP including contents and datafiles;  --DROP OLD ONE (TEMP)

Tablespace dropped.

SQL> CREATE TEMPORARY TABLESPACE TEMP;                       --RECREATE as TEMP

Tablespace created.

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMP;

Database altered.

SQL> DROP TABLESPACE TEMP_NEW including contents and datafiles;

Tablespace dropped.

Retrieve ‘SID_NUMBER’ & ‘SERIAL#NUMBER’ of existing live session’s who are using old temporary tablespace ( i.e. TEMP ) and kill them.

SQL> SELECT b.tablespace,b.segfile#,b.segblk#,b.blocks,a.sid,a.serial#,
a.username,a.osuser, a.status
FROM v$session a,v$sort_usage b
WHERE a.saddr = b.session_addr;

Provide above inputs to following query, and kill session’s.

SQL> alter system kill session 'SID_NUMBER, SERIAL#NUMBER';

or

SQL> alter system kill session 'SID_NUMBER, SERIAL#NUMBER,@INST_ID';




For example:

SQL> alter system kill session '59,57391';

or

SQL> alter system kill session '59,57391,@1';

Now, we can drop old temporary tablespace without any trouble with following:

SQL> DROP TABLESPACE old_temp_tablespace including contents and datafiles;

 Contents and datafiles are deleted successfully.

If you wish to continue with old temporary tablespace name, i.e. ‘TEMP’ then follow same step mentioned above to recreate temp tablespace with old name.

Purge huge SYS.AUD$, this option always work

Issue:

SYS.AUD$ table in one of my production crossed all thresholds and was of size 300Gb where no delete/truncate statement was working. I did the way mentioned below and all went fine.

Error:

SQL> truncate table sys.aud$;
truncate table sys.aud$
                   *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL> //
truncate table sys.aud$
                   *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired


SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

Solution:

oracle@host1:/cs/oracle/data $ [ORADB1]sqlplus /nolog

SQL*Plus: Release 11.2.0.2.0 Production on Wed Dec 28 06:50:07 2016

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

SQL> connect /as sysdba
Connected.
SQL> truncate table SYS.AUD$;

Table truncated.

SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, Oracle Label Security,
OLAP, Data Mining, Oracle Database Vault and Real Application Testing options

Practical Examples of Linux Find Command

Practical Examples of Linux Find Command

The Linux Find Command is one of the most important and much used command in Linux sytems. Find command used to search and locate list of files and directories based on conditions you specify for files that match the arguments. Find can be used in variety of conditions like you can find files by permissions, users, groups, file type, date, size and other possible criteria.

Linux Find Command

Through this article we are sharing our day-to-day Linux find command experience and its usage in the form of examples. In this article we will show you the most used 35 Find Commands examples in Linux. We have divided the section into Five parts from basic to advance usage of find command.

Part I: Basic Find Commands for Finding Files with Names
Part II: Find Files Based on their Permissions
Part III: Search Files Based On Owners and Groups
Part IV: Find Files and Directories Based on Date and Time
Part V: Find Files and Directories Based on Size
Part VI: Find Multiple Filenames in Linux
Part I – Basic Find Commands for Finding Files with Names

1. Find Files Using Name in Current Directory

Find all the files whose name is tecmint.txt in a current working directory.

# find . -name tecmint.txt
./tecmint.txt

2. Find Files Under Home Directory

Find all the files under /home directory with name tecmint.txt.

# find /home -name tecmint.txt
/home/tecmint.txt

3. Find Files Using Name and Ignoring Case

Find all the files whose name is tecmint.txt and contains both capital and small letters in /home directory.

# find /home -iname tecmint.txt
./tecmint.txt
./Tecmint.txt

4. Find Directories Using Name

Find all directories whose name is Tecmint in / directory.

# find / -type d -name Tecmint
/Tecmint

5. Find PHP Files Using Name

Find all php files whose name is tecmint.php in a current working directory.

# find . -type f -name tecmint.php
./tecmint.php

6. Find all PHP Files in Directory

Find all php files in a directory.

# find . -type f -name "*.php"
./tecmint.php
./login.php
./index.php
Part II – Find Files Based on their Permissions

7. Find Files With 777 Permissions

Find all the files whose permissions are 777.

# find . -type f -perm 0777 -print

8. Find Files Without 777 Permissions

Find all the files without permission 777.

# find / -type f ! -perm 777

9. Find SGID Files with 644 Permissions

Find all the SGID bit files whose permissions set to 644.

# find / -perm 2644

10. Find Sticky Bit Files with 551 Permissions

Find all the Sticky Bit set files whose permission are 551.

# find / -perm 1551

11. Find SUID Files

Find all SUID set files.

# find / -perm /u=s

12. Find SGID Files

Find all SGID set files.

# find / -perm /g=s

13. Find Read Only Files

Find all Read Only files.

# find / -perm /u=r

14. Find Executable Files

Find all Executable files.

# find / -perm /a=x

15. Find Files with 777 Permissions and Chmod to 644

Find all 777 permission files and use chmod command to set permissions to 644.

# find / -type f -perm 0777 -print -exec chmod 644 {} \;

16. Find Directories with 777 Permissions and Chmod to 755

Find all 777 permission directories and use chmod command to set permissions to 755.

# find / -type d -perm 777 -print -exec chmod 755 {} \;

17. Find and remove single File

To find a single file called tecmint.txt and remove it.

# find . -type f -name "tecmint.txt" -exec rm -f {} \;

18. Find and remove Multiple File

To find and remove multiple files such as .mp3 or .txt, then use.

# find . -type f -name "*.txt" -exec rm -f {} \;
OR
# find . -type f -name "*.mp3" -exec rm -f {} \;

19. Find all Empty Files

To find all empty files under certain path.

# find /tmp -type f -empty

20. Find all Empty Directories

To file all empty directories under certain path.

# find /tmp -type d -empty

21. File all Hidden Files

To find all hidden files, use below command.

# find /tmp -type f -name ".*"
Part III – Search Files Based On Owners and Groups

22. Find Single File Based on User

To find all or single file called tecmint.txt under / root directory of owner root.

# find / -user root -name tecmint.txt

23. Find all Files Based on User

To find all files that belongs to user Tecmint under /home directory.

# find /home -user tecmint

24. Find all Files Based on Group

To find all files that belongs to group Developer under /home directory.

# find /home -group developer

25. Find Particular Files of User

To find all .txt files of user Tecmint under /home directory.

# find /home -user tecmint -iname "*.txt"
Part IV – Find Files and Directories Based on Date and Time

26. Find Last 50 Days Modified Files

To find all the files which are modified 50 days back.

# find / -mtime 50

27. Find Last 50 Days Accessed Files

To find all the files which are accessed 50 days back.

# find / -atime 50

28. Find Last 50-100 Days Modified Files

To find all the files which are modified more than 50 days back and less than 100 days.

# find / -mtime +50 –mtime -100

29. Find Changed Files in Last 1 Hour

To find all the files which are changed in last 1 hour.

# find / -cmin -60

30. Find Modified Files in Last 1 Hour

To find all the files which are modified in last 1 hour.

# find / -mmin -60

31. Find Accessed Files in Last 1 Hour

To find all the files which are accessed in last 1 hour.

# find / -amin -60
Part V – Find Files and Directories Based on Size

32. Find 50MB Files

To find all 50MB files, use.

# find / -size 50M

33. Find Size between 50MB – 100MB

To find all the files which are greater than 50MB and less than 100MB.

# find / -size +50M -size -100M

34. Find and Delete 100MB Files

To find all 100MB files and delete them using one single command.

# find / -size +100M -exec rm -rf {} \;

35. Find Specific Files and Delete

Find all .mp3 files with more than 10MB and delete them using one single command.

# find / -type f -name *.mp3 -size +10M -exec rm {} \;
That’s it, We are ending this post here, In our next article we will discuss more about other Linux commands in depth with practical examples. Let us know your opinions on this article using our comment section.