SQL*Plus: Release 9.2.0.1.0 - Production on Mon Jan 26 21:20:53 2004
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn /as sysdba;
Connected.
SQL>
SQL> desc dba_tables;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER NOT NULL VARCHAR2(30)
TABLE_NAME NOT NULL VARCHAR2(30)
TABLESPACE_NAME VARCHAR2(30)
CLUSTER_NAME VARCHAR2(30)
IOT_NAME VARCHAR2(30)
PCT_FREE NUMBER
PCT_USED NUMBER
INI_TRANS NUMBER
MAX_TRANS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
LOGGING VARCHAR2(3)
BACKED_UP VARCHAR2(1)
NUM_ROWS NUMBER
BLOCKS NUMBER
EMPTY_BLOCKS NUMBER
AVG_SPACE NUMBER
CHAIN_CNT NUMBER
AVG_ROW_LEN NUMBER
AVG_SPACE_FREELIST_BLOCKS NUMBER
NUM_FREELIST_BLOCKS NUMBER
DEGREE VARCHAR2(10)
INSTANCES VARCHAR2(10)
CACHE VARCHAR2(5)
TABLE_LOCK VARCHAR2(8)
SAMPLE_SIZE NUMBER
LAST_ANALYZED DATE
PARTITIONED VARCHAR2(3)
IOT_TYPE VARCHAR2(12)
TEMPORARY VARCHAR2(1)
SECONDARY VARCHAR2(1)
NESTED VARCHAR2(3)
BUFFER_POOL VARCHAR2(7)
ROW_MOVEMENT VARCHAR2(8)
GLOBAL_STATS VARCHAR2(3)
USER_STATS VARCHAR2(3)
DURATION VARCHAR2(15)
SKIP_CORRUPT VARCHAR2(8)
MONITORING VARCHAR2(3)
CLUSTER_OWNER VARCHAR2(30)
DEPENDENCIES VARCHAR2(8)
SQL> select owner,table_name,pct_free,pct_used,tablespace_name from dba_tables w
here table_name='EMP';
OWNER TABLE_NAME PCT_FREE
------------------------------ ------------------------------ ----------
PCT_USED TABLESPACE_NAME
---------- ------------------------------
SCOTT EMP 10
40 SYSTEM
SQL> COLUMN TABLE_NAME FORMAT A12;
SQL> COLUMN TABLESPACE_NAME FORMAT A17;
SQL> COLUMN OWNER FORMAT A10;
SQL> select owner,table_name,pct_free,pct_used,tablespace_name from dba_tables w
here table_name='EMP';
OWNER TABLE_NAME PCT_FREE PCT_USED TABLESPACE_NAME
---------- ------------ ---------- ---------- -----------------
SCOTT EMP 10 40 SYSTEM
SQL>
SQL> alter table scott.emp
2 pctfree 20
3 pctused 60;
Table altered.
SQL> select owner,table_name,pct_free,pct_used,tablespace_name from dba_tables w
here table_name='EMP';
OWNER TABLE_NAME PCT_FREE PCT_USED TABLESPACE_NAME
---------- ------------ ---------- ---------- -----------------
SCOTT EMP 20 60 SYSTEM
SQL>
===========================================================================================
move table from one tablespace to another tablespace
===========================================================================================
SQL> alter table scott.emp move tablespace users;
Table altered.
SQL> select owner,table_name,pct_free,pct_used,tablespace_name from dba_tables w
here table_name='EMP';
OWNER TABLE_NAME PCT_FREE PCT_USED TABLESPACE_NAME
---------- ------------ ---------- ---------- -----------------
SCOTT EMP 20 USERS
SQL>
================================================================================
RENAMING A COLUMN
================================================================================
SQL> conn scott/tiger;
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
LOAD TABLE
RAJU_KU TABLE
SALGRADE TABLE
TK_KU TABLE
TT TABLE
8 rows selected.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
SQL> conn /as sysdba;
Connected.
SQL> alter table scott.emp rename column deptno to department_no;
Table altered.
SQL> conn scott/tiger;
Connected.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPARTMENT_NO NUMBER(2)
SQL>
==========================================================================================
DROPPING A COLUMN
===========================================================================================
SQL> conn scott/tiger;
Connected.
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPARTMENT_NO NUMBER(2)
SQL>
SQL> conn /as sysdba;
Connected.
SQL> ALTER TABLE SCOTT.EMP DROP COLUMN JOB CASCADE CONSTRAINTS CHECKPOINT 1000;
Table altered.
SQL> CONN SCOTT/TIGER;
Connected.
SQL> DESC EMP;
Name Null? Type
----------------------------------------- -------- ---------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPARTMENT_NO NUMBER(2)
SQL>
===========================================================================================
TRUNCATING A TABLE
=========================================================================================
SQL> conn scott/tiger;
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
LOAD TABLE
RAJU_KU TABLE
SALGRADE TABLE
TK_KU TABLE
TT TABLE
8 rows selected.
SQL> CONN /AS SYSDBA;
Connected.
SQL> TRUNCATE TABLE SCOTT.EMP;
Table truncated.
SQL> conn scott/tiger;
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
LOAD TABLE
RAJU_KU TABLE
SALGRADE TABLE
TK_KU TABLE
TT TABLE
8 rows selected.
SQL>
SQL> DESC EMP;
Name Null? Type
----------------------------------------- -------- ---------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPARTMENT_NO NUMBER(2)
SQL>
============================================================================================
DROPPING A TABLE
=========================================================================================
SQL> conn scott/tiger;
Connected.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
LOAD TABLE
RAJU_KU TABLE
SALGRADE TABLE
TK_KU TABLE
TT TABLE
8 rows selected.
SQL> CONN /AS SYSDBA;
Connected.
SQL> DROP TABLE SCOTT.DEPT CASCADE CONSTRAINTS;
Table dropped.
SQL> conn scott/tiger;
Connected.
SQL> SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
EMP TABLE
LOAD TABLE
RAJU_KU TABLE
SALGRADE TABLE
TK_KU TABLE
TT TABLE
7 rows selected.
SQL>
No comments:
Post a Comment