ALTER TABLESPACE TEMP1 ADD TEMPFILE '<datafile.dbf>' SIZE 2G AUTOEXTEND ON NEXT 10M MAXSIZE 2G;Remove datafile from temporary tablespace:
alter tablespace TEMP1 drop tempfile '<datafile.dbf>';
select segment_name,segment_type,bytes/1024/1024 MB
from dba_segments where segment_type='TABLE' and segment_name=upper('FND_LOG_MESSAGES');
SELECT owner,
segment_name,
segment_type,
tablespace_name,
bytes/1048576 MB,
initial_extent,
next_extent,
extents,
pct_increase
FROM
DBA_SEGMENTS
WHERE
OWNER = 'table owner' AND
SEGMENT_NAME = 'table name' AND
SEGMENT_TYPE = 'TABLE'
/
-- list all tablespaces with their associated files, the
-- tablespace's allocated space, free space, and the
-- next free extent:
clear breaks
SET linesize 130
SET pagesize 60
break ON tablespace_name skip 1
col tablespace_name format a15
col file_name format a50
col tablespace_kb heading 'TABLESPACE|TOTAL KB'
col kbytes_free heading 'TOTAL FREE|KBYTES'
SELECT dd.tablespace_name tablespace_name, dd.file_name file_name, dd.bytes/1024 TABLESPACE_KB, SUM(fs.bytes)/1024 KBYTES_FREE, MAX(fs.bytes)/1024 NEXT_FREE
FROM sys.dba_free_space fs, sys.dba_data_files dd
WHERE dd.tablespace_name = fs.tablespace_name
AND dd.file_id = fs.file_id
GROUP BY dd.tablespace_name, dd.file_name, dd.bytes/1024
ORDER BY dd.tablespace_name, dd.file_name;
-- list datafiles, tablespace names, and size in MB:
col file_name format a50
col tablespace_name format a10
SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB
FROM dba_data_files
ORDER BY 1;
-- list tablespaces, size, free space, and percent free
-- query originally developed by Michael Lehmann
SELECT df.tablespace_name TABLESPACE, df.total_space TOTAL_SPACE,
fs.free_space FREE_SPACE, df.total_space_mb TOTAL_SPACE_MB,
(df.total_space_mb - fs.free_space_mb) USED_SPACE_MB,
fs.free_space_mb FREE_SPACE_MB,
ROUND(100 * (fs.free_space / df.total_space),2) PCT_FREE
FROM (SELECT tablespace_name, SUM(bytes) TOTAL_SPACE,
ROUND(SUM(bytes) / 1048576) TOTAL_SPACE_MB
FROM dba_data_files
GROUP BY tablespace_name) df,
(SELECT tablespace_name, SUM(bytes) FREE_SPACE,
ROUND(SUM(bytes) / 1048576) FREE_SPACE_MB
FROM dba_free_space
GROUP BY tablespace_name) fs
WHERE df.tablespace_name = fs.tablespace_name(+)
ORDER BY fs.tablespace_name;
set pages 9999 feed on lines 150
col user_concurrent_program_name format a40 head PROGRAM trunc
col elapsed format 9999
col request_id format 9999999 head REQUEST
col user_name format a12
col oracle_process_id format a5 head OSPID
col inst_name format a10
col sql_text format a30
col outfile_tmp format a30
col logfile_tmp format a30
select /*+ ordered */
fcp.user_concurrent_program_name
, fcr.request_id
, round(24*60*( sysdate - actual_start_date )) elapsed
, fu.user_name
, fcr.oracle_process_id
, sess.sid
, sess.serial#
, inst.inst_name
, sa.sql_text
, cp.plsql_dir || '/' || cp.plsql_out outfile_tmp
, cp.plsql_dir || '/' || cp.plsql_log logfile_tmp
from apps.fnd_concurrent_requests fcr
, apps.fnd_concurrent_programs_tl fcp
, apps.fnd_concurrent_processes cp
, apps.fnd_user fu
, gv$process pro
, gv$session sess
, gv$sqlarea sa
, sys.v_$active_instances inst
where fcp.concurrent_program_id = fcr.concurrent_program_id
and fcp.application_id = fcr.program_application_id
and fcr.controlling_manager = cp.concurrent_process_id
and fcr.requested_by = fu.user_id (+)
and fcr.oracle_process_id = pro.spid (+)
and pro.addr = sess.paddr (+)
and pro.inst_id = sess.inst_id (+)
and sess.sql_address = sa.address (+)
and sess.sql_hash_value = sa.hash_value (+)
and sess.inst_id = inst.inst_number (+)
and fcr.phase_code = 'R' /* only running requests */
yum groupinstall "GNOME Desktop Environment" "X Window System" "Desktop"
