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