Solved

oracle sql + output

Posted on 2013-02-01
6
449 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
Comment Utility
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
Comment Utility
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
Comment Utility
Is there anywhere to see a list of fields per each of the tables/views you mention..
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 3

Author Comment

by:pma111
Comment Utility
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
Comment Utility
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
Comment Utility
In sqlplus try if

set colsep ";"

gets what you want
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

I annotated my article on ransomware somewhat extensively, but I keep adding new references and wanted to put a link to the reference library.  Despite all the reference tools I have on hand, it was not easy to find a way to do this easily. I finall…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

743 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

16 Experts available now in Live!

Get 1:1 Help Now