SQL常用的查询

查看表中字段的属性：
desc table_name

oracle中修改某个字段属性：
ALTER TABLE tablename MODIFY column_name varchar(32)
如：
alter table T_PORT modify MDUMODEL varchar2(32)

 

oracle中修改某个字段名称

alter table T_PORT  rename column NOTE to memoNote 

oracle中给某张表增加一个字段：
ALTER TABLE tablename ADD column_name  varchar2(32)
如：
ALTER TABLE T_DSLAM ADD POPNAME  varchar2(32)

 

oracle中在某张表中删除一个字段：

 ALTER TABLE tablename DROP COILUMN  column_name

如：

 ALTER TABLE test_a DROP COILUMN  name

 

查询本用户所有的表：
select * from user_tab_comments

查询表:
select * from cat;

select * from tab;

select table_name from user_tables;

查询某个视图：

select text from user_views where view_name=upper('&view_name');

查询索引：

select b.table_name, b.uniqueness, b.index_name from user_indexes b order by b.table_type, b.table_name, b.index_name;

某个表上的所有的索引及相关的列：

col column_name for a30

select b.table_name, b.uniqueness, b.index_name, a.column_name from user_ind_columns a, user_indexes b where a.index_name = b.index_name and a.table_name = b.table_name and b.table_name = upper('yours tablename') order by b.table_type, b.table_name,b.index_name, column_position;


查询表空间：
1、先连接到sys用户：
SQL> connect sys
Enter password:as sysdba
Connected.

a、查询oracle用户的默认表空间和临时表空间

select default_tablespace, temporary_tablespace, d.username from dba_users d where d.username='BBASS' group by default_tablespace, temporary_tablespace, d.username

b、查询当前数据库中的所有的临时表空间
select distinct tablespace_name from dba_temp_files;

c、查询所有表空间
select tablespace_name from dba_tablespaces;

d、查看表空间的名称及大小
select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_size from dba_tablespaces t, dba_data_files d where t.tablespace_name = d.tablespace_name group by t.tablespace_name; 

e、查看表空间物理文件的名称及大小
select tablespace_name, file_id, file_name, round(bytes/(1024*1024),0) total_space  from dba_data_files order by tablespace_name;

f、查看表空间的使用情况（空闲大小）
select sum(bytes)/(1024*1024) as free_space,tablespace_name from dba_free_space  group by tablespace_name;

g、查询表空间的总大小、已用大小 、空闲大小
SELECT A.TABLESPACE_NAME,ROUND(A.BYTES/(1024*1024),0) TOTAL,ROUND(B.BYTES/(1024*1024),0) USED, ROUND(C.BYTES/(1024*1024),0) FREE,(B.BYTES*100)/A.BYTES "% USED",(C.BYTES*100)/A.BYTES "% FREE" FROM SYS.SM$TS_AVAIL A,SYS.SM$TS_USED B,SYS.SM$TS_FREE C WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME AND A.TABLESPACE_NAME=C.TABLESPACE_NAME; 
h、查询物理文件、表空间、总大小、空间大小、已用百分比
select b.file_name filepath,b.tablespace_name tablespace,b.bytes/1024/1024 "total(MB)",floor(sum(nvl(a.bytes,0))/1024/1024) "free(MB)",substr((b.bytes-sum(nvl(a.bytes,0)))/(b.bytes)*100,1,5) "used%" from dba_free_space a,dba_data_files b where a.file_id=b.file_id group by b.tablespace_name,b.file_name,b.bytes order by b.tablespace_name;

==============创建索引语句=============================
联合索引：
  CREATE INDEX "BBASS"."RATE_CREATETIME_PORT" ON "BBASS"."T_PORTRATE" ("CREATETIME", "PORTID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "BBASS" ;
单个索引：
  CREATE INDEX "BBASS"."RATE_PORTID" ON "BBASS"."T_PORTRATE" ("PORTID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 NOLOGGING COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "BBASS" ;

或

create index CABLE_CODE on T_CABLENAME (CABLECODE)
  tablespace BBASS
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );