mails2008的专栏

11。查看数据表的参数信息SELECT partition_name, high_value, high_value_length, tablespace_name, pct_free, pct_used, ini_trans, max_trans, initial_extent, next_extent, min_extent, max_extent, pc

http://www.cnblogs.com/hero4china/articles/base_rule_oracle_procedure.html

原文：http://www.open-open.com/lib/view/open1328069374499.html我们不但会写SQL,还要做到写出性能优良的SQL,今天看了别人的文章，感觉很好、自己又加工了一下， 贴出来与大家分享，如果你还有好的经验 也拿出来分享一下（1） 选择最有效率的表名顺序(只在基于规则的优化器中有效)：ORACLE 的解析器按照从右到左的顺序处

$ lsnrctl start启动监听SQL> drop user username cascade; SQL> SET TIMING ONSQL> show sga;SQL> select component, granule_size from v$sga_dynamic_components;SQL> SELECT sql_text FROM v$sqlarea WHERE exe

--controlfile/*examplaeSTARTUP NOMOUNTCREATE CONTROLFILE REUSE DATABASE "X201006" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOG

########################## 增强的 group by 子句 #########################select [column,] group_function(column)...from table[WHERE condition][GROUP BY [ROLLUP] group_by_expression][HAVING having

select name,checkpoint_change# from v$database;select name,checkpoint_change# from v$datafile;select name,last_change# from v$datafile;select name,checkpoint_change# from v$datafile_header;select

###### 分析日志文件 logmnr ##############1) 在 init.ora 中 set utl_file_dir 参数2) 重新启动 oracle3) create 目录文件desc dbms_logmnr_d;dbms_logmnr_d.build;4) 加入日志文件 add/remove log filedhms_logmnr.add_logf

24．查询表空间使用情况select a.tablespace_name, round(a.abytes / 1024 / 1024) totalmb, round((a.abytes - f.fbytes) / 1024 / 1024) usedmb, round(f.fbytes / 1024 / 1024) freemb, (1 -

1、查看表空间的名称及大小 select t.tablespace_name, round(sum(bytes/(1024*1024)),0) ts_sizefrom dba_tablespaces t, dba_data_files dwhere t.tablespace_name = d.tablespace_namegroup by t.tablespace_name; 2、查

--a list of datafilesSELECT * FROM V$DATAFILE;--a listing of datafiles along with their associated tablespaces:SELECT t.NAME "Tablespace", f.NAME "Datafile" FROM V$TABLESPACE t, V$DATAFILE f WH

##################### CREATE/ALTER TABLE #######################alter table table_name drop column column_name ;---drop columnalter table table_name set unused (col1,col2,...);---- 设置列无效，这个比较快。a

########### Basic SQL SELECT ################select col_name as col_alias from table_name ;select col_name from table_name where col1 like '_o%'; ----'_' 匹配单个字符/* 使用字符函数 ( 右边截取 , 字段中包含某个字符 , 左边填

################## managing password security and resources ####################alter user user_name account unlock/open;---- 锁定 / 打开用户 ;alter user user_name password expire;--- 设定口令到期/* 建立口令配置文

########## Managing Tables ###########/*char type maxlen=2000;varchar2 type maxlen=4000 bytesrowid 是 18 位的 64 进制字符串 (10 个 bytes 80 bits)rowid 组成 : object#( 对象号 )--32bits,6 位rfile#( 相对文件号 )--10

===========================================================######### 创建数据库----look $ORACLE_HOME/rdbms/admin/buildall.sql ############# create database db01maxlogfiles 10maxdatafiles 1024maxinsta

declares_row number;username varchar2(200);vsql varchar2(2000);t_count number;x array;cursor cur is select * from dba_users where rownum<200;begin select count(*) into s_row from all_tables w