Notes about Oracle

1. View a table take up the tablespace size: SELECT bytes/1024/1024| | ' MB ', A.*from user_segments a WHERE a.segment_name = ' tl_sp_nonreal_list_201505 '; 103 Query Error single table space SELECT segment_name,sum (bytes)/1024/1024 from user_segments a WHERE a.tablespace_name= ' Zhjs_list_error ' and a.segment_type= ' TABLE PARTITION ' and a.bytes<> ' 8388608 ' and a.segment_name not like '%200906 ' GROUP by a.segment _name

2. View the actual size of a tablespace: SELECT sum (bytes)/1024/1024| | ' MB ' from user_segments a WHERE a.tablespace_name = ' sett_analyse_list_201505 ';

3. View a table space corresponding data file: SELECT * from Dba_data_files a WHERE a.tablespace_name = ' sett_analyse_list_201505 ';

4, the total size of the query tablespace, and the size of select a.tablespace_name,a.bytes/1024/1024 "Sum MB", (a.bytes-b.bytes)/1024/1024 "used MB", b.bytes/1024/1024 "Free MB", Round (((a.bytes-b.bytes)/a.bytes) *100,2) ' percent_used ' from (select Tablespace_name,sum (bytes) bytes from Dba_data_files GROUP by Tablespace_name A, (select Tablespace_name,sum (bytes) bytes,max (bytes) larges T from Dba_free_space Group by Tablespace_name) b where A.tablespace_name=b.tablespace_name order by ((a.bytes-b.bytes)/A . bytes) desc;

5, create a large data volume of the table, occupy the table space size several g,delete this table of data, at this time only the query found very slow, the query table data is empty, in fact, its table space is not released for the sake of. Execute ALTER TABLE jk_test move or ALTER TABLE jk_test move storage (initial 64k) or ALTER TABLE Jk_test deallocate unused KEEP 0 or ALTER TABLE jk_test shrink space. When this is done, the index is lost, which is the ability to rebuild the failed index select index_name,table_name,tablespace_name,index_type,status  from dba_indexes   where table_owner= ' Sett_analyse '; According to the status value, rebuilding the invalid is good. Alter OWNER.INDEX_NAME rebuild; , the query is very small. Note: The Drop...purge and truncate tables, the space they occupy will be released, the reason is not to enter the Recycle Bin, (separate drop will go into the Recycle Bin) data can be retrieved through the flashback: The specific method is as follows: Select Original_name,dropscn from RecycleBin where lower (original_name) = ' js_stat_sp_report_sec_1009 '; Flashback table Jianbiao_beifen to before drop;

6, whether delete or truncate the corresponding data file size will not change, if you want to change the size of the data file occupies space executable: ALTER DATABASE datafile ' filename ' resize 8g, redefine file size Additions to purge: Purge tablespace tablespace_name--emptying the tablespace Recycle Bin purge tablespace tablespace_name user username--the specified object of the Recycle Bin for the specified table Space drop table table_name purge--permanently deleted, cannot be restored with flashback

7. Move table Space partition table: Altrer table tl_y_toll_xjjs_list_201603 move partition d_21 tablespace zhjs_dsyy; Non-partitioned table: ALTER TABLE tl_y_toll_xjjs_list_201603 move tablespace zhjs_dsyy;

8. Query which table is indexed: SELECT * from User_indexes; The field that determines which table is indexed: SELECT * from User_ind_columns;

9. If you are adding data files to an existing tablespace, use: Alter tablespace table space name add datafile ' data file name path ' size 50M; If a new table space is created: create tablespace table space name datafile ' data file name path ' size 50M;

Out of the library: p_expdb_collect_list

Move_list_data_to_szx

GDB Trace command: GDB process name Process number B *.cpp: line number: Break point in which line P variable: print out the value of the variable N: Next S: Enter method exit exit

ppstdcdr--standard format wjjsct_ngn.h inherit cdrbase.sh #include "cdrbase.h"

After TRUNCATE TABLE, it is possible that the tablespace is still not released and can use the following statement: ALTER TABLE name deallocate UNUSED KEEP 0;  Note that if you do not add keep 0, the tablespace will not be freed. For example: ALTER TABLE F_MINUTE_TD_NET_FHO_B7 deallocate UNUSED KEEP 0;  Or: TRUNCATE table (SCHEMA) table_name DROP (reuse) storage to release the tablespace. For example: Truncate TABLE test1 DROP STORAGE; Third, query partition table which partition: Query partition table, you can query in User_tab_partitions. Example: SELECT ' ALTER TABLE ' | | T.table_name | | ' truncate PARTITION ' | | T.partition_name from User_tab_partitions t where t.table_name like ' f_% ' query each partition take up table space

SELECT Tablespace_name,partition_name,to_char (SUM (BYTES)/(1024*1024), ' 999g999d999 ') from Dba_extents WHERE Segme Nt_name= ' tl_err_d_sms ' and segment_type like ' table% ' GROUP by Tablespace_name,partition_name;

Clears the partition data for the specified partition table: ALTER TABLE name TRUNCATE partition partition name; Iv. clear space occupied by partition table: ALTER TABLE name DROP partition partition name; For example: ALTER TABLE F_hour_td_net_mpvoice DROP partition p_09121913;

   --View the size of a single table taking up physical space---Query partition table SELECT a.* from (  select T.owner,t.tablespace_name,t.segment_name, SUM (t.bytes)/1024/1024/1024 total    from Sys.sys_dba_segs t   WHERE T.partition_name is isn't NULL   an d   t.segment_name not like '%$% '/*  and   lower (t.owner) = ' zhjs_app ' */  and   UPPER (T.tablespace_name) = UPPER (' zhjs_zdjs_0510 ')   GROUP by T.owner,t.tablespace_name,t.segment_name) a/*where A.total >= 2.5 * * ORDER by a.total DESC; --Querying non-partitioned tables SELECT a.* from (  select T.owner,t.tablespace_name,t.segment_name,sum (t.bytes)/1024/1024/1024 Total & nbsp;  from Sys.sys_dba_segs t   WHERE T.partition_name are NULL   and   t.segment_name not like ' %$% '/*  and   lower (t.owner) = ' zhjs_app ' * *   and   UPPER (t.tablespace_name) = UPPER (' ZHJ s_zdjs_0510 ')   GROUP by T.owner,t.tablespace_name,t.segment_name) a/*where a.total >= 2.5 */ORDER by A.total DESC;

TRUNCATE TABLE Ter_510.bss_zszh_coupon_backup; TRUNCATE TABLE Ter_510.tl_error_list_else; TRUNCATE TABLE Ter_510.tl_sett_list_his; TRUNCATE TABLE TER_510.BSS_TD_STORE_INOUT_CHANGE_BKP; TRUNCATE TABLE ter_510.bss_channel_temp;

Notes about Oracle