Showing posts with label SQL. Show all posts
Showing posts with label SQL. Show all posts
Friday, December 8, 2023

How to generate a cold backup script for a database

›
Probably many ways to to this, but here is how I generated a simple file that after being made executable will copy all files to a specifi...
Friday, February 5, 2021

Find duplicate file names

›
You can find duplicate file names, on different file systems, by using this query: set lines 200 col "file_name" format a30 col...
Wednesday, May 13, 2020

How to use the TIMESTAMP_TO_SCN function

›
select timestamp_to_scn(to_timestamp('2020-05-11 14:36:22', 'YYYY-MM-DD HH24:MI:SS')) "scn" from dual; s...
Thursday, February 20, 2020

What does the SELECT ... FOR UPDATE statement do?

›
From the documentation : The FOR UPDATE clause lets you lock the selected rows so that other users cannot lock or update the rows until ...
Friday, August 9, 2019

How to find the number of executions for a specific SQL ID during a day

›
This query will gather data from DBA_HIST_ACTIVE_SESS_HISTORY for a specific day, and count the number of executions within that day: sel...
Tuesday, April 9, 2019

How to create a unique constraint with an existing index (the USING-clause)

›
Create the index: CREATE UNIQUE INDEX MYTABLE_IDX1 ON MYTABLE (ID,CATEGORY) LOCAL TABLESPACE USERS ONLINE; Create a unique constraint: ...
Monday, January 28, 2019

How to enable DML logging

›
DML logging can be used as a workaround if you have a large number of rows to process, and you expect that some of these will fail to be i...
Sunday, October 7, 2018

How to list all schemas and their sizes

›
If you want a list of schema sizes, join dba_segments with dba_users to limit the extract to users that are non-oracle maintained: set lin...
Thursday, March 15, 2018

›
The error SP2-0552: Bind variable "B2" not declared. can arise when using incorrect declarations in your SQL scripts. For exa...
Thursday, August 31, 2017

How to explain a SQL statement using bind variables

›
Sometimes I am asked to analyze an SQL statement picked up from Enterprise Manager Cloud Control. These SQL statements only reveal the bind...
Tuesday, March 14, 2017

How to use the Oracle 12c Row-limiting clauses

›
One of the most useful features of Oracle 12c is the ability to limit the number of rows from a query using top-N queries. Amongst others, t...
Wednesday, February 22, 2017

Insert As Select statements

›
The syntax for an Insert As Select Statement would be: INSERT INTO MY_INTERIM_TABLE (E_ID, ENAME,CREATED_DT, REVISION, FISCALYEAR, FPERIOD,...
Wednesday, February 1, 2017

How to display a SYSTIMESTAMP data type as a TIMESTAMP WITH TZ

›
To display a value of datatype SYSTIMESTAMP as TIMESTAMP WITH TIME ZONE, use TO_CHAR first, then convert to TIMESTAMP WITH TIMEZONE. The l...
Thursday, December 15, 2016

How to create a line-by-line comparison between the parameters used in two different databases

›
Here is a simple way to create a line-by-line comparison between two databases over a database link. First make sure there is tns Connecti...
Wednesday, August 24, 2016

How to add minutes, seconds, hours or days to a datetime value

›
Simply add the desired interval to your timestamp value: select current_timestamp, current_timestamp + interval '3' minute ...
Friday, August 19, 2016

Example on how to use WITH CHECK OPTION in an INSERT statement

›
The table "ALBUM" has the following columns: Name Null Type ------------ -------- ---------...
Thursday, July 14, 2016

INTERVAL conversion functions

›
There are 4 convertions functions that deal with INTERVAL datatypes, as listed below. The two first converts strings, the later two convert...
Wednesday, July 13, 2016

SYSTIMESTAMP and SYSDATE VS. CURRENT_TIMESTAMP and CURRENT_DAY

›
Most DBAs I've spoken to are familiar with the function SYSDATE, but there are some other functions, too, that may prove to be useful, e...
Tuesday, July 12, 2016

Overview of role privileges

›
The following UNION can be handy to get an overview of what system privileges, object privileges and other roles a specific role may contain...
Wednesday, July 6, 2016

A workaround for ORA-02287: sequence number not allowed here when using GROUP BY in a subquery

›
When you use GROUP BY in the subquery of an INSERT statement, Oracle will throw ORA-02287: sequence number not allowed here if you try t...
1 comment: