[Original] Oracle FAQs

SQL Statement1. obtain which users are using the database select username from V $ session; 2. obtain the SID select name from V $ database; 3. view the maximum number of sessions select * from V $ parameter where name like 'proc % '; 4. view all users in the database and all tables select owner, table_name from dba_tables; 5. determine from which machine a user logs on to the select machine of Oracle, terminal from V $ session; 6. query the permissions of each user, select * From dba_sys_privs; 7. query the current user object select * From user_objects; Table space management and user management  8. query table Space Information Select * From dba_data_files; 9. You do not need to enter the user name and password when running sqlplus. Connect
Sqlplus/nolog
SQL> connect Scott/tiger10. move the table to another tablespace.
Alter table tablename move tablespace tablespacename; 11. Create Table creae table tablename
(Column1 colutype default (value) not null)
(Column2 colutype default (value) not null); 12. Create the primary key alter table tablename
Add constraint constraintname primary key (column1, column2) 13. delete constraint alter table tablename drop constraint constraintname; drop table tablenaem cascade constraints; (delete the foreign key used after the table is deleted) 14. add the alter table tablename column to the table.
Add column colutype default (value) not null; 15. Add the default value alter table tablename to the column.
Modify columnname default (value) not null; 16. Create the primary key alter table tablename
Add constraint constraintname primary key (column1, column2) 17. Add the foreign key alter table tablename to the table
Add constraint constraintname
Foreign key (column) References table1name (column1); 18. re-create the index alter index indexname rebuild tablespace; 19. Create a user and grant the permission create user 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; 20. Modify the data file size alter Database
Datafile 'C:/users01113.dbf' resize 40 m; 21. Delete the tablespace drop tablespace user_data including contents; 22. Add the data file alter tablespace user_data
Add datafile 'C:/users01113.dbf' size 50 m; Alter tablespace user_data
Add datafile 'C:/users01114.dbf' size 50 m
Autoextend on 23. view the tablespace and data file select file_name, tablespace_name, autoextensible from dba_data_files; 24. Call the stored procedure set serveroutput on in sqlplus
Declare
Out_param varchar2 (100 );
Begin
Your_proc (1, out_param );
Dbms_output.put_line (out_param );
End;
/
Set serveroutput off 25. view the current session (1) userenv () function select userenv ('language') from Dual Character Set
Select userenv ('isdba ') from dual whether DBA
Select userenv ('sessionid') from dual sessionid
Select userenv ('terminal') from dual client name
Select userenv ('instance') from dual instance count (2) sys_context () function select sys_context ('userenv', 'current _ scheme') 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; select sys_context ('userenv', 'IP _ address') from dual; view IP 26. step Win2000 with OEM backup or Exp: (1 ). control Panel> Administrative Tools> Computer Management> local users and groups> Users> new users sys and sysman (accounts of SYS and sysman need and login database accounts are the same );
(2). Control Panel-> Administrative Tools-> Local Security Policies-> Local Policies-> User Rights Assignment->
Log in as a batch job-> Add two accounts: SYS and sysman.
(3). Use Enterprise Manager to configure Auxiliary Tools
Start → program → Oracle-orahome81 → enterprise manager → configuration Assistant
A. Use the configuration assistant tool to create a new archive. (4) Control Panel> Management Tools> service. Check whether the oracleorahome81managementserver is started. If not, manually start the service.
(5) log on to DBA studio with sysman/oem_temp (default)
(Option 2: log on to the Oracle Management Server), and immediately change the password of the user sysman you just created under NT.
(6) log on to the console with sysman/*** (bluesky) from start → program → Oracle-orahome81 → console.
In the system → preferences → preferred identification (my preferred identity settings are as follows :)
Default node: Name: sysman
Default Database: Name: SYS
(7 ). after searching for/adding a node, log on to the node with sysman/*** and log on to the database with sys/*** as sysdba (that is, the result set in the preferred identity ).
(8). Choose tools> Backup management> wizard> pre-defined backup policy (custom backup policy)> submit backup plan
(9 ). log on to the console from Start> program> Oracle-orahome81> console and check the activity (History) to check whether your backup is successful. If not, click Backup To View Details. (I did not succeed for the first time. Later, I modified the temporary directory c:/winnt/temp → C:/temp/systmp of the system and restarted the machine.) 27. modify the table column name oracle9i:
Alter table xxx rename column XX to YY; Oracle8i & Lower version
Connect sys/passed;
Update Col $ set name = xx where OBJ # = Object ID and name = Field
(Generally, this method is not recommended, which may lead to unexpected results)
Note: It is best to delete and create a new column 28. Oracle login issues, user names and passwords can be directly entered:
Internal/Oracle @ serivce_name
Sys/change_on_install @ serivce_name
System/manager @ serivce_name
Scott/tiger @ serivce_name
Note:
9i does not contain internal/Oracle
If you select a typical installation, Scott will apply.
If you do not need to install Scott
If it is a local machine, you can omit @ serivce_nameoem :( Oracle Enterprise Manager)
Sysman/oem_temp 29. How do I completely uninstall oracle in Windows 2000? I. system environment:
(1) Operating System: Windows 2000 Server, 128 MB of machine memory
(2) Database: Oracle 8i R2 (8.1.6) for NT Enterprise Edition
(3) installation path: D:/Oracle 2. Uninstall steps:
(1) Start-> Settings-> Control Panel-> management tools-> services
Stop all Oracle services. (2) Start-> Program-> Oracle-orahome81-> Oracle Installation products-> universal Installer
Uninstall all Oracle products (3), Run regedit, select HKEY_LOCAL_MACHINE/software/Oracle, and press del to Delete this entry. (4) Run regedit and select HKEY_LOCAL_MACHINE/system/CurrentControlSet/services to scroll through the list and delete all Oracle entries (5), from the desktop, and startup) in the group and program menu, delete all Oracle-related groups and icons (6), restart the computer, and delete the Oracle directory (7) delete Oracle-related files, select the default Oracle directory c:/Oracle, delete the entry directory and all sub-directories, and from the Windows 2000 directory (generally C:/WINNT) delete the following file oracle. INI, oradim80.ini (8), win. if the INI file contains a [Oracle] Tag segment, delete the segment -------------------------------------------------------------------- 30. Exp And IMP Specific usage  Exp username/password @ mzbs_61 full = y file = yourdata. dmp grants = y rows = y
IMP username/password full = y ignore = y file = yourdata. dmp grants = yexp mzbs/mzbs @ mzbs_61 file = C:/zzzzzzz. dmp grants = y rows = y
IMP mzbs/mzbs @ mzbs_61 file = C:/zzzzzzz. dmp grants = y ignore = y full = Y (1) exp parameter:
Keyword description (default)
----------------------------------------------
Userid username/password
Full export the entire file (N)
Buffer data buffer size
Owner owner username list
File output file (expdat. dmp)
Tables Table Name List
Compress imports a range (y)
Recordlength Io record length
Grants export permission (y)
Inctype incremental export type
Indexes export index (y)
Incremental export of record tracking (y)
Rows export data rows (y)
Parfile parameter file name
Constraints export limit (y)
Consistent cross tabulation consistency
Log File output by log Screen
Statistics Analysis object (estimate)
Direct direct path (N)
Triggers export trigger (y)
Feedback displays the progress of each X row (0)
Filesize maximum size of each dump file
Query:
Keyword description (default)
----------------------------------------------
Userid username/password
Full import of the entire file (N)
Buffer data buffer size
Fromuser User Name List
File input file (expdat. dmp)
Touser User Name List
Show only lists file content (N)
Tables Table Name List
Ignore ignore creation error (N)
Recordlength Io record length
Grants import permission (y)
Inctype incremental Import Type
Indexes import index (y)
Commit submits array insert (N)
Rows import data rows (y)
Parfile parameter file name
Log File output by log Screen
Constraints import restrictions (y)
Destroy overwrite the tablespace data file (N)
Indexfile writes table/index information to the specified file
Skip_unusable_indexes skips maintenance of unavailable indexes (N)
Analyze executes the analyze Statement (y) in the dump file)
Feedback displays the progress of each X row (0)
Toid_novalidate skips the verification of the specified type ID
Filesize maximum size of each dump file
Recalculate_statistics re-calculate the statistical value (N) (2) 1. Create an expdata. SQL file userid = rmtaisi/3 here write your username and password
Buffer= 32768
Owner = rmtafis' write and export users here
File = E:/EXP/rmtafiis. dmp exported file, which can be a relative path
Rows = y
Grants = y
Compress = y
Consistent = Y 2. Create an expdata. bat
Exp parfile = expdata. SQL
If it is 805
Exp80 parfile = expdata. SQL
Double-click expdata. BAT to export the data.  30. Full Database Import and Export  Exp username/password full = y file = yourdata. dmp grants = y rows = y
IMP username/password full = y ignore = y file = yourdata. dmp grants = y  31 .Insertion of single quotes SQL> insert into a values ('I' m good '); -- two ''can represent a' SQL> insert into a values ('I' | CHR (39) |'m good '); -- CHR (39) represents the character' SQL> insert into a values ('A' | '&' | 'B ');  32. Oracle dynamic library and configuration file 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