Ora-1552 Error
Contents |
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development
Ora-01552: Cannot Use System Rollback Segment For Non-system Tablespace
Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle ora-01552 solution Books Oracle Scripts Ion Excel-DB Don Burleson Blog
Cannot Use System Rollback Segment For Non-system Tablespace Psapsr3
ORA-01552: cannot use system rollback segment for non-system tablespace "string" tips Oracle Error Tips by Burleson undo_management=auto Consulting (S. Karam) The Oracle docs note this on the ORA-01552 error: ORA-01552: cannot use system rollback segment for non-system tablespace "string" Cause: Tried to use the system rollback segment for operations involving non-system tablespace. If this is a clone database then create undo tablespace this will happen when attempting any data modification outside of the system tablespace. Only the system rollback segment can be online in a clone database. Action: Create one or more private/public segment(s), shutdown and then startup again. May need to modify the INIT.ORA parameter rollback_segments to acquire private rollback segment. If this is a clone database being used for tablspace point in time recovery then this operation is not allowed. If the non-system tablespace has AUTO segment space management, then create an undo tablespace. On Oracle DBA Forums , user Chris_Pena encountered ORA-01552 while using this SQL statement: SQL> select segment_name, status from dba_rollback_segs; SEGMENT_NAME STATUS ------------------------------ ---------------- SYSTEM ONLINE _SYSSMU1$ OFFLINE _SYSSMU2$ OFFLINE _SYSSMU3$ OFFLINE _SYSSMU4$ OFFLINE _SYSSMU5$ OFFLINE _SYSSMU6$ OFFLINE _SYSSMU7$ OFFLINE _SYSSMU8$ OFFLINE _SYSSMU9$ OFFLINE _SYSSMU10$ OFFLINE As a response, Burleson reminded
and SafetyAsset NetworkAsset Operations and 01552 zip code MaintenanceCommerceOverviewSubscription Billing and Revenue ManagementMaster Data Management for CommerceOmnichannel CommerceFinanceOverviewAccounting and Financial CloseCollaborative Finance OperationsEnterprise Risk and ComplianceFinancial Planning and AnalysisTreasury and Financial http://www.dba-oracle.com/t_ora_01552_cannot_use_system_rollback_segment_for_non_system_tablespace_string.htm Risk ManagementHuman ResourcesOverviewCore Human Resources and PayrollHuman Capital AnalyticsTalent ManagementTime and Attendance ManagementManufacturingOverviewManufacturing NetworkManufacturing OperationsResponsive ManufacturingMarketingOverviewMarket with Speed and AgilityUnique Customer ExperiencesReal-Time Customer InsightsR&D/EngineeringOverviewDesign NetworkDesign OrchestrationProject and Portfolio ManagementSalesOverviewCollaborative https://scn.sap.com/thread/1895382 Quote to CashSales Force AutomationSales Performance ManagementSelling Through Contact CentersServiceOverviewEfficient Field Service ManagementOmnichannel Customer ServiceTransparent Service Process and OperationsSourcing and ProcurementOverviewContingent Workforce ManagementDirect ProcurementSelf-Service ProcurementServices ProcurementStrategic Sourcing and Supplier ManagementSupply ChainOverviewDemand ManagementDemand NetworkLogistics NetworkManufacturing Planning and SchedulingResponse NetworkResponse and Supply ManagementSales, Inventory, and Operations PlanningTransportation ManagementWarehouse ManagementSustainabilityOverviewEnvironment, Health, and SafetyProduct Safety and Stewardship NetworkBrowse by IndustryAerospace and DefenseAutomotiveBankingChemicalsConsumer ProductsDefense and SecurityEngineering, Construction, and OperationsHealthcareHigh TechHigher Education and ResearchIndustrial Machinery and ComponentsInsuranceLife SciencesMediaMill ProductsMiningOil and GasProfessional ServicesPublic SectorRetailSports and EntertainmentTelecommunicationsTravel and TransportationUtilitiesWholesale DistributionBrowse Platform and T
importing a dump file to oracle 9.2.0.7 database. The problem was about "ORA-01552: cannot use system rollback segment for non-system tablespace". I investigated http://ora-exp.blogspot.com/2007/03/solution-of-cannot-use-system-rollback.html the problem and find a solution... SQL> show parameter undo_management; NAME TYPE VALUE --------------- ---------- --------- undo_management string MANUAL SQL> show parameter undo_tablespace; NAME TYPE VALUE --------------- ---------- --------- undo_tablespace string SQL> alter system set undo_management=AUTO scope=spfile System altered. SQL> alter system set undo_tablespace=UNDOTBS1 scope=spfile; System altered. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. cannot use SQL> startup ORACLE instance started. Total System Global Area 253202360 bytes Fixed Size 733112 bytes Variable Size 218103808 bytes Database Buffers 33554432 bytes Redo Buffers 811008 bytes Database mounted. Database opened. SQL> show parameter undo_management; NAME TYPE VALUE --------------- ---------- --------- undo_management string AUTO SQL> show parameter undo_tablespace; NAME TYPE VALUE --------------- ---------- --------- undo_tablespace string UNDOTBS1 Posted cannot use system by Mennan Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest Labels: administration, oracle errors 4 comments: parash prasad said... great solution, it works thanks a lot. 16/9/15 09:55 Unknown said... thanks so much!!!!! 15/3/16 12:34 Unknown said... thanks so much! 15/3/16 12:35 Anonymous said... great solution.... working for me thnks 18/7/16 11:09 Post a Comment Newer Post Older Post Home Subscribe to: Post Comments (Atom) Quick Find History ► 2013 (12) ► August (2) ► April (1) ► March (7) ► January (2) ► 2011 (23) ► December (3) ► November (11) ► October (3) ► September (6) ► 2009 (2) ► November (2) ► 2008 (8) ► September (5) ► January (3) ▼ 2007 (36) ► December (3) ► October (2) ► September (1) ► July (6) ► June (7) ► April (4) ▼ March (8) Transferring(Copy, Clone) Files(Binary, Text etc.)... Oracle Directory Objects and Solution of ORA-29280... Sequence Generator In Oracle Database Management S... Establishing Source Code Security With WRAP In Ora... Copy Table Data Across Databases/Schemas In Oracle... Solution of