Month: June 2009

For all tables from the SCOTT schema

EXEC dbms_stats.gather_schema_stats(’SCOTT’, cascade=>TRUE);

For the table EMP of SCOTT schema

EXEC dbms_stats.gather_table_stats(‘SCOTT’,’EMP’,cascade=>TRUE);

References:

http://www.oradev.com/create_statistics.jsp

# ——————————————————————————————-
Principle:

• Step 1: the generate_drop_all.sql script is used to generate drop statements for all objects that belongs to the current user. Theses statements are generated into drop_all_objects.sql
• Step 2: the drop_all_objects.sql script is used to drop everything prior to import
• Step 3: import…

Details:

# Step 1 :Move to the /tmp directory
# Copy the generate_drop_all.sql file here
# Generate the drop script
exit | sqlplus target_user/target_password @generate_drop_all.sql

# -> a new file drop_all.sql is generated

# Step2 : Run the drop script
exit | sqlplus target_user/target_password @drop_all_objects.sql

# Step3 : Import the dump
imp userid=target_user/target_password file= the_dump_file.dump log=the_log_file.log fromuser=origin_user_name touser=target_user_name

Content of the generate_drop_all.sql file

set feedback off
set trimspool on
set echo off
set verify off
set linesize 200
set pagesize 0
spool drop_all_objects.sql

select ‘drop ‘ || object_type || ‘ ‘ || object_name || ‘ cascade constraints;’
from user_objects
where object_type in (‘TABLE’);

select ‘drop ‘ || object_type || ‘ ‘ || object_name || ‘;’
from user_objects
where object_type not in (‘TABLE’, ‘INDEX’, ‘PACKAGE BODY’, ‘LOB’);

select ‘commit;’
spool off

Import an Oracle dump to oracle XE in 3 steps:

• Create a directory to contain a new tablespace
• Create a dedicated tablespace and user that reflects the name of the ones used to export
• Import the dump into this new tablespace

Detailled instructions

————————————————
Create a directory for Data (tablespace)
———————————————–

mkdir C:\myDataFiles

———————————————————
Create a TAB_CS tablespace

Use exactly this name as it will be requested for import
Connect to XE as System user (use SQL developper for instance)
and run the following script and COMMIT !
———————————————————–

CREATE  TABLESPACE TAB_CS
DATAFILE 'C:\myDataFiles\TAB_CS.ora'
SIZE 10M
AUTOEXTEND ON NEXT 5M;
 
CREATE  USER MyUser
IDENTIFIED BY MyPass
DEFAULT TABLESPACE TAB_CS
TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK
QUOTA UNLIMITED ON TAB_CS;
 
GRANT CONNECT TO MyUser;
GRANT RESOURCE TO MyUser;
GRANT DBA  TO MyUser;

—————-
Import the DUMP
—————-
Copy the dump file to c:\TheDumpFIle.dump

Open windows command prompt (Start/Run/cmd) and run:

C:\oraclexe\app\oracle\product\10.2.0\server\BIN\imp.exe M260/M260 file=TheDumpFIle.dump full=yes

Import should run without any warning

Verify that everything is OK ussing an Oracle client connect as MyUservia SQL developper and check tables