Check Free/Used space per tablespace :
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;
Sample output:
Tablespace Size (MB) Free (MB) % Free % Used
------------------------------ ---------- ---------- ---------- -----------------
UNDOTBS1 65 17.8125 27 73
EXAMPLE 100 22.625 23 75
USERS 5 1.0625 21 79
TEMP 20 2 10 90
SYSAUX 625.125 304.5 48 52
SYSTEM 700 9.0625 1 99
---------------------------------------------------------------------------
NLS_LANG parameter :
select DECODE(parameter, 'NLS_CHARACTERSET', 'CHARACTER SET',
'NLS_LANGUAGE', 'LANGUAGE',
'NLS_TERRITORY', 'TERRITORY') name,
value from v$nls_parameters
WHERE parameter IN ( 'NLS_CHARACTERSET', 'NLS_LANGUAGE', 'NLS_TERRITORY');
NAME VALUE
————- —————–
LANGUAGE AMERICAN
TERRITORY AMERICA
CHARACTER SET WE8ISO8859P1
export NLS_LANG=
-----------------------------------------------------------------------------
Object Count , Object Type for one particular User :
select owner,object_type,count(*) from dba_objects where owner='XXXXXXX' group by owner,object_type;
-----------------------------------------------------------------------------
Find running jobs in oracle database :
select sid, job,instance from dba_jobs_running;
select sid, serial#,machine, status, osuser,username from v$session where username!='NULL'; --all active users;
-----------------------------------------------------------------------------
Find long running jobs in oracle database :
select username,to_char(start_time, 'hh24:mi:ss dd/mm/yy') started, time_remaining remaining, message from v$session_longops where time_remaining = 0 order by time_remaining desc;
-----------------------------------------------------------------------------
Find the Size of Schema:
SELECT SUM (bytes / 1024 / 1024) "size"
FROM dba_segments WHERE owner = '&owner';
-----------------------------------------------------------------------------
How to shift a table from one tablespace to another.
ALTER TABLE <TABLE NAME to be moved> MOVE TABLESPACE <destination TABLESPACE NAME>;
------------------------------------------------------
Query to catch and generate script to kill the blocking sessions
select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select * from dba_blockers);
------------------------------------------------------------------------------------
Find the last DDL on Particular Object
SELECT object_name, object_type, last_ddl_time
FROM dba_objects
WHERE
object_name = 'xxxxxxxxxxx';
----------------------------------------------------------------------
Sessions :
To find total session on particular server ( machine )
select * from gv$session where MACHINE like '%XXXXX%' ;
To find session which are Active
select * from gv$session where MACHINE like '%XXXXX%' AND status='ACTIVE';
To find users machines by order
select machine, count(*) from gv$session group by machine order by 2;
----------------------------------------------------------------------
To find uptime of instnace
SELECT host_name, instance_name,
TO_CHAR(startup_time, 'DD-MM-YYYY HH24:MI:SS') startup_time,FLOOR(sysdate-startup_time) days
FROM v_$instance;
----------------------------------------------------------------------
SYS.USER$ is an Internal table in Oracle Database
PTIME provides the date the password was last changed.
LCOUNT provides the number of failed logins.
CTIME provides the date the user was created.
LTIME provides the date the user was last locked.
SELECT name " USER NAME ",
ctime " Date user was created ",
ptime " Password was last changed "
FROM sys.user$
WHERE name = 'XXXX';
** List of all database users and the date when the last password change occurred
select du.username, du.profile, du.account_status, u.ptime last_pwd_change
from dba_users du, sys.user$ u
where du.username = u.name
order by 2, 4;
----------------------------------------------------------------------
------------------------------------------------------
Query to catch and generate script to kill the blocking sessions
select 'alter system kill session '''||sid||','||serial#||''';' from v$session where sid in (select * from dba_blockers);
------------------------------------------------------------------------------------
Find the last DDL on Particular Object
SELECT object_name, object_type, last_ddl_time
FROM dba_objects
WHERE
object_name = 'xxxxxxxxxxx';
----------------------------------------------------------------------
Sessions :
To find total session on particular server ( machine )
select * from gv$session where MACHINE like '%XXXXX%' ;
To find session which are Active
select * from gv$session where MACHINE like '%XXXXX%' AND status='ACTIVE';
ACTIVE - Session currently executing SQLTo find users machines by order
select machine, count(*) from gv$session group by machine order by 2;
----------------------------------------------------------------------
To find uptime of instnace
SELECT host_name, instance_name,
TO_CHAR(startup_time, 'DD-MM-YYYY HH24:MI:SS') startup_time,FLOOR(sysdate-startup_time) days
FROM v_$instance;
----------------------------------------------------------------------
SYS.USER$ is an Internal table in Oracle Database
PTIME provides the date the password was last changed.
LCOUNT provides the number of failed logins.
CTIME provides the date the user was created.
LTIME provides the date the user was last locked.
SELECT name " USER NAME ",
ctime " Date user was created ",
ptime " Password was last changed "
FROM sys.user$
WHERE name = 'XXXX';
** List of all database users and the date when the last password change occurred
select du.username, du.profile, du.account_status, u.ptime last_pwd_change
from dba_users du, sys.user$ u
where du.username = u.name
order by 2, 4;
----------------------------------------------------------------------
Find unusable indexes:-
SELECT owner, index_name, tablespace_name
FROM dba_indexes
WHERE status = 'UNUSABLE';
Index partitions:
SELECT index_owner, index_name, partition_name, tablespace_name
FROM dba_ind_PARTITIONS
WHERE status = 'UNUSABLE';
Rebuild unusable indexes
Indexes:
SELECT 'alter index '||owner||'.'||index_name||' rebuild tablespace '||tablespace_name ||';'
FROM dba_indexes WHERE status = 'UNUSABLE';
Index partitions:
SELECT 'alter index '||owner||'.'||index_name ||' rebuild partition '||PARTITION_NAME||' TABLESPACE '||tablespace_name ||';'
FROM dba_ind_partitions
WHERE status = 'UNUSABLE';
Now verify the status of the index, it will be VALID
---------------------------------------------------------------------------------
How to Check the installed Oracle Software is 32/64 bit ?
select length(addr)*4 || '-bits' word_length from v$process where rownum=1;
---------------------------------------------------------------------------------
IDLE SESSIONS FOR MORE THAN 1 HOUR
(last_call_et/3600,2)||' hr'
last_call_et
from V$session where
last_call_et > 3600 and username is not null;
