Create an instance of Oracle data

Have time today to tidy up the Oracle database notes. In the development process, more or less involved in some of the database aspects of things, it is necessary to sort out the need to drop.

/* First step: Create a DB instance */

You can build on the steps of your Oracle-brought configuration instance step-by-step.

/* Step two: CREATE TABLE space */

Once logged in as an administrator,

CREATEtablespace tsp_test_provincedatafile'c:/app/administrator/oradata/test/tsp_test_province.dbf'SIZE 5024MAUTOEXTEND onNEXT1024MMAXSIZE unlimitedextent MANAGEMENT LOCAL;CREATESmallfileTemporarytablespace tsp_test_temptempfile'c:/app/administrator/oradata/test/tsp_test_temp.dbf'SIZE 1024MAUTOEXTEND onNEXT1024MMAXSIZE unlimitedextent MANAGEMENT LOCAL;

/* Step three: Create user */

CREATE USER TESTDB identified by    TESTDBDEFAULT  tablespace tsp_test_province  Temporary  tablespace tsp_test_tempaccount UNLOCK; GRANT  to TESTDB  ;

/* Import the database */

Connect Sys/password as Sysdba
(1) Connect Sys/password as Sysdba
(2) Create directory Dmp_dir as ' e:\dmp ';
Grant Read,write on directory Dmp_dir to TEXTDB;
(3) IMPDP TEXTDB/TEXTDB@TEXT directory=dmp_dir dumpfile=TEXTDB. DMP logfile=TEXTDB. Log

/** Extension Table space */
ALTER tablespace tsp_test_temp1 ADD datafile ' c:/app/administrator/oradata/test/tsp_test_temp1.dbf '

SIZE 100M autoextend on NEXT 100M MAXSIZE UNLIMITED;

/** Delete table space and its files */
DROP tablespace tsp_test_temp1 including CONTENTS and datafiles CASCADE CONSTRAINTS;

/** adding tablespace Files */
ALTER tablespace tsp_test_temp1 ADD datafile 'c:/app/administrator/oradata/test/tsp_test_temp2.dbf' SIZE 32767M;

/** View tablespace Use */

SELECT UPPER(f.tablespace_name) "tablespace name", D.TOT_GROOTTE_MB "table space size (M)", D.TOT_GROOTTE_MB-f.total_bytes "used Space (M)", To_char (ROUND((D.TOT_GROOTTE_MB-F.total_bytes)/D.tot_grootte_mb*  -,2),'990.99')|| '%'"Use ratio", f.total_bytes "free Space (M)", F.max_bytes "Max Block (m)" from(SELECTTablespace_name,ROUND(SUM(BYTES)/(1024x768 * 1024x768),2) Total_bytes,ROUND(MAX(BYTES)/(1024x768 * 1024x768),2) Max_bytes fromSYS. Dba_free_spaceGROUP  bytablespace_name) F, (SELECTDD. Tablespace_name,ROUND(SUM(DD. BYTES)/(1024x768 * 1024x768),2) TOT_GROOTTE_MB fromSYS. Dba_data_files DDGROUP  byDD. Tablespace_name) DWHERED.tablespace_name=F.tablespace_nameORDER  by 1;

/** All tables under query table space */

SELECT * from All_tables where tablespace_name= ' TSP_TEST_TEMP1 '

/** query table space is used */

Select Segment_name,partition_name,tablespace_name from
dba_extents where Tablespace_name=upper (' TSP_TEST_TEMP1 ')

Create an instance of Oracle data