ORA2PG Demo

-------------------------At Oracle--------------------------------------------------- PREREQUEST: 1.In a database all schema size: set linesize 150 set pagesize 5000 col owner for a15 col segment_name for a30 col segment_type for a20 col TABLESPACE_NAME for a30 clear breaks clear computes compute sum of SIZE_IN_GB on report break on report select OWNER,sum(bytes)/1024/1024/1000 "SIZE_IN_GB" from dba_segments group by owner order by owner; 2.issuing below script you will extract the size & path of datafiles along with tablespace name,allocated space in GB, used space in GB & free space in GB. set linesize 200 set pagesize 2000 COLUMN tablespace_name format a10 COLUMN file_name format a45 COLUMN free% format a7 SELECT df.tablespace_name,SUBSTR (df.file_name, 1, 60) file_name, df.bytes/1024/1024/1024 allocated_GB, round(((df.bytes/1024 /1024/1024) – NVL(SUM(dfs.bytes)/1024/1024 /1024, 0)),1) used_GB, round(NVL(SUM (dfs.bytes)/1024/1024/1024, 0),2) free_space_GB FROM dba_data_files df, dba_free_space dfs WHERE df.FILE_ID = dfs.file_id(+) GROUP BY df.tablespace_name, dfs.file_id, df.file_id , df.bytes, df.file_NAME ORDER BY df.tablespace_name; From SQL> conn sys/oracle@testdb as sysdba Connected. SQL> @tsutil TsUtil_TESTDB_200924212645 !!!!! Table Space Utilization !!!!! 24/09/2020 21:26:45 Tablespace Name AUT Total Space Used apace Free Space Max Size Free % ------------------------------ --- ----------- ---------- ---------- ---------- ---------- SYSAUX YES 570 535 35 32767.9844 98.37 SYSTEM YES 900 890.5 9.5 32767.9844 97.28 UNDOTBS1 YES 270 54.125 215.875 32767.9844 99.83 USERS YES 1355 1289.6875 65.3125 32767.9844 96.06 !!!!! END !!!!! SQL> @datafiles DataFiles_TESTDB_200924212654 PL/SQL procedure successfully completed. 24/09/2020 21:26:54 TABLESPACE_NAME FILE_NAME USER_BYTES_GB BYTES_GB MAX_GB AUT STATUS ONLINE_ ------------------------------ ---------------------------------------------------------------------- ------------- -------- -------- --- --------- ------- SYSAUX /u01/oradata/TESTDB/sysaux01.dbf .56 .56 32.00 YES AVAILABLE ONLINE SYSTEM /u01/oradata/TESTDB/system01.dbf .88 .88 32.00 YES AVAILABLE SYSTEM UNDOTBS1 /u01/oradata/TESTDB/undotbs01.dbf .26 .26 32.00 YES AVAILABLE ONLINE USERS /u01/oradata/TESTDB/users01.dbf 1.32 1.32 32.00 YES AVAILABLE ONLINE !!!!! END !!!!! POSTREQUEST: ------------------------- AT PostgreSQL----------------------------------------------- 1.CHECK PostgreSQL all schema Size But size will be varried compare to destination, event though check the schema's table count also: Edit: just noticed the workaround with summing up all tables to get the database size is not necessary: From AT POSTGRES SIDE PRECHECKS: SELECT schema_name, pg_size_pretty(sum(table_size)::bigint), (sum(table_size) / pg_database_size(current_database())) * 100 FROM ( SELECT pg_catalog.pg_namespace.nspname as schema_name, pg_relation_size(pg_catalog.pg_class.oid) as table_size FROM pg_catalog.pg_class JOIN pg_catalog.pg_namespace ON relnamespace = pg_catalog.pg_namespace.oid ) t GROUP BY schema_name ORDER BY schema_name schema_name | pg_size_pretty | ?column? --------------------+----------------+------------------------- information_schema | 96 kB | 1.24304042650610727800 pg_catalog | 5992 kB | 77.58643995442286261700 pg_toast | 600 kB | 7.76900266566317048900 (3 rows) 2.All tablespace size for refernece only not mandatory: SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) FROM pg_tablespace; postgres=# SELECT spcname, pg_size_pretty(pg_tablespace_size(spcname)) FROM pg_tablespace; spcname | pg_size_pretty ------------+---------------- pg_default | 45 MB pg_global | 574 kB (2 rows) [root@localhost DBD-Oracle-1.75_2]# perl Makefile.PL Using DBI 1.627 (for perl 5.016003 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/vendor_perl/auto/DBI/ Configuring DBD::Oracle for perl 5.016003 on linux (x86_64-linux-thread-multi) If you encounter any problem, a collection of troubleshooting guides are available under lib/DBD/Oracle/Troubleshooting. 'DBD::Oracle::Troubleshooting' is the general troubleshooting guide, while platform-specific troubleshooting hints live in their labelled sub-document (e.g., Win32 hints are gathered in 'lib/DBD/Oracle/Troubleshooting/Win32.pod'). Installing on a linux, Ver#2.6 Using Oracle in /u01/app/oracle/product/19.0.0/dbhome_1 DEFINE _SQLPLUS_RELEASE = "1903000000" (CHAR) Oracle version 19.3.0.0 (19.3) Found direct-link candidates: libclntsh.so Oracle sysliblist: -ldl -lm -lpthread -lnsl -lirc -limf -lirc -lrt -laio -lresolv -lsvml Found header files in /u01/app/oracle/product/19.0.0/dbhome_1/rdbms/public. Your LD_LIBRARY_PATH env var is set to '/u01/app/oracle/product/19.0.0/dbhome_1/lib' client_version=19.3 DEFINE= -Wall -Wno-comment -DUTF8_SUPPORT -DORA_OCI_VERSION=\"19.3.0.0\" -DORA_OCI_102 -DORA_OCI_112 Checking for functioning wait.ph System: perl5.016003 linux x86-021.build.eng.bos.redhat.com 2.6.32-431.4.1.el6.x86_64 #1 smp thu dec 19 10:26:41 est 2013 x86_64 x86_64 x86_64 gnulinux Compiler: gcc -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -D_REENTRANT -D_GNU_SOURCE -fno-strict-aliasing -pipe -fstack-protector -I/usr/local/include -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 Linker: /usr/bin/ld Sysliblist: -ldl -lm -lpthread -lnsl -lirc -limf -lirc -lrt -laio -lresolv -lsvml Linking with -lclntsh. Checking if your kit is complete... Looks good LD_RUN_PATH=/u01/app/oracle/product/19.0.0/dbhome_1/lib:/lib64 Using DBD::Oracle 1.75_2. Using DBD::Oracle 1.75_2. Using DBI 1.627 (for perl 5.016003 on x86_64-linux-thread-multi) installed in /usr/lib64/perl5/vendor_perl/auto/DBI/ Writing Makefile for DBD::Oracle Writing MYMETA.yml and MYMETA.json [root@localhost DBD-Oracle-1.75_2]#