How to solve ORA-02291: integrity constraint violated - parent key not found .
=================================================================================
SQL> insert into emp values(7935,'XXX','XXXX',7782,'23-JAN-83',10000,null,50) ;
insert into emp values(7935,'XXX','XXXX',7782,'23-JAN-83',10000,null,50)
*
ERROR at line 1:
ORA-02291: integrity constraint (ANUJ1.FK_DEPTNO) violated - parent key not found
ALTER TABLE anuj1.dept DISABLE PRIMARY KEY;
ww
alter table anuj1.dept disable primary key cascade ;
inserted wrong row !!! in dept table
insert into emp values(7935,'XXX','XXXX',7782,'23-JAN-83',10000,null,50) ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7935 XXX XXXX 7782 23-JAN-83 10000 50 <<<<<
alter table anuj1.dept enable primary key cascade ;
SQL> alter table anuj1.dept enable primary key ; <<<< Primary Key enabled !!!
Table altered.
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850 30
7782 CLARK MANAGER 7839 09-JUN-81 2450 10
7839 KING PRESIDENT 17-NOV-81 5000 10
7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30
7900 JAMES CLERK 7698 03-DEC-81 950 30
7934 MILLER CLERK 7782 23-JAN-82 1300 10
7935 XXX XXXX 7782 23-JAN-83 10000 50 <<<<<
set linesize 300
COLUMN owner FORMAT A25
COLUMN table_name FORMAT A25
COLUMN constraint_name FORMAT A25
COLUMN constraint_type FORMAT A25
COLUMN status FORMAT A25
COLUMN validated FORMAT A25
SELECT
owner,
table_name,
constraint_name,
constraint_type,
status,
validated
FROM
dba_constraints
WHERE 1=1
and status = 'DISABLED'
and owner='ANUJ1'
ORDER BY table_name, constraint_type;
OWNER TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE STATUS VALIDATED
------------------------- ------------------------- ------------------------- ------------------------- ------------------------- -------------------------
ANUJ1 BIN$RFG84ieDVBXgYyL1akC3x BIN$RFG84ieCVBXgYyL1akC3x P DISABLED NOT VALIDATED
A==$0 A==$0
ANUJ1 EMP FK_DEPTNO R DISABLED NOT VALIDATED
2 rows s
set linesize 300
COLUMN owner FORMAT A25
COLUMN table_name FORMAT A25
COLUMN constraint_name FORMAT A25
COLUMN constraint_type FORMAT A25
COLUMN status FORMAT A25
COLUMN validated FORMAT A25
SELECT
owner,
table_name,
constraint_name,
status,
validated
FROM
all_constraints
WHERE
status != 'ENABLED'
--AND validated = 'NOT VALIDATED'
and owner='ANUJ1'
ORDER BY owner, table_name;
set serveroutput on
define O='ANUJ1' -----<<<<<<<<<<<<
DECLARE
v_sql_stmt VARCHAR2(200);
BEGIN
DBMS_OUTPUT.PUT_LINE('-- Starting Constraint Enable Script --');
DBMS_OUTPUT.PUT_LINE('--');
FOR rec IN (
SELECT
owner,
table_name,
constraint_name
FROM
all_constraints -- Use ALL_CONSTRAINTS to cover all schemas you can see
WHERE
status = 'DISABLED'
-- Optional: Filter by specific schema if needed (e.g., AND owner = 'ANUJ1')
AND owner IN ('&O') -- Exclude system constraints
ORDER BY
owner, table_name, constraint_name
) LOOP
-- Generate the ENABLE statement, using VALIDATE (default) to check data integrity
v_sql_stmt := 'ALTER TABLE ' || rec.owner || '.' || rec.table_name || ' ENABLE CONSTRAINT ' || rec.constraint_name || ';';
-- Print the generated SQL statement
DBMS_OUTPUT.PUT_LINE(v_sql_stmt);
END LOOP;
DBMS_OUTPUT.PUT_LINE('--');
DBMS_OUTPUT.PUT_LINE('-- Constraint Enable Script Generated Successfully --');
END;
/
ALTER TABLE ANUJ1.EMP ENABLE CONSTRAINT FK_DEPTNO;
SQL> SQL> ALTER TABLE ANUJ1.EMP ENABLE CONSTRAINT FK_DEPTNO;
ALTER TABLE ANUJ1.EMP ENABLE CONSTRAINT FK_DEPTNO
*
ERROR at line 1:
ORA-02298: cannot validate (ANUJ1.FK_DEPTNO) - parent keys not found
COLUMN foreign_key_owner FORMAT A25
COLUMN foreign_key_name FORMAT A25
COLUMN child_table FORMAT A25
COLUMN foreign_key_columns FORMAT A25
COLUMN parent_owner FORMAT A25
COLUMN parent_table FORMAT A25
COLUMN parent_key_columns FORMAT A25
SELECT
c.owner AS foreign_key_owner,
c.constraint_name AS foreign_key_name,
cc.table_name AS child_table,
LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position) AS foreign_key_columns,
r.owner AS parent_owner,
r.table_name AS parent_table,
LISTAGG(rc.column_name, ', ') WITHIN GROUP (ORDER BY rc.position) AS parent_key_columns
FROM all_constraints c
JOIN all_cons_columns cc ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
JOIN all_constraints r ON c.r_owner = r.owner AND c.r_constraint_name = r.constraint_name
JOIN all_cons_columns rc ON r.owner = rc.owner AND r.constraint_name = rc.constraint_name AND cc.position = rc.position
WHERE
c.owner = 'ANUJ1'
AND c.constraint_name = 'FK_DEPTNO'
AND c.constraint_type = 'R'
GROUP BY c.owner, c.constraint_name, cc.table_name, r.owner, r.table_name;
FOREIGN_KEY_OWNER FOREIGN_KEY_NAME CHILD_TABLE FOREIGN_KEY_COLUMNS PARENT_OWNER PARENT_TABLE PARENT_KEY_COLUMNS
------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- -------------------------
ANUJ1 FK_DEPTNO EMP DEPTNO ANUJ1 DEPT DEPTNO
SELECT DISTINCT e.DEPTNO AS offending_deptno
FROM ANUJ1.EMP e
WHERE
e.DEPTNO IS NOT NULL
MINUS ------<<<<<<
SELECT d.DEPTNO
FROM ANUJ1.DEPT d
;
DELETE FROM ANUJ1.EMP e
WHERE e.DEPTNO IS NOT NULL
AND e.DEPTNO IN (
SELECT e_bad.DEPTNO
FROM ANUJ1.EMP e_bad
MINUS
SELECT d.DEPTNO
FROM ANUJ1.DEPT d
);
1 row deleted.
SQL> ALTER TABLE ANUJ1.EMP ENABLE CONSTRAINT FK_DEPTNO;
Table altered.
COLUMN foreign_key_owner FORMAT A25
COLUMN foreign_key_name FORMAT A25
COLUMN child_table FORMAT A25
COLUMN foreign_key_columns FORMAT A25
COLUMN parent_owner FORMAT A25
COLUMN parent_table FORMAT A25
COLUMN parent_key_columns FORMAT A25
COLUMN status FORMAT A10
COLUMN validated FORMAT A15
SELECT
c.owner AS foreign_key_owner,
c.constraint_name AS foreign_key_name,
cc.table_name AS child_table,
LISTAGG(cc.column_name, ', ') WITHIN GROUP (ORDER BY cc.position) AS foreign_key_columns,
r.owner AS parent_owner,
r.table_name AS parent_table,
LISTAGG(rc.column_name, ', ') WITHIN GROUP (ORDER BY rc.position) AS parent_key_columns,
c.status AS status,
c.validated AS validated
FROM all_constraints c
JOIN all_cons_columns cc ON c.owner = cc.owner AND c.constraint_name = cc.constraint_name
JOIN all_constraints r ON c.r_owner = r.owner AND c.r_constraint_name = r.constraint_name
JOIN all_cons_columns rc ON r.owner = rc.owner AND r.constraint_name = rc.constraint_name AND cc.position = rc.position
WHERE 1=1
and c.owner = 'ANUJ1'
-- AND c.constraint_name = 'FK_DEPTNO'
-- AND c.constraint_type = 'R'
GROUP BY c.owner, c.constraint_name, cc.table_name, r.owner, r.table_name, c.status, c.validated;
FOREIGN_KEY_OWNER FOREIGN_KEY_NAME CHILD_TABLE FOREIGN_KEY_COLUMNS PARENT_OWNER PARENT_TABLE PARENT_KEY_COLUMNS STATUS VALIDATED
------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ------------------------- ---------- ---------------
ANUJ1 FK_DEPTNO EMP DEPTNO ANUJ1 DEPT DEPTNO ENABLED VALIDATED
ANUJ1 FK_INVITDEL T_INVOICE_ITEM_DELIVERY INVOICE_ID, PRODUCT_ID ANUJ1 T_INVOICE_ITEM INVOICE_ID, PRODUCT_ID ENABLED VALIDATED
ANUJ1 FK_PROD_PROD T_PRODUCT PRODUCT_EQUIVALENT ANUJ1 T_PRODUCT PRODUCT_ID ENABLED VALIDATED
ANUJ1 FK_PROD_PROV T_PRODUCT PROVIDER_ID ANUJ1 T_PROVIDER PROVIDER_ID ENABLED VALIDATED
ANUJ1 FK_INVITEM_INVOICE T_INVOICE_ITEM INVOICE_ID ANUJ1 T_INVOICE INVOICE_ID ENABLED VALIDATED
ANUJ1 FK_INVITEM_PRODUCT T_INVOICE_ITEM PRODUCT_ID ANUJ1 T_PRODUCT PRODUCT_ID ENABLED VALIDATED
ANUJ1 FK_PRODWARE_PRODUCT T_PRODUCT_WAREHOUSE PRODUCT_ID ANUJ1 T_PRODUCT PRODUCT_ID ENABLED VALIDATED
ANUJ1 FK_PRODWARE_WAREHOUSE T_PRODUCT_WAREHOUSE WAREHOUSE_ID ANUJ1 T_WAREHOUSE WAREHOUSE_ID ENABLED VALIDATED
8 rows selected.
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
v_sql_stmt VARCHAR2(500);
v_target_owner CONSTANT VARCHAR2(128) := 'ANUJ1' -- or keep NULL to target all
BEGIN
DBMS_OUTPUT.PUT_LINE('-- START: Dynamic Primary Key DISABLE Script (DBA_CONSTRAINTS) --');
DBMS_OUTPUT.PUT_LINE('--');
FOR rec IN (
SELECT
owner,
table_name
FROM
dba_constraints
WHERE
constraint_type = 'P' -- 'P' for Primary Key
AND status = 'ENABLED'
-- Filter out system schemas
AND owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'CTXSYS')
-- Optional: Filter by specific owner if V_TARGET_OWNER is set
AND (v_target_owner IS NULL OR owner = v_target_owner)
ORDER BY owner, table_name
) LOOP
-- Generate the DISABLE command with CASCADE to handle foreign key dependencies
v_sql_stmt := 'ALTER TABLE ' || rec.owner || '.' || rec.table_name || ' DISABLE PRIMARY KEY CASCADE;';
-- Print the generated SQL statement
DBMS_OUTPUT.PUT_LINE(v_sql_stmt);
END LOOP;
DBMS_OUTPUT.PUT_LINE('--');
DBMS_OUTPUT.PUT_LINE('-- END: Primary Key DISABLE Script Generated. Copy and execute above statements. --');
END;
/
===
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
v_sql_stmt VARCHAR2(500);
-- Define the schema owner you want to target, or NULL for all non-system schemas
v_target_owner CONSTANT VARCHAR2(128) := 'ANUJ1' -- or keep NULL to target all
BEGIN
DBMS_OUTPUT.PUT_LINE('-- START: Dynamic Primary Key ENABLE Script (DBA_CONSTRAINTS) --');
DBMS_OUTPUT.PUT_LINE('--');
FOR rec IN (
SELECT
owner,
table_name
FROM
dba_constraints
WHERE
constraint_type = 'P' -- 'P' for Primary Key
AND status = 'DISABLED'
-- Filter out system schemas
AND owner NOT IN ('SYS', 'SYSTEM', 'OUTLN', 'DBSNMP', 'CTXSYS')
-- Optional: Filter by specific owner if V_TARGET_OWNER is set
AND (v_target_owner IS NULL OR owner = v_target_owner)
ORDER BY
owner, table_name
) LOOP
-- Generate the ENABLE command (default is ENABLE VALIDATE)
v_sql_stmt := 'ALTER TABLE ' || rec.owner || '.' || rec.table_name || ' ENABLE PRIMARY KEY;';
-- Print the generated SQL statement
DBMS_OUTPUT.PUT_LINE(v_sql_stmt);
END LOOP;
DBMS_OUTPUT.PUT_LINE('--');
DBMS_OUTPUT.PUT_LINE('-- END: Primary Key ENABLE Script Generated. Copy and execute above statements. --');
END;
/
Thursday, 4 December 2025
How to solve ORA-02291: integrity constraint violated - parent key not found
Wednesday, 26 November 2025
unix command for DBA
Basic File Navigation
The "pwd" command displays the current directory.
root> pwd
/u01/app/oracle/product/9.2.0.1.0
The "ls" command lists all files and directories in the specified directory. If no location is defined
it acts on the current directory.
root> ls
root> ls /u01
root> ls -al
The "-a" flag lists hidden "." files. The "-l" flag lists file details.
The "cd" command is used to change directories.
root> cd /u01/app/oracle
The "touch" command is used to create a new empty file with the default permissions.
root> touch my.log
The "rm" command is used to delete files and directories.
root> rm my.log
root> rm -R /archive
The "-R" flag tells the command to recurse through subdirectories.
The "mv" command is used to move or rename files and directories.
root> mv [from] [to]
root> mv my.log my1.log
root> mv * /archive
root> mv /archive/* .
The "." represents the current directory.
The "cp" command is used to copy files and directories.
root> cp [from] [to]
root> cp my.log my1.log
root> cp * /archive
root> cp /archive/* .
The "mkdir" command is used to create new directories.
root> mkdir archive
The "rmdir" command is used to delete directories.
root> rmdir archive
The "find" command can be used to find the location of specific files.
root> find / -name dbmspool.sql
root> find / -print | grep -i dbmspool.sql
The "/" flag represents the staring directory for the search. Wildcards such as "dbms*" can be
used for the filename.
The "which" command can be used to find the location of an executable you are using.
oracle> which sqlplus
The "which" command searches your PATH setting for occurrences of the specified executable.
File Permissions
See Linux Files, Directories and Permissions.
The "umask" command can be used to read or set default file permissions for the current user.
root> umask 022
The umask value is subtracted from the default permissions (666) to give the final permission.
666 : Default permission
022 : - umask value
644 : final permission
The "chmod" command is used to alter file permissions after the file has been created.
root> chmod 777 *.log
Owner Group World Permission
========= ========= ========= ======================
7 (u+rwx) 7 (g+rwx) 7 (o+rwx) read + write + execute
6 (u+rw) 6 (g+rw) 6 (o+rw) read + write
5 (u+rx) 5 (g+rx) 5 (o+rx) read + execute
4 (u+r) 4 (g+r) 4 (o+r) read only
2 (u+w) 2 (g+w) 2 (o+w) write only
1 (u+x) 1 (g+x) 1 (o+x) execute only
Character eqivalents can be used in the chmod command.
root> chmod o+rwx *.log
root> chmod g+r *.log
root> chmod -Rx *.log
The "chown" command is used to reset the ownership of files after creation.
root> chown -R oinstall.dba *
The "-R" flag causes the command ro recurse through any subdirectories.
OS Users Management
See Linux Groups and Users.
The "useradd" command is used to add OS users.
root> useradd -G oinstall -g dba -d /usr/users/my_user -m -s
/bin/ksh my_user
• The "-G" flag specifies the primary group.
• The "-g" flag specifies the secondary group.
• The "-d" flag specifies the default directory.
• The "-m" flag creates the default directory.
• The "-s" flag specifies the default shell.
The "usermod" command is used to modify the user settings after a user has been created.
root> usermod -s /bin/csh my_user
The "userdel" command is used to delete existing users.
root> userdel -r my_user
The "-r" flag removes the default directory.
The "passwd" command is used to set, or reset, the users login password.
root> passwd my_user
The "who" command can be used to list all users who have OS connections.
root> who
root> who | head -5
root> who | tail -5
root> who | grep -i ora
root> who | wc -l
• The "head -5" command restricts the output to the first 5 lines of the who command.
• The "tail -5" command restricts the output to the last 5 lines of the who command.
• The "grep -i ora" command restricts the output to lines containing "ora".
• The "wc -l" command returns the number of lines from "who", and hence the number of
connected users.
Process Management
See Linux Process Management (ps, top, renice, kill).
The "ps" command lists current process information.
# ps
# ps -ef | grep -i ora
# ps -ef | grep -i ora | grep -v grep # ps -ef | grep -i [o]ra
Specific processes can be killed by specifying the process id in the kill command.
# kill 12345
# kill -9 12345
You can kill multiple processes using a single command by combining "kill" with the "ps" and
"awk" commands.
# kill -9 `ps -ef | grep ora | awk '{print $2}'`
uname and hostname
The "uname" and "hostname" commands can be used to get information about the host.
root> uname -a
OSF1 oradb01.lynx.co.uk V5.1 2650 alpha
root> uname -a | awk '{ print $2 }' oradb01.lynx.co.uk
root> hostname oradb01.lynx.co.uk
Error Lines in Files
You can return the error lines in a file using.
root> cat alert_LIN1.log | grep -i ORA-
The "grep -i ORA-" command limits the output to lines containing "ORA-". The "-i" flag makes
the comparison case insensitive. A count of the error lines can be returned using the "wc"
command. This normally give a word count, but the "-l" flag alteres it to give a line count.
root> cat alert_LIN1.log | grep -i ORA- | wc -l
Remove Old Files
The find command can be used to supply a list of files to the rm command or the "-delete"
command can be used directly.
find /backup/logs/ -name daily_backup* -mtime +21 -exec rm -f {}
;
find /backup/logs/daily_backup* -mtime +5 -exec rm -f {} \;
find /backup/logs/daily_backup* -mtime +5 -delete;
File Exists Check
The Bash shell allows you to check for the presence of a file using the "[ -e filepath ]"
comparison. In the following script a backup log is renamed if it is present and files older than 30
days are deleted are deleted.
#!/bin/bash
if [ -e /tmp/backup.log ]; then
DATE_SUFFIX=`date +"%Y"-"%m"-"%d"` mv /tmp/backup.log
/tmp/backup-$DATE_SUFFIX.log fi
# Delete old log files. find /tmp/backup*.log -mtime +30 -
delete;
This is one example of a log rotation, where the most current log doesn't include the date in it's
name.
Rotate Log Files
See the previous section for another variant on log rotation.
The following script provides an example of how to manage a log rotation using the Bash shell.
The log file includes the date in the file name. Files older than 30 days are deleted.
#!/bin/bash
DATE_SUFFIX=`date +"%Y"-"%m"-"%d"`
LOG_FILE=/tmp/backup-$DATE_SUFFIX.log
# Do something that needs logging.
echo "Send this to log" >> $LOG_FILE 2>&1
# Delete old log files. find /tmp/backup*.log -mtime +30 -
delete; Find Big Files
Find Big Files
Find the top 20 biggest files recursively from this directory.
$ find . -type f -print0 | xargs -0 du -h | sort -hr | head -20
Perform Action for Every File in a Directory
The following scripts shows two methods for performing an action for each file in a directory.
#!/bin/bash
for FILE in `ls /tmp/`;
do echo $FILE; done
# Do something with the file name.
# Or this.
for FILE in $( ls /tmp/ );
do echo $FILE; done
Perform Action for Every Line in a File
The following scripts shows a method for performing an action for each line in a file.
#!/bin/bash
while read LINE; do
# Do something with the line.
echo $LINE; done < /tmp/myfile.txt
alias
An alias is a named shortcut for a longer command using the following format.
alias name='command'
For example, if you require sudo access for a specific command, you might want to include this
as an alias so you don't have to remember to type it.
alias myscript='sudo -u oracle /path/to/myscript'
Remove DOS CR/LFs (^M)
Remove DOS style CR/LF characters (^M) from UNIX files using.
sed -e 's/^M$//' filename > tempfile
The newly created tempfile should have the ^M character removed.
Where available, it is probably better to use the dos2unix and unix2dos commands.
Run Commands As Oracle User From Root
The following scripts shows how a number of commands can be run as the "oracle" user the
"root" user.
#!/bin/ksh
su - oracle < filename"
option to list the contents and pipe this to a file. Once you've editied the file you can then apply it
using the "crontab filename".
Login as root
crontab -l > newcron
Edit newcron file.
crontab newcron
Alternatively you can use the "crontab -e" option to edit the crontab file directly.
The entries have the following elements.
field allowed values ----- --------------
minute 0-59 hour 0-23 day of month 1-31
month 1-12
day of week 0-7 (both 0 and 7 are Sunday) user
Valid OS user command Valid command or script.
The first 5 fields can be specified using the following rules.
* - All available values or "first-last".
3-4 - A single range representing each possible from the
start to the end of the range inclusive.
1,2,5,6 - A specific list of values.
1-3,5-8 - A specific list of ranges.
0-23/2 - Every other value in the specified range.
The following entry runs a cleanup script a 01:00 each Sunday. Any output or errors from the
script are piped to /dev/null to prevent a buildup of mails to root.
0 1 * * 0 /u01/app/oracle/dba/weekly_cleanup > /dev/null 2>&1
To prevent a new job starting if the last run is still running, consider using flock. The job will
only run if a lock can be obtained on the specified lockfile.
From:
0 1 * * 0 /u01/app/oracle/dba/weekly_cleanup > /dev/null 2>&1
To:
0 1 * * 0 /usr/bin/flock -n /tmp/weekly_cleanup.lockfile
/u01/app/oracle/dba/weekly_cleanup > /dev/null 2>&1
Cluster Wide CRON Jobs On Tru64
On clustered systems cron is node-specific. If you need a job to fire once per cluster, rather than
once per node you need an alternative approach to the standard cron job. One approach is put
forward in the HP best practices document (Using cron in a TruCluster Server Cluster), but in my
opinion a more elegant solution is proposed by Jason Orendorf of HP Tru64 Unix Enterprise
Team (TruCluster Clustercron).
In his solution Jason creates a file called /bin/cronrun with the following contents.
#!/bin/ksh
set -- $(/usr/sbin/cfsmgr -F raw /) shift 12
[[ "$1" = "$(/bin/hostname -s)" ]] && exit 0 exit 1
This script returns TRUE (0) only on the node which is the CFS serving cluster_root.
All cluster wide jobs should have a crontab entry on each node of the cluster like.
5 * * * /bin/cronrun && /usr/local/bin/myjob
Although the cron jobs fire on all nodes, the "/bin/cronrun &&" part of the entry prevents the
script from running on all nodes except the current CFS serving cluster_root.
NFS Mount (Sun)
The following deamons must be running for the share to be seen by a PC.
/usr/lib/nfs/nfsd -a
/usr/lib/nfs/mountd
/opt/SUNWpcnfs/sbin/rpc.pcnfsd
To see a list of the nfs mounted drives already present type.
exportfs
First the mount point must be shared so it can be seen by remote machines.
share -F nfs -o ro /cdrom
Next the share can be mounted on a remote machine by root using.
mkdir /cdrom#1 mount -o ro myhost:/cdrom /cdrom#1
NFS Mount (Tru64)
On the server machine, if NFS is not currently setup do the following.
• Application Manager -> System Admin -> Configuration -> NFS
Select the "Configure system as an NFS server" option.
Accept all defaults.
Create mount point directory.
mkdir /u04/backup
Append the following entry to the "/etc/exports" file.
/u04/backup
Make sure the correct permissions are granted on the directory.
chmod -R 777 /u04/backup
On the client machine, if NFS is not currently setup do the following.
Application Manager -> System Admin -> Configuration -> NFS
Select the "Configure system as an NFS client" option.
Accept all defaults.
Create mount point directory.
mkdir /backup
Append an following entry to the "/etc/fstab" file.
nfs-server-name:/u04/backup /backup nfs rw,bg,intr 0
0
Finally, mount the fileset.
mount /backup
At this point you can start to use the mount point from your client machine. Thanks to Bryan
Mills for his help with Tru64.
Samba/CIFS Mount (Linux)
See Linux Samba Configuration.
Create a directory to use for the mount point.
# mkdir /host
Add the following line to the "/etc/fstab" file.
//192.168.0.4/public /host cifs
rw,credentials=/root/.smbcred,uid=500,guid=500 0 0
Create a file called "/root/.smbcred" with the following contents.
username=myuser password=mypassword
Change the permissions on the credentials file.
# chmod 600 /root/.smbcred
Mount the share.
# mount /host
PC XStation Configuration
Download the CygWin setup.exe from http://www.cygwin.com.
Install, making sure to select all the X11R6 (or XFree86 in older versions) optional packages.
If you need root access add the following entry into the /etc/securettys file on each server.
<:0
From the command promot on the PC do the following.
set PATH=PATH;c:cygwinbin;c:cygwinusrX11R6bin XWin.exe :0 -query
The X environment should start in a new window.
Many Linux distributions do not start XDMCP by default. To allow XDMCP access from
Cygwin edit the "/etc/X11/gdm/gdm.conf" file. Under the "[xdmcp]" section set "Enable=true".
If you are starting any X applications during the session you will need to set the DISPLAY
environment variable. Remember, you are acting as an XStation, not the server itself, so this
variable must be set as follows.
DISPLAY=:0.0; export DISPLAY
xauth (Magic Cookie)
Access to X servers can get broken when using su and sudo commands. The xauth command
provides a solution to this. The process involves the following stages:
• Check your current display number.
• Use xauth list to get a list of magic cookies. Switch to the new user.
• Use xauth add to set the magic cookie for your display number.
An example of this is shown below.
$ echo $DISPLAY localhost:12.0 $ xauth list
ol6.localdomain/unix:12 MIT-MAGIC-COOKIE-1
be64852468ca3c334720b10bb3c4d3cb
$ sudo su oracle
$ xauth add ol6.localdomain/unix:12 MIT-MAGIC-COOKIE-1
be64852468ca3c334720b10bb3c4d3cb
You will now be able to access the X server, just as you could before the user switch.
Useful Profile Settings
See Linux Groups and Users : Important Files.
The following ".profile" settings rely on the default shell for the user being set to the Korn shell
(/bin/ksh).
The backspace key can be configured by adding the following entry.
stty erase "^H"
The command line history can be accessed using the [Esc][k] by adding the following entry.
set -o vi
Auto completion of paths using a double strike of the [Esc] key can be configured by adding the
following entry.
set filec
Summary:
Navigation and File Management:
cd: Change directory. Example: cd /home/oracle/data
ls: List directory contents. Example: ls -l (long listing
format)
pwd: Print working directory. Example: pwd
mkdir: Create a new directory. Example: mkdir backup_dir
cp: Copy files and directories. Example: cp database.sql
backup_dir
mv: Move or rename files and directories. Example: mv
archive.log /archive_logs
rm: Remove files and directories. Example: rm -rf temp_files
(use with caution!)
Viewing and Editing Files:
cat: Display file contents. Example: cat table_schema.sql
more: View files page-by-page. Example: more log_file.txt
less: View files with more navigation options. Example: less
error_report
head: View the first few lines of a file. Example: head -n 10
config.ini
tail: View the last few lines of a file. Example: tail -f
transaction_log (follow updates)
nano: Simple text editor. Example: nano script.sh
vi: Powerful text editor (steeper learning curve). Example: vi
config.xml
Text Manipulation:
grep: Search for patterns in files. Example: grep "ERROR"
log_file.txt
awk: Extract specific data from files. Example: awk '{print $4}'
employee_data.csv
sed: Modify text in files. Example: sed
's/old_value/new_value/g' config.txt
Running Commands and Processes:
ps: List running processes. Example: ps aux | grep oracle
top: Monitor system resource usage. Example: top
kill: Terminate processes. Example: kill -9 12345 (process ID)
bg: Move a process to the background. Example: bg %1
fg: Bring a process to the foreground. Example: fg %2
DBA-Specific Commands:
sqlplus: Connect to an Oracle database. Example: sqlplus / as
sysdba
rman: Oracle Recovery Manager for backup and recovery. Example:
rman target /
lsnrctl: Manage Oracle listener processes. Example: lsnrctl
start
ps -ef | grep postgres: Check for running PostgreSQL processes.
mysql: Connect to a MySQL database. Example: mysql -u root -p
Useful Files
Here are some files that may be of use.
Path Contents
/etc/passwd User settings
/etc/shadow Where encrypted user passwords are stored
/etc/group Group settings for users.
/etc/hosts Hostname lookup information.
/etc/system
Kernel parameters for Solaris.
/etc/sysconfigtab Kernel parameters for Tru64.
/etc/sysctl.conf Kernel parameters for Linux.
Tuesday, 18 November 2025
ON DELETE CASCADE | CASCADE CONSTRAINTS
ON DELETE CASCADE and CASCADE CONSTRAINTS ==================================================== CREATE TABLE DEPTX (DEPTNO NUMBER PRIMARY KEY, DNAME VARCHAR2(10)) ; CREATE TABLE EMPx (EMPNO NUMBER PRIMARY KEY, ENAME VARCHAR2(10), DEPTNO NUMBER CONSTRAINT EMP_DEPT_FK REFERENCES DEPTX (deptno) ON DELETE CASCADE ); INSERT INTO DEPTx (deptno,dname) VALUES (50,'CREDIT'); INSERT INTO EMPx (EMPNO,ENAME,DEPTNO) VALUES (9999,'JOEL',50); SQL> insert into EMPx values (999,'XXXXX',50); 1 row created. SQL> commit ; Commit complete. SQL> delete from deptx where DEPTNO=50; 1 row deleted. SQL> commit ; Commit complete. SQL> select * from EMPx ; no rows selected
Wednesday, 12 November 2025
ORA-02292: integrity constraint violated - child record found
SQL> delete from ANUJ.DEPT where deptno=10;
delete from ANUJ.DEPT where deptno=10
*
ERROR at line 1:
ORA-02292: integrity constraint (ANUJ.FK_DEPTNO) violated - child record found
set heading off echo off pages 999 long 90000
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
select dbms_metadata.get_ddl('TABLE','DEPT','ANUJ') output from dual;
-- Index
select dbms_metadata.get_dependent_ddl('INDEX','DEPT','ANUJ') output from dual;
-- Constraint
select dbms_metadata.get_dependent_ddl('CONSTRAINT','DEPT','ANUJ') output from dual;
-- REF_CONSTRAINT
select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT','DEPT','ANUJ') output from dual;
-- Trigger
select dbms_metadata.get_dependent_ddl('TRIGGER','DEPT','ANUJ') output from dual;
set linesize 300 col OWNER for a15 col R_OWNER for a15 col TABLE_NAME for a25 col SEARCH_CONDITION for a15 col constraint_type for a15 SELECT owner , table_name, constraint_name, constraint_type, r_owner, r_constraint_name,SEARCH_CONDITION FROM all_constraints WHERE owner='XXX' AND constraint_name='FK_DEPTNO'
;
-- to enable the PK of the given table
define owner='ANUJ'
define table_name='DEPT'
select 'alter table '||a.OWNER||'.'||a.TABLE_NAME||' enable constraint '||a.CONSTRAINT_NAME||';'
from all_constraints a
where a.TABLE_NAME=nvl(upper('&table_name'),a.table_name)
and a.OWNER=nvl(upper('&owner'),a.owner)
and a.CONSTRAINT_TYPE in ('P','U');
-- to enable FK referencing the PK of the given table
define owner='ANUJ'
define table_name='DEPT'
select 'alter table '||b.OWNER||'.'||b.TABLE_NAME||' enable constraint '||b.CONSTRAINT_NAME||';'
from all_constraints a, all_constraints b
where a.CONSTRAINT_NAME = b.R_CONSTRAINT_NAME
and a.TABLE_NAME=nvl(upper('&table_name'),a.table_name)
and a.OWNER=nvl(upper('&owner'),a.owner)
and b.CONSTRAINT_TYPE='R'
;
set linesize 300
col PARENT_OWNER for a15
col PARENT_TABLE for a15
col PARENT_CONSTRAINT for a15
col CHILD_OWNER for a15
col CHILD_TABLE for a15
col CHILD_CONSTRAINT for a15
SELECT
pk.owner AS parent_owner,
pk.table_name AS parent_table,
pk.constraint_name AS parent_constraint,
fk.owner AS child_owner,
fk.table_name AS child_table,
fk.constraint_name AS child_constraint
FROM
dba_constraints pk
JOIN
dba_constraints fk ON pk.constraint_name = fk.r_constraint_name -- Join condition: FK references PK
WHERE
pk.constraint_type = 'P' -- Select only primary keys for the parent
and pk.table_name='DEPT'
and pk.owner='ANUJ'
and fk.owner='ANUJ'
AND fk.constraint_type = 'R' -- Select only foreign keys for the child
;
=====================================================================
define 1='ANUJ'
define 2='EMP'
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT DBMS_METADATA.get_ddl ('REF_CONSTRAINT', constraint_name, owner)
FROM all_constraints
WHERE owner = UPPER('&1')
AND table_name = DECODE(UPPER('&2'), 'ALL', table_name, UPPER('&2'))
AND constraint_type = 'R';
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
ALTER TABLE "ANUJ"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "ANUJ"."DEPT" ("DEPTNO") ENABLE;
=============================================================
from Web https://connor-mcdonald.com/2018/06/25/ddl-for-constraints-subtle-things/
set pagesize 0
set long 90000
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'CONSTRAINTS',false);
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM, 'REF_CONSTRAINTS',false);
create table anuj.tab1(id number, name varchar2(100),
constraint pk_tab1_id primary key(id));
create table anuj.tab2(id number, name varchar2(100),
constraint pk_tab2_id primary key(id));
create table anuj.tab3(id number, name varchar2(100), int_id number,
constraint pk_tab3_id primary key(id),
constraint fk_tab1_id foreign key(int_id) references anuj.tab1(id),
constraint fk_tab2_id foreign key(int_id) references anuj.tab2(id));
set serverout on
begin
for i in (
select t.table_name,
dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name, owner) ddl
from dba_tables t
where table_name = 'TAB3'
)
loop
dbms_output.put_line(i.ddl);
--execute immediate i.ddl;
end loop;
end;
/
ALTER TABLE "ANUJ"."TAB3" ADD CONSTRAINT "FK_TAB1_ID" FOREIGN KEY ("INT_ID")
REFERENCES
"ANUJ"."TAB1" ("ID") ENABLE;
ALTER TABLE "ANUJ"."TAB3" ADD CONSTRAINT "FK_TAB2_ID" FOREIGN KEY
("INT_ID")
REFERENCES "ANUJ"."TAB2" ("ID") ENABLE;
=====================
set linesize 300 pagesize 300
col XCOLUMNS for a20
col R_COLUMNS for a20
col type for a8
col TABLE_NAME for a29
col R_OWNER for a12
col status for a12
col R_CONSTRAINT_NAME for a20
col CONSTRAINT_NAME for a20
col OWNER for a20
col R_TABLE_NAME for a20
select rcon.owner as r_owner, rcon.constraint_name as r_constraint_name,rcon.status, rcon.table_name as r_table_name,
listagg (rcol.column_name, ', ') WITHIN GROUP (ORDER BY rcol.owner, rcol.table_name, rcol.constraint_name) R_COLUMNS,
rcon.constraint_type as type, con.owner, con.table_name, con.constraint_name,
listagg (col.column_name, ', ') WITHIN GROUP (ORDER BY col.owner, col.table_name, col.constraint_name) XCOLUMNS
from all_constraints rcon
join all_cons_columns rcol on rcol.owner=rcon.owner and rcol.table_name=rcon.table_name and rcol.constraint_name=rcon.constraint_name
left join all_constraints con on rcon.owner = con.r_owner and rcon.constraint_name = con.r_constraint_name
left join all_cons_columns col on col.owner=con.owner and col.table_name=con.table_name and col.constraint_name=con.constraint_name
and rcol.position = col.position
where rcon.owner = upper('ANUJ1')
and rcon.table_name = upper('DEPT')
--and rcon.constraint_type in ('P','U')
--and rcon.constraint_name = nvl(upper(p_constraint),rcon.constraint_name)
and rcon.status = 'ENABLED'
group by rcon.owner, rcon.constraint_name,rcon.status, rcon.table_name, rcon.constraint_type,con.owner, con.table_name, con.constraint_name
order by rcon.owner, rcon.constraint_name, rcon.constraint_type;
create delete script
below sql generate script
set linesize 300 pagesize 0
col XCOLUMNS for a20
col R_COLUMNS for a20
col type for a8
col TABLE_NAME for a29
col R_OWNER for a12
col status for a12
col R_CONSTRAINT_NAME for a15
col OWNER for a15
col R_TABLE_NAME for a18
col CONSTRAINT_NAME for a20
select 'delete from ' ||OWNER||'.' || TABLE_NAME ||' where '|| XCOLUMNS ||' in (select '|| R_COLUMNS|| ' from ' || R_OWNER ||'.'||R_TABLE_NAME ||' WHERE deptno=10 );'
--select 'delete from ' ||OWNER||'.' || TABLE_NAME ||' where '|| XCOLUMNS ||' in (select '|| R_COLUMNS|| ' from ' || R_OWNER ||'.'||R_TABLE_NAME ||' WHERE CREATE_DATE <''01-JAN-24'' );'
--select R_OWNER ,R_CONSTRAINT_NAME , STATUS , R_TABLE_NAME , R_COLUMNS, TYPE ,OWNER,TABLE_NAME ,CONSTRAINT_NAME , XCOLUMNS
from (select rcon.owner as r_owner, rcon.constraint_name as r_constraint_name,rcon.status, rcon.table_name as r_table_name,
listagg (rcol.column_name, ', ') WITHIN GROUP (ORDER BY rcol.owner, rcol.table_name, rcol.constraint_name) R_COLUMNS,
rcon.constraint_type as type, con.owner, con.table_name, con.constraint_name,
listagg (col.column_name, ', ') WITHIN GROUP (ORDER BY col.owner, col.table_name, col.constraint_name) XCOLUMNS
from all_constraints rcon
join all_cons_columns rcol on rcol.owner=rcon.owner and rcol.table_name=rcon.table_name and rcol.constraint_name=rcon.constraint_name
left join all_constraints con on rcon.owner = con.r_owner and rcon.constraint_name = con.r_constraint_name
left join all_cons_columns col on col.owner=con.owner and col.table_name=con.table_name and col.constraint_name=con.constraint_name
and rcol.position = col.position
where rcon.owner = upper('ANUJ')
and rcon.table_name = upper('DEPT')
--and rcon.constraint_type in ('P','U')
--and rcon.constraint_name = nvl(upper(p_constraint),rcon.constraint_name)
--and rcon.status = 'ENABLED'
group by rcon.owner, rcon.constraint_name,rcon.status, rcon.table_name, rcon.constraint_type,con.owner, con.table_name, con.constraint_name
order by rcon.owner, rcon.constraint_name, rcon.constraint_type
)
where 1=1
--and TABLE_NAME is not null
--and CONSTRAINT_NAME ='FK_DEPTNO'
;
set linesize 300 pagesize 300
===define schema_name='ANUJ1'
define u_table_name='DEPT'
col table_name format a30
col constraint_name format a30
col r_constraint_name format a30
set linesize 200 trimspool on
set pagesize 100
with fks as (
select c1.owner,c1.table_name
, c1.constraint_name
, c2.r_constraint_name
from dba_constraints c1
left join dba_constraints c2
on (
c2.owner = c1.owner
and c2.table_name = c1.table_name
and c2.constraint_type='R'
)
and c1.constraint_type in ('U','P')
where c1.owner = '&schema_name'
and c2.r_constraint_name is not null
),
pks as (
select c1.owner,c1.table_name, c1.constraint_name, null r_constraint_name
from dba_constraints c1
where c1.constraint_name in (
select r_constraint_name
from fks
)
and c1.owner = '&schema_name'
),
all_data as (
select owner
,table_name
, constraint_name
, r_constraint_name
from fks
union
select owner
,table_name
, constraint_name
, r_constraint_name
from pks
)
select distinct owner||'.'||lpad(' ', 2 * (level - 1))|| table_name table_name
, constraint_name
, r_constraint_name
, level
from all_data
start with table_name = '&u_table_name'
connect by nocycle prior constraint_name = r_constraint_name
and level <=5
order by level
/
TABLE_NAME CONSTRAINT_NAME R_CONSTRAINT_NAME LEVEL
------------------------------ ------------------------------ ------------------------------ ----------
DEPT PK_DEPT 1
EMP PK_EMP PK_DEPT 2
EMP1 PK_EMP1 PK_DEPT 2
set linesize 300 pagesize 300
col SCHEMA_NAME for a20
col COLUMN_NAME for a20
col CONSTRAINT for a20
col constraint_type_desc for a30
select ctr.owner as schema_name,
ctr.table_name,
ctr.constraint_name,
col.column_name,
ctr.search_condition as constraint,
ctr.status,
ctr.constraint_type,
decode(CONSTRAINT_TYPE,
'C', 'Check constraint on a table',
'P','Primary key',
'U', 'Unique key',
'R', 'Referential integrity',
'V', 'With check option, on a view',
'O', 'With read only on a view',
'H', 'Hash expression',
'F','Constraint that involves a REF column',
'S','Supplemental logging'
) constraint_type_desc
from sys.dba_constraints ctr
join sys.dba_cons_columns col
on ctr.owner = col.owner
and ctr.constraint_name = col.constraint_name
and ctr.table_name = col.table_name
where 1=1
-- and ctr.constraint_type = 'C'
and ctr.owner='ANUJ1'
and ctr.table_name='DEPT'
--and column_name != 'LABEL'
order by ctr.owner, ctr.table_name, ctr.constraint_name;
=======define schema_name='ANUJ1'
define u_table_name='DEPT'
col table_name format a30
col constraint_name format a30
col r_constraint_name format a30
set linesize 200 trimspool on
set pagesize 100
with fks as (
select c1.owner,c1.table_name
, c1.constraint_name
, c2.r_constraint_name
from dba_constraints c1
left join dba_constraints c2
on (
c2.owner = c1.owner
and c2.table_name = c1.table_name
and c2.constraint_type='R'
)
and c1.constraint_type in ('U','P')
where c1.owner = '&schema_name'
and c2.r_constraint_name is not null
),
pks as (
select c1.owner,c1.table_name, c1.constraint_name, null r_constraint_name
from dba_constraints c1
where c1.constraint_name in (
select r_constraint_name
from fks
)
and c1.owner = '&schema_name'
),
all_data as (
select owner
,table_name
, constraint_name
, r_constraint_name
from fks
union
select owner
,table_name
, constraint_name
, r_constraint_name
from pks
)
select distinct owner||'.'||lpad(' ', 2 * (level - 1))|| table_name table_name
, constraint_name
, r_constraint_name
, level
from all_data
start with table_name = '&u_table_name'
connect by nocycle prior constraint_name = r_constraint_name
and level <=5
order by level
/
set serverout on
begin
for i in (
select t.table_name,
dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name, owner) ddl
from dba_tables t
where table_name = 'TAB3'
)
loop
dbms_output.put_line(i.ddl);
--execute immediate i.ddl;
end loop;
end;
/delete from ANUJ1.DEPT * ERROR at line 1: ORA-02292: integrity constraint (ANUJ1.FK_DEPTNO) violated - child record found
set serverout on begin for i in ( select t.table_name, dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name, owner) ddl FROM all_constraints WHERE owner='ANUJ1' AND constraint_name='FK_DEPTNO' ) loop dbms_output.put_line(i.ddl); --execute immediate i.ddl; end loop; end; /ALTER TABLE "ANUJ1"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "ANUJ1"."DEPT" ("DEPTNO") ENABLE;delete from "ANUJ1"."EMP" where deptno in (select DEPTNO from "ANUJ1"."DEPT" )delete from "ANUJ1"."EMP" where deptno in (select DEPTNO from "ANUJ1"."DEPT" )SQL> 2 / 11 rows deleted.select distinct * from ( select 'ALTER TABLE '||UC.owner||'.'||UC.TABLE_NAME||' ENABLE CONSTRAINT '||UC.constraint_name || ';' x FROM DBA_CONSTRAINTS UC, DBA_CONS_COLUMNS UCC WHERE UC.R_CONSTRAINT_NAME = UCC.CONSTRAINT_NAME --AND uc.constraint_type = 'R' --and UCC.table_name in ('xx') and UC.owner='ANUJ1' and uc.constraint_name='FK_DEPTNO' ORDER BY UCC.TABLE_NAME,UC.R_CONSTRAINT_NAME,UCC.TABLE_NAME,UCC.COLUMN_NAME );
SELECT owner, constraint_name, constraint_type, table_name, r_owner, r_constraint_name,SEARCH_CONDITION FROM all_constraints WHERE owner='XXXX' AND constraint_name='FK_DEPTNO' ;
set heading off
set echo off
Set pages 999
set long 90000
exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);
exec dbms_metadata.set_transform_param(dbms_metadata.session_transform,'SQLTERMINATOR',true);
-- Constraint
select dbms_metadata.get_dependent_ddl('CONSTRAINT','TABLE','OW') output from dual;
-- REF_CONSTRAINT
select dbms_metadata.get_dependent_ddl('REF_CONSTRAINT','TABLE','OW') output from dual;
SET LINESIZE 400 PAGESIZE 300
COL R_COLUMN_NAME FOR A27
COL OWNER FOR A12
COL R_OWNER FOR A12
COL r_pk FOR A15
col TABLE_NAME for a15
col COLUMN_NAME for a15
col CONSTRAINT_NAME for a15
col R_TABLE_NAME for a15
SELECT
c.owner
,a.table_name
, a.column_name
, a.constraint_name
, c.r_owner
, c_pk.table_name r_table_name
, c_pk.constraint_name r_pk
, cc_pk.column_name r_column_name
FROM DBA_cons_columns a
JOIN DBA_constraints c ON (a.owner = c.owner AND a.constraint_name = c.constraint_name )
JOIN DBA_constraints c_pk ON (c.r_owner = c_pk.owner AND c.r_constraint_name = c_pk.constraint_name )
JOIN DBA_cons_columns cc_pk on (cc_pk.constraint_name = c_pk.constraint_name AND cc_pk.owner = c_pk.owner )
WHERE a.owner = 'ANUJ1'
--AND a.table_name IN ( 'XX')
--AND a.table_name IN ('vvv')
ORDER BY 1,6
;
====
to disable and enable constraint
DECLARE
/*The name of the schema that should be synchronized.*/
Schema_Name VARCHAR2(4000) :='ANUJ1';
/*The operation type:*/
/* ON — enable foreign keys;*/
/* OFF — disable foreign keys.*/
ON_OFF VARCHAR2(4000) :='OFF';
PROCEDURE CONSTRAINTS_ON_OFF
(Target_Schema_Name IN VARCHAR2, Action IN VARCHAR2:='')
IS
sql_str VARCHAR2(4000);
FK_name VARCHAR2(4000);
var_action VARCHAR2(4000);
CURSOR cCur1 IS
/*Creating the list of foreign keys that should be disabled/enabled,*/
/*with creating a command at the same time.*/
SELECT
'ALTER TABLE '||OWNER||'.'||
TABLE_NAME||' '||var_action||' CONSTRAINT '||CONSTRAINT_NAME AS sql_string,
CONSTRAINT_NAME
FROM
ALL_CONSTRAINTS
WHERE 1=1
--and CONSTRAINT_TYPE='R'
AND OWNER='ANUJ1'
and TABLE_NAME='EMP'
;
BEGIN
IF upper(Action)='ON' THEN
var_action :='ENABLE';
ELSE
var_action :='DISABLE';
END IF;
OPEN cCur1;
LOOP
FETCH cCur1 INTO SQL_str,fk_name;
EXIT WHEN cCur1%NOTFOUND;
/*Disabling/Enabling foreign keys.*/
-- EXECUTE IMMEDIATE SQL_str;
DBMS_Output.PUT_LINE(SQL_str);
-- DBMS_Output.PUT_LINE('Foreign key '||FK_name||' is '||var_action||'d');
END LOOP;
EXCEPTION
WHEN OTHERS THEN
BEGIN
DBMS_Output.PUT_LINE(SQLERRM);
END;
CLOSE cCur1;
END;
BEGIN
CONSTRAINTS_ON_OFF(Schema_Name,ON_OFF);
/*specify additional calls if necessary*/
END;
/
ALTER TABLE ANUJ1.EMP DISABLE CONSTRAINT FK_DEPTNO
ALTER TABLE ANUJ1.EMP DISABLE CONSTRAINT PK_EMP
PL/SQL procedure successfully completed.
==============
constraint to table metadata delete from anuj1.dept where deptno=20 * ERROR at line 1: ORA-02292: integrity constraint (ANUJ1.FK_DEPTNO) violated - child record found ===== define O='ANUJ1' define C='FK_DEPTNO' set linesize 300 col OWNER for a15 col R_OWNER for a15 col TABLE_NAME for a25 col SEARCH_CONDITION for a15 col constraint_type for a15 col CONSTRAINT_NAME for a15 col R_CONSTRAINT_NAME for a15 SELECT owner , table_name, constraint_name, constraint_type, r_owner, r_constraint_name,SEARCH_CONDITION FROM all_constraints WHERE owner='&O' AND constraint_name='&C' union all SELECT owner , table_name, constraint_name, constraint_type, r_owner, r_constraint_name,SEARCH_CONDITION FROM all_constraints WHERE owner='&O' AND constraint_name in ( select r_constraint_name FROM all_constraints where owner='&O' AND constraint_name='&C' ) order by constraint_type ; OWNER TABLE_NAME CONSTRAINT_NAME CONSTRAINT_TYPE R_OWNER R_CONSTRAINT_NA SEARCH_CONDITIO --------------- ------------------------- --------------- --------------- --------------- --------------- --------------- ANUJ1 DEPT PK_DEPT P ANUJ1 EMP FK_DEPTNO R ANUJ1 PK_DEPT Table metadata ---- for above set PAGESIZE 80 SET LONG 10000 LONGCHUNKSIZE 10000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON BEGIN DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true); DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true); END; / SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner) FROM all_tables WHERE 1=1 and owner = '&O' AND table_name in ( SELECT --owner , table_name --, constraint_name, constraint_type, r_owner, r_constraint_name,SEARCH_CONDITION FROM all_constraints WHERE owner='&O' AND constraint_name='&C' union all SELECT --owner , table_name --, constraint_name, constraint_type, r_owner, r_constraint_name,SEARCH_CONDITION FROM all_constraints WHERE owner='&O' AND constraint_name in ( select r_constraint_name FROM all_constraints where owner='&O' AND constraint_name='&C' ) --order by constraint_type ) CREATE TABLE "ANUJ1"."DEPT" ( "DEPTNO" NUMBER(2,0), "DNAME" VARCHAR2(14), "LOC" VARCHAR2(13), CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; CREATE TABLE "ANUJ1"."EMP" ( "EMPNO" NUMBER(4,0), "ENAME" VARCHAR2(10), "JOB" VARCHAR2(9), "MGR" NUMBER(4,0), "HIREDATE" DATE, "SAL" NUMBER(7,2), "COMM" NUMBER(7,2), "DEPTNO" NUMBER(2,0), CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO") USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ENABLE, CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") <<<< this table REFERENCES "ANUJ1"."DEPT" ("DEPTNO") ENABLE >>>>> reference table ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" ; *******************************************************************************
define O='ANUJ1'
define T='DEPT'
define O='ANUJ1'
define T='EMP'
SET LONG 10000 LONGCHUNKSIZE 10000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
SELECT dbms_metadata.get_dependent_ddl('CONSTRAINT', table_name, owner) FROM all_tables
WHERE 1=1
and owner = UPPER('&O')
AND table_name = UPPER('&T')
union all
SELECT dbms_metadata.get_dependent_ddl('REF_CONSTRAINT', table_name, owner) FROM all_tables
WHERE 1=1
and owner = UPPER('&O')
AND table_name = UPPER('&T')
/
ALTER TABLE "ANUJ1"."DEPT" ADD CONSTRAINT "PK_DEPT" PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE;
ERROR:
ORA-31608: specified object of type REF_CONSTRAINT not found
ORA-06512: at "SYS.DBMS_METADATA", line 6478
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 6469
ORA-06512: at "SYS.DBMS_METADATA", line 9297
ORA-06512: at line 1
ALTER TABLE "ANUJ1"."EMP" ADD CONSTRAINT "PK_EMP" PRIMARY KEY ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS" ENABLE;
ALTER TABLE "ANUJ1"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "ANUJ1"."DEPT" ("DEPTNO") ENABLE;
define O='ANUJ1'
define T='EMP1'
define C='FK_DEPTNO'
set linesize 300 set pagesize 80
col OWNER for a15
col R_OWNER for a15
col TABLE_NAME for a25
col SEARCH_CONDITION for a15
col constraint_type for a15
col CONSTRAINT_NAME for a15
col R_CONSTRAINT_NAME for a15
col SCHEMA_NAME for a20
col COLUMNS for a20
select acc.owner as schema_name,
acc.table_name,
acc.constraint_name,
LISTAGG(acc.column_name,',')
WITHIN GROUP (order by acc.position) as columns
,con.constraint_type
from sys.dba_constraints con
join sys.dba_cons_columns acc on con.owner = acc.owner
and con.constraint_name = acc.constraint_name
where 1=1
-- and con.constraint_type = 'P'
and acc.owner=UPPER('&O')
-- and acc.table_name not like 'BIN$%'
and con.constraint_name='&C'
group by acc.owner,
acc.table_name,
acc.constraint_name
,con.constraint_type
--order by acc.owner, acc.constraint_name
union all
select acc.owner as schema_name,
acc.table_name,
acc.constraint_name,
LISTAGG(acc.column_name,',')
WITHIN GROUP (order by acc.position) as columns
,con.constraint_type
from sys.dba_constraints con
join sys.dba_cons_columns acc on con.owner = acc.owner
and con.constraint_name = acc.constraint_name
where 1=1
-- and con.constraint_type = 'P'
and acc.owner=UPPER('&O')
-- and acc.table_name not like 'BIN$%'
AND con.constraint_name in ( select r_constraint_name FROM all_constraints
where owner='&O'
AND constraint_name='&C'
)
group by acc.owner,
acc.table_name,
acc.constraint_name
,con.constraint_type
order by 5
/
SCHEMA_NAME TABLE_NAME CONSTRAINT_NAME COLUMNS CONSTRAINT_TYPE
-------------------- ------------------------- --------------- -------------------- ---------------
ANUJ1 DEPT PK_DEPT DEPTNO P
ANUJ1 EMP FK_DEPTNO DEPTNO R
all_constraints status <<<<<<<<<<<
set linesize 300 pagesize 200
col SCHEMA_NAME for a20
col CONSTRAINT for a15
col table_name for a15
select ctr.owner as schema_name,
ctr.constraint_name,
ctr.table_name,
col.column_name,
ctr.search_condition as constraint,
ctr.status
from sys.all_constraints ctr
join sys.all_cons_columns col
on ctr.owner = col.owner
and ctr.constraint_name = col.constraint_name
and ctr.table_name = col.table_name
where 1=1
--ctr.constraint_type = 'C'
and ctr.owner in ('ANUJ1')
--and ctr.table_name='DEPT'
and STATUS!='ENABLED'
order by ctr.owner, ctr.table_name, ctr.constraint_name;
SCHEMA_NAME CONSTRAINT_NAME TABLE_NAME COLUMN_NAME CONSTRAINT STATUS
-------------------- ---------------------- --------------- --------------- --------------- --------
ANUJ1 FK_DEPTNO EMP DEPTNO DISABLED
-- Enable
ALTER table table_name enable constraint constraint_name;
--Disable
ATLER table table_name disable constraint constraint_name;SQL> delete from ANUJ1.DEPT where deptno=10; delete from ANUJ1.DEPT where deptno=10 * ERROR at line 1: ORA-02292: integrity constraint (ANUJ1.FK_DEPTNO) violated - child record found
========================================================================
create script!!!!!
SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE
v_schema VARCHAR2(100) := 'ANUJ1';
v_root_table VARCHAR2(100) := 'DEPT';
v_filter VARCHAR2(500) := ' deptno=10';
--v_filter VARCHAR2(500) := 'CREATE_DATE < DATE ''2024-01-05''';
-- Get PK columns (supports composite PK)
FUNCTION get_pk_columns(p_table VARCHAR2) RETURN VARCHAR2 IS
v_cols VARCHAR2(2000);
BEGIN
SELECT LISTAGG(acc.column_name, ',') WITHIN GROUP (ORDER BY acc.position)
INTO v_cols
FROM all_constraints ac
JOIN all_cons_columns acc
ON ac.owner = acc.owner
AND ac.constraint_name = acc.constraint_name
WHERE ac.owner = v_schema
AND ac.table_name = p_table
AND ac.constraint_type = 'P';
RETURN v_cols;
END get_pk_columns;
-- Recursive delete printer
PROCEDURE print_delete(p_table VARCHAR2, p_filter VARCHAR2) IS
v_pk VARCHAR2(2000);
BEGIN
v_pk := get_pk_columns(p_table);
FOR c IN (
SELECT ac.table_name AS child_table,
acc.column_name AS child_column
FROM all_constraints ac
JOIN all_cons_columns acc
ON ac.owner = acc.owner
AND ac.constraint_name = acc.constraint_name
WHERE ac.owner = v_schema
AND ac.constraint_type = 'R'
AND ac.r_constraint_name = (
SELECT constraint_name
FROM all_constraints
WHERE owner = v_schema
AND table_name = p_table
AND constraint_type = 'P'
)
) LOOP
-- Recurse first
print_delete(
c.child_table,
c.child_column || ' IN (SELECT ' || v_pk ||
' FROM ' || v_schema || '.' || p_table ||
' WHERE ' || p_filter || ')'
);
-- Output delete statement
DBMS_OUTPUT.PUT_LINE(
'DELETE FROM ' || v_schema || '.' || c.child_table ||
' WHERE ' || c.child_column || ' IN (SELECT ' || v_pk ||
' FROM ' || v_schema || '.' || p_table ||
' WHERE ' || p_filter || ');' || CHR(10)
);
END LOOP;
END print_delete;
BEGIN
DBMS_OUTPUT.PUT_LINE('--- PRINT-ONLY FK-SAFE DELETE SCRIPT ---');
DBMS_OUTPUT.PUT_LINE('Schema: ' || v_schema || ' Root Table: ' || v_root_table);
DBMS_OUTPUT.PUT_LINE('Filter: ' || v_filter || CHR(10));
print_delete(v_root_table, v_filter);
DBMS_OUTPUT.PUT_LINE(
'DELETE FROM ' || v_schema || '.' || v_root_table ||
' WHERE ' || v_filter || ';'
);
DBMS_OUTPUT.PUT_LINE(CHR(10) || '--- SCRIPT COMPLETE ---');
END;
/
================================================================
--- PRINT-ONLY FK-SAFE DELETE SCRIPT ---
Schema: ANUJ1 Root Table: DEPT
Filter: deptno=10
DELETE FROM ANUJ1.EMP WHERE DEPTNO IN (SELECT DEPTNO FROM ANUJ1.DEPT WHERE
deptno=10);
DELETE FROM ANUJ1.DEPT WHERE deptno=10;
--- SCRIPT COMPLETE ---
PL/SQL procedure successfully completed.
SQL> DELETE FROM ANUJ1.EMP WHERE DEPTNO IN (SELECT DEPTNO FROM ANUJ1.DEPT WHERE
deptno=10);
2
3 rows deleted.
SQL> SQL> DELETE FROM ANUJ1.DEPT WHERE deptno=10;
1 row deleted.
SQL> SQL> roll;
Rollback complete.
Wednesday, 5 November 2025
Oracle 26ai ( on free vm )
export TWO_TASK=
sqlplus / as sysdba
SQL*Plus: Release 23.26.0.0.0 - Production on Wed Nov 5 14:25:30 2025
Version 23.26.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle AI Database 26ai Free Release 23.26.0.0.0 - Develop, Learn, and Run for Free
Version 23.26.0.0.0
SQL> select * from dual;
D
-
X
************
cat /opt/oracle/product/26ai/dbhomeFree/network/admin/tnsnames.ora
# tnsnames.ora Network Configuration File: /opt/oracle/product/26ai/dbhomeFree/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
FREE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FREE)
)
)
LISTENER_FREE =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
FREEPDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = FREEPDB1)
)
)
Friday, 17 October 2025
ORA-00054: resource busy and acquire with NOWAIT
ORA-00054: resource busy and acquire with NOWAIT
set linesize 300 pagesize 300
col file# for 9999999
col block# for 9999999999999999
col obj for a18
col type for a9
col lm for 99
col bsid for 99999999
col event for a35
select count(*) cnt,
session_id sid,
substr(event,1,30) event,
mod(p1,16) as lm,
sql_id,
CURRENT_OBJ# || ' ' || object_name obj
, o.object_type type
, CURRENT_FILE# file#
, CURRENT_BLOCK# block#
, blocking_session bsid
,CON_ID "CON_ID**"
from gv$active_session_history ash,
all_objects o
where 1=1
--and event like 'enq: T%'
and o.object_id (+)= ash.current_obj#
--and SAMPLE_TIME >sysdate -1
and sample_time > sysdate - interval '5' minute
group by event,session_id,p1,sql_ID,CURRENT_OBJ#,OBJECT_NAME,OBJECT_TYPE,CURRENT_FILE#, CURRENT_BLOCK#, BLOCKING_SESSION,CON_ID
order by count(*)
/
set linesize 300
col object_name for a20
select s.sid, s.serial#,event,sql_id,o.object_name,FINAL_BLOCKING_SESSION_STATUS
from v$locked_object l, dba_objects o, v$session s
where o.object_name in('XXX')
and l.object_id = o.object_id
and l.session_id = s.sid
;
col ORACLE_USERNAME for a20
col kill_session for a70
col KILL_SESSION for a50
col OWNER for a20
col OS_USER_NAME for a12
select l.session_id, l.oracle_username, l.os_user_name, o.object_name , o.owner,event,
'alter system kill session ''' || s.sid || ',' || s.serial# ||''';' kill_session
from v$locked_object l, dba_objects o, v$session s
where l.object_id = o.object_id
and l.session_id = s.sid
--and OBJECT_NAME='XXX'
;
from web !!!
set linesize 300 pagesize 300
DEFINE 1="username||':'||program2||event2 "
DEFINE 2="session_type='FOREGROUND'"
--DEFINE 3="sysdate-1/24"
DEFINE 3="sysdate-10/24/60" ---10 min
DEFINE 4="sysdate"
DEFINE ashtable="DBA_HIST_ACTIVE_SESS_HISTORY"
--- DBA_HIST_ACTIVE_SESS_HISTORY
set linesize 300 pagesize 300
COL wait_chain FOR A100 WORD_WRAP
COL "%This" FOR A6
PROMPT
PROMPT -- Display ASH Wait Chain Signatures script v0.4 BETA by Tanel Poder ( http://blog.tanelpoder.com )
WITH
bclass AS (SELECT class, ROWNUM r from v$waitstat),
ash AS (SELECT /*+ QB_NAME(ash) LEADING(a) USE_HASH(u) SWAP_JOIN_INPUTS(u) */
a.*
, u.username
, CASE WHEN a.session_type = 'BACKGROUND' OR REGEXP_LIKE(a.program, '.*\([PJ]\d+\)') THEN
REGEXP_REPLACE(SUBSTR(a.program,INSTR(a.program,'(')), '\d', 'n')
ELSE
'('||REGEXP_REPLACE(REGEXP_REPLACE(a.program, '(.*)@(.*)(\(.*\))', '\1'), '\d', 'n')||')'
END || ' ' program2
, NVL(a.event||CASE WHEN a.event IN ('buffer busy waits', 'gc buffer busy', 'gc buffer busy acquire', 'gc buffer busy release')
THEN ' ['||NVL((SELECT class FROM bclass WHERE r = a.p3),'undo @bclass '||a.p3)||']' ELSE null END,'ON CPU')
|| ' ' event2
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p1 ELSE null END, '0XXXXXXXXXXXXXXX') p1hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p2 ELSE null END, '0XXXXXXXXXXXXXXX') p2hex
, TO_CHAR(CASE WHEN session_state = 'WAITING' THEN p3 ELSE null END, '0XXXXXXXXXXXXXXX') p3hex
, CASE WHEN BITAND(time_model, POWER(2, 01)) = POWER(2, 01) THEN 'DBTIME ' END
||CASE WHEN BITAND(time_model, POWER(2, 02)) = POWER(2, 02) THEN 'BACKGROUND ' END
||CASE WHEN BITAND(time_model, POWER(2, 03)) = POWER(2, 03) THEN 'CONNECTION_MGMT ' END
||CASE WHEN BITAND(time_model, POWER(2, 04)) = POWER(2, 04) THEN 'PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 05)) = POWER(2, 05) THEN 'FAILED_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 06)) = POWER(2, 06) THEN 'NOMEM_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 07)) = POWER(2, 07) THEN 'HARD_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 08)) = POWER(2, 08) THEN 'NO_SHARERS_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 09)) = POWER(2, 09) THEN 'BIND_MISMATCH_PARSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 10)) = POWER(2, 10) THEN 'SQL_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 11)) = POWER(2, 11) THEN 'PLSQL_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 12)) = POWER(2, 12) THEN 'PLSQL_RPC ' END
||CASE WHEN BITAND(time_model, POWER(2, 13)) = POWER(2, 13) THEN 'PLSQL_COMPILATION ' END
||CASE WHEN BITAND(time_model, POWER(2, 14)) = POWER(2, 14) THEN 'JAVA_EXECUTION ' END
||CASE WHEN BITAND(time_model, POWER(2, 15)) = POWER(2, 15) THEN 'BIND ' END
||CASE WHEN BITAND(time_model, POWER(2, 16)) = POWER(2, 16) THEN 'CURSOR_CLOSE ' END
||CASE WHEN BITAND(time_model, POWER(2, 17)) = POWER(2, 17) THEN 'SEQUENCE_LOAD ' END
||CASE WHEN BITAND(time_model, POWER(2, 18)) = POWER(2, 18) THEN 'INMEMORY_QUERY ' END
||CASE WHEN BITAND(time_model, POWER(2, 19)) = POWER(2, 19) THEN 'INMEMORY_POPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 20)) = POWER(2, 20) THEN 'INMEMORY_PREPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 21)) = POWER(2, 21) THEN 'INMEMORY_REPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 22)) = POWER(2, 22) THEN 'INMEMORY_TREPOPULATE ' END
||CASE WHEN BITAND(time_model, POWER(2, 23)) = POWER(2, 23) THEN 'TABLESPACE_ENCRYPTION ' END time_model_name
FROM
&ashtable. a
, dba_users u
WHERE
a.user_id = u.user_id (+)
AND sample_time BETWEEN &3 AND &4
),
ash_samples AS (SELECT DISTINCT sample_id FROM ash),
ash_data AS (SELECT * FROM ash),
chains AS (
SELECT
sample_time ts
, level lvl
, session_id sid
, REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 AND d.blocking_session IS NOT NULL THEN ' -> [idle blocker '||d.blocking_inst_id||','||d.blocking_session||','||d.blocking_session_serial#||(SELECT ' ('||s.program||')' FROM gv$session s WHERE (s.inst_id, s.sid , s.serial#) = ((d.blocking_inst_id,d.blocking_session,d.blocking_session_serial#)))||']' ELSE NULL END path -- there's a reason why I'm doing this
--, SYS_CONNECT_BY_PATH(&1, ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 THEN '('||d.session_id||')' ELSE NULL END path
-- , REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ')||CASE WHEN CONNECT_BY_ISLEAF = 1 AND LEVEL > 1 THEN ' [sid='||session_id||' seq#='||TO_CHAR(seq#)||']' ELSE NULL END path -- there's a reason why I'm doing this
--, REPLACE(SYS_CONNECT_BY_PATH(&1, '->'), '->', ' -> ') path -- there's a reason why I'm doing this (ORA-30004 :)
, CASE WHEN CONNECT_BY_ISLEAF = 1 THEN d.session_id ELSE NULL END sids
, CONNECT_BY_ISLEAF isleaf
, CONNECT_BY_ISCYCLE iscycle
, d.*
FROM
ash_samples s
, ash_data d
WHERE
s.sample_id = d.sample_id
AND d.sample_time BETWEEN &3 AND &4
CONNECT BY NOCYCLE
( PRIOR d.blocking_session = d.session_id
-- ash was saved from V$ not GV$ - AND PRIOR d.blocking_inst_id = d.inst_id
AND PRIOR s.sample_id = d.sample_id
)
START WITH &2
)
SELECT * FROM (
SELECT
LPAD(ROUND(RATIO_TO_REPORT(COUNT(*)) OVER () * 100)||'%',5,' ') "%This"
, COUNT(*) seconds
, ROUND(COUNT(*) / ((CAST(&4 AS DATE) - CAST(&3 AS DATE)) * 86400), 1) AAS
, path wait_chain
, TO_CHAR(MIN(sample_time), 'YYYY-MM-DD HH24:MI:SS') first_seen
, TO_CHAR(MAX(sample_time), 'YYYY-MM-DD HH24:MI:SS') last_seen
-- , COUNT(DISTINCT sids)
-- , MIN(sids)
-- , MAX(sids)
FROM
chains
WHERE
isleaf = 1
GROUP BY
&1
, path
ORDER BY
COUNT(*) DESC
)
WHERE
ROWNUM <= 30
/
define sql_id='XXXXXXX'
set long 30000 pagesize 500 linesize 300
col frm heading from
select * from (select 'gv$sql' frm , sql_fulltext sql_text from gv$sql where sql_id='&&sql_id'
union all
select 'gv$sqlstats' frm , sql_fulltext sql_text from gv$sqlstats where sql_id='&&sql_id'
union all
select 'dba_hist', sql_text from dba_hist_sqltext where sql_id='&&sql_id'
)
where 1=1
and rownum<2
;
Tuesday, 9 September 2025
How to get Oracle current session id ?
How to get Oracle current session id ? Oracle my session ALTER SESSION SET nls_date_format='DD-MON-YYYY HH24:MI:SS'; set linesize 300 col PROGRAM for a50 col MACHINE for a30 SELECT sid, serial#,con_id,MACHINE ,PROGRAM FROM SYS.V_$SESSION WHERE SID = (SELECT DISTINCT SID FROM SYS.V_$MYSTAT); -- To kill set page 0 select 'alter system kill session '''||sid||','||serial#||''' IMMEDIATE;' FROM SYS.V_$SESSION WHERE SID = (SELECT DISTINCT SID FROM SYS.V_$MYSTAT); set page 80
Saturday, 30 August 2025
Oracle datafile info
Datafile info --
define tablespace_name='TBS_ANUJ'
set pages 500 lines 400 term off
col fn new_value fname
select 'a'||(max(length(FILE_NAME))+1) fn from DBA_DATA_FILES;
set pages 700 lines 500
col TABLESPACE_NAME for a15
col FILE_NAME format a95
col MAXSIZE_MB for 99999
col COMPRESS_FOR for a14
col fn new_value fname
select 'a'||(max(length(FILE_NAME))+1) fn from DBA_DATA_FILES;
col file_name for &fname heading "File Name"
select d.FILE_ID,d.TABLESPACE_NAME
, d.FILE_NAME
, d.BYTES/1024/1024 SIZE_MB, d.AUTOEXTENSIBLE, d.MAXBYTES/1024/1024 MAXSIZE_MB,
d.INCREMENT_BY*(v.BLOCK_SIZE/1024)/1024 INCREMENT_BY_MB
,CREATION_TIME
,t.BIGFILE,ENCRYPTED,t.COMPRESS_FOR,t.CONTENTS,d.STATUS
from dba_data_files d, v$datafile v
,DBA_TABLESPACES t
where 1=1
and d.FILE_ID = v.FILE#
--and d.TABLESPACE_NAME='ANUJ' ---<<<
and d.tablespace_name = upper( decode('&&tablespace_name',null,d.tablespace_name,'&&tablespace_name'))
and d.TABLESPACE_NAME=t.TABLESPACE_NAME
order by d.TABLESPACE_NAME, d.FILE_NAME
;
CREATE TABLESPACE tbs_anuj DATAFILE '+DATA' SIZE 1M;
Tablespace created.
output --
FILE_ID TABLESPACE_NAME File Name SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB CREATION_ BIG ENC COMPRESS_FOR CONTENTS STATUS
---------- --------------- -------------------------------------------------- ---------- --- ---------- --------------- --------- --- --- -------------- --------------------- ---------
18 TBS_ANUJ +DATA/IBRAC/DATAFILE/tbs_anuj.3620.1210509953 1 NO 0 0 30-AUG-25 NO NO PERMANENT AVAILABLE
alter database datafile 18 resize 1g;
output --
FILE_ID TABLESPACE_NAME File Name SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB CREATION_ BIG ENC COMPRESS_FOR CONTENTS STATUS
---------- --------------- -------------------------------------------------- ---------- --- ---------- --------------- --------- --- --- -------------- --------------------- ---------
18 TBS_ANUJ +DATA/IBRAC/DATAFILE/tbs_anuj.3620.1210509953 1024 NO 0 0 30-AUG-25 NO NO PERMANENT AVAILABLE
alter database datafile 18 size 1G autoextend on next 10M;
col datafile for a100
select 'alter database datafile '''||file_name||''' AUTOEXTEND On;' datafile from dba_data_files
where 1=1
--autoextensible='YES'
and tablespace_name = upper( decode('&&tablespace_name',null,tablespace_name,'&&tablespace_name'))
;
output --
DATAFILE
----------------------------------------------------------------------------------------------------
alter database datafile '+DATA/IBRAC/DATAFILE/tbs_anuj.3620.1210509953' AUTOEXTEND On;
1 row selected.
SQL> alter database datafile '+DATA/IBRAC/DATAFILE/tbs_anuj.3620.1210509953' AUTOEXTEND On;
Database altered.
output --
FILE_ID TABLESPACE_NAME File Name SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB CREATION_ BIG ENC COMPRESS_FOR CONTENTS STATUS
---------- --------------- -------------------------------------------------- ---------- --- ---------- --------------- --------- --- --- -------------- --------------------- ---------
18 TBS_ANUJ +DATA/IBRAC/DATAFILE/tbs_anuj.3620.1210509953 1024 YES 32768 .0078125 30-AUG-25 NO NO PERMANENT AVAILABLE
ALTER DATABASE DATAFILE 18 AUTOEXTEND ON MAXSIZE UNLIMITED;
Database altered.
output --
FILE_ID TABLESPACE_NAME File Name SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB CREATION_ BIG ENC COMPRESS_FOR CONTENTS STATUS
---------- --------------- -------------------------------------------------- ---------- --- ---------- --------------- --------- --- --- -------------- --------------------- ---------
18 TBS_ANUJ +DATA/IBRAC/DATAFILE/tbs_anuj.3620.1210509953 1024 YES 32768 .0078125 30-AUG-25 NO NO PERMANENT AVAILABLE
SQL>
set line 999 pages 999
col FILE_NAME format a50
col tablespace_name format a15
col "MAXSIZE GB" for 99999.99
Select tablespace_name, file_name, autoextensible, bytes/1024/1024/1024 "USEDSPACE GB", maxbytes/1024/1024/1024 "MAXSIZE GB"
from dba_data_files
where 1=1
and tablespace_name = upper( decode('&&tablespace_name',null,tablespace_name,'&&tablespace_name'))
order by tablespace_name;
alter database datafile 18 autoextend off; ---> MAXSIZE_MB=0
output --
FILE_ID TABLESPACE_NAME File Name SIZE_MB AUT MAXSIZE_MB INCREMENT_BY_MB CREATION_ BIG ENC COMPRESS_FOR CONTENTS STATUS
---------- --------------- -------------------------------------------------- ---------- --- ---------- --------------- --------- --- --- -------------- --------------------- ---------
18 TBS_ANUJ +DATA/IBRAC/DATAFILE/tbs_anuj.3620.1210509953 1024 NO 0 0 30-AUG-25 NO NO PERMANENT AVAILABLE
define tablespace_name='TBS_ANUJ'
SET LONG 20000 LONGCHUNKSIZE 20000 PAGESIZE 0 LINESIZE 1000 FEEDBACK OFF VERIFY OFF TRIMSPOOL ON
BEGIN
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'SQLTERMINATOR', true);
DBMS_METADATA.set_transform_param (DBMS_METADATA.session_transform, 'PRETTY', true);
END;
/
select
tablespace_name,
dbms_metadata.get_ddl(object_type => 'TABLESPACE', name=> tablespace_name) metadata from dba_tablespaces
where 1=1
and tablespace_name = upper( decode('&&tablespace_name',null,tablespace_name,'&&tablespace_name'))
;
SET PAGESIZE 14 LINESIZE 100 FEEDBACK ON VERIFY ON
TBS_ANUJ
CREATE TABLESPACE "TBS_ANUJ" DATAFILE
SIZE 1073741824
LOGGING ONLINE PERMANENT BLOCKSIZE 8192
EXTENT MANAGEMENT LOCAL AUTOALLOCATE DEFAULT
NOCOMPRESS SEGMENT SPACE MANAGEMENT AUTO;
Wednesday, 16 July 2025
Table statistics Export import in Oracle
exec dbms_stats.create_stat_table(ownname => 'SCOTT', stattab => 'STAT_BACKUP',tblspace=>'USERS');
1. Create STAT_BACKUP table
exec dbms_stats.create_stat_table(ownname => 'SCOTT', stattab => 'STAT_BACKUP',tblspace=>'USERS');
PL/SQL procedure successfully completed.
SQL> desc scott.STAT_BACKUP
Name Null? Type
----------------------------------------- -------- ----------------------------
STATID VARCHAR2(128)
TYPE CHAR(1)
VERSION NUMBER
FLAGS NUMBER
C1 VARCHAR2(128)
C2 VARCHAR2(128)
C3 VARCHAR2(128)
C4 VARCHAR2(128)
C5 VARCHAR2(128)
C6 VARCHAR2(128)
N1 NUMBER
N2 NUMBER
N3 NUMBER
N4 NUMBER
N5 NUMBER
N6 NUMBER
N7 NUMBER
N8 NUMBER
N9 NUMBER
N10 NUMBER
N11 NUMBER
N12 NUMBER
N13 NUMBER
D1 DATE
T1 TIMESTAMP(6) WITH TIME ZONE
R1 RAW(1000)
R2 RAW(1000)
R3 RAW(1000)
CH1 VARCHAR2(1000)
CL1 CLOB
BL1 BLOB
2.
-- if required !!
SQL> truncate table scott.STAT_BACKUP;
Table truncated.
3.
exec dbms_stats.export_table_stats(ownname=>'SCOTT', tabname=>'EMP', stattab=>'STAT_BACKUP', cascade=>true);
SQL> exec dbms_stats.export_table_stats(ownname=>'SCOTT', tabname=>'EMP', stattab=>'STAT_BACKUP', cascade=>true);
PL/SQL procedure successfully completed.
SQL> select count(*) from scott.STAT_BACKUP ;
COUNT(*)
----------
21
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set lines 200 pagesize 200
col table_name for a12
col owner for a12
select owner,table_name,last_analyzed from dba_tables where table_name='EMP' and owner='SCOTT';
OWNER TABLE_NAME LAST_ANALYZED
------------ ------------ ----------------
SCOTT EMP 27-03-2024 22:00
4.
expdp STAT_BACKUP table !!!!
expdp scott/vihaan123 tables=STAT_BACKUP dumpfile=STAT_BACKUP.dmp logfile=STAT_BACKUP.log
===
$expdp scott/vihaan123 tables=STAT_BACKUP dumpfile=STAT_BACKUP.dmp logfile=STAT_BACKUP.log DIRECTORY=ORACLE_BASE
Export: Release 12.2.0.1.0 - Production on Wed Jul 16 09:27:54 2025
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SCOTT"."SYS_EXPORT_TABLE_01": scott/******** tables=STAT_BACKUP dumpfile=STAT_BACKUP.dmp logfile=STAT_BACKUP.log DIRECTORY=ORACLE_BASE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "SCOTT"."STAT_BACKUP" 19.58 KB 21 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/STAT_BACKUP.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jul 16 09:32:32 2025 elapsed 0 00:04:33
expdp \"/ as sysdba\" tables=scott.STAT_BACKUP dumpfile=STAT_BACKUP1.dmp logfile=STAT_BACKUP1.log DIRECTORY=ORACLE_BASE
Export: Release 12.2.0.1.0 - Production on Wed Jul 16 10:15:05 2025
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Starting "SYS"."SYS_EXPORT_TABLE_01": "/******** AS SYSDBA" tables=scott.STAT_BACKUP dumpfile=STAT_BACKUP1.dmp logfile=STAT_BACKUP1.log DIRECTORY=ORACLE_BASE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
. . exported "SCOTT"."STAT_BACKUP" 19.58 KB 21 rows
Master table "SYS"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/STAT_BACKUP1.dmp
Job "SYS"."SYS_EXPORT_TABLE_01" successfully completed at Wed Jul 16 10:19:36 2025 elapsed 0 00:04:26
====================
5. STAT_BACKUP table to anuj from scott !!!
$impdp \"/ as sysdba\" tables=scott.STAT_BACKUP DIRECTORY=ORACLE_BASE DUMPFILE=STAT_BACKUP1.dmp remap_schema=scott:anuj TABLE_EXISTS_ACTION=replace LOGFILE=STAT_BACKUP1.log
Import: Release 12.2.0.1.0 - Production on Wed Jul 16 10:25:03 2025
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production
Master table "SYS"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_TABLE_01": "/******** AS SYSDBA" tables=scott.STAT_BACKUP DIRECTORY=ORACLE_BASE DUMPFILE=STAT_BACKUP1.dmp remap_schema=scott:anuj TABLE_EXISTS_ACTION=replace LOGFILE=STAT_BACKUP1.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "ANUJ"."STAT_BACKUP" 19.58 KB 21 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
Job "SYS"."SYS_IMPORT_TABLE_01" successfully completed at Wed Jul 16 10:25:42 2025 elapsed 0 00:00:36
SQL> select count(*) from anuj.STAT_BACKUP ;
COUNT(*)
----------
21
6.
import stats from STAT_BACKUP table to anuj.emp table
exec dbms_stats.IMPORT_table_stats(ownname=>'ANUJ', tabname=>'EMP', stattab=>'STAT_BACKUP');
SQL> exec dbms_stats.IMPORT_table_stats(ownname=>'ANUJ', tabname=>'EMP', stattab=>'STAT_BACKUP');
PL/SQL procedure successfully completed.
alter session set nls_date_format='dd-mm-yyyy hh24:mi';
set lines 200 pagesize 200
col table_name for a12
col owner for a12
select owner,table_name,last_analyzed from dba_tables where table_name='EMP' and owner='ANUJ';
Subscribe to:
Comments (Atom)
