1 / 55

Oracle8 Advanced Replication

Oracle8 Advanced Replication. A Beginner’s Perspective Brian Hitchcock, DBA Sun Microsystems brian.hitchcock@sun.com. Historical Background. Our group Track all Real Estate holdings for Sun who is in which building, office, cubicle etc. Our application

mraulston
Télécharger la présentation

Oracle8 Advanced Replication

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle8 Advanced Replication A Beginner’s Perspective Brian Hitchcock, DBA Sun Microsystems brian.hitchcock@sun.com Oracle8 Advanced Replication A Beginner’s Perspective

  2. Historical Background • Our group • Track all Real Estate holdings for Sun • who is in which building, office, cubicle etc. • Our application • tracks occupancy of Sun facilities world-wide • forecasting of short and long-term needs • Replicate application tables only • keep it simple • start with basic table replication Oracle8 Advanced Replication A Beginner’s Perspective

  3. My Background • DBA for 6 years • Asymmetric replication • non-Oracle, single master, multiple replicate sites • LAN • replication from production system to reporting system Oracle8 Advanced Replication A Beginner’s Perspective

  4. Why Use Replication? • Application deployed globally • Network performance • Disaster Recovery • Distributed data Oracle8 Advanced Replication A Beginner’s Perspective

  5. Why Use Replication? • Application deployed globally • US, Holland, Singapore • users want consistent application response time(s) • Network performance • gets worse for sites far from US • total network delay can become unacceptable for Europe/Asia Oracle8 Advanced Replication A Beginner’s Perspective

  6. Why Use Replication? • Disaster Recovery • backup copy of critical data up-to-date within seconds • each site independent during network downtime • Distributed data • each site can run reports locally • all data available at all sites Oracle8 Advanced Replication A Beginner’s Perspective

  7. Additional Replication Benefits • Provides automatic archiving versus full db dumps • only changes are moved to other sites • don’t have to store multiple copies of all existing data • Improved Disaster Recovery • can reduce data loss window with less impact to network and disk space • don’t have to make complete backups of all data, only the updates Oracle8 Advanced Replication A Beginner’s Perspective

  8. Issues • Conflicts • Support/Training • Existing application design • Resynchronization • is more than just rows of data • must synch all portions of replicated table (constraints, indexes etc.) • replication won’t do this automatically Oracle8 Advanced Replication A Beginner’s Perspective

  9. Asynchronous • Local commands • Remote execution • Latency • What’s happening? Oracle8 Advanced Replication A Beginner’s Perspective

  10. System Diagram Holland US Ultra2, 512 Mb 75 Gb (mirrored) Solaris 2.5.1 E4000 512 Mb 75 Gb (mirrored) Solaris 2.5.1 Ultra2, 512 Mb 75 Gb (mirrored) Solaris 2.5.1 Singapore Oracle8 Advanced Replication A Beginner’s Perspective

  11. Schema Diagram Holland US Singapore 4 schemas -- plan -- planning -- allocation -- supplyplan Oracle8 Advanced Replication A Beginner’s Perspective

  12. My Experience • Consultant • Snapshots • Updateable bring data back from remote sites • Simple move lookup data to remote sites • Replaced cron jobs • Didn’t work… Oracle8 Advanced Replication A Beginner’s Perspective

  13. What Went Wrong? • Consultant • No replication experience, no training • Couldn’t use OEM, had to use API • Oracle8 Adv Rep manual assumes OEM • Used 7.3 manuals for API • Specific problems • was setup as ‘repadmin’, granted DBA role • scheduling ‘push’ job, db links were ‘creative’ • global_name was not understood Oracle8 Advanced Replication A Beginner’s Perspective

  14. Why We Went to Advanced Replication • Conflicts avoided by application design • only one user can update data • don’t require snapshots to prevent conflicts • Data owners travel worldwide • want to be able to update their data anywhere • and have best possible response time • Disaster Recovery • all data can be updated at any site Oracle8 Advanced Replication A Beginner’s Perspective

  15. Oracle Training • 90% API • Covers what manuals should cover • Jenny Tsai -- a ‘goddess’ • ‘dog years’ joke is too old... • Labs • provides template for replication setup • excellent practice starting replication Oracle8 Advanced Replication A Beginner’s Perspective

  16. Oracle Training Message • You must understand API, OEM good for monitoring • Retrofit of replication to existing application is ‘virtually impossible’ • 3 sites may never converge • Conflict issues drive implementation • Oracle replication uses after row triggers Oracle8 Advanced Replication A Beginner’s Perspective

  17. My Message • Don’t rely on the manuals • Do take the Oracle training • Get copies of Oracle Tech Support Tech Notes, scripts, FAQs • Setup rep config on development servers • API -- get the book... • Oracle Built-in Packages, O’Reilly • Steven Feuerstein, et al, ISBN 1-56592-375-8 • chapters 14 thru 17 Oracle8 Advanced Replication A Beginner’s Perspective

  18. Replicated Tables • Must have primary key • Can’t replicate without • Don’t assume they exist • Check each table for PK Oracle8 Advanced Replication A Beginner’s Perspective

  19. Referential Integrity • Rep doesn’t recreate at master sites • You must ensure that all parts of each replicated table are re-created at each site • use export/import to create replicate objects Oracle8 Advanced Replication A Beginner’s Perspective

  20. Configuration Recommendations • Implementation • Link naming • Link configuration • Server configuration • Practice complete rep installation Oracle8 Advanced Replication A Beginner’s Perspective

  21. Recommendation -- Implementation • export tables from master definition site • import to all master sites • create repgroup as master definition site • reuse=>TRUE, copy_rows=>FALSE • add master site(s) • resume master activity Oracle8 Advanced Replication A Beginner’s Perspective

  22. Recommendations -- Link Naming • init parameters • db_name = <SID> • db_domain = WORLD • global_names = TRUE • check that global_name = <SID>.WORLD • links named <SID>.WORLD • tnsnames.ora use <SID> for service identifier Oracle8 Advanced Replication A Beginner’s Perspective

  23. Recommendation -- Link Configuration • Follow setup shown in training class • public link • create link to remote site, but don’t use ‘connect as’ clause • easier to maintain when master sites change • only need to change tnsnames service identifier(s) • private link • create link that only specifies the ‘connect as’ clause • protects security of the link password • stays that same even when public link has to change Oracle8 Advanced Replication A Beginner’s Perspective

  24. Recommendations -- Server Config • SGA 40 - 80 Mb • RBS tablespace - 30 Mb • init<SID>.ora parameters • Background processes • snapshot_refresh_processes = 2 • job_queue_processes = 2 • job_queue_interval = 60 (seconds -- default) • Other • distributed_lock_timeout = 300 (seconds) Oracle8 Advanced Replication A Beginner’s Perspective

  25. Recommendations -- Practice • Complete script to start and stop replication • Appendix B and C • Use script to setup development • correct syntax errors etc. • Use script to setup production • Script then serves • training doc • tech support doc Oracle8 Advanced Replication A Beginner’s Perspective

  26. Supporting Replication • Central organization supplies DBA support • DBA’s have ever supported replication • None have Oracle8 replication training • Existing application support persons have no replication experience • Application developers have not been exposed to replication issues • must have SYS access to install/config • repadmin user must have DBA role Oracle8 Advanced Replication A Beginner’s Perspective

  27. If Replication Breaks... • Whatta ya gonna do? • default is keep replicating after error is detected • ok for some apps, but not for others • Checking account balance... • Updating status of an order... • Fallback plan • cron jobs • easier for ‘simple’ replication scheme Oracle8 Advanced Replication A Beginner’s Perspective

  28. Once Replication Breaks • Business impact of replication downtime • can business users work around? • Time frame • how long can replication be down? • how long to re-synch? • system locked while synchronizing? • synch time increases as datasets grow Oracle8 Advanced Replication A Beginner’s Perspective

  29. Troubleshooting Example 1 • No PK on table at master definition site • create PK, add table to repgroup, add master site • Table at master site has no PK • Solution • re-create master repobject at master definition site with retry=>true Oracle8 Advanced Replication A Beginner’s Perspective

  30. Troubleshooting Example 2 • Added master site, table already exists at master site • Dropped master site • Can’t add master site again • repgroup already at master site • Can’t drop repobject from master def site • master def doesn’t know about master site anymore • Read Manuals -- look at options on various commands • can drop repgroup from any site • drop repgroup at master site • at master def site add repobject with reuse=>TRUE, copy_rows=>FALSE Oracle8 Advanced Replication A Beginner’s Perspective

  31. Troubleshooting Example 3 • Replication setup seems to work • all admin commands had to be manually pushed to complete • update to replicated table just sits • nothing seems to be happening… • None of the scheduled ‘jobs’ are running • in dba_jobs view, LAST_DATE and LAST_SEC are NULL (no entry) • in dba_jobs view, NEXT_DATE and NEXT_SEC never change • deferred job queue doesn’t seem to be working Oracle8 Advanced Replication A Beginner’s Perspective

  32. Troubleshooting Example 3 • Advanced Replication Manual -- repcatlog errors section • job queue for newly created db doesn’t start until database restarted • our dbs are plenty old, but restarting the db did start the job queue • this ‘detail’ is not mentioned under troubleshooting job queues... Oracle8 Advanced Replication A Beginner’s Perspective

  33. Troubleshooting Example 4 • At master definition site • create replication group(s), add objects • add second master site • Errors in repcatlog • ERROR ORA-00060: deadlock detected while waiting for resource • But, everything looks ok… • Oracle automatically re-applied the transactions, no problem! • Set init<SID>.ora parameter distributed_lock_timeout = 300 (seconds) Oracle8 Advanced Replication A Beginner’s Perspective

  34. Documentation Issues • Replication dba_* views documented • never referenced as dba_<viewname> in the docs • init parameter -- snapshot_refresh_processes • described in Advanced Replication manual • not listed in server reference (under init parameters) • init parameter -- job_queue_processes • what is different from snapshot_refresh_processes? • I can’t find any difference... • scheduled links • the database link is always there • a ‘push’ job is scheduled which will use that link Oracle8 Advanced Replication A Beginner’s Perspective

  35. Command Line vs OEM • OEM -- not all options of the command line • Example -- create user repadmin • OEM can’t assign repadmin default and temp tablespaces • OEM good for monitoring • once replication is setup and running • Command line offers more flexibility • Tech support assumes the command line • Training uses command line, not OEM Oracle8 Advanced Replication A Beginner’s Perspective

  36. Replication Setup Process • At each step • is it ok to start the next step? • how to know if previous step succeeded? (or finished failing…?) Oracle8 Advanced Replication A Beginner’s Perspective

  37. Replication Setup Process • Look at • catrep.sql -- check for all valid packages and for 46 DBMS_REPCAT packages • deferror -- conflicts (unresolved) • deftran -- entries stay until purge job runs • deftrandest -- gone when tran delivered to destination • regroup -- status • number of repobjects -- 5 per table, table + 2 packages + 2 package bodies • create repgroup -- new admin push job appears • create replication support -- ‘generated’ in dba_repobject Oracle8 Advanced Replication A Beginner’s Perspective

  38. Replication Views • Too much info in most views • Need consistent output format • Use SQL scripts (Appendix A) • dba_jobs_psycho.sql (dba_jobs view) • place these scripts in $ORACLE_HOME/local/sql/psycho_scripts • maintain scripts at one site copy to all servers • Scripts make troubleshooting much easier • Scripts helps work with tech support Oracle8 Advanced Replication A Beginner’s Perspective

  39. Lessons Learned • Oracle8 Advanced Replication Documents don’t do the job • much too focused on selling OEM • shielding user from API is a dis-service, API must be understood • Oracle training class is a pre-requisite to using Advanced Replication • We shouldn’t have been so eager to try replication in production Oracle8 Advanced Replication A Beginner’s Perspective

  40. Lessons Learned • To have success, must practice setup on development servers • catrep.sql must be run in SVRMGRL • in SQL*Plus, catrep.sql asks for input (scary!) • manuals don’t discuss running catrep.sql • User education • once replicating, can’t drop/recreate table • need to have data in place, not just schema • update data through DML only Oracle8 Advanced Replication A Beginner’s Perspective

  41. Brian R. Hitchcock • brian.hitchcock@sun.com • Appendix A -- Scripts • Appendix B -- Script Starting Replication • Appendix C -- Script Stopping Replication Oracle8 Advanced Replication A Beginner’s Perspective

  42. Appendix A -- Scripts • $ORACLE_HOME/local/sql/psycho_scripts • check_catrep_psycho • check_ques_psycho • dba_db_links_psycho • dba_jobs_psycho • dba_repcatlog_psycho • dba_repgroup_psycho • dba_repobject_psycho • dba_repobject_tables_psycho • dba_repsites_psycho • deferror_psycho • deftran_psycho • deftrandest_psycho Oracle8 Advanced Replication A Beginner’s Perspective

  43. Scripts SQL> @check_ques_psycho GLOBAL_NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PLAN.WORLD USER is "REPADMIN" rows in dba_repcatlog... COUNT(*) ---------- 0 rows in deferror... COUNT(*) ---------- 3 rows in deftran... COUNT(*) ---------- 5 rows in deftrandest... COUNT(*) ---------- 0 Oracle8 Advanced Replication A Beginner’s Perspective

  44. Scripts SQL> @dba_jobs_psycho GLOBAL_NAME -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- PLAN.WORLD USER is "REPADMIN" number of jobs currently... COUNT(*) ---------- 7 selecting jobs info from dba_jobs view... JOB LOG_USER PRIV_USER SCHEMA_USE ---------- ---------- ---------- ---------- 59 REPADMIN REPADMIN REPADMIN 57 REPADMIN REPADMIN REPADMIN 58 REPADMIN REPADMIN REPADMIN 63 REPADMIN REPADMIN REPADMIN 64 REPADMIN REPADMIN REPADMIN 65 REPADMIN REPADMIN REPADMIN 66 REPADMIN REPADMIN REPADMIN 7 rows selected. current time at this site Mon Nov 2 13:14:20 PST 1998 JOB LAST_DATE LAST_SEC THIS_DATE THIS_SEC NEXT_DATE NEXT_SEC ---------- --------- -------- --------- -------- --------- -------- 59 02-NOV-98 07:20:30 03-NOV-98 07:20:30 57 02-NOV-98 13:01:30 02-NOV-98 14:01:30 58 02-NOV-98 13:02:30 02-NOV-98 14:02:30 63 02-NOV-98 13:11:48 02-NOV-98 13:21:48 64 02-NOV-98 13:06:47 02-NOV-98 13:16:47 65 02-NOV-98 13:07:30 02-NOV-98 13:17:30 66 02-NOV-98 13:13:48 02-NOV-98 13:23:48 7 rows selected. Oracle8 Advanced Replication A Beginner’s Perspective

  45. Scripts JOB TOTAL_TIME B INTERVAL FAILURES ---------- ---------- - -------------------- ---------- 59 54 N sysdate + 1 0 57 214 N sysdate + 1/24 0 58 2553 N sysdate + 1/24 0 63 72 N SYSDATE + (1/144) 0 64 139 N SYSDATE + (1/144) 0 65 37 N SYSDATE + (1/144) 0 66 40 N SYSDATE + (1/144) 0 7 rows selected. JOB WHAT ---------- -------------------------------------------------------------------------------- 59 declare rc binary_integer; begin rc := sys.dbms_defer_sys.purge( delay_seconds=> 0); end; 57 declare rc binary_integer; begin rc := sys.dbms_defer_sys.push(destination=>'EUR O.WORLD', stop_on_error=>FALSE, delay_seconds=>0, parallelism=>1); end; 58 declare rc binary_integer; begin rc := sys.dbms_defer_sys.push(destination=>'ASI A.WORLD', stop_on_error=>FALSE, delay_seconds=>0, parallelism=>1); end; 63 dbms_repcat.do_deferred_repcat_admin('"PLAN"', FALSE); 64 dbms_repcat.do_deferred_repcat_admin('"PLANNING"', FALSE); JOB WHAT ---------- -------------------------------------------------------------------------------- 65 dbms_repcat.do_deferred_repcat_admin('"ALLOCATION"', FALSE); 66 dbms_repcat.do_deferred_repcat_admin('"SUPPLYPLAN"', FALSE); 7 rows selected. Oracle8 Advanced Replication A Beginner’s Perspective

  46. Scripts :::::::::::::: dba_db_links_psycho.sql :::::::::::::: select * from global_name; show user column owner format a10 column db_link format a15 column username format a15 column host format a15 select owner, db_link, username, host, created from dba_db_links; :::::::::::::: dba_jobs_psycho.sql :::::::::::::: select * from global_name; show user column log_user format a10 column priv_user format a10 column schema_user format a10 column interval format a20 column what format a80 PROMPT PROMPT number of jobs currently... select count(*) from dba_jobs; PROMPT selecting jobs info from dba_jobs view... select job, log_user, priv_user, schema_user from dba_jobs; PROMPT current time at this site ! date select job, last_date, last_sec, this_date, this_sec, next_date, next_sec from dba_jobs; select job, total_time, broken, interval, failures from dba_jobs; select job, what from dba_jobs; :::::::::::::: check_catrep_psycho.sql :::::::::::::: select * from global_name; show user set linesize 200 select substr(owner, 1,6), substr(object_name, 1, 20), object_type, status from dba_objects where object_name like '%DBMS%'; PROMPT count(*) from dba_objects where object_name like '%DBMS_REPCAT%'; select count(*) from dba_objects where object_name like '%DBMS_REPCAT%'; PROMPT count(*) from dba_objects where object_name like '%DBMS_REPCAT%' and status='VALID'; select count(*) from dba_objects where object_name like '%DBMS_REPCAT%' and status='VALID'; :::::::::::::: check_ques_psycho.sql :::::::::::::: select * from global_name; show user PROMPT rows in dba_repcatlog... select count(*) from dba_repcatlog; PROMPT rows in deferror... select count(*) from deferror; PROMPT rows in deftran... select count(*) from deftran; PROMPT rows in deftrandest... select count(*) from deftrandest; Oracle8 Advanced Replication A Beginner’s Perspective

  47. Scripts :::::::::::::: dba_repcatlog_psycho.sql :::::::::::::: column id format 9999 column source format a15 column master format a15 column userid format a10 column sname format a20 column gname format a20 select * from global_name; show user select id, status, source, master, timestamp, role from dba_repcatlog; select id, status, sname, gname, type from dba_repcatlog; select id, status, request, errnum from dba_repcatlog; select id, status, message from dba_repcatlog; :::::::::::::: dba_repgroup_psycho.sql :::::::::::::: select * from global_name; show user column sname format a10 column gname format a10 column master format a4 column schema_comment format a30 select gname, sname, master, status, schema_comment from dba_repgroup; :::::::::::::: dba_repobject_psycho.sql :::::::::::::: select * from global_name; show user column sname format a10 column oname format a20 column gname format a10 column object_comment format a40 select oname, gname, type, status, generation_status, id from dba_repobject; select oname, sname, object_comment, min_communication from dba_repobject; :::::::::::::: dba_repobject_tables_psycho.sql :::::::::::::: select * from global_name; show user column sname format a10 column oname format a20 column gname format a10 column object_comment format a40 select oname, gname, type, status, generation_status, id from dba_repobject where type='TABLE'; :::::::::::::: dba_repsites_psycho.sql :::::::::::::: select * from global_name; show user column gname format a10 column dblink format a20 column masterdef format a4 column snapmaster format a4 column master format a4 column my_dblink format a6 column master_comment format a10 column masterdef heading MDEF column snapmaster heading SMAS column master heading MAST column my_dblink heading MYLINK select gname, dblink, masterdef, snapmaster, master, prop_updates, my_dblink, master_comment from dba_repsites; Oracle8 Advanced Replication A Beginner’s Perspective

  48. Scripts :::::::::::::: deferror_psycho.sql :::::::::::::: select * from global_name; show user column deferred_tran_id format a15 column origin_tran_db format a15 column origin_tran_id format a15 column callno format 9999 column destination format a15 column error_msg format a50 column receiver format a15 select deferred_tran_id, origin_tran_db, origin_tran_id, callno, destination, start_time from deferror; select error_number, receiver, error_msg from deferror; :::::::::::::: deftran_psycho.sql :::::::::::::: select * from global_name; show user select * from deftran; :::::::::::::: deftrandest_psycho.sql :::::::::::::: select * from global_name; show user column dblink format a15 select * from deftrandest; Oracle8 Advanced Replication A Beginner’s Perspective

  49. Appendix B -- Start Replication ALLOCATION schema ----------------- ALLOC_ALLOCATION ALLOC_INT_LEASE ALLOC_QUARTER ALLOC_LEASE_FUNCTION ALLOC_LEASE_PLAN ALLOC_LEASE_PLAN_SEGMENT ALLOC_SEGMENT_TYPE ********PLAN -- check for tables, primary key on each table -- REPADMIN in SQLPLUS column object_name format a30 column owner format a15 column constraint_name format a20 select object_name, object_type, status from dba_objects where object_name like 'PLAN_%' and object_type='TABLE' and owner='PLAN' order by object_name; select owner, constraint_name, constraint_type, table_name from dba_constraints where table_name like 'PLAN_%' and owner='PLAN' and constraint_type='P' order by table_name; select object_name, object_type, status from dba_objects where object_name like 'OCC_%' and object_type='TABLE' and owner='PLANNING' order by object_name; select owner, constraint_name, constraint_type, table_name from dba_constraints where table_name like 'OCC_%' and owner='PLANNING' and constraint_type='P' order by table_name; select object_name, object_type, status from dba_objects where object_name like 'ALLOC_%' and object_type='TABLE' and owner='ALLOCATION' order by object_name; select owner, constraint_name, constraint_type, table_name from dba_constraints where table_name like 'ALLOC_%' and owner='ALLOCATION' and constraint_type='P' order by table_name; select object_name, object_type, status from dba_objects where object_name like 'ALLOC_%' and object_type='TABLE' and owner='SUPPLYPLAN' order by object_name; select owner, constraint_name, constraint_type, table_name from dba_constraints where table_name like 'ALLOC_%' and owner='SUPPLYPLAN' and constraint_type='P' order by table_name; *******PLAN create any needed primary keys... *******PLAN -- tables to replicate and check for primary keys... tables to be replicated... PLAN schema ----------- PLAN_BU <-------------not currently used, no need to replicate PLAN_BUILDING PLAN_COUNTRY PLAN_LEASE PLAN_LOCATION PLAN_LOCATION_LEVEL PLAN_ORG PLAN_ORG_LEVEL PLAN_ORG_MAPPING PLAN_PERSON PLAN_SITE PLANNING schema --------------- OCC_ACCESS OCC_COMPANY OCC_DEPARTMENT OCC_DESIGN_VALUES OCC_DIVISION OCC_FLOOR OCC_GEO OCC_HR_ORG OCC_LOAD OCC_METRO_AREA OCC_ORG_MAP OCC_PLANNER OCC_QUARTER OCC_RED_SEAT_SUMMARY OCC_ROOM OCC_SEAT OCC_SEAT_SUMMARY OCC_SOS Oracle8 Advanced Replication A Beginner’s Perspective

  50. Script -- Start Replication *******PLAN -- export schemas cd /home/dbdump/export exp plan/****** owner=plan file=PLAN_plan_103098.exp exp planning/****** owner=planning file=PLAN_planning_103098.exp exp allocation/****** owner=allocation file=PLAN_allocation_103098.exp exp supplyplan/****** owner=supplyplan file=PLAN_supplyplan_103098.exp ls -l *******EURO -- drop/recreate user schemas -- SYS in SQL*PLUS select * from global_name; select count(*) from dba_objects where owner='PLAN'; select count(*) from dba_objects where owner='PLANNING'; select count(*) from dba_objects where owner='ALLOCATION'; select count(*) from dba_objects where owner='SUPPLYPLAN'; select * from dba_users where username='PLAN'; select * from dba_users where username='PLANNING'; select * from dba_users where username='ALLOCATION'; select * from dba_users where username='SUPPLYPLAN'; drop user PLAN cascade; drop user PLANNING cascade; drop user ALLOCATION cascade; drop user SUPPLYPLAN cascade; select username from dba_users; create user PLAN identified by plan default tablespace DATA1 temporary tablespace TEMP quota unlimited on DATA1; grant connect to PLAN; grant resource to PLAN; create user PLANNING identified by planning default tablespace DATA1 temporary tablespace TEMP quota unlimited on DATA1; grant connect to PLANNING; grant resource to PLANNING; create user ALLOCATION identified by allocation default tablespace DATA1 temporary tablespace TEMP quota unlimited on DATA1; grant connect to ALLOCATION; grant resource to ALLOCATION; create user SUPPLYPLAN identified by supplyplan default tablespace DATA1 temporary tablespace TEMP quota unlimited on DATA1; grant connect to SUPPLYPLAN; grant resource to SUPPLYPLAN; select * from dba_users where username='PLAN'; select * from dba_users where username='PLANNING'; select * from dba_users where username='ALLOCATION'; select * from dba_users where username='SUPPLYPLAN'; *******ASIA -- drop/recreate user schemas select * from global_name; select count(*) from dba_objects where owner='PLAN'; select count(*) from dba_objects where owner='PLANNING'; select count(*) from dba_objects where owner='ALLOCATION'; select count(*) from dba_objects where owner='SUPPLYPLAN'; select * from dba_users where username='PLAN'; select * from dba_users where username='PLANNING'; select * from dba_users where username='ALLOCATION'; select * from dba_users where username='SUPPLYPLAN'; drop user PLAN cascade; drop user PLANNING cascade; drop user ALLOCATION cascade; drop user SUPPLYPLAN cascade; select username from dba_users; Oracle8 Advanced Replication A Beginner’s Perspective

More Related