Common Oracle test table big_table

Create test tables, which are frequently used by DBAs. Generally, they are created based on dba_objects. This article is based on the big_table of Master Tom for your reference.

1. big_table based on Oracle 10 GB

--==============================================-- Create a test table for Oracle 10g-- File   : cr_big_tb_10g.sql-- Author : Robinson-- Blog   : http://blog.csdn.net/robinson_0612--==============================================promptprompt     Create a big table from all_objectsprompt    ======================================CREATE TABLE big_tableAS SELECT ROWNUM id, a.*FROM all_objects aWHERE 1=0; promptprompt  Modify table to nologgming modeprompt  ==========================ALTER TABLE big_table NOLOGGING; prompt prompt     Please input rows number to fill into big_tableprompt     ============================================DECLARE        l_cnt NUMBER;        l_rows NUMBER := &1;BEGIN        INSERT /*+ append */        INTO big_table                SELECT rownum, a.*                FROM all_objects a;        l_cnt := SQL%ROWCOUNT;        COMMIT;        WHILE (l_cnt < l_rows)        LOOP                INSERT /*+ APPEND */                INTO big_table                        SELECT rownum + l_cnt                             ,owner                             ,object_name                             ,subobject_name                             ,object_id                             ,data_object_id                             ,object_type                             ,created                             ,last_ddl_time                             ,TIMESTAMP                             ,status                             ,temporary                             ,generated                             ,secondary                        FROM big_table                        WHERE rownum <= l_rows - l_cnt;                l_cnt := l_cnt + SQL%ROWCOUNT;                COMMIT;        END LOOP;END; / prompt prompt      Add primary key for  big table prompt     =====================================ALTER TABLE big_table ADD CONSTRAINT big_table_pk PRIMARY KEY (id); prompt prompt      Gather statistics for big_tableprompt     =====================================BEGIN        dbms_stats.gather_table_stats(ownname => USER,                                     tabname => 'BIG_TABLE',                                     method_opt => 'for all indexed columns',                                     cascade => TRUE);END; / prompt prompt      check total rows  for big_table prompt     ====================================SELECT COUNT(*)FROM big_table;

Ii. big_table based on Oracle 11g

--==============================================-- Create a test table for Oracle 11g-- File   : cr_big_tb_11g.sql-- Author : Robinson-- Blog   : http://blog.csdn.net/robinson_0612--==============================================promptprompt     Create a big table from all_objectsprompt    ======================================CREATE TABLE big_tableAS SELECT ROWNUM id, a.*FROM all_objects aWHERE 1=0; promptprompt  Modify table to nologgming modeprompt  ==========================ALTER TABLE big_table NOLOGGING; prompt prompt      Please input rows number to fill into big_tableprompt     ============================================DECLARE        l_cnt NUMBER;        l_rows NUMBER := &1;BEGIN        INSERT /*+ append */        INTO big_table                SELECT rownum, a.*                FROM all_objects a;        l_cnt := SQL%ROWCOUNT;        COMMIT;        WHILE (l_cnt < l_rows)        LOOP                INSERT /*+ APPEND */                INTO big_table                        SELECT rownum + l_cnt                             ,owner                             ,object_name                             ,subobject_name                             ,object_id                             ,data_object_id                             ,object_type                             ,created                             ,last_ddl_time                             ,TIMESTAMP                             ,status                             ,temporary                             ,generated                             ,secondary                             ,namespace                             ,edition_name                        FROM big_table                         WHERE rownum <= l_rows - l_cnt;                l_cnt := l_cnt + SQL%ROWCOUNT;                COMMIT;        END LOOP;END; / prompt prompt      Add primary key for  big table prompt     =====================================ALTER TABLE big_table ADD CONSTRAINT big_table_pk PRIMARY KEY (id); prompt prompt      Gather statistics for big_tableprompt     =====================================BEGIN        dbms_stats.gather_table_stats(ownname => USER,                                     tabname => 'BIG_TABLE',                                     method_opt => 'for all indexed columns',                                     cascade => TRUE);END; / prompt prompt      check total rows  for big_table prompt     ====================================SELECT COUNT(*)FROM big_table;

Iii. Description
1. The school is organized according to the original big_table of Master Tom.
2. Oracle 11g all_objects has two more columns than Oracle 10g, so two different versions are used.
3. The ID column of big_table is a unique value, and a primary key is created on it.
4. The logging mode should be enabled for testing redo and other information about the table.

For more information, see:

For more information about Oracle RAC, see
Use crs_setperm to modify the resource owner and permissions of RAC.
Use crs_profile to manage RAC resource configuration files
RAC database startup and Shutdown
Oracle RAC services
Services in Oracle Database 10g
Migrate datbase from single instance to Oracle RAC
Connect Oracle RAC to a specified instance
Oracle RAC load balancing test (combined with server and client)
Oracle RAC server connection Load Balance)
Load Balance)
Non-Default port listening configuration in Oracle RAC (listener. ora tnsnames. ora)
Oracle RAC Listener Configuration (listener. ora tnsnames. ora)
Configure RAC load balancing and Failover
CRS-1006, CRS-0215 fault case
Installing Oracle 10g RAC Based on Linux (RHEL 5.5)
Use runcluvfy to verify the Oracle RAC installation environment

For more information about the basics and concepts of Oracle network configuration, see:
Configure dynamic service registration for non-default ports
Configure sqlnet. ora to restrict IP Access to Oracle
Configure and manage Oracle listener logs
Set the Oracle listener password (listener)
Configure the Oracle client to connect to the database

For more information about user-managed backup and recovery, see
Oracle cold backup
Oracle Hot Backup
Concept of Oracle backup recovery
Oracle instance recovery
Oracle recovery based on user management
System tablespace management and Backup Recovery
Sysaux tablespace management and recovery
Oracle backup control file recovery (unsing backup controlfile)

For information on RMAN backup recovery and management, see
RMAN overview and architecture
RMAN configuration, Monitoring and Management
Detailed description of RMAN backup
RMAN restoration and recovery
Create and use RMAN catalog
Create RMAN storage script based on catalog
Catalog-based RMAN backup and recovery
RMAN backup path confusion
Use RMAN for recovery from different machine backups (WIN platform)
Use RMAN to migrate a file system database to ASM
Linux RMAN backup shell script
Use RMAN to migrate the database to a different machine

For the Oracle architecture, see
Oracle tablespace and data files
Oracle Password File
Oracle parameter file
Oracle online redo log file)
Oracle Control File)
Oracle archiving logs
Oracle rollback and undo)
Oracle database instance startup and Shutdown Process
Automated Management of Oracle 10g SGA
Oracle instances and Oracle databases (Oracle Architecture)