Solved

oracle sql + output

Posted on 2013-02-01
6
462 Views
Last Modified: 2013-02-12
I have an oracle DBA exporting a querys results from an 11g database and the format it is outputting in is far from user freindly. i.e. its been output in a txt file but its not in any sort of tabular format.

I beleive the default tool to retreive rows is sql + in oracle (you may correct me), is there an easy way to export the rows in something that will be easily imported to excel, i.e. csv format, or at least a tabular format.

One last question in 11g, which table will list all database accounts, account status, password last set dates etc.
0
Comment
Question by:pma111
6 Comments
 
LVL 12

Accepted Solution

by:
praveencpk earned 167 total points
ID: 38843171
You can use SQL Developer to connect to oracle database 11g which free tool from oracle, using this you can export the table data into excel,CSV or Text in tabular form.

http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Here is the stepts to use

http://www.oracle.com/technetwork/developer-tools/sql-developer/export-intro-1-161239.html#exp
0
 
LVL 12

Expert Comment

by:praveencpk
ID: 38843175
you can get the account details in dba_users, all_users and sys.user$ tables but the password is encrypted in the DB.
0
 
LVL 3

Author Comment

by:pma111
ID: 38843180
Is there anywhere to see a list of fields per each of the tables/views you mention..
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 3

Author Comment

by:pma111
ID: 38843184
No way to do this in sql+ or sql* or whatever its called...
0
 
LVL 37

Assisted Solution

by:Gerwin Jansen
Gerwin Jansen earned 167 total points
ID: 38843336
For tables, have a look in ALL_TAB_COLUMNS - like this:

select
OWNER
,table_name
,column_NAME
,data_type
,data_length
from
 all_tab_columns
where
 OWNER not in ('SYS','SYSTEM')
order by owner,table_name, column_name;
0
 
LVL 20

Assisted Solution

by:flow01
flow01 earned 166 total points
ID: 38844386
In sqlplus try if

set colsep ";"

gets what you want
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains all about SQL Server Piecemeal Restore with examples in step by step manner.
Never store passwords in plain text or just their hash: it seems a no-brainier, but there are still plenty of people doing that. I present the why and how on this subject, offering my own real life solution that you can implement right away, bringin…
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now