OracleStorage
Add space: resize or add a datafile
Templates for the three usual fixes. The resize runs as written; the other two are commented out. Change names and sizes, and uncomment the one you want instead if needed.
Not yet verified. How scripts are tested
1-- Grow an existing file2ALTER DATABASE DATAFILE '/u02/oradata/ORCL/users01.dbf' RESIZE 20G;3 4-- Or add a new file (file system)5-- ALTER TABLESPACE users ADD DATAFILE '/u02/oradata/ORCL/users02.dbf'6-- SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32G;7 8-- Or add a new file (ASM or Oracle Managed Files)9-- ALTER TABLESPACE users ADD DATAFILE SIZE 10G AUTOEXTEND ON NEXT 1G MAXSIZE 32G;Save it as ora-add-space.sql and run it with SQL> @ora-add-space.
Open in denrepoRelated tool: Tablespace runway and datafiles
Helps with
- ORA-01652: unable to extend temp segment by %s in tablespace %s
- ORA-01653: unable to extend table %s.%s by %s in tablespace %s
- ORA-01654: unable to extend index %s.%s by %s in tablespace %s
- ORA-01688: unable to extend table %s.%s partition %s by %s in tablespace %s
- ORA-01691: unable to extend lob segment %s.%s by %s in tablespace %s
- ORA-30036: unable to extend segment by %s in undo tablespace '%s'
More Oracle scripts: Storage
- Datafile usage with a bar graphEvery datafile with its size, used space, max size and a ten-character usage bar (X = used, - = free). Free_MB is the largest free extent in the…
- Database size three ways: allocated, used and totalAllocated datafile size, space actually used by segments, and the overall footprint including temp files, online redo logs and control files. All in…
- Database size summary: total, used and freeOne line with the database name and its total size (datafiles, temp files and redo logs), used space and free space, rounded to whole GB.
- Size of one schemaTotal segment size in GB for the schema you enter.
- Tablespace usageUsed and free space in GB against the maximum each tablespace can autoextend to, with its type (permanent, temporary or undo), fullest first.
- Datafiles with size and autoextend limitsEvery datafile, its current size, whether it can grow, and how far. Files with AUTOEXTEND NO in a busy tablespace are the ones that page you at night.