Oracle Basic Syntax

1, CREATE table space, table space name is database, directory is datafile ' d:\oracle\product\10.2.0\oradata\data01.dbf ', size is 100M, each auto expansion 10M, maximum unrestricted. Create tablespace database datafile ' d:\oracle\product\10.2.0\oradata\data01.dbf ' size 100M autoextend on next 10M maxsiz e Unlimited; 2. Delete table space database drop tablespace database; 3, query table space SELECT * from Dba_data_files; 4. Add data files to the table space. Alter TABLESPACE database add datafile ' d:\oracle\product\10.2.0\oradata\data02.dbf ' size 100m autoextend on 10M maxsize u nlimited; 5. Create user and specify default tablespace create user Wyy identified by wyy default Tablespace database; 6, query all users select * from All_users; 7, delete user drop users wyy cascade; 8. Delete Table space drop tablespace database including contents and datafiles cascade constraints;

Note: including contents and datafiles Delete table space contents, data files

Cascade constraints to delete a reference foreign key for a table space

8, authorizing the user

Grant Connect,resource to wyy  remove permissions revoke Connect,resource from wyy 9, querying permissions for the current user select * from user_role_privs;& nbsp;10, query All tables of the user select * from user_tables; 11, create a student student table with two columns  create table student (XH varchar2), XM VARCHAR2 ( 12), add a column to the Table Xbalter table student Add (XB VARCHAR2 (4))  13,   add comment to table comment on column STUDENT.XH is ' study number '  14, query database sidselect name from v$database 15, change a column field length for table   A, if no data   ALTER TABLE Student Modify XH varchar2 (  b), if you have data, create a new column, update the data to a new column, delete the original column, and then change the column name   ALTER TABLE student add Xh_temp varchar2 ( ;  Update student set xh_temp=xh;  commit;  ALTER TABLE student drop xh;  ALTER TABLE student Renam E xh_temp=xh;  16. Selection: SELECT * FROM table1 where range insert: INSERT INTO table1 (field1,field2) VALUES (value1,value2) & nbsp         INSERT INTO table1 values (value1,value2,value3,value4) Remove: Delete from table1 where range update: Update The Table1 set field1=value1 the where range  17, querying the table space for theRate Select A.tablespace_name tablespace name,       total tablespace size,       free table space remaining size,  &NB Sp     (total-free) tablespace usage size,       round ((total-free)/Total, 4) * 100 usage   from (sele CT Tablespace_name, ROUND (SUM (bytes)/(1024x768 * 1024x768), 4) free          from dba_free_space         group by Tablespace_name) a,        (SELECT tablespace_name, ROUND (SUM (byte s)/(1024x768 * 1024x768), 4) total          from dba_data_files         group by tablespace_name) B where A.tablespace_name = b.tablespace_name; 18, modifying the number of Oracle connections view Oracle MAX processes:sql> Show parameter Processes #最大连接SQL > alter system SET processes = value scope = SPFile; #修改连接   Restart the database for the number of connections to take effect.   19. Primary key and foreign key primary key (Primary key): A candidate key to uniquely identify a row in a table, a table can have only one primary key. Foreign key (Foreign key): A table field that is the primary key of another table.  20, copy the INSERT INTO Table 2 (COLUMN1,COLUMN2,COLUMN3) Select from one table to another tableColumn1,column2,column3 from table 1  21, creating an index to create a index name on student (XH,NAME,BJ);  22, descending ascending select * from XS_XJB ORDER BY XH ASC     Ascending   select * from XS_XJB ORDER BY xh desc   Descending  23, querying system current time select S Ysdate from dual 24, changing system capitalization UpdateTable Set Sfzh = Upper (' sfzh ') 25,oracle password exceeds time limit select * from Dba_profile;alter profile default limit Failed_login_attem PTS Unlimited; Alter profile default limit Password_life_time unlimited;

Oracle Basic Syntax