1. Methods of connecting Oracle 8 do not have Oracle 8
Please copy the following file to the directory where the running file is located.
First, ODBC dynamic library:
CTL3D32.DLL MSVCRT40.DLL ODBC16GT.DLL ODBC32.DLL ODBC32GT.DLL ODBCCP32.DLL ODBCCCR32.DLL ODBCINT.DLL
2. Establish an EXTRA subdirectory, copy the msvcrt.dll file to the subdirectory
EXTRA / MSVCRT.DLL
Third, Oracle dynamic library and configuration files
Tnsnames.ora CORE35O.DLL NASNSNT.DLL NAUNTSNT.DLL NCRNT.DLL Nlnt.dll NLSRTL32.DLL Nnfdnt.dll NNFNNT.DLL NSNT.DLL NTNT.DLL NTTNT.DLL CIW32.DLL Ora73.dll OTRACE73.DLL Sqlnet.ora Sqltnsnt. DLL Core35.dll
Fourth, PB dynamic library
Pbvm70.dll pbdwe70.dll pbo7370.dll Pbo8470.dll PBODB70.DLL LIBJCC.DLL
Oracle's client does not install the PB even, I remember some posts, you can search. Specific steps. (1). First install the client on a machine (preferably installed in the C drive); (2). Copy all files in this client Oracle directory as a separate Oracle installation file; (3). Search registration , Find HKEY_LOCAL_MACHINE / SOFTWARE / ORACLE, export all of this project and branch. (4). Pack your PB program, and unitely package the Oracle client and registry export file. (5). To clean client, unpack the registry file, then join the path support: path =% path%; "c: / ora817 / bin" is handled, there should be no problem, because I just This rapidly handles dozens of machines.
If you don't want to engage in registration, you can write a registry in the program, make it necessary for the necessary registry support for Oracle clients, as for the path, handmade, should not be difficult.
As for those files of Oracle clients, this is not easy to say, you can delete all EXEs under those bin directories, Oracle Document delete (7X MP)
As for the definition Oracle service, find ORA817 / NET80 / Admin / TNSName.ora, refer to the format, and generate one in the program.
============================================================================================================================================================================================================= ===== 2, return the cursor result set in Oracle
You need to write a package: create or replace package PAG_CS_POWER AS
TYPE C_TYPE IS REF CURSOR; FUNCTION FUN_CS_GETDICTLIST (v_dictindex in varcha2) Return C_TYPE;
END PAG_CS_POWER;
Function code:
FUNCTION FUN_CS_GETDICTLIST (v_DictIndex in varchar2) return c_Type as c_cursor c_Type; begin open c_cursor for select DICTID, DICTNAME FROM SYS_DICT WHERE DICTINDEX = v_DictIndex; return c_cursor; end FUN_CS_GETDICTLIST; ---------------- -------------------------------------------------- ---- 3, P4 machine Install Oracle (1), copy the Oracle installation software to the hard drive. (2) The files of the hard disk directory file /stage/components/racle.swd.jre/1.1.7.30/1 /datafiles/expanded/jre/win32/bin/symcjit.dll (3). Run the setup.exe file for installation.
-------------------------------------------------- ---------------------- 4, single quotes insertion
SQL> INSERT INTO A VALUES ('I'm Good'); - Two '' can represent a '
SQL> INSERT INTO A VALUES ('I' || CHR (39) || 'M Good'); --chr (39) Representative Character '
SQL> INSERT INTO A VALUES ('a' || '&' || 'b');
-------------------------------------------------- --------------------- 5, the import and export of all databases
Exp username / password full = y file = YourData.dmp grants = y rows = yimp username / password full = y ignore = y file = YourData.dmp grants = Y YOURDATA.DMP GRANTS = Y
6, EXP and IMP specific usage
Exp usrname / password @ mzbs_61 full = y file = YourData.dmp grants = y rows = yimp username / password full = y ignore = y = YourData.dmp grants = y
Exp mzbs / mzbs @ mzbs_61 file = c: / zzzzzz.dmp grants = y rows = y imp mzbs / mzbs @ mzbs_61 file = c: / zzzzzzz.dmp grants = y ignore = y full = y
(1)
EXP parameters: Keyword description (default) ---------------------------------------------------------------------------------------------------------------------- ----- Userid User Name / Password Full Exports The Size of the Buffer Data Buffer OWNER Owner User Name List File Output File (Expdat.dmp) Tables Table Name list Compress Import a range (Y) Recordlength IO Recording Length GRANTS Export Permissions (Y) IncType Increment Export Type Indexes Export Index (Y) RECORD Tracking Increment Export (Y) Rows Export Data Row (Y) PARFILE Parameter File Name Constraints Export Limit (Y) Consistent Cross Table Log Screen Output Log File Statistics Direct Direct Path (N) Triggers Export Trigger (Y) Feedback Displays the maximum size of each x row (0) FileSize Maximum size Query Query Selection Export Table Set Subsequent IMP parameters: Keyword description (default) -------------------------------------- -------------- Userid username / password FULL Imports the entire file (N) Buffer Data Buffer Size fromUser Owner User User List File Enter File (Expdat.dmp) Touser User Name List SHOW only lists file content ( N) TABLES Table Name List IGNORE Ignore ignores Creating Errors (N) Recordlength IO Label GRANTS Import Permissions (Y) IncType Increment Import Type Indexes Import Index (Y) CommT Submit Arch Insert (N) ROWS Import Data Row (Y) PARFILE Parameter file name log screen output log file constraints Import restriction (Y) Destroy Overwrite tablespace Data File (N) IndexFile Write the specified file Skip_unusable_indexes Skip Using Maintenance of Unavailable Index (N) Analyze Execute Dump Analyze statement (Y) Feedback in the file Demath_novAlidate each x row (0) TOID_NOVALIDATE SUVATION FILESIZE FileSize Each Dimensional RecalCulate_statistics recalculates statistics (N)
(2)
First, create an ExpData.sql file
Userid = RMTAFIS / 3 Here you write your username and password Buffer = 32768OWNER = RmTafi here Write exported user file = E: /EXP/RmTafis.dmp export file, can be relative path ROWS = YGRANTS = YCompRESS = YCONSISTENT = Y
Second, establish an expdata.batexp parfile = expdata.sql If it is 805EXP80 PARFILE = EXPDATA.SQL Double-click EXPDATA.BAT to export data
7. If it is in the LIKE variable, it does not use index. Conversely, not starting with '%', and there is corresponding index, it will use index. Specifically you can take a look at Plain Plan.
8, copy space table structure create table new_tableas select * from old_table where 1 = 2; copy table (including record) create table new_tableas select * from old_table;
9. Import a user under another user, but you need to change the name first to export all the tables with EXP; use IMP to import the exported table to new users; under the new user, execute select 'rename table || TNAME || 'to new_' || tname || ';' from tab where tabtype = 'table'; saved the above query results into a SQL file, and execute it after processing.
10, audit step
Modify the parameter file init.ora, the parameter audit_trail value is true; restart the database; open the audit Audit session; (Audit session by username) Execute the login operation; check the audit results: select * from dba_audit_session; select * from sys.AUD $; select * from sys.aud $; select * From dba_audit_trail; select * from dba_audit_exists; About Audit:
In order to make Oracle8i's audit function, you must modify the init_trail initial parameters in the database parameter file, and this modification does not control Oracle8i to record the generated audit record into the audit traces, due to status, privileges and mode objects have been modified, thus auditing The default value is not available, and its parameters should be set to none. The following is listed below the parameters available for audit_trail
DB_ Make database audits and all direct audits to the traces of database audits, OS_ is a database audit based on directly auditing to record the operating system is very concentrated. NONE_ is not available
11, bfile usage
(1), Create or Replace Directory Bfile_Test as '/ Oracle / ORADATA / BFILES';
(2) Grant Read on Directory Bfile_Test to Scott;
(3) Host ls -l /oracle/oradata/bfiles/1.txt
(4) Connect Scott / Tiger Create Table Bfiles (ID Number, Text Bfile);
(5), INSERT INTO BFILES VALUES (1, BFileName ('bfile_test', '1.txt'));
12. How do I uninstall Oracle under Windows 2000? I. System Environment: (1), Operating System: Windows 2000 Server, Machine Memory 128M (2), Database: Oracle 8i R2 (8.1.6) For NT Enterprise (3), Installation Path: D: / Oracle
Second, uninstall step: (1), start-> set -> Control Panel -> Administrative Tools -> Services Stop all Oracle services.
(2), start-> program -> Oracle - OraHome81-> Oracle Installation Products-> Universal Installer Uninstalling All Oracle Products
(3), running regedit, select HKEY_LOCAL_MACHINE / SOFTWARE / ORACLE, press Del to delete this entry.
(4), running regedit, select HKEY_LOCAL_MACHINE / SYSTEM / CURRENTCONTROLSET / SYSTEM / CURRENTCONTROLSET / SERVICES, scroll through this list, delete all Oracle entry (5), from the desktop, STARTUP group, program menu, delete all groups and icons related to Oracle
(6), restart the computer, then restart to completely delete the directory where Oracle is located
(7), delete the file related to Oracle, select the default directory of Oracle C: / Oracle, delete this entry directory and all subdirectories.
And remove the following files from the Windows 2000 directory (generally C: / WinNT)
Oracle.ini, ORADIM80.INI
(8) If there is a tag section of [Oracle] in the win.ini file, remove this paragraph
-------------------------------------------------- ------------------ 13, how to run the script using SQLPLUS and SVRMGRL
(1), call with SQLPLUS:
C: /script.txt content startup;
Command line: SQLPlus Internal / Oracle @c: /script.txt
(2), call with SVRMGRL: c: /script.txt content
CONNECT INTERNAL / ORACLE; STARTUP;
Command line: SVRMGRL @c: /script.txt
-------------------------------------------------- ------------------ 14, Oracle Temporary Table
Create Global Temporary Table TableName (Col1 Varchar2 (10), Col2 Number) On Commit Preserve (Delete) Rows; This temporary table does not occupy the table space, and the different session can not see each other's data after the session ends The data is automatically emptied. If Delete Rows is selected, the data is emptied when submitted, and the preserve has ended until the end of the session.
----------------
In Oracle8i, two temporary tables can be created: (1) Temporary table CREATE GLOBAL TEMPORY
(2) The transaction unique temporary table Create Global Temporary
Temporary table is only saved the data used by the current session (session), and the data exists only during the transaction or session.
Create a temporary table through the Create Global Temporary Table command, for a temporary table of the transaction type, data is only existed during the transaction, and the data is present during the session. The data of the session is private for the current session. Each session can only see and modify its own data. The DML lock does not add data on the temporary table. The existence of the following statement control line. ● On Commit Delete Rows Table Name Row only visible during the transaction ● On Commit preserve rows table name visible during the entire session
You can create an index, view, and starting with the temporary table, you can import the definition of the export table with the Export and Import tools, but cannot export data. The definition of the table is visible to all sessions.
For example: Create Global Temporary Table Temp_Tab1 (Table_name Varchar2 (20), Primary_Key Varchar2 (100), Field Varchar2 (1000)) on Commit Preserve Rows;
Create Global Temporary Table Temp_Tab2 (Table_name Varchar2 (20), Primary_Key Varchar2 (100), Field Varchar2 (1000)) on commit delete rows;
15. How to use OEM first database service, and then check Oracle Manager service. Otherwise, rebuilding the archive library, if you can't do it, you will change the contents of the OracleHome / Network / SqlNet.ora file SQLNET.AUTHENTICATION_SERVICES = (NTS) to SQLNET.AUTHENTICATION_SERVICES = (NONE)
Sign in Sysman / Oem_Temp
16, TNS: No listener problem.
(1) Check if the listener service is started, if not started, run the lsnrctrl start. (2) Check if the listener name, server IP, and database names are correct. (3) Check if the server name in TNSNames.ora, the server IP, the database name is correct.
17, Linux, Unix automatically launch Oracle service
(1)
! / bin / sh
# chkconfig: 345 51 49 # Description: Starts The Oracle Dabase DEAMONS #
ORA_HOME = / U01 / App / Oracle / Product / 8.1.7 Ora_owner = Oracle Case "$ 1" in 'start') echo -n "starting oracle8i:" su - $ ora_owner -c $ ora_home / bin / dbstart touch / var / LOCK / SUBSYS / ORACLE8I ECHO ;;
'stop')
Echo -n "shutting down oracle8i:" su - $ ora_owner -c $ ora_home / bin / dbshut rm -f / var / lock / subsys / oracle8i echo ;;
'Restart')
Echo -n "Restarting Oracle8i:" $ 0 STOP $ 0 Start Echo ;;
*) echo "Usage: Oracle8i {start | stop | rest}" EXIT 1
ESAC EXIT 0
I simply add Su - $ ora_owner -c $ ora_home / bin / dbshut, add Su - $ ora_owner -c $ ora_home / bin / lsnrctl start But Listener is not started when the system is started (2)
/etc/rc.local is changed as follows, you can use Touch / Var / Lock / Subsys / Local # echo 2147483648> / proc / sys / kernel / shmmmaxecho -n "Starting Oracle Database:" DATE "% D% T% a "Su - oracle -c" lsnrctl start "#su - oracle -c" sqlplus / nolog @ Startmaster.sql "echo -n" Oracle Database Started: "Date "% D% T% a "-------- ------- The first # is the second # of the Co-shared memory size # is starting the database.
(3) ftp://ftp.rpmfind.net/linux/rhcontrib/7.1/i386/oraclerun9i-1.0-1.i386.rpm
Download this package and install it.
There are some places to modify each file. Once the configuration is complete, you can find it in the system service configuration, select it, it is possible to start.
18, the problem of insufficient returning segments
(1) First, let the rollback segment taken off. If it is not easy to use, then taken off. Did so good. Alter rollback segment rollbackname offline; (2), increasing the size of the returning segment data file ALTER DATABASE DATAFILE 'DATAFILE' RESIZE 200M;
19, WinNT transplants to Win2000
No exp and IMP
Once the database is stopped, you can make a full backup of a database.
Built a same name in Win2000, just build, smaller, better, can shorten the time. It is possible to restore the database backup of WinNT to the Win2000. But the directory of the establishment must be the same. (Can also be different, but you need to change the connection of the data file)
I have repeatedly transplanted data like this, and there is no loss. Note: Because data is important, it is recommended that you first eXP backup. This is our habit.
20. Oracle SQL Plus Worksheet garbled problem.
DBAPPSCFG.Properties, modify the file to solve the above problems. $ Oracle_home / sysman / config directory, modify # sqlplus_nls_lang = American_america.we8iso8859p1 is SQLPLUS_NLS_LANG = American_America.zHS16GBK.
For Windows operating systems, you also need to modify one # SQLPLUS_SYSTEMROOT = C: // Winnt40 is SQLPLUS_SYSTEMROOT = C: // WinNT
If the main directory of the operating system is under WinNT of the C disk
For later modifications, only the Windows operating system is performed, the Unix operating system is not required. If this item is not modified in the Windows operating system, in Oracle Enterprise Manager, the connection system is connected to the system, it is prompted as follows: ORA-12560 TNS: protocol adapter error or ORA-12545 Connect Failed Because Target Host or Object Does NOT EXIST Re-emerge Connect SQL Plus Worksheet
21, DROP off the name is a small-writen table (enclosed in double quotes). Drop Table "Tablename" Select * from "TableName"
22. Date display format Note: Simplified Chinese "" enclose) other countries do not need "", ",",,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,, ;------------Thursday------------
23, a stored procedure reading a table information from Oracle
The stored procedure can be implemented in the VC: first modify the init.oraa, for example: uTL_File_Dir = / usr // path is the disk where Oracle is located: / usr This process Save the code of the user TEMP P1 process to the Oracle installation disk / USR / TEXT.TXT the create or replace procedure tESTis file_handle utl_file.file_type; STOR_TEXT VARCHAR2 (4000); N NUMBER; I NUMBER; beginI: = 1; SELECT MAX (LINE) INTO N FROM ALL_SOURCE WHERE OWNER = 'TEMP' AND NAME = 'P1'; file_handle: = UTL_FILE.FOPEN ('/ usr', 'test.txt', 'a'); while i <= n loop select text insti stor_text from all_source where owner = 'Temp' and name = ' P1 'and line = i; i: = i 1; utl_file.put_line (file_handle, stor_text); end loop; utl_file.fclose (file_handle); commit; end test; /
24. About modifying the column width (1) of Oracle, no matter how it is backup data. (2) If there is no data, the width can be modified. For example, Number, Char, Varchar2 (3), if there is data, you can increase the width. For example, Number, Char, Varchar2 Note: You can not reduce the width. (4), grammar: Alter Talbe Tablename Modify ColumnName ColumnPE NOT NULL
25, how to view the user's stored procedures and functions
Select Name, Text from user_source where name = procedurename and type = 'procure' order by line;
26. Automatically launch Oracle services in batches (Win2000)
Net Start OraclesIDoServicesId is an instance name of Oracle's instance name
27, when you lock, just lock TB1
SELECT TB1.R1 from TB1, TB2 WHERE TB1.R2 = TB2.R2 and TB2.R1 = XXX for Update of TB1.R1 NOWAIT
28, get the information
desc tablenameselect cname from col where tname = 'TABLENAME'; select column_name from user_tab_columns where table_name = 'TABLENAME'; select column_name from ALL_tab_columns where table_name = 'TABLENAME'; select column_name from dba_tab_columns where table_name = 'TABLENAME'; select column_name from user_col_comments where table_name = 'TABLENAME'; select column_name from all_col_comments where table_name = 'TABLENAME'; select column_name from dba_col_comments where table_name = 'TABLENAME'; 29, the trigger is not valid (login_on)
SVRMGRLCONNECT INTERNAL / ORACLALTER TRIGGER login_on disable;
Enables triggers to invalid Alter Trigger YourTriggerName Disable
If it is for all triggers for a table: Alter Table yourtables, ALL TRIGGERS
30, if you call the stored procedures and functions in SQLPLUS.
Call can only call the stored procedure, add the parentheses (); exec procedurename; () can not add ()) call function name (parameter) from DUAL;
31. If you call the DML statement in the function, you cannot call the SELECT statement.
32. When is the redo log buffer Write to Redo Logfile (1) It must be called (5) before the data write process, which typically checks the data writing process when the log group is switched during the log group or by the initialization parameter setting in CheckPoint.
33. Oracle HTTP Server rushes to the original Web Server, how to solve it?
(1). If your original HTTP Server is done by other publishing tools such as IIS, you can stop the Oracle HTTP Server service in the service and change it manually. (2). If the original HTTP Server is released with Apache, you can change the parameters in http.conf.
34. About Creating Reconstruction View Index
Create an index: create index ind_name on table_name (col1, col2, ...);
Reconstruction Index: Alter Index Ind_Name Rebuild;
View index:
Select * from user_indexes where index_name = 'ind_name';
35. How to kill the user's process one | According to the user's application and SQL statement, find the user's session in DBA Studio and disconnect it. (1), to kill a session should first know its SID and Serial #, suppose you already know. (2), SELECT PADDR FROM V $ sessions where sid = v_sid and serial # = v_serial # SELECT SPID from V $ process where addr = paddr (Isolated on the above statement); (3), using the alter system kill session 'v_sid , v_serial # 'immediate; try to go to the operating system under Linux and UNIX to perform: Kill -9 SPID (Isolated on the above statement) 36, Oracle checks the list of statements
Select a.owner, A. Object_name, B.xidusn, B.xidslot, B.XIDSQN, B.Session_ID, B. Oracle_username, B.OS_USER_NAME, B.PROCHINE, C.STATUS, C.MACHINE, C.STATUS, C , C.SID, C. Serrial #, C.Program from ALL_OBJECTS A, V $ locked_Object B, SYS.GV_ $ session c where (a.object_id = B.Object_id) and (b.Process = C.Process) ORDER BY 1, 2 Kill: Alter System Kill Session 'SID, Serial #' 37, Oracle login problem, username, and password.
Can be entered directly: internal / oracle @ serivce_name sys / change_on_install @ serivce_name system / manager @ serivce_name scott / tiger @ serivce_name Note: 9i No internal / oracle if you choose a typical installation there scott user if the custom may not be mounted scott user if This machine can omit @serivce_name
OEM: (Oracle Enterprise Manager) SYSMAN / OEM_TEMP
38, modify the column name
Oracle9i: Alter Table XXX Rename Column XX to Yy;
Oracle8i & Lower VersionConnect Sys / PaSSED; UPDATE COL $ Set Name = xx where obj # = Object ID and name = field (generally don't use this, can cause unexpected results) Note: It is best to delete and build new columns
39. Move the tablespace where the user mode object is located to the new table space (1). Create the new tablesapce (2). Alter User test default tablespace test_data; (3). Alter User test quota unlimited on test_data; (4) . alter table the_table_name move tablespace test_data; build script: select 'alter table' || tname || 'move tablespace test_date;' from tab where tabtype = 'TABLE' (5) rebuild the indexes; 40, the use of backup or OEM EXP. Step Win2000:
(1). Control Panel -> Administrative Tools -> Computer Management -> Local Users and Groups -> User -> New User Sys and Sysman (Sys and sysman accounts are the same as the account of the login database); (2). Control Panel -> Administrative Tools -> Local Security Policy -> Local Policy -> User Rights Assignment -> As a batch job login -> Add Sys and Sysman two accounts. (3). Use Enterprise Manager Configuring Auxiliary Tools → ORACLE - ORAHOME81 → Enterprise Manager → Configuration Assistanta, use Configuration Assistant Tools to create a new information archive.
(4). Control Panel -> Administrative Tools -> Services, check whether OracleraHome81Management Server starts, if not started, start the service manually. (5). Log in to DBA Studio in sysman / oem_temp (second option: Log in to Oracle Management Server), change the password immediately to the user's password for the user sysman built in NT. (6). From Sysman / *** (Bluesky) from Start → ORACLE - ORAHOME81 → Console Log in to the console. In the system → preference → preferred identity certificate (my preferred identity is set as follows :) Default Node: Name: sysmandefault Database: Name: SYS (7). After searching / adding nodes, log in to this node with sysman / ***, logging in to the database (that is, the result of the preferred identity setting). (8). In Tools → Backup Administration → Wizard → Predefined Backup Policy (Custom Backup Policy) → Submit Backup Plan (9). From the beginning → Program → Oracle - OraHome81 → Console Log in to the console, see if your backup is successful, if you are not successful, you can click backup to see the details. (I didn't succeed in the first time, and later I modified the system's temporary directory C: / Winnt / Temp → C: / TEMP / SYSTMP, restart the machine is OK)
41. How to modify the internal password The following is Oracle8 8i you can use it.
(1), enter DOS
(2), the default internal password file Under C: / ORANT / DATABASE, it is hidden attribute, file name is related to the database instance name.
If the default Oracle instance is named orcl, the INTERNAL password file is named PWDORCL.ORA (3), build a new INTERNAL password file, and a new name is pwdora8.ora
ORAPWD80 file = pwdora8.ora password = b Entries = 5 - Note: Password item must use uppercase and do not use single quotes
(4), copy the pwdora8.ora file to the C: / ORANT / DATABASE directory
(5), running regedit, modify the password file pointing
(6), find hkey_local_machine / Software / Oracle item
Locate Ora_orCl_PWFile sub-item, change its value to c: /orant/database/pwdora8.ora
(7), turn off the Oracle database, restart
(8), enter the SVRMGR30 service, test the internal password to change success
42. The decision method of failure of the credentials.
Cause: Since Oracle cannot apply OS certification, the credential retrieval failure solution: (1). Open SQLNET.ORA to NetWork / Admin to modify SQLNET.AUTHENTICATION _SERVICES = (NONE). (2). Start Net8 Configuration Assistant -> Select the third local network service name configuration -> Delete ... (Delete the original local network service name)
(3). Repeat the second step -> Add .. (New local network service name)
(4) .Restart Oracle Note: NTS is WinNT authentication method
43. Command line compilation stored procedure
Alter Procedure Procedure_name Compile;
44. About how to establish a database link (dblink)
You can set the server's name or IP address to the machine you need to connect by establishing a client database network service name.
If you have to connect it in an app, do your work, then processed as follows
Creating a database connection Create Database Link DBaselinkName Connect To UserName Identified by Password USERNAME IDENTIFIED BY Password Using 'NetServiceName'; DBaseLinkName is the created data connection name UserName is a username Password is a user's password NetServiceName is a connected database network service. Name or database name
Query the table instance of the establishment of data connection Select * from tablename @ dbaselinkname;
Note: If the CREATE DATABASE LINK DBaseLinkName CONNECT TO UserName IDENTIFIED BY Password USING 'NetServiceName'; NetServiceName is the database name in the init.ora Review: global_names = true else in global_names = falseinit.ora: global_names = false 45, Object Browser7.0 Chinese version of the crack method
In the catalog of Object Browser, find the Deisl1.Isu file, open with Notepad, see the garbled? It doesn't matter, putting Stirling Technologies, Including the string of the string before (if any), let's add a space before stirling (must be added), save, quit, re-run, although there is also a prompt Enter the verification information, but don't use him, directly determine it. Is it possible? Guarantee. 46, Error ORA-01536: SPATA EXCEEDEDED INER SOLVER SOLI I: (1) ALTER User UserName Quota 100M on TableSpacename; (2) Alter UserName Quota Unlimited on TableSpaceename ; (3) GRANT UNLIMITED TABLESPACE TO UserName;
47, how to capture all the operations of the SQL statement in Oracle
Select OSuser, Username, SQL_Text from V $ Session A, V $ SQLText B Where A.SQL_Address = B.Address Order by Address, Piece
48. How to implement a self-add field in Oracle:
(1) The first method ORACLE common practice is to use a sequence to generate triggers and the self-energizing field .CREATE SEQUENCE SEQname INCREMENT BY 1 START WITH 1 MAXVALUE 99999999 / CREATE TRIGGER TRGnameBEFORE INSERT ON table_nameREFERENCING NEW AS: NEWFOR EACH ROWBegin SELECT Seqname.nextval Into: New.fieldname from Dual; End;
(2) Second method: create or replace Trigger Tr1 Before INSERT ON TEMP_TABLE for Each RowDeclare COM_NUM NUMBER; BegInselectr Max (ID) INTO COM_NUM from Temp_Table;: New.ID: = COM_NUM 1; END TR1;
49, JOB use:
Modify the initsid.ora parameter job_queue_processes = 4 8i, 9i (number of Jobs allowed simultaneously) JOB_QUEUE_INTERVAL = 10 8i job_queue_keep_connections = true 8i
DBMS_Job.Submit (: jobno, // job "Your_Procedure; ', // To execute the process trunc (sysdate) 1/24, // Next time' trunc (sysdate) 1/24 1 '/ / Interval time); Delete Job: DBMS_Job.Remove (Jobno); Modify the action you want to do: DBMS_Job.What (a); Modify the next execution time: dbms_job.next_date (job, next_date); modify the interval : Dbms_job.interval (job, interval); Stop Job: dbms.broken (job, broker, nextddate); start JOB: dbms_job.run (jobno); Note: Be sure to commit;
Example: variable jobno number; begin dbms_job.submit (: jobno, 'propdemo;', // procdemo is the process name sysdate, 'sysdate 1/720'; commit;
50, how to configure MTS
Modify the initialization parameter file Add the following: mts_dispatchers = "(protocol = tcp) (Disp = 2)" MTS_MAX_DISPATCHERS = 50MTS_SERVERS = 20 mts_max_servers = 50
51, take out the last record of a table
Select * from (SELECT ROWNUM ID, TNAME. * from TNAME) a where a.id = (Select Count (*) from A);
52. Redolog is deleted and what method can be restored!
First mount database, then create the same name file redo01.log, redo02.log, redo03.log then execute the Alter Database Clear Logfile Group N For Group, execute the Alter Database Clear UnarchiveD logfile group n, then Open, OK In
53, Oracle Common Service
Several main:
OracleOraHome81TNSListener listening service OracleServiceSID ORACLE service OracleOraHome81Agent agency services OracleOraHome81CMan intelligent connection management services OracleOraHome81HTTPServer APACHE WEB service OracleOraHome81ManagementServer ORACLE Enterprise Manager service OracleOraHome81Names ORACLE Naming Service
The remaining is not commonly used.
54, Oracle's hot spare
Oracle's backup is performed when the database is not closed.
The principle stops, you will find this book.
Equipped example: Backup table space Users
ALTER TABLESPACE USERS BEGIN BackupCopy Uses TableSpace Data Files to Backup Directory ALTER TABLESPACE USERS End Backup
55, resulting in a solution that does not work
Your question I just handled, is caused by the Optimizer_Mode parameter, the default value of this parameter is Choose, that is, if there is Statis, the query is based on the COST, otherwise the rule-based approach, because you can have the following Several solutions. (1) Simply set Optimizer_Mode = Rule in INIT
(1). View related processes in the database Select A.SID, A.Serial #, A.Program, A.Status, Substr (A.Machine, 1, 20), A.Terminal, B.spidFrom V $ Session A, V $ process bwhere a.paddr = b.addrand b.spid = & spid;
(2). View objects and related sessions in the database Select A.SID, A.SERIAL #, A.USERNAME, A.PROGRAM, C.OWNER, C.Object_name from v $ session a, v $ located_object B , all_Objects cwhere a.sid = b.session_id andc.object_id = B.Object_id;
(3). View related sessions are being executed SQLSELECT SQL_TEXT from V $ SQLAREA WHERE Address = (SELECT SQL_ADDRESS FROM V $ Session Where Sid = & SID);
57. View the IP address Select Sys_Context ('Userenv', 'IP_Address') from Dual;
58. Do not enter the username and password when running SQLPLUS, use Connect Sqlplus / Nolog SQL> Connect Scott / Tiger after entering
59, check the current session
Userenv () function
select userenv ( 'language') from dual character set select userenv ( 'isdba') from whether dual DBA select userenv ( 'sessionid') from dual sessionid select userenv ( 'TERMINAL') from dual client name select userenv ( 'INSTANCE' ) from the number of examples
The SYS_CONTEXT () function select SYS_CONTEXT ( 'USERENV', 'CURRENT_SCHEMA') from dual; current mode select SYS_CONTEXT ( 'USERENV', 'CURRENT_SCHEMAID') from dual; current mode ID select SYS_CONTEXT ( 'USERENV', 'CURRENT_USER') from dual Current User Select Sys_Context ('Userenv', 'DB_Name') from Dual; Database Select Sys_Context ('Userenv', 'Host') from Dual; Host ........ 60, delete repetition column
(1) DELETE FROM table_name A WHERE ROWID> (SELECT min (rowid) FROM table_name B WHERE A.key_values = B.key_values); (2) create table table2 as select distinct * from table1; drop table1; rename table2 to table1; (3) Delete from mytable where rowid not in (select max (rowid) from mytable group by column_name); (4) delete from mytable t1 where exists (select 'x' from my_table t2 where t2.key_value1 = t1.key_value1 and t2 .key_value2 = t1.key_value2 ... and t2.rowid> t1.rowid);
61, ORA-12571: TNS: Packet Writer Failure (Package)
(1) This error encountered in the client, usually re-connecting the server is fine. When the server is restarted, this error will also be encountered in the client. This error is you encountered on the server or the Client? The most common way is to add tracking, check the trace record, analyze the cause of the error. This error will also occur, such as the network route is not configured. (2) Installing anti-virus software (3) server-side IP is changed (4), finally, recreate the listener 62, Oracle service cannot be automatically started.
You can get out of the SQLNET.AUThentication_Service = (NTS) comment in the OracleHome / Network / Admin / SqlNet.ora file.
63, incomplete point in time recovery
Shutdown ImmediateCopy Backup files to the directory you need to recover STARTUP MOUNTRECOVER DATABASE UnTil Time '2002-12-26 09:00:00'
Alter Database Open Resetlogs
Check it carefully and do not happen.
64. How to set up the query timeout
SELECT / * TIMEOUT 30 * / * from VeryLargetable65, modify the character set
(1), Alter Database Character Set Simplified Chinese_China.zHS16GBK;
(2), Update ProPs $ set value $ = 'zhs16cgb231280' where name = 'nls_characterset'; Update Props $ set value $ = 'zhs16cgb231280' where name = 'nls_nchar_characterset';
Recommendation is not used (2)
Note: (1), execute the ALTER DATABASE Character Set must have Sysdba permissions, and execute (2) in Startup Restrict mode (2), the original character set must be a true subset of the target character set (that is, the prodigal is only from we8359p1 To zHS16GBK reasons) (3), the Clob field can be problematic, it is recommended to export the table with the clob field before the conversion is returned (4) after the conversion, the conversion is not reversible, so before doing this operation Recommended to do database full backup
66, modify the database name
(1), start SVRMGRL, back up the control file in text mode, Oracle> SVRMGRL SVRMGRL> Connect Internal SVRMGRL> ALTER SYSTEM Backup ControlFile to TRACE (2), editing generated tracking file, change Create ControlFile Reuse Database "CTC" in udump directory Reuse in NoreSetlogs ArchiveLog is set to set the Create ControlFile, and then start the library, start to Nomount Svrmgrl> Shutdown Immediate SVRMGRL> Startup Nomount (4), execute the Create Controlfile Open Database SVRMGRL> ALTER DATABASE OPEN If prompted with the resetLogs option, use SVRMGRL> ALTER DATABASE OPEN RESETLOGS (8), and modify the initialization parameters
67, Rownum usage
Select * from (select t. *, rownum id from dept t) Where id betWeen 1 and 20
68, Oracle's internal parameters
Select A.KSPPINM Name, B.Ksppstdf Default_val, A.Ksppdesc Description from x $ ksppi a, x $ ksppcv b Where a.indx = B.indx and subs (a.ksppinm, 1, 1) = '_' order by A.KSPPINM
69,9i Installation Times Areasqueries error solutions include IAS and IDS to change all of the installation source file directory to English letters or numbers Note: Can't be a Chinese path
70, how do I know how much it is available in a table space?
(1), SELECT UPPER (F.TABLESPACE_NAME) Table Space Name, D. Tot_Grootte_MB "Table Space Size (M)", D. Tot_Grootte_MB - F. Total_Bytes "Used Space (M)", Round ((D. Tot_Grootte_MB - F. TOTAL_BYTES) / D. Total_BYTES) / D. Tot_Grootte_MB * 100, 2) "Use", f. Total_BYTES "Idle Space (M)", F.max_Bytes "Maximum Block (M)" from (SUM (SUM (Bytes) / (1024 * 1024), 2) Total_Bytes, Round (Max (Bytes) / (1024 * 1024), 2) Max_Bytes from sys.dba_free_space group by tablespace_name) f, (Select Dd.TablesPace_Name, Round (Sum (DD.bytes) ) / (1024 * 1024), 2) Tot_grootte_Mb FROM sys.dba_data_files dd GROUP BY dd.tablespace_name) dWHERE d.tablespace_name = f.tablespace_name ORDER BY 4 DESC (2), select tablespace_name, round (sum (bytes) / 1024 / 1024, 2) "M" from DBA_FREE_SPACE GROUP BY TABLESPACE_NAME71, CRECK PL / SQL Developer method
(1), install PL / SQL Developer (2), open the program plsqldev.exe with UltraEdit, set UltraEdit to 16 Enciprome mode (4), look up string: ba 1e 00 00 00 2b D0 modification: 2b D0 is: 4A 90 (5), the storage exit (6), run PLSQLDEV.EXE, if you prompt you for 29 days, then congratulations!
72, make index invalid
Alter index idx unusable; alter index idx_acctno disable; (Only to a function based index)
73. Take the specified user in SQLPLUS to set autotrace on / off in SCOTT users as an example:
SQL> CONNECT scott / tiger connected SQL> @ $ ORACLE_HOME / RDBMS / ADMIN / UTLXPLAN.SQL Table created SQL> CONNECT / AS SYSDBA connected SQL> @ $ ORACLE_HOME / SQLPLUS / ADMIN / PLUSTRCE.SQL drop role plustrace;... Role Dropped. CREATE ROLE PLUSTRACE; ROLE CREATED. GRANT PLUSTRACE TO DBA with Admin Option; Grant succeeded.
SQL> Grant Plustrace to Scott;
Grant succeeded.
SQL> Connect Scott / Tiger
Connected.SQL> Set autotrace on
SQL>
74, four states on constraints
Disabled Novalidate: When constraints are not possible, the constraint rules cannot be enforced above the data (contained in the constraint). But the definition of constraints is saved in the data dictionary. This method is useful when performing a data warehouse roll or loading and speeds up. Enabled Novalidate: It is invalid that the table of this state can contain illegal data, but it is impossible to add new illegal data. Enabled Validate: Enable valid, an enable constraint is forced, and the data check is valid.
75, call the stored procedure in SQLPLUS
SET ServerOutput on Declare Out_Param Varchar2 (100); Begin Your_Proc (1, Out_Param); DBMS_OUTPUT.PUT_LINE (OUT_PARAM); END; / SET Serveroutput OFF
75, generate three files for system tables and stored procedures.
Cat * .sql dbms * .sql utl * .sql
76, use of the date in JOB
Every month: Last_Day (sysdate) 1 Every quarter of each quarter: to_date (decode (sysdate, 'q'), '1', to_char (sysdate, 'yyyy') || '0101' , '2', 'YYYY') || '0401', '3', TO_CHAR (SYSDATE, 'YYYY') || '0701', '4', TO_CHAR (SYSDATE, 'YYYY') | | '1001'), 'YYYYMMDD') Daily: sysdate 1 for each week: decode (to_char (sysdate, 'w'), '1', sysdate 7, to_char (sysdate, 'w'), '2 ', sysdate 6, to_char (sysdate,' w '),' 3 ', sysdate 5, to_char (sysdate,' w '),' 4 ', sysdate 4, to_char (sysdate,' w '),' 5 ', sysdate 3, to_char (sysdate,' w '),' 6 ', sysdate 2, to_char (sysdate,' w '),' 7 ', sysdate 1)
Every week x at 3 o'clock in the afternoon: Interval (21, 'NEXT_DAY (Trunc (Sysdate), X 1) 15/24');
The first week of each quarter X: Interval (21, 'NEXT_DAY (Trunc (Sysdate,' 'q' '), 3), 5)');
77, using Execute Immediate problem
8i or more support EXECUTE IMMEDIATE 8.05 can only use dbms_sql to best use Execute Immediate
78. Method for deleting data files in table space in Oracle9i DROP TABLESPACE TBSNAME INCLUDING CONTENTS
79, find out the numbers in the string
Select Translate ('2krw229', '0123456789abcdefghijklmnopqrstuvwxyz', '0123456789') "Translate Example" from Dual / 2229
- All numbers: select * from your table where translate (your column, '0123456789', ') =' ';
Select * from your table Where Trim (RTRIM (RTRIM (RTRIM (RTRIM (RTRIM (RTRIM (RTRIM (RTRIM (RTRIM (RTRIM (RTRIM (RTRIM (RTRIM (RTRIM (RTRIM (Replace (Col_name, '0123456789', '')))
80. Analysis Table Analyze Table Mzbs.db_code Estimate Statistics Sample 20 percent;
81, Table Space Management and User Management
- View tablespaces and data files
SELECT FILE_NAME, TABLESPACE_NAME, AUTOEXTENSIBLE from DBA_DATA_FILES
- Data table space
CREATE TABLESPACE USER_DATA LOGGING DATAFILE 'D: /ORACLE/ORADATA/ORCL/test.DBF' SIZE 50m REUSE, 'c: /USERS01112.DBF' SIZE 50m REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE 16383M EXTENT MANAGEMENT LOCAL
- Temporary table space
Create Temporary TableSpace User_Data_temp Tempfile 'D: /Temp0111.dbf' Size 50m Reuse Autoextend On Next 1024k MaxSize 16383M Extent Management Local Uniform Size 1024K
- Add data files
ALTER TABLESPACE USER_DATA ADD DATAFILE 'C: /USERS01113.DBF' Size 50M;
ALTER TABLESPACE USER_DATA ADD DATAFILE 'C: /USERS01114.DBF' Size 50M Autoextend ON;
- Delete table space
DROP TABLESPACE USER_DATA INCLUDING CONTENTS;
- Modify data file size ALTER DATABASE DATAFILE 'C: /USERS01113.DBF' Resize 40M;
- Create a user, give permission
Create User_Data profile default identified by user_data default tablespace user_data temporary tablespace user_data account unlock; grant connect to user_data; grant resource to user_data;
- Move the table to another. Alter Table TableName Move TableSpace TableSpacename;
- Create an index
Create Index IndexName on TableName (ColumnName);
Create Index IndexName on Tablename (ColumnName) TableSpace TableSpacename
- Re-establish index
Alter Index IndexName Rebuild TableSpace TableSpace; - Create a table
Creae Table Tablename (Column1 Colutype Default (Value) NOT NULL;
- Construction of the index store allocation
CREATE TABLE summit.employee (id NUMBER (7) CONSTRAINT employee_id_pk PRIMARY KEY DEFERRABLE USING INDEX STORAGE (INITIAL 100K NEXT 100K) TABLESPACE indx, last_name VARCHAR2 (25) CONSTRAINT employee_last_name_nn NOT NULL, dept_id NUMBER (7)) TABLESPACE data;
- Establish primary key
Alter Table TablenameAdd Constraint Constraintname Primary Key (Column1, Column2)
- Cause the constraint
Alter Table Tablename Enable NoValidate Constraint Constraname
Alter Table TableName Enable Validate Constraint Constraname
- Delete constraint
Alter Table TableName Drop Constraint ConstraintName
Drop Table Tablenaem Cascade constraints; (delete the foreign key used after deleting the table)
- Add columns to the table
Alter Table TablenameAdd Column Colutype Default (Value) Not null;
- Add the default value to the column
Alter Table Tablenamemodify ColumnName Default (Value) Not null;
- Increase the foreign key Alter Table TableName Add Constraint Constraintname Foreign Key (Column) References Table1Name (Column1);
