Use the DBMS_STATS package to perform the export and import operations

1. In the production database, create the STATS table in the JOE schema in the TOOLS tablespace. This table will be used to hold the production schema statistics.

SQL> EXECUTE DBMS_STATS.CREATE_STAT_TABLE
(‘JOE’,’STATS’,’TOOLS’);

2. Capture the current statistics for Joe’s schema and store them in the newly created STATS table.

SQL> EXECUTE DBMS_STATS.EXPORT_SCHEMA_STATS
(‘JOE’,’STATS’);

3. Use the Oracle Export utility from the OS command line to export the contents of the STATS table from the production database.

$ exp joe/sidney@PROD file=stats.dmp tables=
(STATS) log=stats.log

4. Move the export dump file from the production server to the Development server using FTP.

$ ftp devl
Connected to DEVL
220 DEVL FTP server (Tue Oct 22 16:48:12 EDT 2002) ready.
Name (DEVL:jjohnson): johnson
331 Password required for johnson.
Password:
230 User johnson logged in.
Remote system type is Unix.
Using binary mode to transfer files.
ftp> put stats.dmp
200 PORT command successful.
150 Opening BINARY mode data connection for stats.dmp
226 Transfer complete.
ftp> bye

5. In the Development database, create the STATS table in the JOE schema in the TOOLS tablespace. This table will hold the exported contents of the STATS table from the production database.

SQL> EXECUTE DBMS_STATS.CREATE_STAT_TABLE
(‘JOE’,’STATS’,’TOOLS’);

6. Use the Oracle Import utility from the operating system command line to import the STATS dump file created on the production server into the STATS table on the Development server.

$ imp joe/sidney@DEVL file=stats.dmp log=stats.log full=y

7. Move the statistics in Joe’s STATS table into the Development database’s data dictionary.

SQL> EXECUTE DBMS_STATS.IMPORT_SCHEMA_STATS
(‘JOE’,’STATS’);