Dropping user

--check the username,machine which are currently active
select username,machine,program,status from v$session where status='ACTIVE';
--Drop the user
SQL>DROP USER PCPCC_O_0302 CASCADE;

--If it gives the below error

SQL>;DROP USER PCPCC_O_0302 CASCADE;

DROP USER PCPCC_O_0302 CASCADE;
*
ERROR at line 1:
ORA-01940: cannot drop a user that is currently connected
--check the user which session id is active
SQL>select s.sid, s.serial#, s.status, p.spid  from v$session s, v$process p  where s.username = 'PCPCC_O_0302' and p.addr (+) = s.paddr ;
/
SID SERIAL# STATUS SPID
---------- ---------- -------- ------------
276 8395 INACTIVE 7088
314 10521 INACTIVE 7114

SQL>alter system kill session '276,8395';
SQL>alter system kill session '314,10521';

ssh/scp command with out password

SCP WITH OUT PASSWORD:-

scp command:-
scp [username]@host:folder
password:- give the password
Example:- scp root@172.22.33.48:/data
this will ask the password:- root123#

Using SSH and SCP without a password

From time to time you may want to write scripts that will allow you to copy files to a server, or login, without being prompted for passwords. This can make them simpler to write and also prevents you from having to embed the password in your code.

SCP has a feature that allows you to do this. You no longer have to worry about prying eyes seeing your passwords nor worrying about your script breaking when someone changes the password. You can configure SSH to do this by generating and installing data transfer encryption keys that are tied to the IP addresses of the two servers. The servers then use these pre-installed keys to authenticate one another for each file transfer. As you may expect, this feature doesn't work well with computers with IP addresses that periodically change, such as those obtained via DHCP.

There are some security risks though. The feature is automatically applied to SSH as well. Someone could use your account to log in to the target server by entering the username alone. It is therefore best to implement this using unprivileged accounts on both the source and target servers.

The example that follows enables this feature in one direction (from server bigboy to server smallfry) and only uses the unprivileged account called filecopy.

Configuration: Client Side(FILE WILL SEND FROM THIS MACHINE)

Here are the steps you need to do on the computer that acts as the SSH client:

1) Generate your SSH encryption key pair for the filecopy account. Press the Enter key each time you are prompted for a password to be associated with the keys. (Do not enter a password.)

[filecopy@bigboy filecopy]# ssh-keygen -t dsa

Generating public/private dsa key pair.
Enter file in which to save the key
(/filecopy/.ssh/id_dsa):
Enter passphrase (empty for no passphrase):
Enter same passphrase again:
Your identification has been saved in
/filecopy/.ssh/id_dsa.
Your public key has been saved in
/filecopy/.ssh/id_dsa.pub.
The key fingerprint is:
1e:73:59:96:25:93:3f:8b:50:39:81:9e:e3:4a:a8:aa
[filecopy@bigboy filecopy]#

2) These keyfiles are stored in the.ssh subdirectory of your home directory. View the contents of that directory. The file named id_dsa is your private key, and id_dsa.pub is the public key that you will be sharing with your target server. Versions other than RedHat/Fedora may use different filenames, use the SSH man pages to verify this.

[filecopy@bigboy filecopy]# cd ~/.ssh
[filecopy@bigboy filecopy]# ls
id_dsa id_dsa.pub known_hosts
[filecopy@bigboy .ssh]#
3) Copy only the public key to the home directory of the account to which you will be sending the file.
[filecopy@bigboy .ssh]# scp id_dsa.pub filecopy@smallfry:public-key.tmp
Now, on to the server side of the operation.

Configuration - Server Side(FILE WILL REACH TO THIS MACHINE)

Here are the steps you need to do on the computer that will act as the SSH server.

1) Log into smallfry as user filecopy. Create an .ssh subdirectory in your home directory and then go to it with cd.

[filecopy@smallfry filecopy]# ls

public-key.tmp

[filecopy@smallfry filecopy]# mkdir .ssh
[filecopy@smallfry filecopy]# chmod 700 .ssh

[filecopy@smallfry filecopy]# cd .ssh

2) Append the public-key.tmp file to the end of the authorized_keys file using the >> append redirector with the cat command. The authorized_keys file contains a listing of all the public keys from machines that are allowed to connect to your Smallfry account without a password. Versions other than RedHat/Fedora may use different filenames, use the SSH man pages to verify this.


[filecopy@smallfry .ssh]# cat ~/public-key.tmp >> authorized_keys

[filecopy@smallfry .ssh]# rm ~/public-key.tmp

to check current temporary tablespace usage

---------current tempoary tablespace usage---------------
SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

-----whos is using temporary tablespace and what is the size----------

select su.username
, dbms_lob.substr(sq.sql_fulltext, 4000, 1) sql_text
, su.blocks
, su.extents
, su.segtype
, (su.blocks*8)/1024 size_MB
, sq.last_active_time
, sq.last_load_time
from v$sort_usage su
join v$sqlarea sq on (su.sql_id = sq.sql_id)
left join v$session s on (s.sql_id=su.sql_id)
where su.tablespace='TEMP';

----------------current Temporay tablespace usage--------------


select TABLESPACE_NAME, BYTES_USED/1024/1024, BYTES_FREE/1024/1024 from V$TEMP_SPACE_HEADER;

-----------checking the temporary tablespace size---------------------------

SELECT A.tablespace_name tablespace, D.mb_total,
SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_used,
D.mb_total - SUM (A.used_blocks * D.block_size) / 1024 / 1024 mb_free
FROM v$sort_segment A,
(
SELECT B.name, C.block_size, SUM (C.bytes) / 1024 / 1024 mb_total
FROM v$tablespace B, v$tempfile C
WHERE B.ts#= C.ts#
GROUP BY B.name, C.block_size
) D
WHERE A.tablespace_name = D.name
GROUP by A.tablespace_name, D.mb_total;

--------------------what is the query executed in the temp---------

SELECT a.username,
b.tablespace,
ROUND(((b.blocks*p.value)/1024/1024),2)||'M' "SIZE OF THE QUERY",
v.sql_text,
V.LAST_LOAD_TIME,
a.program
FROM v$session a,
sys.v_$sort_usage b,
sys.v_$parameter p,
V$SQL v
WHERE p.name = 'db_block_size'
AND a.saddr = b.session_addr and a.SQL_ID = v.SQL_ID
ORDER BY b.tablespace, b.blocks;

-------------checking the temporary file and temptablespace--------------
select th.TABLESPACE_NAME,
th.BYTES_USED/1024/1024 "USED MB",
th.BYTES_FREE/1024/1024 "FREE MB",
vt.name
from V$TEMP_SPACE_HEADER th,v$tempfile vt
where th.FILE_ID=vt.FILE#;
-----------------------------free size per the datafile-----------------------------------
SELECT SUBSTR (df.NAME, 1, 40) file_name, df.bytes / 1024 / 1024 allocated_mb,
((df.bytes / 1024 / 1024) - NVL (SUM (dfs.bytes) / 1024 / 1024, 0))  used_mb,
NVL (SUM (dfs.bytes) / 1024 / 1024, 0)  free_space_mb
FROM v$datafile df, dba_free_space dfs
WHERE df.file# = dfs.file_id(+)
GROUP BY dfs.file_id, df.NAME, df.file#, df.bytes
ORDER BY file_name;

---------------check free size along with temp file-----------------------------------

SELECT /* + RULE */ df.tablespace_name "Tablespace",
df.bytes / (1024 * 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free (MB)",
Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs,
(SELECT tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */ df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024),
Nvl(Round((SUM(fs.bytes) - df.bytes_used) * 100 / fs.bytes), 1),
Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs,
(SELECT tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;

check the free space size of tablespace in %value:-
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024, 0) "Size (M)",
(NVL(a.bytes -NVL(f.bytes, 0), 0)/1024/1024) "Used (M)",
TO_CHAR(NVL((a.bytes -NVL(f.bytes, 0)) / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select
tablespace_name, sum(bytes) bytes from dba_data_files group by tablespace_name) a, (select
tablespace_name, sum(bytes) bytes from dba_free_space group by tablespace_name) f WHERE
d.tablespace_name like '&TABLESPACE%' and d.tablespace_name = a.tablespace_name(+) AND d.tablespace_name = f.tablespace_name(+) AND NOT
(d.extent_management like 'LOCAL' AND d.contents like 'TEMPORARY')
UNION ALL
SELECT d.status "Status", d.tablespace_name "Name", d.contents "Type", d.extent_management "Extent Management",
NVL(a.bytes / 1024 / 1024, 0) "Size (M)", NVL(t.bytes, 0)/1024/1024 "Used (M)",
TO_CHAR(NVL(t.bytes / a.bytes * 100, 0), '990.00') "Used %"
FROM sys.dba_tablespaces d, (select tablespace_name, sum(bytes) bytes from dba_temp_files
group by tablespace_name) a, (select tablespace_name, sum(bytes_cached) bytes from
v$temp_extent_pool group by tablespace_name) t WHERE d.tablespace_name = a.tablespace_name(+) AND
d.tablespace_name = t.tablespace_name(+) AND d.extent_management like 'LOCAL' AND d.contents like
'TEMPORARY'
order by 2
/