Solved

oracle sql + output

Posted on 2013-02-01
6
485 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 3

Author Comment

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

Assisted Solution

by:Gerwin Jansen, EE MVE
Gerwin Jansen, EE MVE 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database

726 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