1. Summary
  2. Files
  3. Support
  4. Report Spam
  5. Create account
  6. Log in

Manage tables

From smenu

Jump to: navigation, search

Smenu offers the shortcut 'tbl' to manage all about tables. when you just type 'tbl' you get the help:


[Linux]:/home/oracle> tbl

           tbl -u <owner>  [-ord <n>]
           tbl -u <owner> -t <table_name>                           tbl -ext
           tbl -u <owner> -t <table_name> -ldl                      tbl -u <owner> -ext <table_name> -drop
           tbl -u <owner> -t <table_name>  -p  -d                   tbl -u <owner> -t <table_name> -ddl      # get the ddl
           tbl -u <owner> -t <table_name>  -c  -d                   tbl -u <owner> -t <table_name>  -pred    # list table predicate usage
           tbl -u <owner> -t <table_name>  -a  -d -i                tbl -u <owner> -cd <CONSTRAINT TYPE : ie 'P'> -x
           tbl -u <owner> -t <table_name>  -s                       tbl -u <owner> -log|-logc | -logu
           tbl -u <owner> -t <table_name>  -l                       tbl -t <table_name>  -lbs <lob column_name>
           tbl -u <owner> -cl <CONSTRAINT TYPE : ie 'P'>            tbl -u <owner> -t <table_name> -noidxfk
           tbl -u <owner> -ce <CONSTRAINT TYPE : ie 'P'> -x         tbl -n <table_name> (Add a % before, after or at both end) to the part name
           tbl -txn -t <table_name> -u < OWNER> [ -rn <nn> ]        tbl -t <table_name>  -spc|-uspc  -part <PARTITION_NAME>  [-u <OWNER>]
           tbl -g -u <OWNER> -t <table_name>                        tbl [-u <owner>] -t <table_name> -col <COLUMN_NAME>
           tbl -dep -t <table_name> -u < OWNER>                     tbl -ntbs <tbs> -t <table> -u <owner> [-nitbs <tbs>]

           add to -t :
           ==============
                 -u  limit to owner                                -a  Chained rows
                 -c  Constraints                                   -d  Table description
                 -i  additional info on  table                   -lob  show lobs info
                 -p  (sub)partitions                               -s  List stats info gathered on columns
               -ldl  display last ddl applied on the table       -ddl  extract ddl of table
              -drop  drop external table                         -lbs  List lob size distrubtion
               -spc  List space map using dbms_space.space_usage -uspc List unused space using dbms_space.unused_space
               -dep  List all dependent segments                 -txn  List transaction available in flash.
              -pred  show predicate usage;                       -ord <n> # values are 1 to 7 and represent columns
                -g   List table columns with histogram on them   -col <COLUMN_NAME> List histograms on COLUMN_NAME
                -cl  List shema Constraints. Constraint type is the letter representing the type of the constraint.  type 'ALL' to see all constraintstype
                -cd  Generate script to Disable shema|table Constraints. Constraint type is the letter representing the type of the constraint.
                     type 'ALL' affect all constraintstype. If you omit table and leave only schema, then all constraints are taken in account
                -ce  Generate script to Enable shema|table Constraints. Constraint type is the letter representing
                     the type of the constraint.  type 'ALL' affect all constraintstype. If you omit table and leave
                     only schema, then all constraint are taken in account
          -truncate  Generate script to truncate all tables in a given schema. '-x' option will not execute this.
               -ext  List all  externamal tables; drop it if -t <table> -drop is added
           -noidxfk  List Foreign key without index and the Parent table name and columns
              -ntbs  Move table to new tbs <tablespace_name>. with -mi move also the indexes
             -nitbs  In conjunction with '-ntbs', move also the indexes to new tablespace
                 -n  List all tables whose name is like %xxx%. You need to provide the % yourself : ie: tlb -u soe -n MV_%

               -log  Supplemental log groups    -logc  supplemental column       -logu  supplemental column in table that without FK, PK or BITMAP

      Also you can use : 'idx -t <table_name>' to list associated indexes.  It is intentional that -drop only access external table.
     If you want to drop a table, connect into db.  The time it takes gives you a chance to realize what you do.

     Example:

          tbl -t EMP -u SCOTT -p           # List all partitions
          tbl -t EMP -u SCOTT -g           # display all colums of table EMP with histogram on them
          tbl -t EMP -u SCOTT -col DEPNO   # display the histogram on column DEPNO
          tbl -t EMP -u SCOTT -ce ALL      # generate script to enable all constraints

Let's start modestly and request all tables into the schema MDSYS. We get immdiatly:

[linux0001dev:TEST011]:/home/oracle> tbl -u mdsys

MACHINE linux0001dev     - ORACLE_SID : TEST011                                                                             Page:   1
Date              -  Thursday  17th December  2009  15:02:26
Username          -  SYS        Show partition table
                                                                                                         Chain   Avg row  Glob Row
Table name               Owner     NUM_ROWS     BLOCKS Size (m)      Tablespace       LAST_ANALYSED       Cnt    Len      stat Mov
------------------------ -------- ---------- ---------- -------- -------------------- ------------------- ------ -------- ---- ---
FINAL_PARTITION          MDSYS             0          0        0 MDSYS                2009-09-14 11:44:49      0        0 YES  DIS
PARTITION_TMP_2          MDSYS             0          0        0 MDSYS                2009-09-14 11:44:49      0        0 YES  DIS
OGIS_GEOMETRY_COLUMNS    MDSYS             0          0        0 MDSYS                2009-09-14 11:44:49      0        0 YES  DIS
PARTITION_TMP_3          MDSYS             0          0        0 MDSYS                2009-09-14 11:44:49      0        0 YES  DIS
OGIS_SPATIAL_REFERENCE   MDSYS             0          0        0 MDSYS                2009-09-14 11:44:49      0        0 YES  DIS
SDO_TOPO_METADATA_TABLE  MDSYS             0          0        0 MDSYS                2009-09-14 11:44:51      0        0 YES  DIS
SDO_LRS_METADATA_TABLE   MDSYS             0          0        0 MDSYS                2009-09-14 11:44:51      0        0 YES  DIS
SDO_GEOM_METADATA_TABLE  MDSYS             0          0        0 MDSYS                2009-09-14 11:44:51      0        0 YES  DIS
SDO_INDEX_METADATA_TABLE MDSYS             0          0        0 MDSYS                2009-09-14 11:44:51      0        0 YES  DIS
SDO_NETWORK_USER_DATA    MDSYS             0          0        0 MDSYS                2009-09-14 11:44:51      0        0 YES  DIS
SDO_NETWORK_CONSTRAINTS  MDSYS             0          0        0 MDSYS                2009-09-14 11:44:51      0        0 YES  DIS
SDO_NETWORK_METADATA     MDSYS             0          0        0 MDSYS                2009-09-14 11:44:51      0        0 YES  DIS
SDO_GEOR_PLUGIN_REGISTRY MDSYS             0          0        0 MDSYS                2009-09-14 11:44:51      0        0 YES  DIS
SDO_GEOR_SYSDATA_TABLE   MDSYS             0          0        0 MDSYS                2009-09-14 11:44:51      0        0 YES  DIS
SDO_THEMES_TABLE         MDSYS             0          0        0 MDSYS                2009-09-14 11:44:51      0        0 YES  DIS
SDO_MAPS_TABLE           MDSYS             0          0        0 MDSYS                2009-09-14 11:44:51      0        0 YES  DIS
SDO_PREFERRED_OPS_USER   MDSYS             0          0        0 MDSYS                2009-09-14 11:44:51      0        0 YES  DIS
SDO_PREFERRED_OPS_SYSTEM MDSYS             0          0        0 MDSYS                2009-09-14 11:44:51      0        0 YES  DIS
SDO_GEOR_XMLSCHEMA_TABLE MDSYS             1          5        0 MDSYS                2009-09-14 11:44:51      0      144 YES  DIS
SDO_XML_SCHEMAS          MDSYS             4          5        0 MDSYS                2009-09-14 11:44:51      0      140 YES  DIS
SDO_PRIME_MERIDIANS      MDSYS            16          5        0 MDSYS                2009-09-14 11:44:51      0       52 YES  DIS
SDO_COORD_AXIS_NAMES     MDSYS            28          5        0 MDSYS                2009-09-14 11:44:50      0       18 YES  DIS
SDO_PROJECTIONS_OLD      MDSYS            42          5        0 MDSYS                2009-09-14 11:44:51      0       19 YES  DIS
SDO_ELLIPSOIDS_OLD       MDSYS            47          5        0 MDSYS                2009-09-14 11:44:51      0       27 YES  DIS
SDO_COORD_SYS            MDSYS            65          5        0 MDSYS                2009-09-14 11:44:51      0      125 YES  DIS
.
.
.
                                                           ----- 
sum                                                            6

to view a single table:

[Linux]:/home/oracle> tbl -u mdsys -t CUSTOMER_T

MACHINE linux0001dev     - ORACLE_SID : TEST011                                                                             Page:   1
Date              -  Thursday  17th December  2009  15:06:13
Username          -  SYS Show partition table
                                                                                                       Chain  Avg row Glob Row
Table name          Owner         NUM_ROWS     BLOCKS Size (m)      Tablespace     LAST_ANALYSED        Cnt       Len stat Mov
-------------- ---------------- ---------- ---------- -------- ------------------ ------------------- ------ -------- ---- ---
CUSTOMER_T            MDSYS          4384        370        3     MDSYS           2009-09-14 11:44:51      0      508 YES  DIS
                                                         --------
sum                                                         3

Note that if there is only one occurrence of the table then you are given the response. But if many occurences of the table exists, you are given a list of user to select from:

 there are many tables for CUSTOMER_T:
 Use :

 tbl -t CUSTOMER_T-u <user>


OWNER                          TABLE_NAME                     'TABL
------------------------------ ------------------------------ -----
CUST01_M                       CUSTOMER_T                            table
CUST02_M                       CUSTOMER_T                            table
CUST03_M                       CUSTOMER_T                            table
CUST04_M                       CUSTOMER_T                            table
CUST05_M                       CUSTOMER_T                            table
CUST06_M                       CUSTOMER_T                            table

We could view columns details of our table:

[linux0001dev:TEST011]:/home/oracle> tbl -t CUSTOMER_T -s

MACHINE linux0001dev     - ORACLE_SID : TEST011                                             Page:   1
Date              -  Thursday  17th December  2009  15:09:24
Username          -  SYS  Show table/col stats

                                                                                                                         Agv
                                                   Num     Rows per key            Glob    Num                    Num    Col
COLUMN_NAME                    Data Type        distinct  (Selectivity)    Density Stat   Nulls    Histogram     Bucket  Len Last Analysed
------------------------------ ---------------- --------- ------------- ---------- ---- ---------- ------------ ------- ---- ------------------
CS_NAME                        VARCHAR2(80)          4264           1.0 .000234522 YES           0 NONE               2   28 14-09-09 11:44:51
SRID                           NUMBER(22)            4384           1.0 .000228102 YES           0 NONE               2    5 14-09-09 11:44:51
AUTH_SRID                      NUMBER(22)            4384           1.0 .000228102 YES           0 NONE               2    5 14-09-09 11:44:51
AUTH_NAME                      VARCHAR2(256)          153          20.0 .006535948 YES        1319 NONE               2   18 14-09-09 11:44:51
WKTEXT                         VARCHAR2(2046)        4115           1.0 .000243013 YES         253 NONE               2  454 14-09-09 11:44:51
CS_BOUNDS                      SDO_GEOMETRY(1)                       .0            NO              NONE               0

6 rows selected.

Or view the constraints (option -c):


[linux0001dev:TEST011]:/home/oracle> tbl -t CUSTOMER_T -c

MACHINE linux0001dev     - ORACLE_SID : TEST011                         Page:   1

Date              -  Thursday  17th December  2009  15:12:04
Username          -  SYS       List Constraints which point to the Table CUSTOMER_T


CONSTRAINT_NAME                Constraint Col or Condition                          Pos Status     DEFERRABLE     DEFERRED
------------------------------ ---------- ---------------------------------------- ---- ---------- -------------- --------
SYS_C004038                    PRIMARY    SRID                                        1 ENABLED    NOT DEFERRABLE IMMEDIATE
SYS_C004037                    Check      "SRID" IS NOT NULL                          1 ENABLED    NOT DEFERRABLE IMMEDIATE

We can ask Smenu to generate statement to enable/disable constraints for this table (option -cd (disable) -ce (enable):

[linux0001dev:TEST011]:/home/oracle> tbl -t CUSTOMER_T -cd ALL

alter table MDSYS.CUSTOMER_T disable constraint SYS_C004038 cascade;
alter table MDSYS.CUSTOMER_T disable constraint SYS_C004037 cascade;

To enable these constraints:


[linux0001dev:TEST011]:/home/oracle> tbl -t CUSTOMER_T -ce ALL
alter table MDSYS.CUSTOMER_T enable constraint SYS_C004038;
alter table MDSYS.CUSTOMER_T enable constraint SYS_C004037;

For table partitioning, Smenu can show the partitions, subpartions. Just add '-p' to the statment

[linux0001dev:TEST011]:/home/oracle> tbl -t INVENTRY -u CUSTOMER_01 -p

MACHINE linux0001dev     - ORACLE_SID : TEST011
Date              -  Thursday  17th December  2009  15:15:43
Username          -  SYS            Show partitions for table: INVENTRY
                                                                                                                           Chain  Avg row Glob Row
Table name                            Owner         NUM_ROWS     BLOCKS Size (m)      Tablespace      LAST_ANALYSED          Cnt      Len stat Mov
-------------------------------- ---------------- ---------- ---------- -------- -------------------- ------------------- ------ -------- ---- ---
INVENTRY                     CUSTOMER_01          318010      11089       87 -- partitioned --    2009-12-17 13:42:10      0      182 YES  ENA
                                                                        --------
sum                                                                           87


Part col -->  MSG_TIMESTAMP  ( '+' signal aggregate stats - stats derived from others stats)

 Part                         Partition                                                          Subpart
  Pos Partition name          Num rows     Subpartition name              Tablespace name       Num rows Last Analyzed
----- ----------------------- ------------ ------------------------- ------------------------- --------- -----------------
    1 INVENTRY20091027_12     4182         INVENTRY20091027_12_P1    TBS_INV1                       2535 27-10-09 22:05:48
                                           INVENTRY20091027_12_P2    TBS_INV1                       1647 27-10-09 22:05:48
    2 INVENTRY20091028_00     3022         INVENTRY20091028_00_P1    TBS_INV1                         10 27-10-09 22:05:39
                                           INVENTRY20091028_00_P2    TBS_INV1                       3012 28-10-09 22:09:34
    3 INVENTRY20091028_12     3141         INVENTRY20091028_12_P1    TBS_INV1                          3 28-10-09 22:09:11
                                           INVENTRY20091028_12_P2    TBS_INV1                       3138 28-10-09 22:09:22
    4 INVENTRY20091029_00     3366         INVENTRY20091029_00_P1    TBS_INV1                         26 28-10-09 22:09:12
                                           INVENTRY20091029_00_P2    TBS_INV1                       3340 29-10-09 22:10:52
    5 INVENTRY20091029_12     52047        INVENTRY20091029_12_P1    TBS_INV1                         20 29-10-09 22:09:31
                                           INVENTRY20091029_12_P2    TBS_INV1                      52027 29-10-09 22:10:08
    6 INVENTRY20091030_00     39032        INVENTRY20091030_00_P1    TBS_INV1                        868 29-10-09 22:09:40
                                           INVENTRY20091030_00_P2    TBS_INV1                      38197 29-10-09 22:10:02
    7 INVENTRY20091030_12     6179         INVENTRY20091030_12_P1    TBS_INV1                          6 30-10-09 22:11:33
                                           INVENTRY20091030_12_P2    TBS_INV1                       6173 30-10-09 22:11:42
    8 INVENTRY20091031_00     5605         INVENTRY20091031_00_P1    TBS_INV1                        183 31-10-09 06:05:35
                                           INVENTRY20091031_00_P2    TBS_INV1                       5422 31-10-09 06:05:37
    9 INVENTRY20091031_12     4931         INVENTRY20091031_12_P1    TBS_INV1                        172 02-11-09 22:23:06
                                           INVENTRY20091031_12_P2    TBS_INV1                       4759 02-11-09 22:23:08
   10 INVENTRY20091101_00     5300         INVENTRY20091101_00_P1    TBS_INV1                        172 02-11-09 22:22:46
                                           INVENTRY20091101_00_P2    TBS_INV1                       5128 02-11-09 22:22:56
   11 INVENTRY20091101_12     5299         INVENTRY20091101_12_P1    TBS_INV1                        173 02-11-09 22:22:47
                                           INVENTRY20091101_12_P2    TBS_INV1                       5126 02-11-09 22:22:58

Show the value of the partitions key

[linux0001dev:TEST011]:/home/oracle> tbl -t INVENTRY -u CUSTOMER_01 -ph

MACHINE linux0001dev     - ORACLE_SID : TEST011                                                                                                                    Page:   1
Date              -  Thursday  17th December  2009  15:18:06
Username          -  SYS                            Show partitions high values for table: INVENTRY


Part col -->  MSG_TIMESTAMP

Partion_name                  High_value
----------------------------- ----------------------------------------------------------------------
INVENTRY20091219_12           TIMESTAMP'2009-12-19 12:00:00'
INVENTRY20091219_00           TIMESTAMP'2009-12-19 00:00:00'
INVENTRY20091218_12           TIMESTAMP'2009-12-18 12:00:00'
INVENTRY20091218_00           TIMESTAMP'2009-12-18 00:00:00'
INVENTRY20091217_12           TIMESTAMP'2009-12-17 12:00:00'
INVENTRY20091217_00           TIMESTAMP'2009-12-17 00:00:00'
INVENTRY20091216_12           TIMESTAMP'2009-12-16 12:00:00'
INVENTRY20091216_00           TIMESTAMP'2009-12-16 00:00:00'
INVENTRY20091215_12           TIMESTAMP'2009-12-15 12:00:00'
INVENTRY20091215_00           TIMESTAMP'2009-12-15 00:00:00'
INVENTRY20091214_12           TIMESTAMP'2009-12-14 12:00:00'
INVENTRY20091214_00           TIMESTAMP'2009-12-14 00:00:00'
INVENTRY20091213_12           TIMESTAMP'2009-12-13 12:00:00'
INVENTRY20091213_00           TIMESTAMP'2009-12-13 00:00:00'
INVENTRY20091212_12           TIMESTAMP'2009-12-12 12:00:00'
INVENTRY20091212_00           TIMESTAMP'2009-12-12 00:00:00'
INVENTRY20091211_12           TIMESTAMP'2009-12-11 12:00:00'
.
.
.

Smenu can give the size of each LOB for each partitions:

[linux0001dev:TEST011]:/home/oracle> tbl -t INVENTRY -u CUSTOMER_01 -lob



MACHINE linux0001dev     - ORACLE_SID : TEST011 Page:   1

Date              -  Thursday  17th December  2009  15:19:49
Username          -  SYS
List tables info : CUSTOMER_01


                                                                                  Lob partition
Owner           Segment Name         PARTITION_NAME                 Lob Column    name               Tablespace TYPE              Size(m)
--------------- -------------------- ------------------------------ ------------- ------------------ ---------- -------------- ----------
CUSTOMER_01     INVENTRY             INVENTRY20091027_12_P1         -              -                  TBS_INV1  sub_partition        0.50
                                     INVENTRY20091027_12_P2         -              -                  TBS_INV1  sub_partition        0.50
                                     INVENTRY20091028_00_P1         -              -                  TBS_INV1  sub_partition        0.06
                                     INVENTRY20091028_00_P2         -              -                  TBS_INV1  sub_partition        0.69
                                     INVENTRY20091028_12_P1         -              -                  TBS_INV1  sub_partition        0.06
                                     INVENTRY20091028_12_P2         -              -                  TBS_INV1  sub_partition        0.81
                                     INVENTRY20091029_00_P1         -              -                  TBS_INV1  sub_partition        0.06
                                     INVENTRY20091029_00_P2         -              -                  TBS_INV1  sub_partition        0.94
                                     INVENTRY20091029_12_P1         -              -                  TBS_INV1  sub_partition        0.06
                                     INVENTRY20091029_12_P2         -              -                  TBS_INV1  sub_partition        9.00
.
.

It is easy also to request the internal space occupency:

[linux0001dev:TEST011]:/home/oracle> tbl -t INVENTRY -u CUSTOMER_01 -spc

owner=CUSTOMER_01 Table=INVENTRY  Partition= type=TABLE SUBPARTITION

segtype=TABLE SUBPARTITION
partition INVENTRY20091204_12_P2 Unformatted Blocks = 0   fs1=0   fs2=1   fs3=0   fs4=0 Full Blocks = 4
partition INVENTRY20091204_12_P1 Unformatted Blocks = 0   fs1=0   fs2=0   fs3=0   fs4=7 Full Blocks = 6
partition INVENTRY20091112_00_P2 Unformatted Blocks = 0   fs1=1   fs2=1   fs3=0   fs4=3 Full Blocks = 8
partition INVENTRY20091112_00_P1 Unformatted Blocks = 0   fs1=0   fs2=0   fs3=3   fs4=7 Full Blocks = 3
partition INVENTRY20091112_12_P2 Unformatted Blocks = 0   fs1=0   fs2=0   fs3=1   fs4=3 Full Blocks = 9
partition INVENTRY20091112_12_P1 Unformatted Blocks = 0   fs1=0   fs2=2   fs3=0   fs4=8 Full Blocks = 3
partition INVENTRY20091207_00_P2 Unformatted Blocks = 0   fs1=0   fs2=0   fs3=2   fs4=6 Full Blocks = 5
partition INVENTRY20091207_00_P1 Unformatted Blocks = 0   fs1=0   fs2=1   fs3=0   fs4=0 Full Blocks = 12
partition INVENTRY20091207_12_P2 Unformatted Blocks = 0   fs1=0   fs2=0   fs3=0   fs4=0 Full Blocks = 0
partition INVENTRY20091207_12_P1 Unformatted Blocks = 0   fs1=0   fs2=1   fs3=0   fs4=0 Full Blocks = 27
partition INVENTRY20091103_00_P2 Unformatted Blocks = 94   fs1=0   fs2=0   fs3=0   fs4=29 Full Blocks = 121
partition INVENTRY20091103_00_P1 Unformatted Blocks = 0   fs1=0   fs2=1   fs3=0   fs4=2 Full Blocks = 17
partition INVENTRY20091103_12_P2 Unformatted Blocks = 78   fs1=0   fs2=0   fs3=0   fs4=43 Full Blocks = 123
.
.
.
partition INVENTRY20091219_00_P1 Unformatted Blocks = 0   fs1=0   fs2=0   fs3=0   fs4=0 Full Blocks = 0
partition INVENTRY20091219_12_P2 Unformatted Blocks = 0   fs1=0   fs2=0   fs3=0   fs4=0 Full Blocks = 0
partition INVENTRY20091219_12_P1 Unformatted Blocks = 0   fs1=0   fs2=0   fs3=0   fs4=0 Full Blocks = 0
Unformatted Blocks          = 1056
0-25%   free space  Blocks# = 51
25-50%  free space  Blocks# = 78
50-75%  free space  Blocks# = 69
75-100% free space  Blocks# = 1577
Full Blocks                 = 8258
Total blocks                = 11089

PL/SQL procedure successfully completed.

Or request to generate the statement to move the table to another tablespace. Smenu support as far as IOT partioned LOBS and Overflow segment:

[linux0001dev:TEST011]:/home/oracle> tbl -t PRODUCT_INVENTORY -u CUSTOMER_01 -ntbs USERS -nitbs users

Rem No execution requested
Rem Tablespace for table USERS exists : TRUE
Rem
Rem Tablespace for index USERS exists : TRUE
Rem
alter table CUSTOMER_01.PRODUCT_INVENTORY move partition SYS_P8289  tablespace USERS overflow tablespace USERS;
alter table CUSTOMER_01.PRODUCT_INVENTORY move partition SYS_P8290  tablespace USERS overflow tablespace USERS;
alter table CUSTOMER_01.PRODUCT_INVENTORY move partition SYS_P8291  tablespace USERS overflow tablespace USERS;
alter table CUSTOMER_01.PRODUCT_INVENTORY move partition SYS_P8292  tablespace USERS overflow tablespace USERS;
alter table CUSTOMER_01.PRODUCT_INVENTORY move partition SYS_P8293  tablespace USERS overflow tablespace USERS;
.
.

Of course you may ask to move also the indexes to the same or to another tablespace:



[linux0001dev:TEST011]:/home/oracle> tbl -t SALES_CUR -u CUSTOMER_01 -ntbs USERS -nitbs INDEX_TBS

Rem No execution requested
Rem Tablespace for table USERS exists : TRUE
Rem
Rem Tablespace for index INDEX_TBS exists : TRUE
Rem
alter table CUSTOMER_01.SALES_CUR move subpartition SALES_CUR20091017_00_P1  tablespace USERS;
alter table CUSTOMER_01.SALES_CUR move subpartition SALES_CUR20091017_00_P2  tablespace USERS;
alter table CUSTOMER_01.SALES_CUR move subpartition SALES_CUR20091204_00_P1  tablespace USERS;
alter table CUSTOMER_01.SALES_CUR move subpartition SALES_CUR20091204_00_P2  tablespace USERS;
alter table CUSTOMER_01.SALES_CUR move subpartition SALES_CUR20091029_00_P1  tablespace USERS;
alter table CUSTOMER_01.SALES_CUR move subpartition SALES_CUR20091029_00_P2  tablespace USERS;
alter table CUSTOMER_01.SALES_CUR move subpartition SALES_CUR20091216_00_P1  tablespace USERS;
alter table CUSTOMER_01.SALES_CUR move subpartition SALES_CUR20091216_00_P2  tablespace USERS;
alter table CUSTOMER_01.SALES_CUR move subpartition SALES_CUR20091026_00_P1  tablespace USERS;
alter table CUSTOMER_01.SALES_CUR move subpartition SALES_CUR20091026_00_P2  tablespace USERS;
alter table CUSTOMER_01.SALES_CUR move subpartition SALES_CUR20091015_00_P1  tablespace USERS;
alter table CUSTOMER_01.SALES_CUR move subpartition SALES_CUR20091015_00_P2  tablespace USERS;
.
.
alter table CUSTOMER_01.SALES_CUR move subpartition SALES_CUR20091219_00_P1  tablespace USERS;
alter table CUSTOMER_01.SALES_CUR move subpartition SALES_CUR20091219_00_P2  tablespace USERS;
Rem
Rem Index name : SALES_CUR2
Rem
ALTER INDEX CUSTOMER_01.SALES_CUR2 REBUILD PARTITION SALES_CUR20091219_00 TABLESPACE INDEX_TBS;
ALTER INDEX CUSTOMER_01.SALES_CUR2 REBUILD PARTITION SALES_CUR20091218_00 TABLESPACE INDEX_TBS;
ALTER INDEX CUSTOMER_01.SALES_CUR2 REBUILD PARTITION SALES_CUR20091217_00 TABLESPACE INDEX_TBS;
ALTER INDEX CUSTOMER_01.SALES_CUR2 REBUILD PARTITION SALES_CUR20091216_00 TABLESPACE INDEX_TBS;
ALTER INDEX CUSTOMER_01.SALES_CUR2 REBUILD PARTITION SALES_CUR20091215_00 TABLESPACE INDEX_TBS;
ALTER INDEX CUSTOMER_01.SALES_CUR2 REBUILD PARTITION SALES_CUR20091214_00 TABLESPACE INDEX_TBS;
ALTER INDEX CUSTOMER_01.SALES_CUR2 REBUILD PARTITION SALES_CUR20091213_00 TABLESPACE INDEX_TBS;
ALTER INDEX CUSTOMER_01.SALES_CUR2 REBUILD PARTITION SALES_CUR20091212_00 TABLESPACE INDEX_TBS;
ALTER INDEX CUSTOMER_01.SALES_CUR2 REBUILD PARTITION SALES_CUR20091211_00 TABLESPACE INDEX_TBS;

[linux0001dev:TEST011]:/home/oracle> tbl -t CUST -u CUSTOMER_M -ntbs USERS

Rem No execution requested
Rem Tablespace for table USERS exists : TRUE
Rem
alter table CUSTOMER_M.CUST move subpartition CUST20091217_00_P1  tablespace USERS;
ALTER TABLE CUSTOMER_M.CUST MOVE SUBPARTITION CUST20091217_00_P1 LOB (OBJECT) STORE AS ( TABLESPACE USERS);
ALTER TABLE CUSTOMER_M.CUST MOVE SUBPARTITION CUST20091217_00_P1 LOB (OBJECT_MODIFIER) STORE AS ( TABLESPACE USERS);
alter table CUSTOMER_M.CUST move subpartition CUST20091219_12_P2  tablespace USERS;
ALTER TABLE CUSTOMER_M.CUST MOVE SUBPARTITION CUST20091219_12_P2 LOB (OBJECT) STORE AS ( TABLESPACE USERS);
ALTER TABLE CUSTOMER_M.CUST MOVE SUBPARTITION CUST20091219_12_P2 LOB (OBJECT_MODIFIER) STORE AS ( TABLESPACE USERS);
alter table CUSTOMER_M.CUST move subpartition CUST20091217_12_P1  tablespace USERS;
ALTER TABLE CUSTOMER_M.CUST MOVE SUBPARTITION CUST20091217_12_P1 LOB (OBJECT) STORE AS ( TABLESPACE USERS);
ALTER TABLE CUSTOMER_M.CUST MOVE SUBPARTITION CUST20091217_12_P1 LOB (OBJECT_MODIFIER) STORE AS ( TABLESPACE USERS);
alter table CUSTOMER_M.CUST move subpartition CUST20091217_00_P2  tablespace USERS;
ALTER TABLE CUSTOMER_M.CUST MOVE SUBPARTITION CUST20091217_00_P2 LOB (OBJECT) STORE AS ( TABLESPACE USERS);
ALTER TABLE CUSTOMER_M.CUST MOVE SUBPARTITION CUST20091217_00_P2 LOB (OBJECT_MODIFIER) STORE AS ( TABLESPACE USERS);
.
.

Each 'move to new tablespace' command generates by default an output of SQL commands. Typically you redirect them into a file and execute them at your convenience. But you may also request to execute them directly. you do this by adding '-x' to the command. In this case Smenu generates the commands and execute them immediately. However a check is performed on the existence of the target tablespace. If it does not exists, then the command is not performed, only the statement text is generated.

And there are much more in 'tbl' such as displays over table columns histograms, supplemental logging, external tables, predicate used in SQL that reference the given table, dependencies, extract ddl, query last ddl on table, chained rows and whatever else is related to table but indexes. Index has its own shortcut 'idx' which is also discussed on this site.

Last note: Smenu display the sql behind when you add option -v at any command. The request to view a simeple table for a given owner but with '-v' option, produces the following :


[Linux:TEST01]:/home/oracle> tbl -t EMP -u CUSTOMER_M

column nline newline
set pagesize 66 linesize 132 termout on pause off embedded on verify off heading off
select 'Date              -  '||to_char(sysdate,'Day Ddth Month YYYY  HH24:MI:SS'),
       'Username          -  '||USER  nline, 'Show partition table' nline
from sys.dual
/
col db_block_size new_value fsize noprint
set head off feed off
select value db_block_size from v$parameter where name = 'db_block_size'
/
set head on
break on partition_position on partition_name on report
COL partition_position       FORMAT  9999 heading 'Part| Pos'
COL fsize                    FORMAT  9999999   heading 'Size (m)'
COL partition_name           FORMAT  A35 head 'Partition name'
COL subpartition_name        FORMAT  A25 head 'System generated|Subpartition name'
COL table_name               FORMAT   A32 head 'Table name'
COL tablespace_name          FORMAT  A20   justify c HEAD 'Tablespace'
COL owner                    FORMAT  A16   justify c HEAD 'Owner'
COL glob                     FORMAT  A4   justify c HEAD 'Glob|stat'
col last_analyzed            format  A19  head 'Last Analyzed'
col created                  format  A19  head 'Created'
col last_ddl_time            format  A19  head 'Last ddl'
col temporary                format  A10  head 'Temporary'
col row_movement head "Row|Movment" justify c
col avg_rpb format 9999999 head "Avg Row|Per blk" justify c
col max_rpb format 9999999 head "Max Row|Per blk" justify c
col empt_clocks head "Empty|Blocks"
col CHAIN_CNT head "Chain|Cnt" format 99999
col AVG_ROW_LEN head "Avg row|Len" format 9999999
col AVG_space format 999999 head "Avg|space"
col NUM_FREELIST_BLOCKS format 99999 head "Num|Frlist|block" justify c
col Empty_BLOCKS head "Empty|blocks"
col TEMPORARY head "Temp" format a5
col BUFFER_POOL format a8 head "Buffer|pool"
col GLOBAL_STATS head "Global|stats" format a5
col USER_STATS head "User|stats" format a5
col IOT_TYPE head "Iot" format a5
col SAMPLE_SIZE head "Sample| size" justify c
col deg head "Deg" format a3 truncate
col dens format 990.99 head "Data|Density" justify c
col ini_trans head "ini|tran" format 999
col max_trans head "max|tran" format 999
col avg_sfp head "Avg space|Free list block"  format 9999 justify c
col FREELISTS head "free|list"  format 999
col FREELIST_GROUPS head "Free list| Group"  format 999
col cache format a5
col compression for a5 head 'Comp|ressed' justify c
col row_movement for a3  head 'Row|Mov'
comp sum of fsize on report


prompt .          tbl -h for extended help
prompt .          use : idx -t <table_name> to list associated indexes of this table



set lines 190
SELECT table_name, owner,trunc(NUM_ROWS)num_rows, blocks,BLOCKS * &fsize/1024/1024 fsize ,
       case nvl(tablespace_name,'0')
            when '0' then
                 case nvl(IOT_TYPE,'0')
                      when '0' then
                           case nvl(partitioned,'0')
                                when '0'  then '-- temporary --'
                                else '-- partitioned -- '
                            end
                       else
                            case nvl(partitioned,'0')
                                 when '0' then '-- '||IOT_TYPE || ' --'
                                 else '-- IOT Partionned --'
                            end
                  end
            else  tablespace_name
        end tablespace_name ,
       TO_CHAR(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS') last_analysed, CHAIN_CNT, AVG_ROW_LEN,
        global_stats glob, substr(row_movement,1,3) row_movement
     FROM DBA_TABLES
     WHERE owner = 'CUSTOMER_M'   and   TABLE_NAME = 'EMP'
     order by 3 , TO_CHAR(LAST_ANALYZED, 'YYYY-MM-DD HH24:MI:SS')
/

MACHINE Linux     - ORACLE_SID : TEST01                                                                             Page:   1
Date              -  Friday    18th December  2009  14:19:39
Username          -  SYS       Show partition table



.          tbl -h for extended help
.          use : idx -t <table_name> to list associated indexes of this table
                                                                                                           Chain  Avg row Glob Row
Table name          Owner         NUM_ROWS     BLOCKS   Size (m)      Tablespace      LAST_ANALYSED          Cnt      Len stat Mov
---------------- ---------------- ---------- ---------- -------- -------------------- ------------------- ------ -------- ---- ---
EMP              CUSTOMER            30013      7828       61      -- partitioned --  2009-12-18 13:41:16      0      563  YES ENA
                                                          --------
sum                                                        61

Personal tools