Shulou(Shulou.com)06/01 Report--

V_sql_time: = lpad (trunc (ceil ((v_beg_time-sysdate) * 24 * 60 * 60) / 3600), 2 recordings 0') |':'| |

Lpad (trunc (mod (ceil ((v_beg_time-sysdate) * 24 * 60 * 60), 3600) / 60), 2memorials 0') | |':'| |

Lpad (round (mod ((v_beg_time-sysdate) * 24 * 60 * 60), 60), 2), 2)

Sp_etl_run_log (vSecretsqlbadge, vascal, sqlforth, time, and department, step, step, and,

Oracle the default is the number of days when the two times are subtracted.

Oracle two times subtract the default is the number of days * 24 is the number of hours of difference

Oracle two times subtract the default is the number of days * 24: 60 is the difference in minutes

Oracle two times subtract the default is the number of days * 24: 60: 60: the difference in seconds

-- MONTHS_BETWEEN (date2,date1)

Give the month of the date2-date1

SQL > select months_between ('19-12-1999 mon_between from dual

MON_BETWEEN

-

nine

SQL > select months_between (to_date ('2000.05.20mm. Dd'), to_date (' 2005.20mm. Dd')) mon_betw from dual

MON_BETW

-

-60

Oracle calculation time difference expression

-- get the number of seconds between the two times

Select ceil ((To_date ('2008-05-02 00 yyyy-mm-dd hh34-mi-ss')-To_date (' 2008-04-30 23 59 To_date 59', 'yyyy-mm-dd hh34-mi-ss')) * 24 * 60 * 60 * 1000) FROM DUAL

/ *

The number of seconds with a difference

-

86401000

1 row selected

, /

-- obtain the number of seconds between two times

Select ceil ((To_date ('2008-05-02 00 yyyy-mm-dd hh34-mi-ss')-To_date (' 2008-04-30 23 59 To_date 59', 'yyyy-mm-dd hh34-mi-ss')) * 24 * 60 * 60) FROM DUAL

/ *

Number of seconds difference

-

86401

1 row selected

, /

-- get the number of minutes between the two times

Select ceil (To_date ('2008-05-02 00 yyyy-mm-dd hh34-mi-ss')-To_date (' 2008-04-30 2323 yyyy-mm-dd hh34-mi-ss' 59', 'yyyy-mm-dd hh34-mi-ss') * 24 * 60) the difference in minutes FROM DUAL

/ *

The difference of minutes

-

1441

1 row selected

, /

-- get the number of hours between the two times

Select ceil ((To_date ('2008-05-02 00 yyyy-mm-dd hh34-mi-ss')-To_date (' 2008-04-30 23 59 To_date 59', 'yyyy-mm-dd hh34-mi-ss')) * 24) difference in hours FROM DUAL

/ *

Difference number of hours

-

twenty-five

1 row selected

, /

-- get the number of days between the two times

Select ceil ((To_date ('2008-05-02 00 yyyy-mm-dd hh34-mi-ss')-To_date (' 2008-04-30 23 59 yyyy-mm-dd hh34-mi-ss' 59', 'yyyy-mm-dd hh34-mi-ss')

/ *

Difference of days

-

two

1 row selected

, /

-

Note: the number of days can be reduced directly by 2 dates, which is more convenient.

-

-- get the month difference between two times

Select (EXTRACT (year FROM to_date ('2009-05-01))-EXTRACT (year FROM to_date (' 2008-04-30)) * 12 +

EXTRACT (month FROM to_date ('2008-05-01))-EXTRACT (month FROM to_date (' 2008-04-30)) months

From dual

/ *

MONTHS

-

thirteen

1 row selected

, /

-

Note: you can use the months_between function for more convenience.

-

-- get the year difference between the two times

Select EXTRACT (year FROM to_date ('2009-05-01))-EXTRACT (year FROM to_date (' 2008-04-30)) years from dual

/ *

YEARS

-

one

Select sysdate,add_months (sysdate,12) from dual;-plus 1 year

Select sysdate,add_months (sysdate,1) from dual;-plus one month

Select sysdate,TO_CHAR (sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual;-plus 1 week

Select sysdate,TO_CHAR (sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual;-plus 1 day

Select sysdate,TO_CHAR (sysdate+1/24,'yyyy-mm-dd HH24:MI:SS') from dual;-add 1 hour

Select sysdate,TO_CHAR (sysdate+1/24/60,'yyyy-mm-dd HH23:MI:SS') from dual;-add 1 minute

Select sysdate,TO_CHAR (sysdate+1/24/60/60,'yyyy-mm-dd HH23:MI:SS') from dual;-add 1 second

Select sysdate+7 from dual;-plus 7 days

Select dbms_lob.substr (clob_field), c. From.

Settings for spool

SET echo off

SET feedback off

SET pagesize 0

SET termout off

SET linesize 10000;

SET heading off

SET feedback off;

Spool F:\ dir\ dd.csv

Select 'TRS_CREATE_TIME,TRANS_ID,PAYMENT_ID,PAY_TYPE,NEW_PAY_CHANNEL,BANK_ID,BANK_NAME,CHANNEL_ID,RSP_CODE,RSP_DESC,BANK_RSP_CODE,BANK_RSP_DESC,PRODUCT_CODE,SUB_TRANS_TYPE,CUSTOMER_ID,MERCHANT_NO,MERCHANT_NAME' from dual

Select xx | |','| | yy

From zz

Where qq

Spool off

SET echo off-when a sql script is executed with the start command, whether the SQL statement that is being executed in the script is displayed

SET feedback off-whether to echo the number of records processed by this sql command. Default is on.

SET heading off-whether to display column headings. Default is on.

SET pagesize 50000-sets the number of lines per page. The default is 14. When the value is set to 0, the output is one page and the column header is not displayed. To avoid paging, we can usually set it to 0.

SET termout off-whether the output is displayed on the screen, or the execution result of the command in the script. The default is on.

SET trimout on;-removes extra spaces after each line of standard output, defaults to off

SET timing off-displays the execution time spent on each sql statement

SET trimspool on-removes trailing spaces for each line of spool output, defaults to off

SET NULL text-when displayed, replace the null value with the text value

SET serveroutput off-- whether to display the information output with DBMS_OUTPUT.PUT_LINE package;-- when writing stored procedures, most of the necessary information will be output.

SET newpage none-sets the separation between pages {1 | n | NONE}; when the value is 0, there is a small black box at the beginning of each page; when the value is n, there are n blank lines between pages; when it is none, there is no gap between pages

SET linesize 1000-sets the number of characters that a line can hold {80 | n}. If the output is greater than the number of characters that a line can hold, the line will be displayed. According to the actual character setting, the export speed is slow if it is too large.

SET wrap on-when the output line length is greater than the set line length (set with the set linesize n command); when the value is on, the extra characters will be displayed on another line, otherwise the extra characters will be removed and will not be displayed

SET verify off-whether to display statements before and after substitution variables are replaced

SET colsep'';-- Domain output delimiter

-

Recommended format for exporting text data:

SQL*PLUS environment settings--

SET echo off-when a sql script is executed with the start command, the SQL statements that are being executed in the script are not displayed

SET heading off-the name of the field is not displayed

SET pagesize 0-sets the number of lines per page of output, which can be set to 0 to avoid paging. When set to 0, the output is one page and the column headings are not displayed with the functionality included in SET heading off.

SET newpage none-there is no separation between pages

SET trimout on-remove extra spaces after each line of standard output

SET trimspool on-removes trailing spaces for each line of spool output

SET linesize 1000-sets the number of characters a line can hold

SET feedback off-whether to echo the number of records processed by this sql command. Default is on.

Export the common format of text data when working:

SET echo off

SET pagesize 0

SET feedback off

SET trimout on

SET trimspool on

SET linesize 1000

Spool path + file name

SQL code to be executed

Spool off

Note: linesize should be slightly larger to avoid data truncation, it should be used in conjunction with the corresponding trimspool to prevent the exported text from having too many trailing spaces. However, if the linesize setting is too large, it will greatly slow down the export speed. In addition, it is best not to use PLSQL export under WINDOWS, because the speed is relatively slow. Use the SQLPLUS command under COMMEND to minimize the window execution directly.

Fields that contain many carriage return newline characters should be filtered to form a more regular text file. In general, we use the SPOOL method to export tables in the database as text files in two ways, such as the following:

Method 1: script in the following format

Set colsep'|'--setting | column delimiter

Set trimspool on

Set linesize 120

Set pagesize 2000

Set newpage 1

Set heading off

Set term off

Set num 18

Set feedback off

Spool path + file name

Select * from tablename

Spool off

Method 2: use the following script

Set trimspool on

Set linesize 120

Set pagesize 2000

Set newpage 1

Set heading off

Set term off

Spool path + file name

Select col1 | |','| | col2 | |','| | col3 | |','| | col4 | |'..' From tablename

Spool off

Compare the above methods, that is, method 1 uses a set delimiter and then sqlplus uses the set separator to divide the field, and method 2 splices the separator in the SELECT statement, that is, manually controls the output format.

In practice, it is found that the data derived from method 1 has great uncertainty, and the possibility of error when the data derived from this method is imported by sqlldr is more than 95%, especially for a large number of data tables, such as 1 million records, and the exported data files are extremely large.

On the other hand, the format of the data file exported by method 2 is very regular, and the size of the data file may be about 1x4 of method 1. When the data files exported by this method are imported by sqlldr, the possibility of error is very small, and basically all of them can be imported successfully.

Some netizens directly edit the script on the linux server:

Export data from the * vaccounsendsmstask table to text (database Oracle 9i operating system SUSE LINUX Enterprise Server 9)

The spool_test.sh script is as follows:

#! / bin/sh

DB_USER=zxdbm_ismp # DB USER

DB_PWD=zxin_smap # DB PASSWORD

DB_SERV=zx10_40_43_133 # DB SERVICE NAME

Sqlplus-s $DB_USER/$DB_PWD@$DB_SERV$set trimspool on

Set linesize 120

Set pagesize 2000

Set newpage 1

Set heading off

Set term off

Spool promt.txt

Select taskindex | |'|'| | commonindex | |'| |'| tasktype | |'| | to_number (to_char (sysdate,'YYYYMMDD')) from * v_sendsms_task

Spool off

EOF

Sp_test.txt is generated after executing. / spool_test.sh, as follows:

83 | 115 | 1 | 20080307

85 | 115 | 11 | 20080307

86 | 115 | 10 | 20080307

84 | 115 | 2 | 20080307

6 | 5 | 14 | 20080307

7 | 5 | 12 | 20080307

9 | 5 | 15 | 20080307

Note: in the above self-test example, the target generation file promt.txt in spool promt.txt calls the spool function of Oracle in the shell script in the HP-UNX environment. If the above logic code is encapsulated as a function, and then the function is called, the promt.txt file will not be generated in the shell script. The logic code can only be executed directly, and after encapsulation, the spool function becomes invalid.

For promt.txt in the relative path, when the methods in the following 2 are executed in the shell environment, the two can only be chosen, and the spool function will become invalid if the two coexist. Suppose the path generated by the promt.txt file is: / home/zxin10/zhuo/batchoperate/spoolfile

Mode [1]

Echo "start spool in shell.."

Sqlplus-s zxdbm_ismp/zxin_smap$set pagesize 0

Set echo off feed off term off heading off trims off

Set colsep'|'

Set trimspool on

Set linesize 10000

Set trimspool on

Set linesize 120

Set newpage 1

Spool / home/zxin10/zhuo/batchoperate/spoolfile/promt.txt

Select batchindex | |'|'| | productid | |'| |'| | contentid | |'| |'| optype | |'| | uploadfile from zxdbm_700.s700_batch_operation where status=1

Spool off

EOF

Echo "end.."

Mode [2]

Echo "start spool in shell.."

Cd / home/zxin10/zhuo/batchoperate/spoolfile

Sqlplus-s zxdbm_ismp/zxin_smap$set pagesize 0

Set echo off feed off term off heading off trims off

Set colsep'|'

Set trimspool on

Set linesize 10000

Set trimspool on

Set linesize 120

Set newpage 1

Spool promt.txt

Select batchindex | |'|'| | productid | |'| |'| | contentid | |'| |'| optype | |'| | uploadfile from zxdbm_700.s700_batch_operation where status=1

Spool off

EOF

Echo "end.."

Therefore, in practice, it is recommended that we use method two to manually control the format of spool files, which can reduce the possibility of errors and avoid a lot of detours.

1. Query two commands of the current SCN of the system

SQL > select current_scn from v$database

SQL > select dbms_flashback.get_system_change_number from dual;-- 1781893

two。 Database global-checkpoint SCN, in the control file.

SYS@bys1 > select dbid,checkpoint_change# from v$database

DBID CHECKPOINT_CHANGE#

3957527513 1753478

3. The current data file SCN. In the control file. Checkpoint scn, which represents the SCN of the last checkpoint operation performed by the data file

SQL > select name,checkpoint_change# from v$datafile

NAME CHECKPOINT_CHANGE#

/ u01/oradata/bys1/system01.dbf 1753478

/ u01/oradata/bys1/sysaux01.dbf 1753478

SQL > select file#,name,checkpoint_change#,to_char (checkpoint_time,'yyyy-mm-dd hh34:mi:ss') cptime from v$datafile

FILE# NAME CHECKPOINT_CHANGE# CPTIME

1 / u01/oradata/bys1/system01.dbf 1753478 2013-09-11 23:00:52

2 / u01/oradata/bys1/sysaux01.dbf 1753478 2013-09-11 23:00:52

4. Query the data file header SCN, in the data file header

SQL > select name,checkpoint_change# from v$datafile_header

NAME CHECKPOINT_CHANGE#

/ u01/app/oracle/oradata/bys001/system01.dbf 1198546

5. The data file ends SCN, in the control file.

LAST_CHANGE#, if the database is not shut down properly, the value is NULL. Normal shutdown is the SCN when it is closed.

Instance recovery is to check this parameter when you open the database to determine if it needs to be restored.

The LAST_CHANGE# is also NULL when the database OPEN, because you are not sure how much SCN is turned off.

SQL > select name,last_change# from v$datafile

NAME LAST_CHANGE#

/ u01/app/oracle/oradata/bys001/system01.dbf

6. SCN range contained in the log

SQL > select GROUP#,sequence#,STATUS,FIRST_CHANGE#,to_char (FIRST_TIME,'yyyy/mm/dd:hh34:mi:ss') time from V$log

GROUP# SEQUENCE# STATUS FIRST_CHANGE# TIME

1 49 INACTIVE 1713778 2013/09/11:10:10:08

2 50 INACTIVE 1744790 2013/09/11:21:34:23

3 51 CURRENT 1753478 2013/09/11:23:00:52

Query the execution status of a stored procedure? Is it running?

SELECT'('| s.sid | |') -'| | username | |'is running'| | o.kglnaobj as sessusr,--'('| | s.sid | |') -'| username AS "(session)-username"

P.KGLPNMOD as proc_state,-- 2 means that the stored procedure is executing

O.kglhdnsp

FROM V$SESSION s

Sys.x$kglob o

Sys.x$kglpn p

WHERE upper (o.kglnaobj) LIKE upper ('% pumped insertinsertblr%')

AND p.kglpnhdl = o.kglhdadr

AND s.SADDR = p.kglpnuse

And o.KGLHDNSP=1

And p.KGLPNMOD=2

Running the above sql statement as a sys user can only find out that the system is currently connected

This can only find out the user name of the current connection to the Oracle database!

In fact, it is still impossible to find out the state of the stored procedure p_insert_tbl!

-- query various information about stored procedures

Select *

From all_source t

Where / * lower (t.text) like'% pay_succs_rate%' and*/ t.nameplate

Select *

From all_dependencies t

Where t. Nameplate, SPV, RPT, CREDITP, PAY, info, SUBDIV.

Select * from V$SQL_BIND_CAPTURE

Select * from all_procedures

Select * from user_arguments where procedure_name='SP_TRUNCAT'

Now we need to extract stored procedure information, because the data source is oracle8i, but there is no view such as user_procedues in 8 to provide stored procedure information, so some information about stored procedures can only be found in the view of user_arguments.

But user_arguments does not include all stored procedures, such as those with no parameters, and if the stored procedure is in a package, where can I find the information about the stored procedure?

-- [] v$process the information provided by this view is all about the oracle service process, and there is no information related to the client program.

There are two types of service processes, one is background, the other is dedicate/shared server

Pid, serial#, this is the PID assigned by oracle.

Spid, this is the pid of the operating system.

Program this is the name of the operating system process corresponding to the service process

-- [] v$session this view mainly provides information about a database connect

Mainly the information on the client side

Which machine is machine on?

What terminal does terminal use?

Who is the user of osuser operating system?

What client programs does program use, such as TOAD

The process number assigned to TOAD by the process operating system

What time is logon_time?

Under what oracle account does username log in?

What type of SQL command did command execute?

Sql_hash_value SQL statement information

There is some information on the server side:

Paddr is the addr of the server process in v$process

The server server is dedicate/shared

There is also some other information that can be understood as information shared by client/server, mainly for this session.

Create or replace function func_get_json_extract (pcharjson varchar2,-- json field value

Pcharsub varchar2,-- the map pair to be taken

Pnum number default 1,-- how many times does it appear?

Psepchar varchar2 default'"',-value quotation marks

Psepfield varchar2 default':'--key delimiter

)

Return varchar2 IS

V_charjson varchar2 (4000): = pcharjson

V_charsub varchar2 (4000): = psepchar | | pcharsub | | psepchar | | psepfield | | psepchar

V_subval varchar2 (4000)

Begin

Select substr (v_charjson

I.poscod+i.len

Posend- (poscod+len)) into v_subval

From (select instr (vicious charjson pnum) as poscod

Length (v_charsub) as len

Instr (vandalism charjsonmum) + length (v_charsub) as posbeg

Instr (vandalism charjson v_charsub'', instr (vandalism charjson record1) + length (v_charsub), 1) as posend

V_charjson

From dual t) I

Return v_subval

EXCEPTION

WHEN others THEN

DBMS_OUTPUT.PUT_LINE ('Please check the parameters and try again')

End

Alter session set nls_date_language='american'; modify the command so that the string format can be successfully imported into the date format

Select to_char (sysdate,'yyyy "mm" month "dd" day ") from dual

Select to_char (sysdate,'month','nls_date_language=American') from dual

Security-permissions-Oracle comes with users

The difference between ① Sysdba and dba

When you create a new one, you are given the system permission sysdba.

You cannot log in to plsql in normal status when using this user later.

Because the user is not given permission to create a session. Just log in with sys

Grant connect,resource to xwm can empower users.

Dba is a kind of object in Oracle. Like User, Role is a physical object that actually exists in Oracle, while sysdba refers to a conceptual operation object that does not exist in Oracle data.

So these two concepts are completely different.

Dba is a role that corresponds to a collection of operation permissions on objects in an Oracle instance, while sysdba is a conceptual role that is a login authentication identity.

SYSDBA is not a permission. When a user logs in to the database as SYSDBA, the login user will become SYS.

Sysdba identity login can open, close the database, create SPFILE, restore the database and so on, which can not be achieved by the DBA role.

Oracle has its own users

Some of the following accounts exist for administrative purposes, some for the additional functionality of the database, and some for example purposes. If you enable some partial features, you will automatically add some users, such as tag security, database safe, and so on.

Each of these accounts you Google can tell a story.

The account is not directly related to the access table, but the permission is. Take SELECT ANY TABLE (view any table) as an example to see who can view any table:

SQL > select grantee from dba_sys_privswhere privilege='SELECT ANY TABLE'

Accounts in oracle fall into two categories:

One is the required account.

One is the account that stores various applications.

[] user-specified / CHANGE_ON_INSTALL during SYS installation

Description: database management account / perform database management tasks, instance data dictionaries are all under SYS / super user, boss

Recreation Script:$ORACLE_HOME/rdbms/admin/sql.bsq.Recover from backup or recreate the database.

Safe To Remove:No

Purpose:Anaccount used to perform. Database administration tasks. Password is created atinstallation or database creation time.

[] SYSMAN OEM_TEMP/manager

Description: enterprise Manager account

Recreation Script:Createdas part of the dbconsole or Enterprise Manager build.

Safe To Remove:Yes

Purpose:Theaccount used to perform. Oracle Enterprise Manager database administrationtasks. The SYS and SYSTEM accounts can also perform. These tasks. Password iscreated at installation or database creation time.

[] user specified during SYSTEM installation

Description: database administration account / used to perform database administration tasks, a small number of instance objects / permissions are also very high, but do not have the following permissions: ALTER DATABASE LINK/ ALTERPUBLIC DATABASE LINK/ EXEMPT ACCESSPOLICY/ EXEMPT IDENTITY POLICY/ SYSDBA/ SYSOPER. SYSDBA and SYSOPER can also start and shut down the database instance, so SYSTEM certainly cannot start and shut down the database instance.

Recreation Script:$ORACLE_HOME/rdbms/admin/sql.bsq.Recover from backup or recreate the database.

Safe To Remove:No

Purpose:A defaultgeneric database administrator account for Oracle databases. For productionsystems, Oracle recommends creating individual database administrator accountsand not using the generic SYSTEM account for database administrationoperations. Password is created at installation or database creation time.

[] ANONYMOUS ANONYMOUS

Description: anonymous user account to access http / account used to access the ORACLE XML DB knowledge base.

Recreation Script:$ORACLE_HOME/rdbms/admin/catqm.sql

Safe To Remove:Yes

Purpose:Accountthat allows HTTP access to Oracle XML DB. It is used in place of theAPEX_PUBLIC_USER account when the Embedded PL/SQL Gateway (EPG) is installed inthe database. EPG is a Web server that can be used with Oracle Database. Itprovides the necessary infrastructure to create dynamic applications. See alsoXDB.

[] CTXSYS CTXSYS

Description: interMedia Text user, a MEDIATEXT user with CONNECT\ RESOURCE\ DBA permissions

Recreation Script:$ORACLE_HOME/ctx/admin/ctxsys.sql

Safe To Remove:Yes

Purpose:Theaccount used to administer Oracle Text. Oracle Text enables the building oftext query applications and document classification applications. It providesindexing, word and theme searching, and viewing capabilities for text.

[] DBSNMP DBSNMP

Description: directory integration platform user / role with NNECT\ RESOUCEHE\ SNMPAGENT permissions, this user and role can be deleted with CATNSMP.SQL script

Recreation Script:$ORACLE_HOME/rdbms/admin/catsnmp.sql

Safe To Remove:Yes- run $ORACLE_HOME/rdbms/admin/catnsnmp.sql

Purpose:Theaccount used by the Management Agent component of Oracle Enterprise Manager tomonitor and manage the database. Password is created at installation ordatabase creation time.

[] DIP DIP

Description: account of directory integration platform (DIRECTORY INTEGERATION PLATFORM)

Recreation Script:$ORACLE_HOME/rdbms/admin/catdip.sql

Safe To Remove:Yes

Purpose:Theaccount used by the Directory Integration Platform. (DIP) to synchronize thechanges in Oracle Internet Directory with the applications in the database.

[] EXFSYS EXFSYS

Description: expression filter account (EXPRESSIO FILTER)

Recreation Script:$ORACLE_HOME/rdbms/admin/exfsys.sql

Safe To Remove:Yes

Purpose:Theaccount used internally to access the EXFSYS schema, which is associated withthe Rules Manager and Expression Filter feature. This feature enables thebuilding of complex PL/SQL rules and expressions. The EXFSYS schema containsthe Rules Manager and Expression Filter DDL, DML, and associated metadata.

[] MDDATA MDDATA

Description: oracle Spatial data account

Recreation Script:$ORACLE_HOME/md/admin/catmd.sql

Safe To Remove:Yes

Purpose:Theschema used by Oracle Spatial for storing Geocoder and router data. See alsoSPATIAL_CSW_ADMIN_USR, SPATIAL_WFS_ADMIN_USR and MDSYS.

[] MDSYS MDSYS

Description: oracle Spatial data Media Administrator / Spatial data (SPATIAL)\ Media (INTERMEDIA)\ Audio (AUDIO)\ Video (VIDEO) and Image Administrator account

Recreation Script:$ORACLE_HOME/ord/admin/ordinst.sql

Safe To Remove:Yes

Purpose:TheOracle Spatial and Oracle Multimedia Locator administrator account. See alsoSPATIAL_CSW_ADMIN_USR, MDDATA and SPATIAL_WFS_ADMIN_USR.

[] MGMT_VIEW MGMT_VIEW

Description: does not own any objects and is only used to query SYSMAN user objects. Created automatically when you install OMS.

Recreation Script:$ORACLE_HOME/sysman/admin/emdrep/bin/RepManager

Safe To Remove:Yes

Purpose:Anaccount used by Oracle Enterprise Manager Database Control. Password israndomly generated at installation or database creation time. Users do not needto know this password.

[] OLAPSYS MANGER

Description: used to create olap metadata / user used to create OLAP metadata. Includes the OLAP_DBA\ RESOURCE\ CONNECT role

Recreation Script:$ORACLE_HOME/olap/admin/amdsys.sql

Safe To Remove:Yes

Purpose:Theaccount that owns the OLAP Catalog (CWMLite). This account has been deprecated,but is retained for backward compatibility.

[] ORDPLUGINS ORDPLUGINS

Description: user names of ORACLE INTERMEDIA and VIDEO, with CONNECT and RESURCE roles, and connection to third parties

Recreation Script:$ORACLE_HOME/ord/admin/ordinst.sql

Safe To Remove:Yes

Purpose:TheOracle Multimedia user. Plug-ins supplied by Oracle and third-party, formatplug-ins are installed in this schema. Oracle Multimedia enables OracleDatabase to store, manage, and retrieve images, audio, video, DICOM formatmedical images and other objects, or other heterogeneous media data integratedwith other enterprise information. See also ORDSYS and SI_INFORMTN_SCHEMA.

[] ORDSYS ORDSYS

Description: user name of image administrator / ORACLE INTERMEDIA and VIDEO, with CONNECT and RESOURCE roles

Recreation Script:$ORACLE_HOME/ord/admin/ordinst.sql

Safe To Remove:Yes

Purpose:TheOracle Multimedia administrator account. See also ORDPLUGINS and SI_INFORMTN_SCHEMA.

[] OUTLN OUTLN

Description: have connect and resource roles

Recreation Script:$ORACLE_HOME/rdbms/admin/sql.bsq.Recover from backup or recreate the database.

Safe To Remove:No

Purpose:Theaccount that supports plan stability. Plan stability prevents certain databaseenvironment changes from affecting the performance characteristics ofapplications by preserving execution plans in stored outlines. OUTLN acts as arole to centrally manage metadata associated with stored outlines.

[] SCOTT Tiger

Description: simple sample account

Recreation Script:$ORACLE_HOME/rdbms/admin/utlsampl.sql

Safe To Remove:Yes

Purpose:Anaccount used by Oracle sample programs and examples.

[] HR hr

Description: simple sample account

Recreation Script:$ORACLE_HOME/demo/schema/human_resources/hr_main.sql

Safe To Remove:Yes- run $ORACLE_HOME/demo/schema/drop_sch.sql

Purpose:Theaccount that owns the Human Resources schema included in the Oracle SampleSchemas. See also BI, OE, SH, IX and PM.

[] SI_INFORMTN_SCHEMA oracleineterMedia,video user

Description: still image standard browsing account

Recreation Script:$ORACLE_HOME/ord/admin/ordinst.sql

Safe To Remove:Yes

Purpose:Theaccount that stores the information views for the SQL/MM Still Image Standard.See also ORDPLUGINS and ORDSYS.

[] WK_TEST WK_TEST

Description: the same as wksys/ manages the account of ORACLE 10g ULTRASEARCH, and has the same function as WKSYS

Recreation Script:$ORACLE_HOME/ultrasearch/admin/wk0csys.sql

Safe To Remove:Yes

Purpose:Theinstance administrator for the default instance, WK_INST. After unlocking thisaccount and assigning this user a password, then the cached schema passwordmust also be updated using the administration tool Edit Instance Page. UltraSearch provides uniform. Search-and-location capabilities over multiplerepositories, such as Oracle databases, other ODBC compliant databases, IMAPmail servers, HTML documents managed by a Web server, files on disk, and more.See also WKSYS

[] WKPROXY

Description: ORACLE 10g accounts related to the proxy server

Recreation Script:$ORACLE_HOME/ultrasearch/admin/wk0csys.sql

Safe To Remove:Yes

Purpose:Anadministrative account of Application Server Ultra Search.

[] WKSYS WKSYS

Description: same as wk_test/ manages ORACLE 10g AS ULTRASEARCH account, and has the same function as WK_TEST

Recreation Script:$ORACLE_HOME/ultrasearch/admin/wk0csys.sql

Safe To Remove:Yes

Purpose:AnUltra Search database super-user. WKSYS can grant super-user privileges toother users, such as WK_TEST. All Oracle Ultra Search database objects areinstalled in the WKSYS schema. See also WK_TEST

[] WMSYS WMSYS

Description: workspace management account / work reality management account

Recreation Script:$ORACLE_HOME/rdbms/admin/owmctab.plb

Safe To Remove:Yes

Purpose:Theaccount used to store the metadata information for Oracle Workspace Manager.

[] XDB CHANGE_ON_INSTALL

Description: ORACLE 10G XML DB account

Recreation Script:$ORACLE_HOME/rdbms/admin/catqm.sql

Safe To Remove:Yes

Purpose:Theaccount used for storing Oracle XML DB data and metadata. See also ANONYMOUS.

[] ORACLE_OCM

Description: Oracle predefined non-administrator users

Recreation Script:$ORACLE_HOME/rdbms/admin/catocm.sql

Safe To Remove:Yes

Purpose:Thisaccount contains the instrumentation for configuration collection used by theOracle Configuration Manager.

[] BI

Description:

Recreation Script:$ORACLE_HOME/demo/schema/bus_intelligence/bi_main.sql

Safe To Remove:Yes-run$ORACLE_HOME/demo/schema/drop_sch.sql

Purpose:Theaccount that owns the Business Intelligence schema included in the OracleSample Schemas. See also HR, OE, SH, IX and PM.

[] OE

Description:

Recreation Script:$ORACLE_HOME/demo/schema/order_entry/oe_main.sql

Safe To Remove:Yes- run $ORACLE_HOME/demo/schema/drop_sch.sql

Purpose:Theaccount that owns the Order Entry schema included in the Oracle Sample Schemas.See also BI, HR, SH, IX and PM.

[] IX

Description:

Recreation Script:$ORACLE_HOME/demo/schema/info_exchange/ix_main.sql

Safe To Remove:Yes- run $ORACLE_HOME/demo/schema/drop_sch.sql

Purpose:Theaccount that owns the Information Transport schema included in the OracleSample Schemas. See also BI, HR, OE, SH and PM.

[] PM

Description:

Recreation Script:$ORACLE_HOME/demo/schema/product_media/pm_main.sql

Safe To Remove:Yes- run $ORACLE_HOME/demo/schema/drop_sch.sql

Purpose:Theaccount that owns the Product Media schema included in the Oracle SampleSchemas. See also BI, HR, OE, SH and IX.

[] SH

Description:

Recreation Script:$ORACLE_HOME/demo/schema/sales_history/sh_main.sql

Safe To Remove:Yes- run $ORACLE_HOME/demo/schema/drop_sch.sql

Purpose:Theaccount that owns the Sales History schema included in the Oracle SampleSchemas and is only available for Enterprise Edition installations. See alsoBI, HR, OE, IX and PM.

[] APEX_030200

Description:

Recreation Script:$ORACLE_HOME/apex/apexins.sql

Safe To Remove:Yes

Purpose:Partof the Oracle Application Express Suite-(Oracle APEX, previously named OracleHTML DB) which is a freeware software development environment. It allows a fastdevelopment cycle to be achieved to create web based applications. The accountowns the Application Express schema and metadata. See also APEX_PUBLIC_USER andFLOW_FILES.

[] APEX_PUBLIC_USER

Description:

Recreation Script:$ORACLE_HOME/apex/apexins.sql

Safe To Remove:Yes

Purpose:Partof the Oracle Application Express Suite-(Oracle APEX, previously named OracleHTML DB) which is a freeware software development environment. It allows a fastdevelopment cycle to be achieved to create web based applications. Thisminimally privileged account is used for Application Express configuration withOracle HTTP Server and mod_plsql. See also APEX_030200 and FLOW_FILES.

[] APPQOSSYS

Description:

Recreation Script:$ORACLE_ADMIN/rdbms/admin/catqos.sql

Safe To Remove:Yes

Purpose:Usedfor storing/managing all data and metadata required by Oracle Quality ofService Management.

[] FLOWS_FILES

Description:

Recreation Script:$ORACLE_HOME/apex/apexins.sql

Safe To Remove:Yes

Purpose:Partof the Oracle Application Express Suite-(Oracle APEX, previously named OracleHTML DB) which is a freeware software development environment. It allows a fastdevelopment cycle to be achieved to create web based applications. This accountowns the Application Express uploaded files. See also APEX_030200 andAPEX_PUBLIC_USER.

[] OWBSYS

Description:

Recreation Script:$ORACLE_HOME/owb/UnifiedRepos/cat_owb.sql

Safe To Remove:Yes

Purpose:Theaccount for administrating the Oracle Warehouse Builder repository. Access thisaccount during the installation process to define the base language of therepository and to define Warehouse Builder workspaces and users. A datawarehouse is a relational or multidimensional database that is designed forquery and analysis. See also OWBSYS_AUDIT.

[] OWBSYS_AUDIT

Description:

Recreation Script:$ORACLE_HOME/owb/UnifiedRepos/cat_owb.sql

Safe To Remove:Yes

Purpose:Thisaccount is used by the Warehouse Builder Control Center Agent to access the heterogeneousexecution audit tables in the OWBSYS schema.

[] SPATIAL_CSW_ADMIN_USR

Description:

Recreation Script:$ORACLE_HOME/md/admin/sdocswpv.sql

Safe To Remove:Yes

Purpose:TheCatalog Services for the Web (CSW) account. It is used by the Oracle SpatialCSW cache manager to load all record type metadata, and record instances fromthe database into the main memory for the record types that are cached. Seealso SPATIAL_WFS_ADMIN_USR, MDDATA and MDSYS.

[] SPATIAL_WFS_ADMIN_USR

Description:

Recreation Script:$ORACLE_HOME/md/admin/sdowfspv.sql

Safe To Remove:Yes

Purpose:TheWeb Feature Service (WFS) account. It is used by the Oracle Spatial WFS cachemanager to load all feature type metadata, and feature instances from thedatabase into main memory for the feature types that are cached. See alsoSPATIAL_CSW_ADMIN_USR, MDDATA and MDSYS.

[] XS$NULL

Description:

Recreation Script:$ORACLE_HOME/rdbms/admin/sql.bsq.Recover from backup or recreate the database.

Safe To Remove:No

Purpose:Aninternal account that represents the absence of a user in a session. BecauseXS$NULL is not a user, this account can only be accessed by the Oracle Databaseinstance. XS$NULL has no privileges and no one can authenticate as XS$NULL, norcan authentication credentials ever be assigned to XS$NULL.

[] LBACSYS

Description:

Recreation Script:$ORACLE_HOME/rdbms/admin/catlbacs.sql

Safe To Remove:Yes

Purpose:Theaccount used to administer Oracle Label Security (OLS). It is created only whenthe Label Security custom option is installed.

[] ORDDATA

Description:

Recreation Script:$ORACLE_HOME/ord/admin/ordisysc.sql

Safe To Remove:Yes

Purpose:Thisaccount contains the Oracle Multimedia DICOM data model.

[] TSMSYS

Description: the new features of all catalogs of Oracle?10g and related views / tables are stored under TSMSYS users.

Recreation Script:

Safe To Remove:

Purpose:

[] DMSYS

Description: Oracle data mining account

Recreation Script:

Safe To Remove:

Purpose:

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.