Oracle sqlplus silence output
Use the following code on your scheduled Oracle scripts to silence terminal output except your data.
SET autocommit off;
SET echo off;
SET feedback off;
SET head off;
SET heading off;
SET linesize 0;
SET newpage none;
SET newpage none;
SET pagesize 0;
SET sqlprompt '';
SET sqlnumber off;
SET sqlbl off;
SET trimspool on;
SET verify off;
SET TRIMSPOOL on;
SET underline off;
spool test.csv
select type_code||', '|| identifier from my_table;
spool off
exit
Tags:
You may also enjoy:
Forcing ASMM component to shrink
#oracle #databaseHere's the way to force the shared pool to shrink dynamically. Documentation states that ASMM can only increase shared pool, and can't shrink. When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this va... Read more
20 Mar 2019 - 2 minute read
How to extract Oracle user DDL
#database #oracleIn some cases, you need to duplicate an Oracle user, along with all it's privileges. Doing this manually can be tedious. Hopefully, we can use the data dictionary to extract the data we need and dump it into a SQL file to modify and replay. Here's how to do it: ```sql set head off set pages 0 set long 9999999 spool user_script.sql SELECT DBMS... Read more
20 Feb 2018 - 1 minute read
Restart a hung Oracle database
#oracleOn rare occasions, mostly during a high server load peak or a process failure, an Oracle instance may not accept any connection. Either from regular users as well as SYSDBA. This situation is called a **hung database** and must be quickly resolved as the database isn't accessible for your users anymore. As you cannot connect to the hung database... Read more
20 May 2017 - 1 minute read
Rename a Postgresql database
#database #postgresqlHere's the procedure to rename a PostgreSQL database: 1. Disconnect from the database that you want to rename and connect to a different database. 2. Check and terminate all active connections to the database that you want to rename. 3. Use the `ALTER DATABASE` statement to rename the database to the new one. Let’s take a look at an example of... Read more
20 May 2019 - 1 minute read
How to duplicate a Postgresql database
#database #postgresqlIf you need to duplicate an existing Postgresql database, and possibly transfer ownership of the database objects to a new user, here's how to do that in a quick way: ```sql -- First, I recommand getting the size of the database to copy, as this might be important for the rest of the process. SELECT pg_database.datname,pg_size_pretty(pg_databas... Read more
20 Apr 2019 - less than 1 minute read
Forcing ASMM component to shrink
#oracle #databaseHere's the way to force the shared pool to shrink dynamically. Documentation states that ASMM can only increase shared pool, and can't shrink. When the automatic shared memory management feature is enabled, the internal tuning algorithm tries to determine an optimal size for the shared pool based on the workload. It usually converges on this va... Read more
20 Mar 2019 - 2 minute read