Get DDL of objects in Oracle

Hi,

To get DDL of oracle database objects you can use dbms_metadata.getddl package.

Syntax:

select dbms_metadata.get_ddl('OBJECT_TYPE','OBJECT_NAME','SCHEMA') from dual;


Below is an example how this works.


set heading off;
set echo off;
Set pages 999;
set long 90000;
set lines 180

spool ddl_EMP.sql

select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

spool off;

Here is the output of spool file:

SQL>
SQL> select dbms_metadata.get_ddl('TABLE','EMP','SCOTT') from dual;

CREATE TABLE "SCOTT"."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)
TABLESPACE "USERS"  ENABLE,
CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO")
REFERENCES "SCOTT"."DEPT" ("DEPTNO") ENABLE) 
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)   TABLESPACE "USERS"                                                                                                                                                                          
                                                                                                                                                                
SQL> 
SQL> spool off;

Similarly, you can get the DDL for Functions, pacakages, stored procedure etc.

select dbms_metadata.get_ddl('PACKAGE','PACKAGE_NAME','SCHEMA_NAME') from dual;

select dbms_metadata.get_ddl('PACKAGE BODY','PACKAGEBODY_NAME','SCHEMA_NAME') from dual;

select dbms_metadata.get_ddl('TRIGGER','TRIGGER_NAME','SCHEMA_NAME') from dual;

Logical backup of sql query using EXP/IMP utility

Hi Folks,

Today I am writing about how to take exp backup of an sql statement. Many times we have a requirements to backup the modified rows. Taking full table backup may not be feasible if table sizes is GB's.

Better approach is to backup on the rows getting modified.

Consider below sql as an example.

select * from EMP where emp_job in ('DBA','DEVELOPER','ANALYST');


vi exp_example.par  (add below content)

file=EMP_EXP.dmp
log=EMP_EXP.log
query="where emp_job in ('DBA','DEVELOPER','ANALYST')"
tables=(EMP)
userid=system/manager

:wq  ( To save the file)

exp parfile=exp_example.par


That's all. Best of luck.

Add a disk to linux in vmware

Below are the steps to add a disk of 20GB and create a new partition in Linux 6 hosted on a vmware.

Go to VM--> Setting ---> Edit virtual machine settings ----> Add---> Hard disk ---> Create new virtual disk ---> SCSI ---> Give size of disk (20 GB) -->Split virtual disk into multiple file --> Next --> Finish

After you add disk, start the host and follow below


[root@localhost ~]# fdisk -l

Disk /dev/sda: 16.1 GB, 16106127360 bytes
255 heads, 63 sectors/track, 1958 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00098575

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          39      307200   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2              39        1449    11324416   83  Linux
/dev/sda3            1449        1959     4096000   82  Linux swap / Solaris

Disk /dev/sdb: 21.5 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00000000

Disk /dev/sdb doesn't contain a valid partition table

=========================================================================
Above you can see,  /dev/sdb as new 20 gb disk is added.  Now follow below steps
==========================================================================
[root@localhost ~]# fdisk /dev/sdb
Device contains neither a valid DOS partition table, nor Sun, SGI or OSF disklabel
Building a new DOS disklabel with disk identifier 0xa22f35c4.
Changes will remain in memory only, until you decide to write them.
After that, of course, the previous content won't be recoverable.

Warning: invalid flag 0x0000 of partition table 4 will be corrected by w(rite)

WARNING: DOS-compatible mode is deprecated. It's strongly recommended to
         switch off the mode (command 'c') and change display units to
         sectors (command 'u').

Command (m for help): n
Command action
   e   extended
   p   primary partition (1-4)
p
Partition number (1-4): 1
First cylinder (1-2610, default 1):
Using default value 1
Last cylinder, +cylinders or +size{K,M,G} (1-2610, default 2610):
Using default value 2610

Command (m for help): w
The partition table has been altered!

Calling ioctl() to re-read partition table.
Syncing disks.
[root@localhost ~]# fdisk -l

Disk /dev/sda: 16.1 GB, 16106127360 bytes
255 heads, 63 sectors/track, 1958 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0x00098575

   Device Boot      Start         End      Blocks   Id  System
/dev/sda1   *           1          39      307200   83  Linux
Partition 1 does not end on cylinder boundary.
/dev/sda2              39        1449    11324416   83  Linux
/dev/sda3            1449        1959     4096000   82  Linux swap / Solaris

Disk /dev/sdb: 21.5 GB, 21474836480 bytes
255 heads, 63 sectors/track, 2610 cylinders
Units = cylinders of 16065 * 512 = 8225280 bytes
Sector size (logical/physical): 512 bytes / 512 bytes
I/O size (minimum/optimal): 512 bytes / 512 bytes
Disk identifier: 0xa22f35c4

   Device Boot      Start         End      Blocks   Id  System
/dev/sdb1               1        2610    20964793+  83  Linux


Now make filesystem of the added disk.


[root@localhost ~]# mkfs -t ext4 /dev/sdb1
mke2fs 1.41.12 (17-May-2010)
Filesystem label=
OS type: Linux
Block size=4096 (log=2)
Fragment size=4096 (log=2)
Stride=0 blocks, Stripe width=0 blocks
1310720 inodes, 5241198 blocks
262059 blocks (5.00%) reserved for the super user
First data block=0
Maximum filesystem blocks=4294967296
160 block groups
32768 blocks per group, 32768 fragments per group
8192 inodes per group
Superblock backups stored on blocks:
        32768, 98304, 163840, 229376, 294912, 819200, 884736, 1605632, 2654208,
        4096000

Writing inode tables: done
Creating journal (32768 blocks): done
Writing superblocks and filesystem accounting information: done

This filesystem will be automatically checked every 24 mounts or
180 days, whichever comes first.  Use tune2fs -c or -i to override.
[root@localhost ~]#

Create directory for filesystem and mount it.

[root@localhost ~]# mkdir /oradata
[root@localhost ~]#
[root@localhost ~]# mount /dev/sdb1 /oradata
[root@localhost ~]#

Add below entry to /etc/fstab so that the partition named oradata remains there after a reboot.

/dev/sdb1 /oradata           ext4    relatime,errors=remount-ro 0       1            reboot



Good luck.....

Vi in Solaris gives "Terminal too wide"

Hi,

We see this problem while working on with Solaris Operating system. While you try to vi any file it says terminal too wide.

vi ashish.ksh
Terminal too wide
:

Solution:

stty columns 120

Now you will be able to vi

ORA-01000: maximum open cursors exceeded

Many times we may see this error. This means we have crossed the limit of open cursor. You can check the value assigned for cursor

show parameter open_cursor

Solution 1:

Increase the value for open_cursor

ALTER SYSTEM SET OPEN_CURSORS=500 SID='*' SCOPE=BOTH;


Solution 2:

Tune the sql statement to limit it for creating multiple sessions. Below sql will help you to check the number of cursor open by sessions.


select sum(a.value) total_cur, avg(a.value) avg_cur,
max(a.value) max_cur, s.username, s.machine
from v$sesstat a, v$statname b, v$session s
where a.statistic# = b.statistic# and s.sid=a.sid
and b.name = 'opened cursor '
group by s.username, s.machine order by 1 desc;