Oracle Error Ora-01631
SQL TuningSecurityOracle UNIXOracle LinuxMonitoringRemote supportRemote plansRemote servicesApplication Server ApplicationsOracle FormsOracle PortalApp UpgradesSQL ServerOracle ConceptsSoftware SupportRemote Support Development Implementation Consulting StaffConsulting PricesHelp Wanted! Oracle PostersOracle Books Oracle Scripts Ion Excel-DB Don Burleson Blog
ORA-01631 max # extents reached in table Oracle Database Tips by Burleson Consulting March 1, 2016 Question: What do I do to address the ORA-01631 error? ORA-01631: max # extents (4096) reached in table EMP Answer: Oracle supports date arithmetic and you can make expressions like "date1 - date2" to get the difference between the two dates. ORA-01631 max # extents (string) reached in table Cause: A table tried to extend past MAXEXTENTS. Action: If MAXEXTENTS is less than the system maximum, raise it. Otherwise, you must re-create with larger initial, next or PCTINCREASE parameters. The solution for the ORA-01631 is to issue an alter table command to set MEXEXTENTS unlimited: SQL> alter table EMP storage (maxextents unlimited); Oracle Training from Don Burleson The best on site "Oracle training classes" are just a phone call away! You can get personalized Oracle training by Donald Burleson, right at your shop! Burleson is the American Team Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals. Feel free to ask questions on our Oracle forum. Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications. Errata? Oracle technology is changing and we strive to update our BC Oracle support information. If you find an error or have a suggestion for improving our content, we would appreciate your feedback. Just e-mail: and include the URL for the page. Burleson Consulting The Oracle of Database Support Oracle Performance Tuning Remote DBA Services Copyright © 1996 - 2016 All rights reserved by Burleson Oracle is the registered trademark of Oracle Corporation. ��and SafetyAsset NetworkAsset Operations and MaintenanceCommerceOverviewSubscription Billing and Revenue ManagementMaster Data Management for CommerceOmnichannel CommerceFinanceOverviewAccounting and Financial CloseCollaborative Finance OperationsEnterprise Risk and ComplianceFinancial Planning and AnalysisTreasury and Financial Risk ManagementHuman ResourcesOverviewCore Human Resources and PayrollHuman Capital AnalyticsTalent ManagementTime and Attendance ManagementManufacturingOverviewManufacturing NetworkManufacturing OperationsResponsive http://www.dba-oracle.com/t_ora_01631_max_number_extents_reached.htm ManufacturingMarketingOverviewMarket with Speed and AgilityUnique Customer ExperiencesReal-Time Customer InsightsR&D/EngineeringOverviewDesign NetworkDesign OrchestrationProject and Portfolio ManagementSalesOverviewCollaborative Quote to CashSales Force AutomationSales Performance ManagementSelling Through Contact CentersServiceOverviewEfficient Field Service ManagementOmnichannel Customer ServiceTransparent Service Process and OperationsSourcing and ProcurementOverviewContingent Workforce ManagementDirect https://scn.sap.com/thread/1471985 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 TechnologyAnalyticsOverviewBusiness IntelligencePredictive AnalyticsGovernance, Risk, and ComplianceEnterprise Performance ManagementApplication Platform and InfrastructureOverviewApplication PlatformContent and CollaborationInterface ManagementProcess Management and IntegrationData ManagementOverviewBig Data on HadoopData WarehousingDatabase ManagementEnterprise Information ManagementIT ManagementOverviewApplication Lifecycle ManagementIT Infrastructure ManagementSecurity SoftwareOverviewApplication and IT Infrastructure SecurityIdentity, Access and Authentication ManagementBrowse by ProductAnalyticsContent and CollaborationCustomer Relationship ManagementData ManagementEnterprise Ma
am UTC Answered by: Tom Kyte � Last updated: March 20, 2007 - 11:14 am UTC Category: Database � Version: 8.1.7 Whilst you are here, check out some content from the https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1142278661072 AskTom team: On Injecting and Comparing Latest Followup You Asked Tom I give http://harvarinder.blogspot.com/2012/06/ora-01631-max-extents-121-reached-in.html a query select * from emp; all the data blocks belong to the emp table are read into the buffer cache. Now once again I give a select like select ename from emp where empno=1010, this time the blocks are not fetched from the disk , but from the disk but from oracle error the buffer cache. How did oracle decide or know that the block is in the buffer cache and not on the disk. Secondly, i.I heard a vague statement from my lead , 'that everything in oracle database is read through the rollback segment except a select', what does that mean. ii.At what stage are records copied to rollback when doing a delete or update. when data oracle error ora-01631 is committed , what happens to the data blocks in the rollback which contained the said data, or are they wiped out, and wait for other update and delete statements. iii.A user has given an update statement against a table and has not committed, now we have the data in the data blocks of that respective table i.e. segment, and also in the rollback data blocks. Now another user comes in and issues a select against the same table, where is the data read from, from the data blocks of the table segment or the data blocks of the rollback segment. thridly what is the reason for SQL> INSERT INTO t SELECT * FROM t1; ERROR at line 1: ORA-01631: max # extents (505) reached in table PLAY.T and how should this be fixed, especially what is the syntax to fix it. and we said... for the IO question, see http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:557622939598 i) thats seems backwards. A select will USE a rollback segment IF and WHEN needed to reconstruct a block as it appeared when the query begin (this is multi-versioning and read consistency). See http://download-east.oracle.com/docs/cd/A81042_01/DOC/server.816/a76965/c23cnsis.htm#17882 it is NOT true that everything is
max # extents (121) reached in table HARVEY.MLOG$_MV_HARVEY ORA-02063: preceding 2 lines from DB_LINK_DATA I was getting the error message when trying to insert a record using database link ofDB_LINK_DATA. This issue was resolved by updating the max_extents to unlimited on the target database (underDB_LINK_DATA). This was done by running the following command on the database underDB_LINK_DATA SQL> select OWNER, TABLE_NAME, TABLESPACE_NAME, PCT_FREE,PCT_USED,STATUS, MIN_EXTENTS, MAX_EXTENTS,NEXT_EXTENT from dba_tables where table_name ='MLOG$_ALL_OBJECT_ROLES'; OWNER TABLE_NAME TABLE PCT_FREE PCT_USED STATUS MIN_EXTENTS MAX_EXTENTS NEXT_EXTENT ---------- ------------------------------ ----- ---------- ---------- -------- ----------- ----------- ----------- HARVEY MLOG$_MV_HARVEY HARVEY 60 30 VALID 1 121 131072 alter table HARVEY.MLOG$_MV_HARVEY storage(maxextents unlimited); SQL> select OWNER, TABLE_NAME, TABLESPACE_NAME, PCT_FREE,PCT_USED,STATUS, MIN_EXTENTS, MAX_EXTENTS,NEXT_EXTENT from dba_tables where table_name ='MLOG$_ALL_OBJECT_ROLES'; OWNER TABLE_NAME TABLE PCT_FREE PCT_USED STATUS MIN_EXTENTS MAX_EXTENTS NEXT_EXTENT ---------- ------------------------------ ----- ---------- ---------- -------- ----------- ----------- ----------- HARVEY MLOG$_MV_HARVEY HARVEY 60 30 VALID 1 2147483645 131072 Posted by Harvey Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest Reactions: No comments: Post a Comment Newer Post Older Post Home Subscribe to: Post Comments (Atom) Android App App development is my hobby and I have created an app to keep track of personal fever, medicine and any notes. This android app can be downloaded by clicking the link below: https://play.google.com/store/apps/details?id=com.appowl247.fever Visitor Number Blog Archive ► 2016 (44) ► October (11) ► September (6) ► August (1) ► June (4) ► May (2) ► April (11) ► February (4) ► January (5) ► 2015 (11) ► August (1) ► January (10) ► 2014 (35) ► December (6) ► June (6) ► May (1) ► April (4) ► March (6) ► February (12) ► 2013 (12) ► August (2) ► July (1) ► June (1) ► April (2) ► March (1) ► February (4) ► January (1) ▼ 2012 (11) ► July (7) ▼ June (3) ORA-01631: max # extents (121) reached in table Adding temp datafile to default temp tablepsace Connection to the repository failed. Verify that t... ► January (1)