Add data files for Oracle tablespaces or temporary tablespaces

Riveore 2021-11-25 18:37:27
add data files oracle tablespaces

background :
When passed ORACLE Medium create table ... as select Statement to create a new table , The data volume of the new table is relatively large , Such as 10 Billion , At this time SQL*Plus It's likely to prompt “ORA-01653: ...” error message . This error message indicates that the table space is not large enough , You need to add data files to the tablespace .

analysis :
1. Size of remaining bytes in query table space
SELECT TABLESPACE_NAME, SUM(BYTES)/1024/1024 AS "FREE SPACE(M)"
FROM DBA_FREE_SPACE
WHERE TABLESPACE_NAME = '&tablespace_name'
GROUP BY TABLESPACE_NAME;
notes : If it's a temporary tablespace , Please check DBA_TEMP_FREE_SPACE
SELECT TABLESPACE_NAME, FREE_SPACE/1024/1024 AS "FREE SPACE(M)"
FROM DBA_TEMP_FREE_SPACE
WHERE TABLESPACE_NAME = '&tablespace_name';

2. If you don't know the table space data file directory planning , You can query all data files in the tablespace first
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "BYTES(M)"
FROM DBA_DATA_FILES
WHERE TABLESPACE_NAME = '&tablespace_name';

notes : If it's a temporary tablespace , Please check DBA_TEMP_FILES
SELECT TABLESPACE_NAME, FILE_ID, FILE_NAME, BYTES/1024/1024 AS "SPACE(M)"
FROM DBA_TEMP_FILES
WHERE TABLESPACE_NAME = '&tablespace_name';

3. Add data files for table spaces with insufficient space
ALTER TABLESPACE &tablespace_name ADD DATAFILE '&datafile_name' SIZE 2G;
notes : If you want to expand the temporary tablespace , Use the following statement
ALTER TABLESPACE &tablespace_name ADD TEMPFILE '&datafile_name' SIZE 2G;

版权声明
本文为[Riveore]所创,转载请带上原文链接,感谢
https://javamana.com/2021/11/20211109102332520C.html

  1. Flink Handling Function Real War II: processfunction class, Java thread interview subject
  2. Oracle SQL injection summary
  3. [Java data structure] you must master the classic example of linked list interview (with super detailed illustration and code)
  4. Record a java reference passing problem
  5. spring JTA 關於异常處理的時機問題
  6. Java - Set - Map (double file) - dija Rewriting, 2021 Java Developer's Performance Optimization
  7. Android入门教程 | OkHttp + Retrofit 取消请求的方法
  8. Java 8 Stream API and common methods, Java Junior Program interview
  9. Github 疯传!史上最强!BAT 大佬,2021年最新Java大厂面试笔试题分享
  10. git(3)Git 分支,zookeeper下载教程
  11. Java Backend Internet 500 questions d'entrevue moyennes et avancées (y compris les réponses), technologie de crochet Linux
  12. Entretien d'entretien d'usine Java post sprint de 100 jours - accumulation de jours et de mois, trois questions par jour [jour 12, fonction de principe de Zookeeper
  13. Tutoriel Java - reflection, tutoriel de téléchargement mongodb
  14. How to analyze several common key and hot issues in redis from multiple dimensions
  15. GIT (3) GIT Branch, Zookeeper Download tutoriel
  16. Tutoriel de démarrage Android | okhttp + Retrofit comment annuler une demande
  17. Design pattern [3.3] - Interpretation of cglib dynamic agent source code
  18. Share the actual operation of private collection project nodejs backend + Vue + Mysql to build a management system
  19. Springboot has 44 application initiators
  20. GitHub上霸榜久居不下的《Java面试突击宝典》,java图形用户界面设计基础
  21. GitHub上访问下载破百万的神仙文档《Java面试神技》看完我呆了,java面试问项目中遇到的问题
  22. GitHub上标星75k 超牛的《Java面试突击版,java高级工程师技能
  23. GitHub上标星2,java项目开发实训教程
  24. Docker development environment Preview
  25. JavaScript高級深入淺出:掌握 this 指向
  26. JavaScript Advanced Insight and outside: Mastering this direction
  27. Vue de l'application pratique de Javascript, drop drag Event
  28. docker 安装部署 Jenkins 2.322
  29. kafka安装
  30. 近九万字图文详解RabbitMQ
  31. Engaged in Java for one and a half years, how to break through yourself
  32. 输出9*9乘法表----java
  33. 判断一个数是不是素数-------java
  34. java项目,记录页面修改值,内部打“官司”用
  35. Docker installation Deployment Jenkins 2.322
  36. Comment porter un pantalon en hiver? Les petits hommes, les jambes épaisses et la largeur de l'entrejambe peuvent être vus. 3 techniques pour éviter la foudre
  37. MySQL下载和安装教程
  38. In depth analysis of rocketmq source code - message storage module
  39. Spring transaction management
  40. mysql恢复ibd数据,为何频频报错?
  41. [skills with annual salary of 60W] after working for 5 years, do you really understand netty and why to use it? (deep dry goods)
  42. Pourquoi MySQL récupère - t - il fréquemment les données ibd?
  43. Tutoriels de téléchargement et d'installation MySQL
  44. In IntelliJ idea, develop a fishing and reading plug-in
  45. Talk about how to integrate SPI with spring
  46. 重学Spring系列之Swagger2.0和Swagger3.0
  47. Hadoop05【命令操作】,Java400道面试题通关宝典助你进大厂
  48. Guava Cache缓存设计原理,java基础入门
  49. Gson:GitHub 标星 18K 的 JSON 解析器,Google 出品的 Java JSON 解析器
  50. gRPC学习之六:gRPC-Gateway集成swagger,java语言程序设计与数据结构进阶版
  51. Projet Java, enregistrer la valeur de modification de la page, utilisé pour les litiges internes
  52. Déterminer si un nombre est un nombre premier - - Java
  53. Sortie 9 * 9 tableau de multiplication - Java
  54. Plusieurs façons de réinitialiser le déplacement du Groupe de consommateurs Kafka
  55. GRPC Learning six: GRPC Gateway Integrated Swagger, Java language programming and data structure Advanced Edition
  56. python数据结构:数组、列表、栈、队列及实现
  57. Gson: json Parser for github Star 18k, Java json Parser from Google
  58. Principes de conception du cache Guava, Introduction à la base Java
  59. Un pantalon beige = élégant, blanc + haut de gamme, si beau cet hiver!
  60. Hadoop05 【 commande 】, Java 400 questions d'entrevue pour vous aider à entrer dans l'usine