Installation/Configurations11g
Useful Commands For Unix Operating System
1.Deleting files older than N number of days ?
find . -name ‘*.trc’ -mtime +[N in days] -exec rm {} \;
Command will delete files older then N days in that directory
2.Copying file keeping timestamp preserve
cp -ip file.txt file_txt_bkp
3.Listing files modified in last N days
find . -mtime -<ndays> -exec ls -lt {} \;
4.Sorting files based on Size of file ?
ls -l | sort -nrk 5 | more
du -sm *|sort -nr|head -10
5.Finding CPU & Memory detail
->cat /proc/cpuinfo (CPU)
->cat /proc/meminfo (Memory)
->topas
6.Finding if any service is listening on particular port or not ?
netstat -an | grep {port no}
7.Finding Process ID (PID) associated with any port ?
lsof | grep {port no.}
lsof should be installed and in path.Many times it will be installed eith root user.Make sure you have that permission
9.Finding a pattern in some file in a directory ?
grep pattern file_name ( find pattern in particular file )
grep -i pattern file_name (find pattern ignoring the case)
10.Create symbolic link to a file ?
ln -s pointing_to_original_file symbolic_link_name
example
ln -s /home/text.txt test.txt
11.History of command executed in UNIX
fc -l
fc -e – ls ( Would execute the last ls command.)
History command will also do the same
history
12. Compressing a big file
zip -r new.zip new
or
compress file_name
13.Creating a tar file
tar -cvwf file.tar file.txt
14.Extracting the files from a tar file
tar -xvwf myfile.tar(System would unarchive (untar) the myfile.tar file into the current directory.)
tar -xvwzf myfile.tar.gz(System would unarchive (untar) the myfile.tar.gz file in the current directory.)
14.Extracting a gz file?
Use guzip command as follows:
gunzip file.gz
OR
gzip -d file.gz
15.Extract a tar.gz or .tgz file?
Files with extension tar.gz or .tgz are tar files compressed with gzip. On Unix system extract them with following command:
gunzip < file.tar.gz | tar xvf –
gunzip < file.tgz | tar xvf –
If you have GNU tar (Linux system) you can use the z option directly:
tar xvzf file.tar.gz
tar xvzf file.tgz
16.Using mailx command
echo “This is going to be body of the mail” |mailx -s “Subject:Testing” “false@gmail.com”
17.Checking the file system free and used space
df -gt
Temporary Tablespace and Datafile Management in Oracle database
1. For Finding temp file and temp tablespace with size
SELECT file#,
status,
bytes / 1024 / 1024 “Size_MB”,
name
FROM v$tempfile;
SELECT file_name,
tablespace_name,
bytes / 1024 / 1024 / 1024,
status
FROM dba_temp_files;
2. Add tempfile to existing temp tablespace
SQL>ALTER TABLESPACE temp ADD tempfile ‘/u02/apps/oracle/temp01_01.dbf’ SIZE
2048m;
3. Add new temp tablespace and make as default
SQL>CREATE TEMPORARY TABLESPACE temp2 tempfile ‘/u02/apps/oracle/temp01.dbf’ SIZE 2g autoextend ON;
SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;
Note: use “reuse” if datafile physically exists.
4. Making old Temporary Tablespace Offline
SQL>ALTER DATABASE tempfile ‘/u02/apps/oradata/temp01.dbf’ OFFLINE;
5. Drop Temporary Tablespace
SQL>DROP TABLESPACE temp INCLUDING CONTENTS AND datafiles;
NOTE: Don’t drop immediatly, Check user is using TEMP tablespace by the Below Given Query, then you can drop
SELECT a.username,
a.osuser,
a.sid
||‘,’
||a.serial# SID_SERIAL,
c.spid Process,
b.tablespace tablespace,
a.status,
SUM(b.extents) * 1024 * 1024 SPACE
FROM v$session a,
v$sort_usage b,
v$process c,
dba_tablespaces d
WHERE a.saddr = b.session_addr
AND a.paddr = c.addr
AND b.tablespace = d.tablespace_name
GROUP BY a.username,
a.osuser,
a.sid
||‘,’
||a.serial#,
c.spid,
b.tablespace,
a.status;
6. Shrink Temporary Tablespace
SQL>ALTER TABLESPACE temp shrink tempfile ‘/u02/apps/oradata/temp01.dbf’ keep 10g;
Change Date and Time Formats In Oracle Database?
Option 1. If We want to change this format to DD/MONTH/YYYY format.
SQL> alter session set nls_date_format=’DD/MONTH/YYYY’;
Session altered.
SQL> select sysdate from dual;
SYSDATE
—————–
20/OCTOBER /2017
Option 2. If we want get the both date and time
SQL> alter session set nls_date_format=’DD/MONTH/YYYY HH24:MI:SS’;
Session altered.
SQL> select sysdate from dual;
SYSDATE
————————–
20/OCTOBER /2017 17:19:12 –It is 24 hours format.
SQL> alter session set nls_date_format=’DD/MONTH/YYYY HH:MI:SS’;
Session altered.
SQL> select sysdate from dual;
SYSDATE
————————–
20/OCTOBER /2017 05:19:43 –It is 12 hours format.
Difference between P-File and SP-File?
| P-FILE | SP-FILE |
| It is called as parameter file. | It is called as server parameter file. |
| P-File is ASCII file and it can be edited Manually | spfile is binary file and it cannot be edited Manually |
Parameters can be changed through following options:
1)alter system
2)alter session
Command
SQL>alter system set resource_limit=true scope=_________;
Where ______ is
BOTH=changes will be applied for current instance and future instance;
OR
MEMORY=changes will be applied only for current instance;
OR
SPFILE=changes will be applied after restart the database.
SP-File Can be created with the following command
SQL> create spfile from pfile;
And Vice Versa Pfile can be Created by
SQL> create pfile from spfile;