Create Folder structure in SQL Developer

Within SQL Developer the Database connections can be structure within a folder structure. This can be done by right clicking on a existing connection and choosing “Add to Folder”:

Untitled2

Advertisements

counting rows in all database tables

The following SQL Code can be used to display the rowcount for all (Orale BI Applications) tables:

select table_name,
to_number(extractvalue(xmltype
(dbms_xmlgen.getxml('select count(*) c from '||table_name))
,'/ROWSET/ROW/C')) count
from user_tables
order by table_name;

image

for further reference, check here.

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