Database
Manually delete the database dropdatabase
Manually delete the database drop database the The simple process is as follows, above version 10g, the database must be in the mount state, in RESTRICTED mode sys@STANDBY3(dtydb5)> shutdown The database has been closed. The database has been unmounted. The ORACLE instance has been shut down. sys@STANDBY3(dtydb5)> startup mount; The ORACLE instance has been started. www.2cto.com Total System Global Area 3140026368 bytes Fixed Size Variable Size 771753656 bytes Database Buffers 2348810240 bytes Redo Buffers The database is loaded. sys@STANDBY3(dtydb5)> drop database; drop database * Error on line 1: ORA-12719: operation requires database to be in RESTRICTED mode the sys@STANDBY3(dtydb5)> alter system enable restricted session; the The system has changed. sys@STANDBY3(dtydb5)> drop database; the The database has been deleted. the illustrate: 1. Only 10g or more can have the drop database command 2. The DROP DATABASE command deletes these files from operating system.…
Manually create the database (including oraclerac)
Manually create databases (including oracle rac) the The easiest way is to use dbca to generate database building scripts, and then manually execute these scripts. The specific process is as follows 1, Manually create relevant directories and environment variables [sql] #!/bin/sh the OLD_UMASK=`umask` umask 0027 mkdir -p /oracle/app/oracle/admin/hrdb/adump mkdir -p /oracle/app/oracle/admin/hrdb/dpdump mkdir -p /oracle/app/oracle/admin/hrdb/hdump mkdir -p /oracle/app/oracle/admin/hrdb/pfile mkdir -p /oracle/app/oracle/cfgtoollogs/dbca/hrdb umask ${OLD_UMASK} www.2cto.com ORACLE_SID=hrdb1; export ORACLE_SID PATH=$ORACLE_HOME/bin:$PATH; export PATH Whether to use the database to start automatically, here is set to false echo You should Add this entry in the /etc/oratab: hrdb:/oracle/app/oracle/product/11.2.0/db_1:N the 2. Create parameter files and password files [sql] password file /oracle/app/oracle/product/11.2.0/db_1/bin/orapwd file=/oracle/app/oracle/product/11.2.0/db_1/dbs/orapwhrdb1 force=y Parameter file $ORACLE_HOME/dbs/inithrdb1.ora *.audit_file_dest='/oracle/app/oracle/admin/hrdb/adump' *.audit_trail='db' *.cluster_database=false *.compatible='11.2.0.0.0' *.cpu_count=8 *.create_stored_outlines='' *.db_block_size=8192 *.db_create_file_dest=’#39;+DATA’ *.db_domain=’#39;’ *.db_name=’#39;hrdb’ *.db_recovery_file_dest='+FRA' *.db_recovery_file_dest_size=107374182400 *.diagnostic_dest='/oracle/app/oracle' hrdb1.instance_number=1 *.log_archive_format='%t_%s_%r.dbf' *.nls_language='SIMPLIFIED CHINESE' *.nls_territory='CHINA' *.open_cursors=1000 *.pga_aggregate_target=1610612736 *.processes=500 www.2cto.com *.remote_listener='dtydb-scan2:1521' *.sga_target=4399824896 the Boot to nomount state sqlplus “/as sysdba” the startup nomount pfile=”/oracle/app/oracle/product/11.2.0/db_1/dbs/inithrdb1.ora”; the 3. Start building the library [sql] CREATE DATABASE “hrdb” MAX INSTANCES 32 MAXLOGHISTORY 1 MAXLOGFILES 192 MAXLOGMEMBERS 3 MAXDATAFILES 1024 DATAFILE SIZE 700M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SYSAUX DATAFILE SIZE 600M AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20M AUTOEXTEND ON NEXT 640K…
ORACLE query lock resource statement
ORACLE query lock resource statement the oracle query lock resources: [sql] select b. SESSION_ID, b.ORACLE_USERNAME, b.OS_USER_NAME, b. PROCESS, b. LOCKED_MODE, a.owner, a.object_name, a.object_id, a.object_type, b.XIDUSN, www.2cto.com b.XIDSLOT, b.XIDSQN from all_objects a, v$locked_object b where a.object_id = b.object_id the the Author ziwen00
How to enable OracleSharedServer
How to enable Oracle Shared Server the Shared servers still have unique advantages in some applications. From the official description, we can see the following advantages: Reduce the number of instance processes Increase the number of users of external services Reduce the number of processes on useless or idle servers Reduce oracle database memory usage and OS overhead To achieve load balancing Compared with the dedicated server mode, an important difference is that the shared server requires the use of net services to connect, even if the user is often on the same machine as the server process. But in some cases, you must use a dedicated server: such as when using sysdba to log in for management operations. Of course, to use these two different modes, it is important to reflect in the client’s connection string configuration after the server is configured Dedicated server connection — (SERVER = DEDICATED) Shared server connection — (SERVER = SHARED) So how to configure the shared server? Two parameters are required: DISPATCHERS www.2cto.com SHARED_SERVERS Optional configuration parameters: MAX_DISPATCHERS MAX_SHARED_SERVERS CIRCUITS SHARED_SERVER_SESSIONS In most cases, we only need to modify DISPATCHERS. Let’s first look at the parameters of the default dedicated server: [oracle@test ~]$…
Understanding oracleROWID
Understanding oracle ROWID the The data is stored in the data file, and its attributes will be determined along with the storage. These attributes include: which data file? Which object does it belong to? Where is the data block located? line number? Combining these attributes constitutes the oracle ROWID. the Therefore, the rowid records the location of the data on the disk in detail. www.2cto.com ROWID can be divided into: physical and logical. Except IOT uses logical ROWID, other types of tables use physical ROWID. ROWID can also be divided into: restricted and extended. After 8i use extended rowid. The following introduces the extended rowid. Extended rowid, using 64-bit encoding, press 6, 3 to open. example: [sql] SQL> show user USER is “HR” SQL> drop table t1 purge; drop table t1 purge * * Error on line 1: ORA-00942: table or view does not exist the SQL> create table t1 (id number,name varchar2(20)); the The table has been created. the www.2cto.com SQL> insert into t1 values(1,'a'); the It has created a row. the the SQL> insert into t1 values(2,'b'); the It has created a row. the the SQL> commit; the Commit complete. the the SQL> select rowid,t1.* from t1; the…
Oracle summarizes according to the time statistics statement
Oracle summarizes according to the time statistics statement the Oracle statistics statement by day http://www.2cto.com/database/201208/148040.html Oracle statistics statement by week http://www.2cto.com/database/201208/148083.html Oracle statistics statement by month http://www.2cto.com/database/201208/148041.html Oracle statistics statement by quarter http://www.2cto.com/database/201208/148085.html Oracle statistics statement by year http://www.2cto.com/database/201208/148086.html the After reading the few articles I posted above, we can easily find a pattern. the According to the time statistics are also regular… the Did you notice any difference? the –Statistics by day SELECT TO_CHAR(T.MODIFIEDTIME,’YYYY-MM-DD’) TIME,COUNT(*) COUNT FROM TEST T –You can add query conditions here WHERE TO_CHAR(T.MODIFIEDTIME,'YYYY') = TO_CHAR(SYSDATE,'YYYY') GROUP BY TO_CHAR(T.MODIFIEDTIME,'YYYY-MM-DD') –Group by date ORDER BY TO_CHAR(T.MODIFIEDTIME, & # 39; YYYY-MM-DD & # 39;) ASC NULLS LAST — Sort by date the –Statistics by week SELECT TO_CHAR(T.MODIFIEDTIME,'YYYY') YEAR,TO_CHAR(T.MODIFIEDTIME,'IW') TIME,COUNT(*) COUNT www.2cto.com FROM TEST T –You can add query conditions here WHERE TO_CHAR(T.MODIFIEDTIME,'YYYY') = TO_CHAR(SYSDATE,'YYYY') GROUP BY TO_CHAR(T.MODIFIEDTIME,'IW'),TO_CHAR(T.MODIFIEDTIME,'YYYY') –Group by week number ORDER BY TO_CHAR(T.MODIFIEDTIME,'YYYY'),TO_CHAR(T.MODIFIEDTIME,'IW') ASC NULLS LAST –sort by week number the — Statistics by month SELECT TO_CHAR(T.MODIFIEDTIME,’YYYY-MM’) TIME,COUNT(*) COUNT FROM TEST T –You can add query conditions here WHERE TO_CHAR(T.MODIFIEDTIME,'YYYY') = TO_CHAR(SYSDATE,'YYYY') GROUP BY TO_CHAR(T.MODIFIEDTIME,'YYYY-MM') –Group by month ORDER BY TO_CHAR(T.MODIFIEDTIME,’YYYY-MM’) ASC NULLS LAST –sort by month the –Statistics by quarter SELECT TO_CHAR(T.MODIFIEDTIME,'YYYY') YEAR,TO_CHAR(T.MODIFIEDTIME,'Q') TIME,COUNT(*) COUNT FROM…
Oracle statistics statement by year
Oracle statistics statement by year the –Create table Test CTEATE TABLE TEST( ID NUMBER NOT NULL, MODIFIEDTIME DATE NOT NULL ) –Statistics by year SELECT TO_CHAR(T.MODIFIEDTIME,’YYYY’) YEAR,COUNT(*) COUNT FROM TEST T www.2cto.com — You can add query conditions here GROUP BY TO_CHAR(T.MODIFIEDTIME,'YYYY') –Group by year ORDER BY TO_CHAR(T.MODIFIEDTIME,'YYYY') ASC NULLS LAST –sort by year — Note: MODIFIEDTIME is the time field in the table TEST, the time type –The above code can be run directly in the database –If there is a quantity field in the table, if you want to count the quantity by day, you can change COUNT(*) to SUM(1) function the the Author guoxuepeng123
Oracle statistics statement by quarter
Oracle statistics statement by quarter the –Create table Test CTEATE TABLE TEST( ID NUMBER NOT NULL, MODIFIEDTIME DATE NOT NULL ) –Statistics by quarter SELECT TO_CHAR(T.MODIFIEDTIME,'YYYY') YEAR,TO_CHAR(T.MODIFIEDTIME,'Q') TIME,COUNT(*) COUNT www.2cto.com FROM TEST T –You can add query conditions here WHERE TO_CHAR(T.MODIFIEDTIME,'YYYY') = TO_CHAR(SYSDATE,'YYYY') GROUP BY TO_CHAR(T.MODIFIEDTIME,'Q'),TO_CHAR(T.MODIFIEDTIME,'YYYY') –group by quarter of each year ORDER BY TO_CHAR(T.MODIFIEDTIME,'YYYY'),TO_CHAR(T.MODIFIEDTIME,'Q') ASC NULLS LAST –Sort according to the quarter of each year the — Note: MODIFIEDTIME is the time field in the table TEST, the time type –The above code can be run directly in the database –If there is a quantity field in the table, if you want to count the quantity by day, you can change COUNT(*) to SUM(1) function the the Author guoxuepeng123
Oracle statistics statement by week
Oracle statistics statement by week the –Create table Test CTEATE TABLE TEST( ID NUMBER NOT NULL, MODIFIEDTIME DATE NOT NULL ) –Statistics by week SELECT TO_CHAR(T.MODIFIEDTIME,'YYYY') YEAR,TO_CHAR(T.MODIFIEDTIME,'IW') TIME,COUNT(*) COUNT www.2cto.com FROM TEST T –You can add query conditions here WHERE TO_CHAR(T.MODIFIEDTIME,'YYYY') = TO_CHAR(SYSDATE,'YYYY') GROUP BY TO_CHAR(T.MODIFIEDTIME,'IW'),TO_CHAR(T.MODIFIEDTIME,'YYYY') –Group by week number ORDER BY TO_CHAR(T.MODIFIEDTIME,'YYYY'),TO_CHAR(T.MODIFIEDTIME,'IW') ASC NULLS LAST –sort by week number — Note: MODIFIEDTIME is the time field in the table TEST, the time type –The above code can be run directly in the database –If there is a quantity field in the table, if you want to count the quantity by day, you can change COUNT(*) to SUM(1) function the Author guoxuepeng123
Oracle statistics statement by month
Oracle statistics statement by month the –Create table Test CTEATE TABLE TEST( ID NUMBER NOT NULL, MODIFIEDTIME DATE NOT NULL ) — Statistics by month SELECT TO_CHAR(T.MODIFIEDTIME,’YYYY-MM’) TIME,COUNT(*) COUNT FROM TEST T www.2cto.com –You can add query conditions here WHERE TO_CHAR(T.MODIFIEDTIME,'YYYY') = TO_CHAR(SYSDATE,'YYYY') GROUP BY TO_CHAR(T.MODIFIEDTIME,'YYYY-MM') –Group by month ORDER BY TO_CHAR(T.MODIFIEDTIME,’YYYY-MM’) ASC NULLS LAST –sort by month the — Note: MODIFIEDTIME is the time field in the table TEST, the time type –The above code can be run directly in the database –If there is a quantity field in the table, if you want to count the quantity by day, you can change COUNT(*) to SUM(1) function the Author guoxuepeng123
