I have collected some frequently used SQL query statements (occasionally updated)

****************
Tablespace
****************

# View the tablespace creation process
Sets long 9999
Select dbms_metadata.get_ddl ('tablespace', 'example ') from dual;

# Query the usage of a single tablespace
Select sysdate, A. tablespace_name "tablespace name ",
Round (total/1024/1024/1024, 3) "tablespace size (GB )",
Round (Free/1024/1024/1024, 3) "remaining tablespace size (GB )",
Round (total-free)/1024/1024/1024, 3) "tablespace size (GB )",
Round (total-free)/total, 4) * 100 "usage %"
From (select tablespace_name, sum (bytes) free
From dba_free_space
Group by tablespace_name),
(Select tablespace_name, sum (bytes) Total
From dba_data_files
Group by tablespace_name) B
Where a. tablespace_name = B. tablespace_name
Order by 4 DESC;

*****
ASM
*****

Begin
Dbms_file_transfer.copy_file (
Source_directory_object => '$ s_dir ',
Source_file_name => '$1 ',
Destination_directory_object => '$ d_dir ',
Destination_file_name => '$1 ');
End;

Copy the ASM file to a local device.
Now there are more and more users using ASM, and the most inconvenient thing about ASM is that all files are stored in the system managed by Oracle. It is troublesome to copy a file. It may depend on RMAN. We can use the following method:

Log on to the Oracle Database Server
1 create or replace directory source_dir as '+ datadg/sfoss/onlinelog /';
2 create or replace directory oracle_dest as '/tmp/oralog/dest ';
3 begin
Export (source_directory_object => 'source _ dir', source_file_name => 'group _ 1.257.695065683 ', destination_directory_object => 'oracle _ dest', destination_file_name => 'redo _ 1. log ');
End;
/
In this way, we will not be afraid to copy any files from ASM in the future.

**************************
Nls_charactercet
**************************

# Viewing database character sets
SQL> select property_name, property_value from database_properties where property_name = 'nls _ characterset ';

# Querying user language environment variables and database character sets
SQL> select userenv ('language') from dual;

A Chinese Character in GBK occupies 2 characters, while a Chinese character in utf8 occupies 3 characters. Therefore, the table fields imported from GBK to utf8 may be too long, leading to errors.
The sqlplus environment variable must also be set to utf8
$ Export nls_lang = "simplified chinese_china.al32utf8"

Impdp and "tabble_exists_action = truncate"

***********************
Nls_date_format
***********************
# Modifying the system Date and Time Format
1. Add the following statement to. bash_profile: Export nls_date_format = 'yyyy-mm-dd hh24: MI: ss'
(Note: to take effect, you must add the following statement before: Export nls_lang = american_america.zhs16gbk or export nls_lang = American)
2. Add SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: MI: ss' to glogin. SQL of sqlplus'
3. Modify the date format of the current session: SQL> alter session set nls_date_format = 'yyyy-mm-dd hh24: MI: ss'
4. to modify the database parameters, restart and take effect: SQL> alter system set nls_date_format = 'yyyy-mm-dd hh24: MI: ss' scope = spfile;

***********
V $ session
***********
SQL> select Sid, serial #, username, status
From v $ session
Where sid in (select blocking_session from V $ session );

***********************
V $ recovery_file_dest
***********************
SQL> select name, floor (space_limit/1024/1024) "size MB ",
SEIL (space_used/1024/1024) "used MB"
From v $ recovery_file_dest order by name;

**********************
V $ session_longops
**********************
SQL> select Sid, serial #, context, SOFAR, totalwork, round (SOFAR/totalwork * 100,2) "% _ complete" from V $ session_longops where opname like 'rman % ';

Bytes -------------------------------------------------------------------------------------------------------

By mongoon8219 chinaunix blog: http://blog.chinaunix.net/uid/24612962.html

Original content. For more information, see the link. Thank you!

Http://blog.csdn.net/aaron8219/article/details/10056055