ORA-01653: unable to extend table by 64 in tablespace APPS_TS_TX_DATA

Unable to extend tablespace error occurs when the tablespace where the data is being loaded is running out of allocated space.
Ex: Table X is created in APPS_TS_TX_DATA with an allocated space of 10GB which might have man other tables.

When a program is trying to load data when the tablespace is full we encounter this error.

Below are couple of queries which I use. (Pls Note :These are from Ask Tom) I am sharing here to help others.

-----Tablespace stats-----
select (select decode(extent_management,'LOCAL','*',' ') ||
decode(segment_space_management,'AUTO','a ','m ')
from dba_tablespaces where tablespace_name = b.tablespace_name) ||
nvl(b.tablespace_name,
nvl(a.tablespace_name,'UNKOWN')) name,
kbytes_alloc/1024/1024 kbytes,
(kbytes_alloc-nvl(kbytes_free,0))/1024/1024 used,
nvl(kbytes_free/1024/1024,0) free,
((kbytes_alloc-nvl(kbytes_free,0))/ kbytes_alloc)*100 pct_used,
nvl(largest,0) largest,
nvl(kbytes_max,kbytes_alloc) Max_Size,
decode( kbytes_max, 0, 0, (kbytes_alloc/kbytes_max)*100) pct_max_used
from ( select sum(bytes)/1024 Kbytes_free,
max(bytes)/1024 largest,
tablespace_name
from sys.dba_free_space
group by tablespace_name ) a,
( select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_data_files
group by tablespace_name
union all
select sum(bytes)/1024 Kbytes_alloc,
sum(maxbytes)/1024 Kbytes_max,
tablespace_name
from sys.dba_temp_files
group by tablespace_name )b
where a.tablespace_name (+) = b.tablespace_name
and a.tablespace_name like '%APPS%DATA%'
order by 1

---Table Sizes------
select segment_name,sum((BYTES)/(1024*1024*1024)) "Allocated(GB)"
from dba_extents
where segment_name like 'XX_%' or segment_name like 'XXCOM_TEST'
group by segment_name
order by 2 desc;

Account Generator.

Found a good document about the basics of Account generator. Thought I would share.

repo.solutionbeacon.net/BrownfieldKaren_AcctGen_CP05.pdf

XML Publisher Excel output numbers getting truncated.

For XML Publisher reports when the output is in excel it is a common issue number fields are truncated. To avoid this use the following piece of code.

--XML Publisher Excel avoid numbers being truncated
TAGfo:bidi-override direction="ltr" unicode-bidi="bidi-override"TAGTAG?YOUR_FIELD?TAGTAG/fo:bidi-overrideTAG

Note this might effect the users from SUM etc on the fields.

FRM-40654 Record Has Been Updated. Requery Block To See Change (Oracle Fix)

Blank/space characters cause a lot of issues paticularly in Oracle forms.
Most of the times when you try to update a record you get an error message

FRM-40654: Record has been updated. Requery block to see change.

Most of the times this is caused by extra chars in the string columns. There is an Oracle fix issue for this issue. Apply the fix in TEST instance before applying in PROD. The fix is as follows.

Run script $FND_TOP/sql/afchrchk.sql

Option1= fnd_flex_values_tl
Option2= description
Option3 = N
Option4 =Y

Below is the explanation for all the options from the oracle script

--Table Name and Column Name (Always better to work on one table and column if known)
prompt Enter the table and column names to check for leading or trailing
prompt spaces and control characters. Like-style expressions are ok.
prompt Leave either table or column blank to check table/column.
prompt Table name (blank for all):
define tblarg = '&1'
prompt Column name (blank for all):
define colarg = '&2'

--I would recommend to input Y with caution for the newline option as oracle stores data with new lines chars in some cases.
--Ex : Atributes info on valuesets are stored with new lines chars
--Default N checks for only leading/trailing spaces
prompt Newline characters are acceptable in columns never queried in
prompt Forms, on only queried in multi-line items.
prompt Enter Y to also look for newline characters.
prompt Check for newline characters (Y/N)?
define newlinearg = '&3'

--option to fix or just list the errors. recommend to first list and then fix errors.
prompt Enter Y to automatically strip all leading/trailing spaces
prompt and control characters found.
prompt *** WARNING ***
prompt It is highly recommended to run first without stripping
prompt to be sure all detected values really should be fixed!

prompt Automatically fix all errors found (Y/N)?
define fixarg = '&4'

Oracle Find Locked Objects (SQL)

Finding Locked in Oracle can be tricky. I use the below approach.

--Locked Objects (Get SID from this query)
select o.object_name,lo.* from v$locked_object lo , dba_objects o
where lo.object_id = o.object_id
and o.object_name like '%' ;
--and o.object_type = 'PACKAGE'

This query finds all the locked objects. You can filter on packages. Sometimes the object may not appear in the above list. Then the below query might help.

--get serial and sid from this query
select * from v$session where sid = 215 or module like 'XX%';

--sid,#serial
alter system kill session '316,25481';

Oracle WEB ADi - Excel Macro settings in MS Office 2010

Well seting the macr settings in Office 2010 is as below. Excel Options -> Trust Center -> Trust Center Settings -> Macro Settings Trust access to the VBA project object model - check.