Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: Export Sequences from a database

RE: RE: Export Sequences from a database

From: Gunawan Yuwono <gunawan.yuwono_at_webbox.com>
Date: Fri, 29 Sep 2000 07:44:27 -0700
Message-Id: <10634.118225@fatcity.com>


This message is in MIME 1.0 Format
--===========_<mailstart.9/29/00.27866.13. gunawan.yuwono_at_webbox.com>=====_ Content-Type: text/plain

If it's not too late, here is a script that I wrote a while ago. It's pretty much the same with Ray's script. I also attached the file.



SET ECHO OFF
SET VERIFY OFF
SET HEADING OFF
SET FEEDBACK OFF
SET SQLCASE UPPER
SET SERVEROUTPUT ON SIZE 100000
ACCEPT v_seq_owner PROMPT 'Enter Existing Sequence Owner: '
ACCEPT v_new_owner PROMPT 'Enter New Sequence Owner: '
ACCEPT v_drop_seq PROMPT 'Include Drop Sequence, Y or N: '
 

SPOOL create_seq.sql

DECLARE

   v_max_value	VARCHAR2(20);
   v_cycle	VARCHAR2(20);
   v_order	VARCHAR2(20);
   v_cache	VARCHAR2(20);
   v_priv	VARCHAR2(20);
   v_grantee	VARCHAR2(20);
   v_count	NUMBER;

   CURSOR C_SEQ IS
	SELECT * FROM dba_sequences WHERE sequence_owner='&&v_seq_owner';

BEGIN
   FOR seq IN C_SEQ LOOP

	SELECT DECODE(SIGN((seq.max_value/2147483647)-1), 1, ' NOMAXVALUE',
		' MAXVALUE ' || to_char(seq.max_value)) INTO v_max_value FROM
DUAL;         SELECT DECODE(seq.cycle_flag, 'Y', ' CYCLE', ' NOCYCLE') INTO v_cycle FROM DUAL;

        SELECT DECODE(seq.order_flag, 'Y', ' ORDER',' NOORDER') INTO v_order FROM DUAL;

	SELECT DECODE(seq.cache_size, 0 , ' NOCACHE',
		' CACHE ' || to_char(seq.cache_size)) INTO v_cache FROM DUAL;

	DBMS_OUTPUT.PUT_LINE('CREATE SEQUENCE &&v_new_owner' || '.'
|| seq.sequence_name);
	DBMS_OUTPUT.PUT_LINE(' START WITH ' || to_char(seq.min_value));
	DBMS_OUTPUT.PUT_LINE(' INCREMENT BY ' || to_char(seq.increment_by));
	DBMS_OUTPUT.PUT_LINE(' MINVALUE ' || to_char(seq.min_value));
	DBMS_OUTPUT.PUT_LINE(v_max_value);
	DBMS_OUTPUT.PUT_LINE(v_cycle);
	DBMS_OUTPUT.PUT_LINE(v_order);
	DBMS_OUTPUT.PUT_LINE(v_cache || ';');
	DBMS_OUTPUT.PUT_LINE('--');


-- GRANT Privileges
SELECT count(*) INTO v_count FROM dba_tab_privs WHERE table_name=seq.sequence_name AND owner='&&v_seq_owner'
and privilege='SELECT';
	IF v_count = 1 THEN
		SELECT privilege, grantee INTO v_priv, v_grantee
		FROM dba_tab_privs
		WHERE table_name=seq.sequence_name AND owner='&&v_seq_owner'
and
			privilege='SELECT';

		DBMS_OUTPUT.PUT_LINE('GRANT ' || v_priv || ' ON &&v_new_owner'
|| '.' ||
			seq.sequence_name || ' TO ' || v_grantee || ';');
	END IF;


	SELECT count(*) INTO v_count
	FROM dba_tab_privs
	WHERE table_name=seq.sequence_name AND owner='&&v_seq_owner'
and privilege='ALTER';
	IF v_count = 1 THEN
		SELECT privilege, grantee INTO v_priv, v_grantee
		FROM dba_tab_privs
		WHERE table_name=seq.sequence_name AND owner='&&v_seq_owner'
and
			privilege='ALTER';

		DBMS_OUTPUT.PUT_LINE('GRANT ' || v_priv || ' ON &&v_new_owner'
|| '.' ||
		seq.sequence_name || ' TO ' || v_grantee || ';');
	END IF;

	DBMS_OUTPUT.PUT_LINE('--');

   END LOOP; END;
/

SPOOL OFF SET VERIFY ON
SET HEADING ON
SET FEEDBACK ON
SET ECHO ON


HTP. Gunawan Yuwono
Oracle DBA
Kansas City, MO

>--- Original Message ---
>From: Mosy Lo <mlo_at_JCICorp.com>
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Date: 9/28/00 9:40:21 PM
>

>Thanks very very much for all your time. I got the sequences import to the

>new db with Raj's script.
>
>-----Original Message-----
>Sent: Thursday, September 28, 2000 11:06 AM
>To: Multiple recipients of list ORACLE-L
>
>
>Import Sequences with current value ...
>
>SELECT 'DROP SEQUENCE ' || SEQUENCE_NAME || ' ;' 
>from ALL_SEQUENCES
>WHERE SEQUENCE_OWNER = <owner_name>
>UNION ALL 
>select 'CREATE SEQUENCE ' || SEQUENCE_NAME || 
>' INCREMENT BY ' || INCREMENT_BY || 
>' START WITH ' || to_char(LAST_NUMBER+1) || 
>' MINVALUE ' || to_char(MIN_VALUE) || 
>Decode(LENGTH(TO_CHAR(MAX_VALUE)), 27, '', ' MAXVALUE ' ||
>TO_CHAR(MAX_VALUE) ) || 
>Decode(CYCLE_FLAG, 'N', ' NOCYCLE', 'Y', ' CYCLE' ) || 
>Decode(CACHE_SIZE, 0, ' NOCACHE', ' CACHE ' || TO_CHAR(CACHE_SIZE)
) ||
>Decode(ORDER_FLAG, 'N', ' NOORDER', 'Y', ' ORDER' ) || ';' 
>from ALL_SEQUENCES
>where SEQUENCE_OWNER = <owner_name>
>
>
>Import Sequences with start value as 1
>SELECT 'DROP SEQUENCE ' || SEQUENCE_NAME || ' ;'  
>from ALL_SEQUENCES
>WHERE SEQUENCE_OWNER = <owner_name>
>UNION ALL 
>select 'CREATE SEQUENCE ' || SEQUENCE_NAME || 
>' INCREMENT BY ' || INCREMENT_BY || 
>' START WITH 1' || 
>Decode(LENGTH(TO_CHAR(MAX_VALUE)), 27, '', ' MAXVALUE ' ||
>TO_CHAR(MAX_VALUE) ) || 
>Decode(CYCLE_FLAG, 'N', ' NOCYCLE', 'Y', ' CYCLE' ) || 
>Decode(CACHE_SIZE, 0, ' NOCACHE', ' CACHE ' || TO_CHAR(CACHE_SIZE)
) ||
>Decode(ORDER_FLAG, 'N', ' NOORDER', 'Y', ' ORDER' ) || ';' 
>from ALL_SEQUENCES
>where SEQUENCE_OWNER = <owner_name>
>
>HTH
>Raj
>______________________________________________________
>Rajendra Jamadagni		MIS, ESPN Inc.
>Rajendra dot Jamadagni at ESPN dot com
>QOTD: Any clod can have facts, but having an opinion is an art
!
>Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
>
>
>
>This e-mail message is confidential, intended only for the named
>recipient(s) above and may contain information that is privileged,
attorney
>work product or exempt from disclosure under applicable law. If you have
>received this message in error, or are not the named recipient(s), please
>immediately notify ESPN at (860) 766-2000 and delete this e-mail message
>from your computer, Thank you.
>
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Jamadagni, Rajendra
>  INET: rajendra.jamadagni_at_espn.com
>
>Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California        -- Public Internet access / Mailing
Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>-- 
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>-- 
>Author: Mosy Lo
>  INET: mlo_at_JCICorp.com
>
>Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
>San Diego, California        -- Public Internet access / Mailing
Lists
>--------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message >to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L >(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).
>
>

----------------

Sent from a WebBox - http://www.webbox.com FREE Web based Email, Files, Bookmarks, Calendar, People and Great Ways to Share them with Others!

--===========_<mailstart.9/29/00.27866.13. gunawan.yuwono_at_webbox.com>=====_

Content-Type: application/octet-stream; name="create_sequence.sql"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="create_sequence.sql"

U0VUIEVDSE8gT0ZGIA0KU0VUIFZFUklGWSBPRkYgDQpTRVQgSEVBRElORyBPRkYNClNFVCBGRUVE QkFDSyBPRkYNClNFVCBTUUxDQVNFIFVQUEVSDQpTRVQgU0VSVkVST1VUUFVUIE9OIFNJWkUgMTAw MDAwDQoNCkFDQ0VQVCB2X3NlcV9vd25lciBQUk9NUFQgJ0VudGVyIEV4aXN0aW5nIFNlcXVlbmNl IE93bmVyOiAnDQpBQ0NFUFQgdl9uZXdfb3duZXIgUFJPTVBUICdFbnRlciBOZXcgU2VxdWVuY2Ug T3duZXI6ICcNCkFDQ0VQVCB2X2Ryb3Bfc2VxIFBST01QVCAnSW5jbHVkZSBEcm9wIFNlcXVlbmNl LCBZIG9yIE46ICcNCiANClNQT09MIGNyZWF0ZV9zZXEuc3FsDQoNCkRFQ0xBUkUNCiAgIHZfbWF4 X3ZhbHVlCVZBUkNIQVIyKDIwKTsNCiAgIHZfY3ljbGUJVkFSQ0hBUjIoMjApOw0KICAgdl9vcmRl cglWQVJDSEFSMigyMCk7DQogICB2X2NhY2hlCVZBUkNIQVIyKDIwKTsNCiAgIHZfcHJpdglWQVJD SEFSMigyMCk7DQogICB2X2dyYW50ZWUJVkFSQ0hBUjIoMjApOw0KICAgdl9jb3VudAlOVU1CRVI7 DQoNCiAgIENVUlNPUiBDX1NFUSBJUw0KCVNFTEVDVCAqIEZST00gZGJhX3NlcXVlbmNlcyBXSEVS RSBzZXF1ZW5jZV9vd25lcj0nJiZ2X3NlcV9vd25lcic7DQoNCkJFR0lODQogICBGT1Igc2VxIElO IENfU0VRIExPT1ANCg0KCS0tIERyb3Agc2VxdWVuY2Ugc3RhdGVtZW50DQoJSUYgc3Vic3RyKCcm JnZfZHJvcF9zZXEnLDEsMSkgPSAnWScgVEhFTg0KCQlEQk1TX09VVFBVVC5QVVRfTElORSgnRFJP UCBTRVFVRU5DRSAmJnZfc2VxX293bmVyJyB8fCAnLicgfHwNCgkJCXNlcS5zZXF1ZW5jZV9uYW1l IHx8ICc7Jyk7DQoJRU5EIElGOw0KDQoJU0VMRUNUIERFQ09ERShTSUdOKChzZXEubWF4X3ZhbHVl LzIxNDc0ODM2NDcpLTEpLCAxLCAnIE5PTUFYVkFMVUUnLA0KCQknIE1BWFZBTFVFICcgfHwgdG9f Y2hhcihzZXEubWF4X3ZhbHVlKSkgSU5UTyB2X21heF92YWx1ZSBGUk9NIERVQUw7DQoNCglTRUxF Q1QgREVDT0RFKHNlcS5jeWNsZV9mbGFnLCAnWScsICcgQ1lDTEUnLCAnIE5PQ1lDTEUnKSBJTlRP IHZfY3ljbGUgRlJPTSBEVUFMOw0KDQoJU0VMRUNUIERFQ09ERShzZXEub3JkZXJfZmxhZywgJ1kn LCAnIE9SREVSJywnIE5PT1JERVInKSBJTlRPIHZfb3JkZXIgRlJPTSBEVUFMOw0KDQoJU0VMRUNU IERFQ09ERShzZXEuY2FjaGVfc2l6ZSwgMCAsICcgTk9DQUNIRScsDQoJCScgQ0FDSEUgJyB8fCB0 b19jaGFyKHNlcS5jYWNoZV9zaXplKSkgSU5UTyB2X2NhY2hlIEZST00gRFVBTDsNCg0KCURCTVNf T1VUUFVULlBVVF9MSU5FKCdDUkVBVEUgU0VRVUVOQ0UgJiZ2X25ld19vd25lcicgfHwgJy4nIHx8 IHNlcS5zZXF1ZW5jZV9uYW1lKTsNCglEQk1TX09VVFBVVC5QVVRfTElORSgnIFNUQVJUIFdJVEgg JyB8fCB0b19jaGFyKHNlcS5taW5fdmFsdWUpKTsNCglEQk1TX09VVFBVVC5QVVRfTElORSgnIElO Q1JFTUVOVCBCWSAnIHx8IHRvX2NoYXIoc2VxLmluY3JlbWVudF9ieSkpOw0KCURCTVNfT1VUUFVU LlBVVF9MSU5FKCcgTUlOVkFMVUUgJyB8fCB0b19jaGFyKHNlcS5taW5fdmFsdWUpKTsNCglEQk1T X09VVFBVVC5QVVRfTElORSh2X21heF92YWx1ZSk7DQoJREJNU19PVVRQVVQuUFVUX0xJTkUodl9j eWNsZSk7DQoJREJNU19PVVRQVVQuUFVUX0xJTkUodl9vcmRlcik7DQoJREJNU19PVVRQVVQuUFVU X0xJTkUodl9jYWNoZSB8fCAnOycpOw0KCURCTVNfT1VUUFVULlBVVF9MSU5FKCctLScpOw0KDQoJ LS0gR1JBTlQgUHJpdmlsZWdlcw0KCVNFTEVDVCBjb3VudCgqKSBJTlRPIHZfY291bnQNCglGUk9N IGRiYV90YWJfcHJpdnMNCglXSEVSRSB0YWJsZV9uYW1lPXNlcS5zZXF1ZW5jZV9uYW1lIEFORCBv d25lcj0nJiZ2X3NlcV9vd25lcicgYW5kIHByaXZpbGVnZT0nU0VMRUNUJzsNCg0KCUlGIHZfY291 bnQgPSAxIFRIRU4NCgkJU0VMRUNUIHByaXZpbGVnZSwgZ3JhbnRlZSBJTlRPIHZfcHJpdiwgdl9n cmFudGVlDQoJCUZST00gZGJhX3RhYl9wcml2cw0KCQlXSEVSRSB0YWJsZV9uYW1lPXNlcS5zZXF1 ZW5jZV9uYW1lIEFORCBvd25lcj0nJiZ2X3NlcV9vd25lcicgYW5kDQoJCQlwcml2aWxlZ2U9J1NF TEVDVCc7DQoNCgkJREJNU19PVVRQVVQuUFVUX0xJTkUoJ0dSQU5UICcgfHwgdl9wcml2IHx8ICcg T04gJiZ2X25ld19vd25lcicgfHwgJy4nIHx8DQoJCQlzZXEuc2VxdWVuY2VfbmFtZSB8fCAnIFRP ICcgfHwgdl9ncmFudGVlIHx8ICc7Jyk7DQoJRU5EIElGOw0KDQoNCglTRUxFQ1QgY291bnQoKikg SU5UTyB2X2NvdW50DQoJRlJPTSBkYmFfdGFiX3ByaXZzDQoJV0hFUkUgdGFibGVfbmFtZT1zZXEu c2VxdWVuY2VfbmFtZSBBTkQgb3duZXI9JyYmdl9zZXFfb3duZXInIGFuZCBwcml2aWxlZ2U9J0FM VEVSJzsNCg0KCUlGIHZfY291bnQgPSAxIFRIRU4NCgkJU0VMRUNUIHByaXZpbGVnZSwgZ3JhbnRl ZSBJTlRPIHZfcHJpdiwgdl9ncmFudGVlDQoJCUZST00gZGJhX3RhYl9wcml2cw0KCQlXSEVSRSB0 YWJsZV9uYW1lPXNlcS5zZXF1ZW5jZV9uYW1lIEFORCBvd25lcj0nJiZ2X3NlcV9vd25lcicgYW5k DQoJCQlwcml2aWxlZ2U9J0FMVEVSJzsNCg0KCQlEQk1TX09VVFBVVC5QVVRfTElORSgnR1JBTlQg JyB8fCB2X3ByaXYgfHwgJyBPTiAmJnZfbmV3X293bmVyJyB8fCAnLicgfHwNCgkJc2VxLnNlcXVl bmNlX25hbWUgfHwgJyBUTyAnIHx8IHZfZ3JhbnRlZSB8fCAnOycpOw0KCUVORCBJRjsNCg0KCURC TVNfT1VUUFVULlBVVF9MSU5FKCctLScpOw0KDQogICBFTkQgTE9PUDsNCg0KRU5EOw0KLw0KDQpT Received on Fri Sep 29 2000 - 09:44:27 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US