oracle数据库表的相关操作

1、数据库表结构查询

SELECT t1.Table_Name || chr(13) || t3.comments       AS "表名称及说明",
       --t3.comments                                 AS "表说明",
       t1.Column_Name                                AS "字段名称",
       t1.DATA_TYPE || '(' || t1.DATA_LENGTH || ')'  AS "数据类型",
       t1.NullAble                                   AS "是否为空",
       t2.Comments                                   AS "字段说明",
       t1.Data_Default "默认值"
       --t4.created                                  AS "建表时间"
  FROM cols t1
  LEFT JOIN user_col_comments t2 
         ON t1.Table_name = t2.Table_name
        AND t1.Column_Name = t2.Column_Name
  LEFT JOIN user_tab_comments t3 
         ON t1.Table_name = t3.Table_name
  LEFT JOIN user_objects t4 
         ON t1.table_name = t4.OBJECT_NAME
  WHERE NOT EXISTS (SELECT t4.Object_Name
          FROM User_objects t4
         WHERE t4.Object_Type = 'TABLE'
           AND t4.Temporary = 'Y'
           AND t4.Object_Name = t1.Table_Name)
  ORDER BY t1.Table_Name, t1.Column_ID;

2、虚列的创建

CREATE TABLE EMP
(
   EMPNO     NUMBER(6),
   SAL       NUMBER(8,2),
   COMM      NUMBER(8,2),
   SAL_PACK  GENERATED ALWAYS AS ( SAL + NVL(COMM,0) ) VIRTUAL
)

3、表空间的相关操作

--查看表空间及数据文件
SELECT FILE_NAME,TABLESPACE_NAME,AUTOEXTENSIBLE FROM dba_data_files;
--------------------------------------------------------------------
--表空间设为自动增长
ALTER DATABASE   
    DATAFILE [数据文件] AUTOEXTEND   
    ON NEXT 200M MAXSIZE UNLIMITED
--------------------------------------------------------------------
--查询所有表空间使用情况
SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_BYTES "最大块(M)"
FROM (SELECT TABLESPACE_NAME,
ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,
ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES
FROM SYS.DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) F,
(SELECT DD.TABLESPACE_NAME,
ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB
FROM SYS.DBA_DATA_FILES DD
GROUP BY DD.TABLESPACE_NAME) D
WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME
ORDER BY 4 DESC;
-------------------------------------------------------------------
--创建表空间
select file_name,tablespace_name from dba_data_files;
create tablespace SHOA datafile '/u01/app/oracle/product/11.2.0/dbhome_2/dbs/SHOA.dbf' size 20m;

--创建多个表空间    
CREATE TABLESPACE TS_IMAGEDATA    
    NOLOGGING     
    DATAFILE  'D:"oracle"oradata"DATA01.dbf'   SIZE 20M,     
              'D:"oracle"oradata"DATA02.dbf'   SIZE 20M,     
              'D:"oracle"oradata"DATA03.dbf'   SIZE 20M,     
              'D:"oracle"oradata"DATA04.dbf'   SIZE 20M,     
              'D:"oracle"oradata"DATA05.dbf'   SIZE 20M EXTENT     
    MANAGEMENT LOCAL SEGMENT     
    SPACE MANAGEMENT  AUTO;   

4、用户创建

-- Create the user 
create user DISP
  identified by ""
  default tablespace DISP
  temporary tablespace TEMP
  profile DEFAULT;
-- Grant/Revoke role privileges 
grant connect to DISP with admin option;
grant dba to DISP with admin option;
grant resource to DISP with admin option;
-- Grant/Revoke system privileges 
grant alter any table to DISP with admin option;
grant create any table to DISP with admin option;
grant create any trigger to DISP with admin option;
grant delete any table to DISP with admin option;
grant drop any table to DISP with admin option;
grant insert any table to DISP with admin option;
grant select any table to DISP with admin option;
grant unlimited tablespace to DISP with admin option;
grant update any table to DISP with admin option;

5、获取一个用户下的所有表，索引，存储过程的ddl

SELECT DBMS_METADATA.GET_DDL(U.OBJECT_TYPE, u.object_name)   
FROM USER_OBJECTS u  where U.OBJECT_TYPE IN ('TABLE','INDEX','PROCEDURE');