oracle,oracle11g,tablespace , Oracle Tablespaces maxsize “unlimited” not really unlimited


Oracle Tablespaces maxsize “unlimited” not really unlimited

Question:

Tag: oracle,oracle11g,tablespace

I recently needed to import a .dmp into a new user I created. I also created a new tablespace for the the user with the following command:

create tablespace my_tablespace
 datafile 'C:\My\Oracle\Install\DataFile01.dbf' size 10M
 autoextend on
 next 512K
 maxsize unlimited;

While the import was running I got an error:

ORA-01652 Unable to extend my_tablespace segment by in tablespace

When I examined the data files in the dba_data_files table, I observed the maxsize was around 34gb. Because I knew the general size of the database, I was able to import the .dmp without any issues after adding multiple datafiles to the tablespace.

Why did I need to add multiple datafiles to the tablespace when the first one I added was set to automatically grow to an unlimited size? Why was the maximum size 34gb and not unlimited? Is there a hard cap of 34gb?


Answer:

As you've discovered, and as Alex Poole pointed out, there are limits to an individual data file size. Smallfiles are limited to 128GB and bigfiles are limited to 128TB, depending on your block size. (But you do not want to change your block size just to increase those limits.) The size limit in the create tablespace command is only there if you want to further limit the size.

This can be a bit confusing. You probably don't care about managing files and want it to "just work". Managing database storage is always gonna be annoying, but here are some things you can do:

  1. Keep your tablespaces to a minimum. There are some rare cases where it's helpful to partition data into lots of small tablespaces. But those rare benefits are usually outnumbered by the pain you will experience managing all those objects.
  2. Get in the habit of always adding more than one data file. If you're using ASM (which I wouldn't recommend if this is a local instance), then there is almost no reason not to go "crazy" when adding datafiles. Even if you're not using ASM you should still go a little crazy. As long as you set the original size to low, you're not close to the MAX_FILES limit, and you're not dealing with one of the special tablespaces like UNDO and TEMP, there is no penalty for adding more files. Don't worry too much about allocating more potential space than your hard-drive contains. This drives some DBAs crazy, but you have to weigh the chance of running out of OS space versus the chance of running out of space in a hundred files. (In either case, your application will crash.)
  3. Set the RESUMABLE_TIMEOUT parameter. Then SQL statements will be suspended, may generate an alert, will be listed in DBA_RESUMABLE, and will wait patiently for more space. This is very useful in data warehouses.

Why is it called "UNLIMITED"?

I would guess the keyword UNLIMITED is a historical mistake. Oracle has had the same file size limitation since at least version 7, and perhaps earlier. Oracle 7 was released in 1992, when a 1GB hard drive cost $1995. Maybe every operating system at the time had a file size limitation lower than that. Perhaps it was reasonable back then to think of 128GB as "unlimited".


Related:


sql script to find index's tablespace_name only


sql,database,oracle
Trying to find the specific tablespace names that were created for indexes only. I don't want to see the tablespaces names that are already used for tables. Something like below, but i couldn't have it working due to syntax error. Can someone fix it? select tablespace_name from dba_indexes where tablespace_name...

Get unmatched records without using oracle minus except not in


oracle,plsql,inner-join,outer-join
Actually I have two table and each having column name, I just want the result which are not there in Table2 Table1 ---- Name --- |A| |B| |C| |D| Table2 ------ |Name| ----- |A| |B| Answer |C| |D| I am able to do it by using minus select name from...

Why does .Where() with a Func parameter executes the query?


c#,oracle,linq,entity-framework
Here's how my DataAccessLayer works : public Foo GetFooBy(Func<Foo, bool> filter) { var query = from item in this.DataService.FooSet select item; var where = query.Where(filter); var first = where.First(); return first; } I assumed the query would be run when First() is called but it's actually executed by the Where()....

ASP.net website is not working properly while configuring in Internet Information Services


c#,asp.net,oracle11g,iis-7
I have created small webpage in ASP.net using c# in front end and oracle 11g at back end. Webpage is running successfully on my local pc as well as in windows server 2008 r/2 in visual studio 2010. So, i have configured same wabpage in IIS in server 2008 r/2...

PLSQL - Error in associative array


oracle,plsql,associative-array
Im trying to delete a set of tables and afterwards I want to recreate them using as select from. For couriousity I wanted to do this with an associative array. Unfortunately something is messed up, several errors appear and I can't find the reasons. This is the code: DECLARE TYPE...

SQL*Loader Control File Custom Date Format


oracle,toad,sql-loader
I need to import from a CSV file in which timestamps are given in the following format 2014-06-14T09:38:29 I tried the following in the control file for SQL*Loader but it doesn't work TIME DATE "YYYY-MM-DDTHH:MI:SS" and TIME DATE "YYYY-MM-DDTHH24:MI:SS" How can I parse this custom date? The error I get...

Cant delete in database because of constraints


c#,sql,asp.net,oracle
I am making an asp.net project and I am trying to delete a row in my database but that table got constraints. I am using this method to delete it.How can I rewrite it so that all constraints are disabled or something in order that i can properly delete a...

Why does the date doesn't match with what I have inserted into the database?


sql,database,oracle
I tried to display the sqlcommand after I saved into the database. Everything seemed to work pretty fine, but when I opened my table from TOAD, the dates are wrong. Here is my sql command: INSERT INTO USERTASK (USERTASKKEY, USERID, TASKKEY, TASKDATE, CREATEDATE, CREATEUSERID) VALUES (USERTASKSEQUENCE.NEXTVAL, 'admin2', '1', TO_DATE('05-06-2015','yyyy/mm/dd HH24:MI:SS'),...

How to derive years of service for employees that have termed and returned several times


sql,oracle,oracle11g
I'm working with Oracle and have sample data to show you regarding my question (sample data is below). I'm trying to figure out how to derive years of service for employees that have terminated and returned several times (up to 6 times). The business rules are that if someone leaves...

'ORA-00942: table or view does not exist' only when running within a Stored procedure


oracle,plsql
This should be easy pickin's for a PL-SQL person. Before you mark this question a duplicate, please ensure that while the error message may be common that the underlying problem is the same as a previous question. If so, please provide a link to the exact logical duplicate question that...

How to join 2 tables with select and count in single query


sql,oracle,left-join
I need to join 2 tables (Person and PersonLine). The result should contain id and name column from Person table and count of personlineid column from PersonLine Table for each id. But sql query returns count of all personlineid. Can anyone help to form the sql. Person: ID NAME AGE...

Changing Primary Key in Oracle


oracle,django-models,oracle-sqldeveloper
I'm updating a table that was originally poorly designed. The table currently has a primary key that is the name of the vendor. This serves as a foreign key to many other tables. This has led to issues with the Vendor name initially being entered incorrectly or with typos that...

oracle sql error Case When Then Else


sql,oracle,oracle11g
SELECT * FROM FirstTable WHERE RowProcessed = 'N' AND ( CASE WHEN EXISTS(SELECT top 1 FROM SecondTable) THEN 1 ELSE EXISTS( SELECT SecondTable.RowProcessed FROM SecondTable WHERE FirstTable.Key = SecondTable.Key AND SecondTable.RowProcessed = 'Y' ) END ) AND OtherConditions Case When then else in where clause. Not sure about the syntax....

Trying to access Oracle's Maven repository


oracle,maven,repository
I'm attempting to access Oracle's repository. Oracle doesn't make it easy. However, I'm attempting to follow the documentation that Oracle provided. I've: Approved the licensing agreement on my system (in case there's some sort of cookie that needs to be set). Set both M2_HOME and MAVEN_HOME to /usr/share/apache-maven. Created an...

SQL Oracle | How to delete records from a table when they match another table?


sql,oracle,delete
How would I delete records from a table where they match a delete table? As in, I have a table of record keys that say what need to be deleted from my main table. How would I write a delete to say "delete anything from my main table where this...

Column ambiguously defined error with Oracle Merge statement


sql,oracle
I have a problem with this query in Oracle SQL. Errore alla riga del comando:18 Colonna:42 Report errori: Errore SQL: ORA-00918: column ambiguously defined 00918. 00000 - "column ambiguously defined" *Cause: *Action: This is the query, but i don't see the column ambiguously defined: MERGE INTO T_HPSM_CM_UBIS H USING (SELECT...

Notice: Array to string conversion in “path of php file” on line 64


php,mysql,arrays,oracle
PHP CODE -: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Untitled Document</title> </head> <body> <?php $rows =0; $fp = fopen("leave1.csv","r"); if($fp){ while(!feof($fp)){ $content = fgets($fp); if($content) $rows++; } } fclose($fp); //echo $rows; $_SESSION['rows'] = $rows; ?>...

Calculating overlap between groups


sql,oracle,oracle11g,pivot
I have a table with two columns of interest, item_id and bucket_id. There are a fixed number of values for bucket_id and I'm okay with listing them out if I need to. Each item_id can appear multiple times, but each occurrence will have a separate bucket_id value. For example, the...

like and regexp_like


sql,regex,oracle,oracle11g,regexp-like
For like we have %. for ex: if we give ad% it ll fetch all the records which starts with ad but i should use regexp_like. for regexp_like what can be used so that it acts as % for like. i cant use ^ad because from UI we ll give...

update a table from another table using oracle db


sql,oracle
I have to update one table from another one: I can do the update with the MySQL sgbd: update product pr , provider p set pr.provider_name = p.name where p.provider_id = pr.provider_id ; but when I try to do it with oracle : I tried this query for oracle UPDATE...

Extracting XML data from CLOB


sql,xml,oracle
How can I extract Food ItemID and Food Item Name and Quantity from the data as mentioned below. This is in clob column in plsql. <ServiceDetails> <FoodItemDetails> <FoodItem FoodItemID="6486" FoodItemName="CARROT" Quantity="2" Comments="" ServingQuantityID="142" ServingQuantityName="SMALL GLASS" FoodItemPrice="50" ItemDishPriceID="5336" CurrencyName="INR" Currency Id="43"/> </FoodItemDetails> <BillOption> <Bill Details Total Price="22222" BillOption="cash"/> </BillOption> <Authoritativeness/>...

Can't obtain connection with the DB due to very long schema validation and connection reset afterwards


java,oracle,hibernate
I have an app where I use Hibernate/Oracle 11g XE/Jboss6.2 and I am having trouble starting up the application on my homolog server at DigitalOcean (Jboss and Oracle xe installed locally). Running everything on my dev machine I have no problem at all and it starts in about 30s, but...

Oracle SQL - Returning the count from a delimited field


oracle
I'm fairly inexperienced with SQL so hopefully this question is not too silly. Here is the scenario: I have a VARCHAR2 column that stores a series of values delimited by product. Depending on on the account, they can have one or multiple products. I'm trying to write a query that...

Oracle 11g Insert Statement into Multiple Tables


sql,oracle,oracle11g,triggers,sql-insert
I am currently having trouble trying to execute multiple statements at the same time. I keep getting this error when trying to run the following INSERT statements: INSERT INTO report_header ( report_number, company_id, user_id, entry_date) VALUES ( 6797, 15967, 84, TRUNC(SYSDATE)); INSERT INTO report_detail (part_id, condition_id, uom_id, dvc_id, cqh_id, alt_part_id,...

Exclude / ignore weekends in Oracle SQL


sql,oracle
I made a query that groups text together and their start and end date, i want to exclude/ignore the weekends. In this example 11-12 and 18-19 april are the weekends create table t ( d date, v varchar2(10)); insert into t values (date '2015-04-10', 'ne'); insert into t values (date...

Order By Two Columns Simultaneously SQL


mysql,sql-server,oracle11g
I have a SQL question about how my query output is being ordered. Here is how the output looks now: REFERRED_FROM REFERRED_FROM_COUNT REFERRED_TO REFERRED_TO_COUNT sample sample number sample sample number sample sample number sample sample number sample sample number sample sample number And here is my desired output: REFERRED_FROM REFERRED_FROM_COUNT...

How to pull the date in proper format from timestamp


oracle,timestamp
I am working in Oracle SQL Developer and have the following code SELECT Trunc(assigned_date, 'IW') AS bonus_week FROM (SELECT Trunc(blf.assigned_date) AS assigned_date FROM olap.bonus_lifecycle_fact blf) I get output in this form 07-12-03 08-01-28 08-01-28 08-01-28 08-01-07 Which is DD-MM-YY That's fine, except when I export to Excel I get some...

Get only Oracle function return table's columns and their types


c#,oracle
Is there an oracle query which I can use to get back the details of an oracle function which returns a table, where I'm looking for the info of that table, say the record name, but mostly the columns in that record and their types. Example function: create or replace...

Find any character occur more than 4 times


sql,regex,oracle
I want to find any character occurs between 4 and 10 times, I used REGEXP_LIKE but it's valid just for one character 'a' , I want to find for all alphabet: SELECT regex_test_name FROM regex_test WHERE REGEXP_LIKE(regex_test_name, 'a{4,10}') ...

Entity Framework code-first: querying a view with no primary key


sql,oracle,entity-framework,view,ef-code-first
Our customer has given the access to views in which there is no primary key is defined. I know Entity Framework needs a primary key for table to identify. But for views not having primary key is it still possible to query. I try to find but always Entity Framework...

Dealing with nulls when selecting in MyBatis


java,oracle,mybatis
I have a query which at first launch returns zero rows: <select id="getParams" parameterType="map" resultMap="ParamsResultMap"> SELECT ID, GROUPS, TEMPLATES, DATE_FROM, DATE_TO FROM MY_FILTERS WHERE ID = #{id} </select> <resultMap id="ParamsResultMap" type="my.domain.ParamsVO"> <result column="ID" property="id"/> <result column="GROUPS" property="groups"/> <result column="TEMPLATES" property="templates"/> <result column="DATE_FROM" property="dateFrom" jdbcType="TIMESTAMP"/> <result...

Trigger to find next available inventory location


oracle,triggers,inventory
I am trying to implement inventory tracking and am running into problems. As this is my first foray into database triggers (& PL/SQL in general) I think I need an adjustment to my thinking/understanding of how to solve this issue. My situation is as follows: Each time a new item...

Result from pipelined function, always will sorted as “written”, or not?


sql,oracle,plsql,oracle12c
Needed get renumbered result set, for example: CREATE OR REPLACE TYPE nums_list IS TABLE OF NUMBER; CREATE OR REPLACE FUNCTION generate_series(from_n INTEGER, to_n INTEGER, cycle_max INTEGER) RETURN nums_list PIPELINED AS cycle_iteration INTEGER := from_n; BEGIN FOR i IN from_n..to_n LOOP PIPE ROW( cycle_iteration ); cycle_iteration := cycle_iteration + 1; IF...

Why I can't compare dates?


sql,oracle,oracle10g
I have this simple query: select sm.id, h.time from main_data sm INNER JOIN TA t on t.id = sm.id AND t.first=1 LEFT OUTER JOIN History h on h.id= sm.id WHERE trunc(TO_DATE(h.time, 'DD-MM-YYYY')) BETWEEN trunc(TO_DATE('07.05.2015', 'DD.MM.YYYY')) AND trunc(TO_DATE('07.06.2015', 'DD.MM.YYYY')); h.time looks like 07-MAY-15 The above query returns no results, even if...

Fill with zero to complete a defined number in sql [closed]


sql,oracle
I need to complete cards numbers in sql. I have the prefix =11111 and the number of the card which is variable, therefore it could be '25' or '2130' but at the end I must have 14 numbers. So I need to fill spaces with zeros. I've read about 'LPAD'...

SQL Developer does not connect with SID as defined in tnsnames.ora


oracle,oracle-sqldeveloper
My tnsnames.ora looks like this: ORACLR_CONNECTION_DATA = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) (CONNECT_DATA = (SID = CLRExtProc) (PRESENTATION = RO) ) ) ORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = orcl)...

Difference between dba_SEGMENTS and dba_data_files


mysql,sql,database,oracle11g,oracle-sqldeveloper
Shouldn't these two queries give the same result? First is a sum of all segments while other is the sum of all data files. They are giving different results for me. While the first one gives me 149522MB, the other gives 214973.34 MB select sum(bytes)/1024/1024 MB from dba_segments; select sum(bytes)/1024/1024...

Optimizer using an index not present in the current schema


oracle,indexing,optimizer
CONNECT alll/all SELECT /*+ FIRST_ROWS(25) */ employee_id, department_id FROM hr.employees WHERE department_id > 50; Execution Plan Plan hash value: 2056577954 | Id | Operation | Name | Rows | Bytes | | 0 | SELECT STATEMENT | | 25 | 200 | 1 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES...

Identifier is too long


sql,oracle
Please help me to find why am getting identifier is too long error , even after having enough space for role in record. Exception : Error report - ORA-06550: line 14, column 24: PLS-00114: identifier 'ou=internal,ou=users,dc=chinas' too long ORA-06550: line 18, column 24: PLS-00114: identifier 'ou=internal,ou=users,dc=chinas' too long 06550. 00000...

Using MyBatis Update with foreach


sql,oracle,mybatis
i am struggling with update statement in MyBatis. I want to put multiple strings in one row: UPDATE MY_FILTERS SET GROUPS = <foreach item="item" collection="selectedGroups" open="" separator="," close=""> #{item} </foreach>, TEMPLATES = <foreach item="item" collection="selectedTemplates" open="" separator="," close=""> #{item} </foreach> where ID = #{id} I've tried the following alternatives: open...

SQL Oracle | How would I select a substring where it begins with a certain letter and ends with a certain symbol?


sql,oracle,select,substring
If I had this: NAME EYES==ID==HAIR Jon Brown==F9182==Red May Blue==F10100==Brown Bill Hazel/Green==F123==Brown ...and I wanted to create a new ID column with the ID alone, and I know that everyone's ID starts with an 'F' and will end at the '=' how would I select a substring from the compact...

How to use subquery result as the column name of another query


sql,oracle,plsql
I want to use the result from subquery as the column name of another query since the data changes column all the time and the subquery will decide which column the current forcast data stored. My example: select item, item_type ... forcast_0 * 0.9 as finalforcast forcast_0 * 0.8 as...

How to catch Oracle exception “ORA-06535: statement string in OPEN is NULL or 0 length”?


oracle,plsql
I would like to catch specific exception for exception "ORA-06535: statement string in OPEN is NULL or 0 length" But couldn't figure out the exact "exception name" for it. Please note I don't want to catch it under generic other exceptions block.. EXCEPTION WHEN <exception_name1> THEN executable_statements; WHEN <exception_nameN> THEN...

SQL Error: ORA-00933: SQL command not properly ended in Oracle Update query


sql,oracle,sql-update
I have SYSTEM_SQL_CHECK table in which i have saved sql in CHECK_SQL column. This column is Varchar data type. Now i want to update particular sql.I have written below update sql query but it gives an error SQL Error: ORA-00933: SQL command not properly ended. I also tried to query...

Groovy - timestamp from minutes


oracle,grails,groovy,timestamp
I have an array or times/values coming back to be in an array like: [0, 60] Which are times in minutes, 0 = 12:00 a.m, 60 = 1:00 a.m. I am wanting to store these in an oracle database as timestamps. How do I convert minutes into timestamps in groovy?...