Shulou(Shulou.com)05/31 Report--

This article mainly explains "what common scripts are used in Oracle". The content of the explanation in the article is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "what common scripts are there in Oracle"?

1.linux starts the database script

#! / bin/bash

Export ORACLE_HOSTNAME=emcc.example.com

Echo "Starting the Oracle Database and network listener."

Echo ""

Export ORACLE_HOME=/u01/db/product/dbhome_1

Export PATH=$ORACLE_HOME/bin:$PATH

Export ORACLE_SID=orcl

$ORACLE_HOME/bin/lsnrctl start

$ORACLE_HOME/bin/sqlplus'/ as sysdba' select nls_charset_name (852) from dual

NLS_CHAR

-

ZHS16GBK

Reverse operation:

SQL > select nls_charset_id ('ZHS16GBK') from dual

NLS_CHARSET_ID ('ZHS16GBK')

-

eight hundred and fifty two

-

How to query the character set of dmp files

The dmp file exported with oracle's exp tool also contains the character set information, and the second and third bytes of the dmp file record the character set of the dmp file. If the dmp file is small, such as only a few megabytes or dozens of megabytes, you can open it with UltraEdit (hexadecimal), look at the contents of the second and third bytes, such as 0354, and then use the following SQL to find its corresponding character set:

SQL > select nls_charset_name (to_number ('0354)) from dual

ZHS16GBK

If the dmp file is large, such as more than 2G (which is also the most common case), and it is slow or impossible to open it with a text editor, you can use the following command (on the unix host):

Cat exp.dmp | od-x | head-1 | awk'{print $2 $3}'| cut-c 3-6

Then using the above SQL, you can also get its corresponding character set.

5. View the use of tablespaces

SELECT trunc (SUM (bytes) / (1024 * 1024 * 1024), 2) AS free_space, tablespace_name

FROM dba_free_space

GROUP BY tablespace_name

SELECT a.tablespace_name

Trunc (a.bytes/ (1024 * 1024 * 1024), 2) total

Trunc (b.bytes/ (1024 * 1024 * 1024), 2) used

Trunc (c.bytes/ (1024 * 1024 * 1024), 2) free

Trunc ((b.bytes * 100) / a.bytesMagne2) "% USED"

Trunc ((c.bytes * 100) / a.bytesMagne2) "% 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

SELECT tablespace_name

File_id

File_name

Round (bytes / (1024 * 1024), 0) total_space

FROM dba_data_files

ORDER BY tablespace_name

6. View objects in a tablespace

COLUMN segment_name format a20

Select * from (

SELECT t.owner, t.segment_name,t.segment_type

Trunc ((SUM (bytes) / 1024 + 1024 *), 2) as SIZE_GB

From dba_segments t

WHERE t.tablespace_name IN ('TBS_SD','TBS_SSD')

AND t.segment_type 'LOBSEGMENT'

GROUP BY t.owner,t.segment_name,t.segment_type

ORDER BY SUM (bytes) desc

) where rownum

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.