CN102446170A - ORACLE database click rate technology based on audit function - Google Patents
ORACLE database click rate technology based on audit function Download PDFInfo
- Publication number
- CN102446170A CN102446170A CN2010102984223A CN201010298422A CN102446170A CN 102446170 A CN102446170 A CN 102446170A CN 2010102984223 A CN2010102984223 A CN 2010102984223A CN 201010298422 A CN201010298422 A CN 201010298422A CN 102446170 A CN102446170 A CN 102446170A
- Authority
- CN
- China
- Prior art keywords
- database
- audit
- select
- clicking rate
- function
- Prior art date
- Legal status (The legal status is an assumption and is not a legal conclusion. Google has not performed a legal analysis and makes no representation as to the accuracy of the status listed.)
- Pending
Links
Images
Landscapes
- Information Retrieval, Db Structures And Fs Structures Therefor (AREA)
Abstract
The invention provides an ORACLE database click rate technology based on an audit function, comprising the following steps of: obtaining contents of SELECT inquiry sentences by utilizing the audit function of an ORACLE database to audit a SELECT authority and a table object; utilizing database timing work to inquire audit results; and then reconstructing a click rate inquiry sentence and a click rate updating sentence according to SELECT sentence conditions, and counting the click rate of accessing data, so as to realize the function of a SELECT inquiry trigger. According to the invention, by utilizing the audit function of the database to count the data click rate, accesses to the data by a user through different paths are not omitted, thereby realizing the count of a table record click rate or an appointed field click rate; if the technology is combined with an application program, detailed conditions including a logging-in IP (Internet Protocol) of the access user and the like can be counted and a counted result is greatly better than that of the condition of only utilizing the application program to count the data click rate; and meanwhile, the defect that the ORACLE database does not have the SELECT trigger is overcome.
Description
Technical field
The technology that the present invention relates to data access rate in a kind of ORACLE of statistics database realizes, is used to realize the select inquiry back function of trigger data query statistic automatically, and the operating position that is particularly useful for adding up the significant data data is the clickstream data rate.
Background technology
At present, the user capture amount of this website of website use clicking rate statistics, user's visit capacity is big more, explains that the popularity of this website is big, thus the advertisement benefit of website is also just bigger.The clicking rate of database is promptly come the operating position of statistics through in database table, adding the clicking rate field, thereby counts the importance of data information.At present, the ORACLE database only provides the trigger function of INSERT, UPDATE, DELETE statement, and the trigger function of SELECT statement is not provided, thereby can not utilize trigger to realize the statistical function to clickstream data rate (being the SELECT inquiry).Comparatively tradition is exactly to utilize application program to carry out the visit situation of statistics of user's to data with simple method; Store visit information in the database table clicking rate field then; This kind mode statistical information is comparatively comprehensive; But it is not necessarily complete; Its prerequisite is to be based upon on the application access data of database data base that the user utilizes appointment, in case the user through the data of other approach accessing database operating position of statistics definitely of just having no idea, certainly will cause the omission phenomenon of data access statistics.
Summary of the invention
In order to increase statistical function to the visit of ORACLE database data; Overcome the shortcoming of conventional statistics mode; The invention provides a kind of database clicking rate technology based on audit function, this technology has remedied the defective that the ORACLE database does not have the SELECT trigger, has realized the SELECT query statistic function to the ORACLE database; Not only can add up visit situation to the ORACLE database data, can also be comparatively complete, the visit information of statistics at length.
The technical solution adopted for the present invention to solve the technical problems is: the audit function that utilizes database; Detail record is to the various operations of database; Through specifying specific the audit rank and the audit target, can realize the SELECT statement " trigger " function accomplishes the visit statistics to database data.The user is recorded in the tables of data SYS.AUD$ the visit information of database data audit (AUDIT) function through database, and DBA_AUDIT_TRAIL is a view based on AUD$, and other view then is the sub-view of DBA_AUDIT_TRAIL.Through utilizing timing operation regularly the audit view to be carried out query statistic and calculating (similar SELECT trigger every night; Trigger interval once a day; Night 0 in triggered time point); And, reach the visit situation of staqtistical data base table record with the clicking rate field of statistics recorded and stored to the associated databases table.
The invention has the beneficial effects as follows; Having remedied the ORACLE database does not effectively have the defective of SELECT trigger; Can intactly count the operating position of database data comparatively comprehensively; Thereby can count the significance level of data, researcher used with statistics significant data data have very big help meaning.
Description of drawings
Simulation SELECT trigger among Fig. 1: through audit function to the SELECT statement; Realized query statistic function in conjunction with the timing operation of ORACLE database to the SELECT statement; The fixed time interval of timing operation can be adjusted as required, and in order not influence the normal function of ORACLE database, fixed time interval is 1 day; Be point at midnight 0 constantly regularly, played the function of similar SELECT trigger.
Open audit function among Fig. 2: the audit function of data all is a closed condition usually, if will use audit function, need open audit function earlier.
Move the audit correlation table among Fig. 2 to other table space: the Table A UD$ that audits usually is stored in other relevant view of auditing and shows in the SYSTEM space; Because SYSTEM table space storage ORACLE system information and space are limited; In order not influence the operation of ORACLE system, the correlation table that needs to audit moves in other table space.
Set up the SELECT audit of object among Fig. 2: promptly specify the SELECT audit through his-and-hers watches, the ORACLE system will write down all SELECT operation informations of his-and-hers watches.
Database timing operation among Fig. 2:, regularly carry out inspection Data Audit information every night at 12 through the timing operation (process) of setting up database.
Clicking rate field in the new record more among Fig. 2: promptly, will carry out statistical query and computing to the SELECT operation of data the same day, and corresponding clicking rate field in the new record more, and realize the visit statistical function of data through carrying out timing operation.
Embodiment
At first, whether the inspection Database Systems open audit function.Under SQLPLUS,, and carry out show parameter audit instruction, check whether audit function is opened with DBA identity log database.Two important parameters are arranged in the Query Result:
Audit_sys_operations and Audit_trail.Audit_sys_operations is defaulted as false, and when being set to true, all sys users' (comprising with sysdba the user of sysoper identity login) operation all can be by record.The value of Audit_trail has following several kinds:
None: be default value, do not do audit;
DB: audit trail is recorded in the audit correlation table of database, like aud$, the result of audit has only link information;
DB, Extended: the auditing result the inside has also comprised the concrete statement of carrying out at that time except link information like this;
OS: audit trail is recorded in the operating system file, and filename is specified by the audit_file_dest parameter;
Increase newly in the XML:10g, audit trail is recorded in the XML file.
Annotate: these two parameters are static parameters, need restart database and just can come into force.
If Audit_sys_operations=false; Audit_trail=None; Then the audit function of database of descriptions is not opened, and needs to carry out to issue orders, and opens audit function.
SQL>?alter?system?set?audit_sys_operations=TRUE?scope=spfile;
SQL>?alter?system?set?audit_trail=db,extended?scope=spfile;
Restart database and can open audit function.
Secondly, move the relevant table of audit to other table space.Usually audit Table A UD$ all gives tacit consent to other view and leaves in the SYSTEM table space, and in order not influence the performance of ORACLE system, the table that audit is correlated with all moves in other newly-built table space < new tablespace >.Execution is to issue orders:
SQL>alter?table?aud$?move?tablespace?<new?tablespace>;
SQL>alter?index?I_aud1?rebuild?online?tablespace?<new?tablespace>;
SQL>?alter?table?audit$?move?tablespace?<new?tablespace>;
SQL>?alter?index?i_audit?rebuild?online?tablespace?<new?tablespace>;
SQL>?alter?table?audit_actions?move?tablespace?<new?tablespace>;
SQL>?alter?index?i_audit_actions?rebuild?online?tablespace?<new?tablespace>;
The 3rd, confirm the audit rank, carry out the database audit.Usually the audit rank has three kinds, and the one, the statement audit is to certain type SQL statement audit, not specified structure or object.The 2nd, the authority audit is then audited when the user has used this authority.The user is the owner that oneself is shown especially, so the table of user capture oneself can not audited.The 3rd, the object audit is to the audit of the appointment statement on the special pattern object. the associative operation like audit ON key word appointed object, if the user is the owner of table, then adopt the object audit measure, and promptly carry out following order:
SQL＞AUDIT?SELECT?TABLE ON?<TABLENAME>?BY ACCESS；
Wherein BY ACCESS refers to each operation of being audited and can generate an AUDIT TRAIL.After the table that needs are carried out clicking rate statistics was all carried out this order, the table under all users had all had the SELECT audit function, if the query manipulation of execution his-and-hers watches, auditing result will be recorded among the AUD$.
The 4th, database timing operation (JOBS), inquiry auditing result, the clicking rate of specified data.Through setting up timing operation, begin implementation 0 o'clock midnight, inquire about the auditing result on the same day; According to the SQL_TEXT statement in the auditing result; Critical field in the option table and clicking rate field are constructed the value that current clicking rate inquired about in new SELECT statement, and the value of current clicking rate is added 1; Again construct the UPDATE statement according to the critical field of inquiring about just now again and upgrade intrarecord clicking rate field, realize statistical function data access in the database.
The order of timing operation is following:
SQL＞VARIABLE?N?NUMBER;
SQL>BEGIN
SQL>DBMS_JOB.SUBMIT(:N,?‘MYJOB;’,SYSDATE,’TRUNC(SYSDATE+1)’);
SQL>COMMIT;
SQL>END;
0 execution at midnight every day, MYJOB is a clicking rate update processing process, will add up the clicking rate result and be saved in the clicking rate field.
The reconstruct of SELECT statement is made: generating new query statement by SQL_TEXT, is SELECT * FROM TABLENAME WHERE KEY=' XX ' like SQL_TEXT; New query statement is SELECT ROWID, HITRATE FROM TABLENAME WHERE KEY=' XX '; After HITRATE carried out add-one operation, obtain new HITRATE value.
UPDATE statement structure: each record is carried out the UPDATE operation, and the UPDATE statement is UPDATE TABLENAME SET HITRATE=' NEWVALUE ' WHERE ROWID=' ROWID ';
The statistics of database clicking rate can be accurate to table, table record and specific field, also can add up the details such as IP address of calling party as if combining with application program.
Claims (6)
1. the ORACLE database clicking rate based on audit function is technological; Utilize the audit function of ORACLE database; The timing operation in binding data storehouse has been realized the function of SELECT querying triggering device, and having remedied the ORACLE database does not have SELECT to cause the defective of trigger; Through the query processing to auditing result, the visit situation of statistical form record and literary name section is if combine also can add up the details such as IP address of calling party with application program.It is characterized in that: the audit function that utilizes database; Through of the inquiry of database timing operation to auditing result; Obtain SELECT statement content; Again construct clicking rate query statement and clicking rate renewal statement according to SELECT statement condition again, carry out the statistics of data access clicking rate, realize that SELECT triggers function.
2. database clicking rate technology according to claim 1 is characterized in that: realized SELECT querying triggering trigger function.
3. database clicking rate technology according to claim 1 is characterized in that: through the SELECT query statistic clicking rate of statistics of user's to the data record, can be accurate to the query statistic to specific field.
4. database clicking rate technology according to claim 1, it is characterized in that: utilize the audit function of database, recording user is to the visit situation of data.
5. database clicking rate technology according to claim 1 is characterized in that: the timing operation through database is realized inquiry and clicking rate computing and renewal to auditing result.
6. database clicking rate according to claim 1 technology is characterized in that: can realize that the details such as IP address to calling party add up if combine with application program.
Priority Applications (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2010102984223A CN102446170A (en) | 2010-10-04 | 2010-10-04 | ORACLE database click rate technology based on audit function |
Applications Claiming Priority (1)
| Application Number | Priority Date | Filing Date | Title |
|---|---|---|---|
| CN2010102984223A CN102446170A (en) | 2010-10-04 | 2010-10-04 | ORACLE database click rate technology based on audit function |
Publications (1)
| Publication Number | Publication Date |
|---|---|
| CN102446170A true CN102446170A (en) | 2012-05-09 |
Family
ID=46008674
Family Applications (1)
| Application Number | Title | Priority Date | Filing Date |
|---|---|---|---|
| CN2010102984223A Pending CN102446170A (en) | 2010-10-04 | 2010-10-04 | ORACLE database click rate technology based on audit function |
Country Status (1)
| Country | Link |
|---|---|
| CN (1) | CN102446170A (en) |
Cited By (2)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104539452A (en) * | 2014-12-18 | 2015-04-22 | 国云科技股份有限公司 | Method for conducting statistics on Web application access regional characteristics |
| CN109962983A (en) * | 2019-03-29 | 2019-07-02 | 北京搜狗科技发展有限公司 | A kind of clicking rate statistical method and device |
Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US20030105745A1 (en) * | 2001-12-05 | 2003-06-05 | Davidson Jason A. | Text-file based relational database |
| US6873980B2 (en) * | 2000-12-15 | 2005-03-29 | Sun Microsystems, Inc. | System for interfacing an application program with diverse databases |
| CN101256561A (en) * | 2007-03-02 | 2008-09-03 | 阿里巴巴集团控股有限公司 | Method, apparatus and system for storing and accessing database data |
| CN101388010A (en) * | 2007-09-12 | 2009-03-18 | 北京启明星辰信息技术有限公司 | Oracle database audit method and system |
-
2010
- 2010-10-04 CN CN2010102984223A patent/CN102446170A/en active Pending
Patent Citations (4)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| US6873980B2 (en) * | 2000-12-15 | 2005-03-29 | Sun Microsystems, Inc. | System for interfacing an application program with diverse databases |
| US20030105745A1 (en) * | 2001-12-05 | 2003-06-05 | Davidson Jason A. | Text-file based relational database |
| CN101256561A (en) * | 2007-03-02 | 2008-09-03 | 阿里巴巴集团控股有限公司 | Method, apparatus and system for storing and accessing database data |
| CN101388010A (en) * | 2007-09-12 | 2009-03-18 | 北京启明星辰信息技术有限公司 | Oracle database audit method and system |
Non-Patent Citations (2)
| Title |
|---|
| 张静: "Oracle 10g的细粒度审计监控", 《科技致富向导》 * |
| 惠黎明等: "Oracle数据库的审计方式", 《舰船防化》 * |
Cited By (3)
| Publication number | Priority date | Publication date | Assignee | Title |
|---|---|---|---|---|
| CN104539452A (en) * | 2014-12-18 | 2015-04-22 | 国云科技股份有限公司 | Method for conducting statistics on Web application access regional characteristics |
| CN104539452B (en) * | 2014-12-18 | 2017-12-01 | 国云科技股份有限公司 | A kind of method that statistics Web applications access regional characteristic |
| CN109962983A (en) * | 2019-03-29 | 2019-07-02 | 北京搜狗科技发展有限公司 | A kind of clicking rate statistical method and device |
Similar Documents
| Publication | Publication Date | Title |
|---|---|---|
| US11263211B2 (en) | Data partitioning and ordering | |
| Gousios | The GHTorent dataset and tool suite | |
| Schwartz et al. | High performance MySQL: optimization, backups, and replication | |
| US9519801B2 (en) | Systems, methods, and apparatuses for implementing data masking via compression dictionaries | |
| CN102651008A (en) | Method and equipment for organizing data records in relational data base | |
| Danilovic et al. | Exploring diffusion and dynamics of corporate social responsibility | |
| Tang et al. | Deferred lightweight indexing for log-structured key-value stores | |
| US20140379631A1 (en) | Transactional key-value database with searchable indexes | |
| AU2014271274A1 (en) | System and method predicting effect of cache on query elapsed response time during application development stage | |
| CN104239353B (en) | WEB classification control and log audit method | |
| Srivastava et al. | Analysis of various NoSql database | |
| Krueger et al. | A case for online mixed workload processing | |
| Neeraj | Mastering Apache Cassandra | |
| CN102446170A (en) | ORACLE database click rate technology based on audit function | |
| Schönig | Mastering PostgreSQL 15: Advanced techniques to build and manage scalable, reliable, and fault-tolerant database applications | |
| Kepner et al. | Lustre, hadoop, accumulo | |
| JP2017537398A (en) | Generating unstructured search queries from a set of structured data terms | |
| Saloustros et al. | Rethinking HBase: design and implementation of an elastic key-value store over log-structured local volumes | |
| Gade et al. | The. GOV internet archive: a big data resource for political science | |
| Mehra et al. | Column based nosql database, scope and future | |
| Gupta et al. | Leveraging metadata in no SQL storage systems | |
| Nagi | Bringing search engines to the cloud using open source components | |
| US10037155B2 (en) | Preventing write amplification during frequent data updates | |
| Singh | NoSQL: A new horizon in big data | |
| Kim et al. | RILCA: Collecting and analyzing user-behavior information in instant search using relational DBMS |
Legal Events
| Date | Code | Title | Description |
|---|---|---|---|
| C06 | Publication | ||
| PB01 | Publication | ||
| C10 | Entry into substantive examination | ||
| SE01 | Entry into force of request for substantive examination | ||
| C02 | Deemed withdrawal of patent application after publication (patent law 2001) | ||
| WD01 | Invention patent application deemed withdrawn after publication |
Application publication date: 20120509 |