logo

Openplatform.xyz

Placeholder for our stuff related to Telecom, IT, Internet of things (IOT), ESP8266, Raspberry Pi

Oracle Database

 PuTTY doesn't support column greater than 180 approx, What you can do is pipe your output through less with the -S option, which tells it not to wrap long lines, and use the left and right arrow keys to scroll sideways:

your_command | less -S
./queryScript | less -S


Note that this will only work if the command is invoked from a shell, but not from within an SQL client

Login as sysdba
sqlplus / as sysdba

Query to get oracle DATABASE name
select * from v$database;

Query to get database mode
select open_mode from v$database;

Query to get hostname
select host_name from v$instance;

Query to get DATABASE_ROLE (PRIMARY or PHYSICAL STANDBY)
select database_role from v$database;

Find last queries of oracle (This will give you last half day i.e. 12 hours).
SELECT * FROM V$SQL V where to_date(v.FIRST_LOAD_TIME,'YYYY-MM-DD hh24:mi:ss') > (sysdate - 1/2)

Dump a Oracle table into a CSV file

SET PAGESIZE 50000 => Set this to a much bigger value. This value indicates the number of lines per page. The header line will get printed in every page. In order to avoid this, set it to a bigger value so that the header appears only once.

SET COLSEP "," => Setting the column separator to ",". With this setting, the list displayed by the SELECT clause will be comma separated.

SET LINESIZE 200 => The number of characters per line. The default is 80 which means after 80 characters, the rest of the content will be in the next line. Set this to a value which is good enough for the entire record to come in a single line.

SET FEEDBACK OFF => When a select query is executed, a statement appears at the prompt, say "25 rows selected". In order to prevent this from appearing in the CSV file, the feedback is put off.

SPOOL $FILE => Spool command records the session queries and results into the file specified. In other words, this will write the results of the query to the file.

SPOOL OFF => To stop writing the contents of the sql session to the file

 



Suresh Hariramani

I am an IOT enthusiast with more than 20 years of experience in the IT sector. Specializing in telecom service's; follow me for some very innovative and best in class IOT products as I unfold my knowledge and passion for the subject.


Vatsal Hariramani

Just me, myself and I, exploring the universe of uknownment. I have a heart of love and interests in technology, IOT and travel . And I want to share my world with you .