本文共 2932 字，大约阅读时间需要 9 分钟。

--查询数据库实例信息select * from v$instance;--查询数据库ID,NAME等信息select * from v$database;--查询当前时间select sysdate from dual;--查询系统所有用户select * from all_users;--查询当前用户的所有对象及列名select * from tab;--查询所有的表空间名select tablespace_name from dba_tablespaces;--select * from dba_tablespaces;select * from dba_tablespaces;--查看所有表空间大小select tablespace_name as 表空间名,(sum(bytes)/1024/1024) as 表空间大小 from dba_data_files group by tablespace_name; --查看未使用的表空间大小select tablespace_name as 表空间名,(sum(bytes)/1024/1024) as 剩余表空间大小  from dba_free_space group by tablespace_name; --新建表空间create tablespace news_tablespace datafile 'F:\app\FUHUI\oradata\JRSCLJT\news_data.dbf' size 200M;--删除表空间（可能需要手动删除物理文件）DROP TABLESPACE news_tablespace INCLUDING CONTENTS  AND DATAFILES CASCADE CONSTRAINTS;--查询所有用户select * from all_users;--查询所有表空间及表select *  from dba_tables;select *  from dba_tables where owner = 'LJT_ADMIN';--查询表空间下的所有表select owner,table_name,tablespace_name from dba_tables where tablespace_name = 'SYSTEM';select owner,table_name,tablespace_name from dba_tables where tablespace_name = 'LJT';select owner,table_name,tablespace_name from dba_tables where tablespace_name = 'LJT_SEMIS';--查询用户的表空间及表名select owner,table_name,tablespace_name  from dba_tables where owner = 'LJT_ADMIN';--查询所有的表空间select tablespace_name from dba_tablespaces;select * from dba_tablespaces;--创建表空间create tablespace LJT        datafile 'F:\app\FUHUI\oradata\JRSCLJT\ljt.dbf'        size 200M;create tablespace LJT_SEMIS        datafile 'F:\app\FUHUI\oradata\JRSCLJT\ljt_semis.dbf'        size 200M;--删除表空间（可能需要手动删除物理文件）--DROP TABLESPACE LJT INCLUDING CONTENTS  AND DATAFILES CASCADE CONSTRAINTS;--创建用户，给定表空间create user LJT_ADMIN identified by LJT_ADMIN default tablespace LJT;--赋予LJT_ADMIN DBA权限grant dba to LJT_ADMIN;--查看用户表空间权限select * from user_ts_quotas;--创建的表的OWNER为当前登录的用户，创建表所在的表空间为用户默认的表空间，如果一个用户拥有多个表空间，在不指定创建表的表空间的情况下默认创建在默认表空间下，--也可以指定表的表空间create table T_STU(       STU_ID CHAR(5) NOT NULL,       STU_NAME VARCHAR2(8) NOT NULL,       CONSTRAINT PK_T_STUT PRIMARY KEY (STU_ID));create table T_STU_TEST(       STU_ID CHAR(5) NOT NULL,       STU_NAME VARCHAR2(8) NOT NULL,       CONSTRAINT PK_T_STU_TEST PRIMARY KEY (STU_ID));create table T_STU_SEMIS(       STU_ID CHAR(5) NOT NULL,       STU_NAME VARCHAR2(8) NOT NULL,       CONSTRAINT T_STU_SEMIS PRIMARY KEY (STU_ID)) tablespace LJT_SEMIS;create table T_SCORE(       EXAM_SCORE NUMBER(5,2),       EXAM_DATE DATE,       AUTOID NUMBER(10) NOT NULL,       STU_ID CHAR(5) NOT NULL,       SUB_ID CHAR(3) NOT NULL,       CONSTRAINT PK_T_SCORE PRIMARY KEY (AUTOID),       CONSTRAINT FK_T_SCORE FOREIGN KEY (STU_ID) REFERENCES T_STU(STU_ID)  );INSERT INTO T_STU VALUES('1','XuLei1');INSERT INTO T_STU VALUES('2','XuLei2');INSERT INTO T_STU VALUES('3','XuLei3');INSERT INTO T_STU VALUES('4','XuLei4');INSERT INTO T_STU VALUES('5','XuLei5');INSERT INTO T_STU VALUES('6','XuLei6');INSERT INTO T_STU VALUES('7','XuLei7');select * from T_STU;

转载地址：http://fcsbb.baihongyu.com/