get COLUMN_NAME and TABLESPACE_NAME in one query

Question

is there any view in oracle where I can get both COLUMN_NAME and TABLESPACE_NAME in one query :

String selectSQL = "SELECT * FROM USER_TABLES WHERE table_name = '"+table+"' order by COLUMN_ID" ;
    try 
    {
        con = DBConne.getConnection();
        preparedStatement = con.prepareStatement(selectSQL);
        ResultSet rs = preparedStatement.executeQuery();
        //Tablespace = rs.getString("tablespace_name");
          while (rs.next()) {
                ClumnName.add(rs.getString("COLUMN_NAME"));
                ClumnType.add(rs.getString("DATA_TYPE"));
                ClumnType.add(rs.getString("DATA_LENGTH"));
                Tablespace = rs.getString("tablespace_name");

            }
    }catch{}

the error is : "Invalid column name"
how can i do it !

[Reference](http://stackoverflow.com/questions/696782/retrieve-column-names-from-java-sql-resultset) — thar45, Feb 19 '16 at 03:31
Please do a search before posting/asking the question that may help you to find more and more things . We can avoid duplicates here — thar45, Feb 19 '16 at 14:32

score 2 · Accepted Answer · answered Feb 19 '16 at 03:51

2

Try this ..

select a.TABLESPACE_NAME, b.TABLE_NAME, b.COLUMN_NAME from 
ALL_ALL_TABLES a, ALL_TAB_COLUMNS b
where a.TABLE_NAME=b.TABLE_NAME;

TABLESPACE_NAME -->> Name of the tablespace containing the table; null for partitioned, temporary, and index-organized tables.

answered Feb 19 '16 at 03:51

May

1,158
2
13
24

i added : "where a.TABLE_NAME=b.TABLE_NAME" + "AND a.TABLE_NAME='"+table+"';" + and it gives me an error : ORA-00933 – adam ar Feb 19 '16 at 09:18

score 0 · Answer 2 · answered Feb 19 '16 at 03:43

0

Use this query

SELECT * FROM USER_TABLES A,USER_TAB_COLUMNS B WHERE A.TABLE_NAME = B.TABLE_NAME

answered Feb 19 '16 at 03:43

Dinesh V

131
4

get COLUMN_NAME and TABLESPACE_NAME in one query

2 Answers2