usefull Oracle queries

oracle.sql

SELECT * FROM V$VERSION; --get current Oracle instance version
--for Oracle 10g 

--get total size in MB for current user
SELECT sum(bytes)/1024/1024 size_in_mb  FROM DBA_SEGMENTS
WHERE OWNER='<user_name>';
--DBA_SEGMENTS describes the storage allocated for all segments in the database.
--related view: USER_SEGMENTS describes the storage allocated for the segments 
--owned by the current user's objects. 
--This view does not display the OWNER, HEADER_FILE, HEADER_BLOCK, or RELATIVE_FNO columns.

--get list of tables for current user
select tablespace_name, table_name from user_tables;


SELECT column_name, data_type, data_length, data_precision, data_scale, num_distinct,
  decode(data_type
          ,'NUMBER'       ,to_char(utl_raw.cast_to_number(low_value))
          ,'VARCHAR2'     ,to_char(utl_raw.cast_to_varchar2(low_value))
          ,'NVARCHAR2'    ,to_char(utl_raw.cast_to_nvarchar2(low_value))
          ,'BINARY_DOUBLE',to_char(utl_raw.cast_to_binary_double(low_value))
          ,'BINARY_FLOAT' ,to_char(utl_raw.cast_to_binary_float(low_value))
          ,'DATE',to_char(1780+to_number(substr(low_value,1,2),'XX')
                          +to_number(substr(low_value,3,2),'XX'))||'-'
                  ||to_number(substr(low_value,5,2),'XX')||'-'
                  ||to_number(substr(low_value,7,2),'XX')||' '
                  ||(to_number(substr(low_value,9,2),'XX')-1)||':'
                  ||(to_number(substr(low_value,11,2),'XX')-1)||':'
                  ||(to_number(substr(low_value,13,2),'XX')-1)
          , 'RAW', utl_raw.cast_to_raw(low_value)
          ,  low_value
                                                                                  ) low_v
            ,decode(data_type
          ,'NUMBER'       ,to_char(utl_raw.cast_to_number(high_value))
          ,'VARCHAR2'     ,to_char(utl_raw.cast_to_varchar2(high_value))
          ,'NVARCHAR2'    ,to_char(utl_raw.cast_to_nvarchar2(high_value))
          ,'BINARY_DOUBLE',to_char(utl_raw.cast_to_binary_double(high_value))
          ,'BINARY_FLOAT' ,to_char(utl_raw.cast_to_binary_float(high_value))
          ,'DATE',to_char(1780+to_number(substr(high_value,1,2),'XX')
                          +to_number(substr(high_value,3,2),'XX'))||'-'
                  ||to_number(substr(high_value,5,2),'XX')||'-'
                  ||to_number(substr(high_value,7,2),'XX')||' '
                  ||(to_number(substr(high_value,9,2),'XX')-1)||':'
                  ||(to_number(substr(high_value,11,2),'XX')-1)||':'
                  ||(to_number(substr(high_value,13,2),'XX')-1)
          ,'RAW',utl_raw.cast_to_raw(high_value)
          ,  high_value
                                                                                   ) hi_v
FROM   all_tab_columns
WHERE  table_name = 'table_name';

--build a column list
--java columns
SELECT column_name || '=' ||
       CASE
       WHEN data_type IN ('VARCHAR', 'VARCHAR2', 'CHAR', 'NCLOB', 'NCHAR', 'NVARCHAR2') THEN 'String'
       WHEN data_type IN ('DATE', 'DATETIME', 'TIME') THEN 'String'
       WHEN data_type='NUMBER' AND data_scale = 0 AND (ABS(utl_raw.cast_to_number(high_value)) BETWEEN 0 AND 127) THEN 'Integer'
       WHEN data_type='NUMBER' AND data_scale = 0 AND (ABS(utl_raw.cast_to_number(high_value)) BETWEEN 128   AND 32767) THEN 'Integer'
       WHEN data_type='NUMBER' AND data_scale = 0 AND (ABS(utl_raw.cast_to_number(high_value)) BETWEEN 32768 AND 2147483647) THEN 'Integer'
       WHEN data_type='NUMBER' AND data_scale = 0 AND (ABS(utl_raw.cast_to_number(high_value)) BETWEEN 2147483648 AND 9223372036854775807) THEN 'Long'
       WHEN data_type='NUMBER' AND data_scale !=0 THEN 'Float'

       ELSE data_type
       END
       || ',' AS type_str
FROM   all_tab_columns
WHERE  table_name = 'ADDRESS' AND data_type != 'BLOB'
;
-- just a list of columns like for select

SELECT  '`' || LOWER(column_name) || '`,' AS c_list, data_type FROM all_tab_columns
WHERE table_name = 'table_name';

--hive-columns
SELECT LOWER(column_name) || ' ' ||
       CASE
          WHEN data_type IN ('VARCHAR', 'VARCHAR2', 'CHAR',  'NCHAR', 'NVARCHAR2') THEN 'STRING'
          WHEN data_type IN ('DATE', 'DATETIME', 'TIME') THEN 'STRING'
          WHEN data_type='NUMBER' AND data_scale = 0 AND (ABS(utl_raw.cast_to_number(high_value)) BETWEEN 0 AND 127) THEN 'TINYINT'
          WHEN data_type='NUMBER' AND data_scale = 0 AND (ABS(utl_raw.cast_to_number(high_value)) BETWEEN 128 AND 32767) THEN 'SMALLINT'
          WHEN data_type='NUMBER' AND data_scale = 0 AND (ABS(utl_raw.cast_to_number(high_value)) BETWEEN 32768 AND 2147483647) THEN 'INT'
          WHEN data_type='NUMBER' AND data_scale = 0 AND (ABS(utl_raw.cast_to_number(high_value)) BETWEEN 2147483648 AND 9223372036854775807) THEN 'BIGINT'
          WHEN data_type='NUMBER' AND data_scale !=0 THEN 'FLOAT'
         ELSE data_type
       END
       || ',' AS type_str, data_type
FROM   all_tab_columns
WHERE  table_name = 'ADDRESS' AND data_type != 'BLOB'
;


-- Limit in oracle select
SELECT * FROM <MY_TABLE>
WHERE ROWNUM <=5;


--list of tables ORACLE
SELECT table_name from user_tables where num_rows>0 --AND ROWNUM<=2;