Oracle query and backup

Limit cannot be used for Oracle paging query.
1. Select * From table_name where rownum> begin and rownum <End
2. SQL = "select * from table"
Con. preparecall ("select * from (select. *, rownum R from ("+ SQL +") A where rownum <= "+ intpage * intpagesize +") B where r> "+ (intPage-1)
* Intpagesize );


Today I want to query all the table names in the Oracle database or all the tables in a user. I haven't thought about it for a long time. I still found the answer online.
Select table_name from all_tables; // All
Select table_name from user_all_tables; // all tables of the user

The following is a post on the SQL statement.

Select * From user_objects; // query all tables
Select * From dba_tables; // query all tables
Select * From all_tables; // query all tables
Select * From user_users // find a user
Select * From all_users // query all users
Select * From dba_users // query all users
Select name, dbid from V $ database; // query the database name and its ID
Select * From SYS. user_tab_columns; // query the table name and display the column name.
Describe table name // query the table structure
Select * From SYS. user_tab_columns where table_name = table name // query the field of the specified table name
2: query database Parameters
Show parameter dB;
3: query the database instance name
Select instance_name from V $ instance;
4: Database Domain Name
After the database is installed, you can use
Select value from V $ parameter where name = 'db _ Domain'
Show parameter domain
5: Database Service name
If the database has a domain name, the Database Service name is the global database name. If no domain name is defined for the database, the Database Service name is the same as the database name.
Show parameter SERVICE_NAME

6: display the current user

Show user
7: Direct Login
Sqlplus "/As sysdba"
8: Current Oracle system time
Select sysdate from dual;

9: query the database dictionary v $ nls_parameter to view character set parameters
Select * from V $ nls_parameters;

//*************
Basic Oracle statements (suitable for beginners)
Oracle operation statement:
1. Create a table
Create Table Name (
Column name 1 type,
Column name 2 type
);
2. Modify class attributes
Alter table table name modify (column name type );
3. Add Columns
Alter table table name Add (column name type );
4. Add primary key constraints and non-empty Constraints
Alter table table name Add constraint pK _ table name primary key (column name );
Alter table table name modify (column name not null );
5. Delete the primary key constraint
Alter table Table Name drop primary key;
Alter table Table Name drop constraint pK _ table name;
6. Failure Constraint
Alter table table name disable primary key;
Alter table table name disable constraint pK _ table name;
7. Valid Constraints
Alter table table name enable primary key;
Alter table table name enable constraint pK _ table name;
8. Delete Columns
Alter table Table Name drop column name;
9. Set a column to unavailable and delete it.
Alter table table name set unused (column name );
Alter table Table Name drop unused columns;
10. Modify the table name
Rename table name 1 to table name 2
Alter table name 1 Rename to table name 2;
11. Table Truncation
Truncate table name;
12. truncate the table and retain row space
Truncate table name resue storage;
13. view the table structure
Desc table name;
14. delete a table
Drop table name;
15. Insert record
Example: insert into table name values (Content 1, content 2, content 3, content 4 );
16. Insert rows in dialog mode with Parameters
Example: insert into table name values (& Column name 1, & Column name 2 );
Insert into table name values (Content 1, content 2 );
17. Insert several columns of records
Insert into Table Name (column name 1, column name 2) values (Content 1, content 2 );
18. Insert a null value for the column (its column cannot be not null)
Insert into table name values (Content 1, null, null );
19. Create a table (including primary key and foreign key settings) method 1
Create Table Name (
Column name 1 Type
Constraint pK _ table name primary key,
Column name 2 type not null,
Column name 3 type
Constraint FK _ Table Name Reference Table Name (column name ),
Column name 3 type
Constraint CK _ table name check (column name 3 in (''content 1 '', ''content 2'', ''content 3 ''))
);
20. query all rows
Select * from table name;
21. query certain Columns
Select column name 1, column name 2 from table name;

22. Duplicate row Elimination
Select distict column name from table name;
23. Where statement Query
Select * from table name where condition order by column name;
(Note: If the number type is queried automatically in ascending order, if you want to sort it in descending order, select * from table name where condition order by column name DESC ;)
24. Create a table. method 2
Create Table Name (
Column name 1 type primary key,
Column name 2 type not null,
Check (column name 3 in ('''','''','''')),
Column name 4 type refernce table name (column name)
);

25. Modify the column = '? 'Data
Update table name set (column = ?) Where column = '? ';

26. Delete rows
Delete from table name where condition;

27. Transaction Processing
-- Transaction Processing
Update table name
Set column name (date) = ''30-May-98''
Where condition;
Savepoint mark1;
Delete from table name where condition;
Savepoint mark2;
Rollback to savepoint mark1;
Rollback;
28. Create user1 with Password
Grant connect and resource permissions to users
Connect role for Logon
The resource role is used to create tables.
Connect system/Manager
Create user user1 identified by password;
Grant connect, resource to password;
29. Data Control Language
Connect Scott/Tiger
30. Grant the permission to query and Modify Table 1 to user1.
Grant select, update on table 1 to user1;
31. Grant the permission to modify column 1 and column 2 in table 1 to user1.
Grant Update (column 1, column 2) on table 1 to user1;
32. Grant the permission to query Table 1 to user user1
In addition, user1 users can grant this permission to other users (with grant option)
Grant select on table 1 to user1 with grant option;
33. revoke the permission to query and Modify Table 1 from user user1
Revoke select, update on table 1 from user1;
This article Article Source of hacker base-the world's largest Chinese hacker site Source: http://www.hackbase.com/tech/2009-09-23/56338.html

 

Oracle impORT/Export command

EXP/IMP instance Exp help = y view help Exp 1. Exp usr/pwd @ Sid file = c: \ TB. Dump tables = tb1 If you export multiple tables, tables = (tb1, tb2) 2. Exp usr/pwd @ Sid file = c: \ TB. Dump -- export all 3. Exp usr/pwd @ Sid file = c: \ TB. Dump owner = (system, sys) Export all tables under the user system and sys 4. Exp usr/pwd @ Sid file = c: \ TB. Dump tables = tb1 query = \ "where name = 'ha '\" Note the position of the semicolon  Run the following command to view the description.C: \> imp help = yC: \> exp help = y Eg:Exp username/password @ orcl file = expfile-% Date :~ 0, 10%. dmp log = exp. LogIMP newname/password @ orcl fromuser = username touser = newname file = expfile2010-12-17.dmp ignore = y log = imp. Log

From http://zsl79812sun.blog.163.com/blog/static/1234112752009631103418551/

Impdp username/password @ orcl directory = dmp_dir dumpfile = XXXX. dmp remap_schema = Username: newname remap_tablespace = oldspace: newspace

Expdp username/password directory = dump_dir dumpfile = expfile-% Date :~ 0, 10%. dmp logfile = exp. Log

More

Http://blog.csdn.net/aicon/archive/2010/07/17/5742382.aspx

Http://wenku.baidu.com/view/44ee901d59eef8c75fbfb38a.html