Query below lists:

(A) all non-nullable columns in tables accessible to the current user in Oracle database

(B) all non-nullable columns in tables in Oracle database

Query was executed under the Oracle9i Database version.

Query

A. Tables accessible to the current user

select col.owner as schema_name,
       col.table_name, 
       col.column_id,        
       col.column_name, 
       col.data_type, 
       col.data_length, 
       col.data_precision, 
       col.data_scale
from sys.all_tab_columns col
inner join sys.all_tables t on col.owner = t.owner 
                              and col.table_name = t.table_name

where col.nullable = 'N'
-- excluding some Oracle maintained schemas
and col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
order by col.owner, 
         col.table_name, 
         col.column_name;

B. If you have privilege on dba_tab_columns and dba_tables

select col.owner as schema_name,
       col.table_name, 
       col.column_id,        
       col.column_name, 
       col.data_type, 
       col.data_length, 
       col.data_precision, 
       col.data_scale
from sys.dba_tab_columns col
inner join sys.dba_tables t on col.owner = t.owner 
                              and col.table_name = t.table_name

where col.nullable = 'N'
-- excluding some Oracle maintained schemas
and col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 'WKSYS',
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC')  
order by col.owner, 
         col.table_name, 
         col.column_name;

Columns

• schema_name - table owner, schema name
• table_name - table name
• column_id - sequence number of the column as created
• column_name - column name
• data_type - column datatype
• data_length - column length in bytes
• data_precision - length - for NUMBER - decimal digits; for FLOAT - binary digits
• data_scale - digits to right of decimal point in a number

Rows

• One row represents one non-nullable column in a specific table in a database
• Scope of rows: (A) all non-nullable columns in tables accessible to the current user in Oracle database, (B) all non-nullable columns in tables in Oracle database
• Ordered by schema name, table name, column name

Sample results