PL/SQL Common statements

>

2, display the current connection user  sql> show user 3, check the system has what users  SQL> select * from  All_users; 4, new user and authorized  SQL> create user a identified by a; (Built in System tablespace by default)  sql> grant connect,resource to a; 5, connecting to new users  SQL>  conn a/a 6, querying all objects under the current user  sql> select * from tab; 7, creating the first table   Sql> create table a (A number),  8, querying table structure  sql> desc a 9, inserting new records  sql> insert into a values (1);  10, query Records  SQL> select *  from a; 11, change record  sql> update a set a=2; 12, delete record  SQL>  DELETE FROM A; 13, rollback  sql> roll; sql> rollback; 14, submit  SQL > commit;  user Authorization: grant alter any index to  "user_id "  grant   "dba "  TO  "user_id  "; alter user " user_id  " DEFAULT ROLE ALL  Create User:  create  USER  "user_id "  PROFILE  "default "  IDENTIFIED BY  " default  TABLESPACE  "users "  TEMPORARY TABLESPACE  "temp "  ACCOUNT UNLOCK;  GRANT  "connect "  TO  "user_id ";  User password settings: alter user  "Cmsdb    IDENTIFIED BY  pass_word    tablespace creation: create tablespace  Table_ space  " LOGGING DATAFILE  ' C:\ORACLE\ORADATA\dbs\table_space.ora '  size 5m -- ---------------------------------------------------------------------- 1, view all current objects  SQL >  select * from tab; 2, build an empty table with the same structure as a table  sql > create table b  as select * from a where 1=2; sql > create table  b (B1,B2,B3)  as select a1,a2,a3 from a where 1=2; 3, view the size of the database, and the use of space  SQL >　　 col tablespace format a20 sql > select b.file_id file id,  B.tablespace_name tablespace,  b.file_name physical file name,  b.bytes Total bytes,  (b.bytes-sum (NVL)) has enabled With,  sum (NVL (a.bytes,0)) remaining,  sum (NVL (a.bytes,0))/(b.bytes) *100 percent remaining   FROM DBA_FREE_SPACE&NB sp;a,dba_data_files b  where a.file_id=b.file_id  Group by b.tablespace_name, b.file_name,b.file_id,b.bytes  order by b.tablespace_name /  dba_free_space -- Table space remaining space condition   dba_data_files --data file space consumption  4, view existing rollback segments and their status  sql > col segment  format a30 sql > select segment_name,owner,tablespace_name,segment_id,file_ Id,status from dba_rollback_segs; 5, view the path of the data file placement  sql > col file_naMe format a50 sql > select tablespace_name,file_id,bytes/1024/1024,file_name  from dba_data_files order by file_id; 6, display current connected user  sql > show  user 7, Sql*plus as Calculator  sql > select 100*20 from dual; 8, connection string  SQL > select  column 1 | | column 2 from  table 1; sql > select  concat (column 1, column 2)  from  table 1; 9, query current date  sql > select to_char (Sysdate, ' Yyyy-mm-dd,hh24:mi:ss ')  from dual; 10, copying data between users  sql > copy from user1  to user2 create table2 using select * from table1; 11, Order by is not available in the view, but can be used instead of group by to achieve sorting purposes  SQL > create view a as  SELECT B1,B2 FROM B GROUP BY B1,B2; 12, create user  SQL > by authorized way  grant connect,resource&nbSp;to test identified by test; sql > conn test/test 13, Identify all table names for the current user.  select unique tname from col; ------------------------------------------------- ---------------------- /*  add fields to a table  */ alter table alist_table add  ADDRESS VARCHAR2 (; /* ) Modify field   Properties   field is empty  */ alter table alist_table  MODIFY ADDRESS VARCHAR2 (; /* ) Modify field name  */ create table alist_ table_copy as select id,name,phone,email, qq as qq2, /*qq  Change to qq2*/  address from alist_table; drop table alist_table; rename alist_table_ copy to alist_table /*  Modify table name  */  null handling   sometimes require that column values cannot be empty  create table  dept  (Deptno number (2)  not null, dname char,  loc char (+));  Add a column of  alte to the base tabler table dept add  (Headcnt number (3));  Modify an existing column property  alter table dept  modify dname char (; ) Note: You can reduce the column value width only if all values in a column are empty.   You can change the column value type only if all of the values in a column are empty.   You can define a column not null only if all values are not empty.   Example: alter table dept modify  (Loc char);  alter table dept  modify loc char (; alter table dept modify ) (Dname char), loc  char ();  Find non-disconnected connection  select process,osuser,username,machine,logon_time ,sql_text  from v$session a,v$sqltext b whe[1] [2]  Next page you are looking at the Oracle Tutorial: Oracle Common SQL statements. re a.sql_address=b.address; -----------------------------------------------------------------  1. The data dictionary view starting with User_ contains information that the current user owns,  query the table information owned by the current user:  select * from user_tables; 2. To All_ The Starting Data dictionary view contains information owned by the Oracle User,  all table information that the user owns or has access to:  select * from all_tables; 3.Dba_ start view typically only Oracle database administrators can access:  select * from dba_tables; 4. Querying Oracle Users:  conn  sys/change_on_install select * from dba_users; conn system/manager;  Select * from all_users; 5. Creating a database User:  create user user_name identified  BY password; GRANT CONNECT TO user_name; GRANT RESOURCE TO  user_name;  Authorized Format: grant  (permissions)  on tablename to username;  delete user (or table):  Drop user (table)  username (tablename)   (cascade)  6. Importing data tables to a built-in user  imp system/manager  fromuser = fuser_name touser = user_name file = c:\expdat. Dmp commit = y 7. Index  create index [index_name] on [table_name] (   "column_name ")