search
HomeDatabaseMysql Tutorial通过shell检查Oracle分区表中是否含有默认分区

在大数据量的系统中,分区表是很常见的,分区有多种类型,可以根据业务来选择自己需要的分区,不过为了数据的兼容性,需要考虑对于分

在大数据量的系统中,分区表是很常见的,分区有多种类型,可以根据业务来选择自己需要的分区,不过为了数据的兼容性,需要考虑对于分区表设定一个默认的表分区,如果数据在插入表分区的时候,,没有符合条件的分区,就会插入默认的表分区中。

这个可以根据自己的需要来设定,如果确实对数据有严格的要求,甚至可以要求不加入默认的分区。

以下的脚本会去"扫描"分区的信息,如果检测到没有默认的分区,就会生成对应的脚本。

可以根据自己的情况来决定是否需要加入分区。

sqlplus -s $1/$2@$SH_DB_SID  set head off
 set pages 100
 set linesize 200
 /* bad performance
 select name,Count(*)from user_part_key_columns where object_type='TABLE' and name in
 (
 select table_name from user_tables where partitioned='YES'
 minus
  (select distinct  table_name
 from user_tab_partitions where  partition_name like '%MAX%'
  )
 )
 group by name;
 */
 select 'alter table  '||table_name||' add partition  PMAXVALUE  values less than ('||
 decode(col_count, 1, 'MAXVALUE',
                                                          2,'MAXVALUE,MAXVALUE',
                                                          3,'MAXVALUE,MAXVALUE,MAXVALUE',
                                                          4,'MAXVALUE,MAXVALUE,MAXVALUE,MAXVALUE','partition out of range')||');'
    from
 (
 (
 select t1.table_name,count(t2.name) col_count from user_tables t1,user_part_key_columns t2
 where t1.partitioned='YES'
 and t1.table_name=t2.name
 and t2.object_type='TABLE'
 group by t1.table_name
 )
 minus
  (
  select  t2.name table_name,count(t2.name)col_count
 from user_tab_partitions t1,user_part_key_columns t2
 where  t1.partition_name like '%MAX%'
 and t1.table_name=t2.name
 and t2.object_type='TABLE'
 group by t2.name
  )
 )
 /

EOF
 exit

 简单运行一下,可以看到有一些分区表是灭有默认的分区的。可以根据自己的情况来定夺。

[ora11g@rac1 dbm_lite]$ ksh getmaxpar.sh system Oracle

alter table  LOGMNRC_GSBA add partition  PMAXVALUE  values less than (MAXVALUE);
 alter table  LOGMNRC_GSII add partition  PMAXVALUE  values less than (MAXVALUE);
 alter table  LOGMNRC_GTCS add partition  PMAXVALUE  values less than (MAXVALUE);
 alter table  LOGMNRC_GTLO add partition  PMAXVALUE  values less than (MAXVALUE);
 alter table  LOGMNRP_CTAS_PART_MAP add partition  PMAXVALUE  values less than (MAXVALUE);
 alter table  LOGMNR_ATTRCOL$ add partition  PMAXVALUE  values less than (MAXVALUE);
 alter table  LOGMNR_ATTRIBUTE$ add partition  PMAXVALUE  values less than (MAXVALUE);
 alter table  LOGMNR_CCOL$ add partition  PMAXVALUE  values less than (MAXVALUE);
 alter table  LOGMNR_CDEF$ add partition  PMAXVALUE  values less than (MAXVALUE);
 alter table  LOGMNR_COL$ add partition  PMAXVALUE  values less than (MAXVALUE);
 alter table  LOGMNR_COLTYPE$ add partition  PMAXVALUE  values less than (MAXVALUE);
 alter table  LOGMNR_DICTIONARY$ add partition  PMAXVALUE  values less than (MAXVALUE);
 alter table  LOGMNR_DICTSTATE$ add partition  PMAXVALUE  values less than (MAXVALUE);
 alter table  LOGMNR_ENC$ add partition  PMAXVALUE  values less than (MAXVALUE);
 alter table  LOGMNR_ICOL$ add partition  PMAXVALUE  values less than (MAXVALUE);

浅谈Oracle分区表之范围分区

Oracle分区表迁移

Oracle分区表使用实例

Oracle分区表 (Partition Table) 的创建及管理

本文永久更新链接地址:

linux

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
MySQL's Role: Databases in Web ApplicationsMySQL's Role: Databases in Web ApplicationsApr 17, 2025 am 12:23 AM

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

MySQL: Building Your First DatabaseMySQL: Building Your First DatabaseApr 17, 2025 am 12:22 AM

The steps to build a MySQL database include: 1. Create a database and table, 2. Insert data, and 3. Conduct queries. First, use the CREATEDATABASE and CREATETABLE statements to create the database and table, then use the INSERTINTO statement to insert the data, and finally use the SELECT statement to query the data.

MySQL: A Beginner-Friendly Approach to Data StorageMySQL: A Beginner-Friendly Approach to Data StorageApr 17, 2025 am 12:21 AM

MySQL is suitable for beginners because it is easy to use and powerful. 1.MySQL is a relational database, and uses SQL for CRUD operations. 2. It is simple to install and requires the root user password to be configured. 3. Use INSERT, UPDATE, DELETE, and SELECT to perform data operations. 4. ORDERBY, WHERE and JOIN can be used for complex queries. 5. Debugging requires checking the syntax and use EXPLAIN to analyze the query. 6. Optimization suggestions include using indexes, choosing the right data type and good programming habits.

Is MySQL Beginner-Friendly? Assessing the Learning CurveIs MySQL Beginner-Friendly? Assessing the Learning CurveApr 17, 2025 am 12:19 AM

MySQL is suitable for beginners because: 1) easy to install and configure, 2) rich learning resources, 3) intuitive SQL syntax, 4) powerful tool support. Nevertheless, beginners need to overcome challenges such as database design, query optimization, security management, and data backup.

Is SQL a Programming Language? Clarifying the TerminologyIs SQL a Programming Language? Clarifying the TerminologyApr 17, 2025 am 12:17 AM

Yes,SQLisaprogramminglanguagespecializedfordatamanagement.1)It'sdeclarative,focusingonwhattoachieveratherthanhow.2)SQLisessentialforquerying,inserting,updating,anddeletingdatainrelationaldatabases.3)Whileuser-friendly,itrequiresoptimizationtoavoidper

Explain the ACID properties (Atomicity, Consistency, Isolation, Durability).Explain the ACID properties (Atomicity, Consistency, Isolation, Durability).Apr 16, 2025 am 12:20 AM

ACID attributes include atomicity, consistency, isolation and durability, and are the cornerstone of database design. 1. Atomicity ensures that the transaction is either completely successful or completely failed. 2. Consistency ensures that the database remains consistent before and after a transaction. 3. Isolation ensures that transactions do not interfere with each other. 4. Persistence ensures that data is permanently saved after transaction submission.

MySQL: Database Management System vs. Programming LanguageMySQL: Database Management System vs. Programming LanguageApr 16, 2025 am 12:19 AM

MySQL is not only a database management system (DBMS) but also closely related to programming languages. 1) As a DBMS, MySQL is used to store, organize and retrieve data, and optimizing indexes can improve query performance. 2) Combining SQL with programming languages, embedded in Python, using ORM tools such as SQLAlchemy can simplify operations. 3) Performance optimization includes indexing, querying, caching, library and table division and transaction management.

MySQL: Managing Data with SQL CommandsMySQL: Managing Data with SQL CommandsApr 16, 2025 am 12:19 AM

MySQL uses SQL commands to manage data. 1. Basic commands include SELECT, INSERT, UPDATE and DELETE. 2. Advanced usage involves JOIN, subquery and aggregate functions. 3. Common errors include syntax, logic and performance issues. 4. Optimization tips include using indexes, avoiding SELECT* and using LIMIT.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. How to Fix Audio if You Can't Hear Anyone
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Chat Commands and How to Use Them
1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

EditPlus Chinese cracked version

EditPlus Chinese cracked version

Small size, syntax highlighting, does not support code prompt function

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

SublimeText3 English version

SublimeText3 English version

Recommended: Win version, supports code prompts!

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment