retrieve Table Sizes in Oracle Database

To retrieve the Table Size of a Database Table in Oracle Database, the following SQL code can be used (also without DBA rights to DBA_Segments Data Dictionary Table):

image

select bytes/1024/1024 MB 
from user_segments 
where segment_name='<Table Name>';

E.g.

select bytes/1024/1024 MB 
from user_segments 
where segment_name='CUSTOMER';

Advertisements

Opening multiple windows/tabs in Oracle SQL Developer

When clicking on a Table in Oracle SQL Developer, the IDE will open the Table, but by default it will only open one at a time. Thus, it is not possible by default to open multiple View panes for viewing multiple tables at the same time. The IDE will expect the user to use the “pin” to pin a view and than open a new (second) view pane.

image

To change this behaviour change:

image

This will open all view panes with a “pinned view” and by default open multiple views of Tables:

image

Importing Oracle Database using imp utility

1)optional but recommended: open SQL Developer and create a new Table space for the new(import) schema from the SYS User (SYSDBA Role)

CREATE TABLESPACE PTS_DW_TS
DATAFILE '/u01/app/oracle/oradata/orcl12c/orcl/pts_dw_ts.dbf' 
SIZE 100M AUTOEXTEND ON  MAXSIZE unlimited;

2) create a new User similar to the User for which the Database export has been created

create user PTS_DW identified by Admin123 
default tablespace PTS_DW_TS quota unlimited on PTS_DW_TS;

3) grant privileges to the new User

grant connect, create session, imp_full_database to PTS_DW ;

image

4) open a terminal and navigate to the imp utility directory and invoke the utility

cd /u01/app/oracle/product/12.1.0.2/db_1/bin
imp PTS_DW/Admin123@orcl 
file=/media/sf_vboxEx/exp_OrdMgmt.dmp 
log=PTS_DW_OBIAimp.log full=y;

image

Exporting Oracle database Tables using exp utility

To export Oracle Database Tables the exp utility can be used. The utility is available on the
Oracle Database machine typicalliy in the following directory $ORACLE_HOME/bin. The following command can be used to export a (selective list) of Database tables:

exp user/password file=filename.dmp tables=Table1,Table2

imageThe exp utility displays the current progress in the terminal:

image
After completion the exported file can be found in the directory where the utility has been executed:
image