Monday, December 24, 2007

Migrating Dictionar managed tablespace to locally managed tablespace

SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TEMPD')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TEMPD'); END;
*
ERROR at line 1:
ORA-03245: Tablespace has to be dictionary managed, online and permanent to be able to migrate
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('USERSD')
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10643: Database should be mounted in restricted mode and Exclusive mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP RESTRICT
ORACLE instance started.
Total System Global Area 160504432 bytes
Fixed Size 453232 bytes
Variable Size 125829120 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPD not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> SELECT * FROM V$TABLESPACE;
TS# NAME INC
---------- ------------------------------ ---
0 SYSTEM YES
1 UNDOTBS1 YES
2 TEMP YES
3 USERS YES
5 USERSD YES
6 TEMPD YES
7 SMALL YES
8 TEMPL YES
8 rows selected.
SQL> ALTER TABLESPACE USERS READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE SMALL READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE USERSD READ ONLY;
Tablespace altered.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPD not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> DROP TABLESPACE TEMPD;
DROP TABLESPACE TEMPD
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPL ;
Database altered.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPL not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> DROP TABLESPACE TEMPD;
Tablespace dropped.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPL not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> SELECT NAME FROM V$TABLESPACE;
NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
USERS
USERSD
SMALL
TEMPL
7 rows selected.
SQL> DROP TABLESPACE &1;
Enter value for 1: USERS
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE USERS
Tablespace dropped.
SQL> /
Enter value for 1: USERSD
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE USERSD
DROP TABLESPACE USERSD
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

SQL> DROP TABLESPACE USERSD INCLUDING CONTENTS ;
Tablespace dropped.
SQL> DROP TABLESPACE &1;
Enter value for 1: SAMLL
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE SAMLL
DROP TABLESPACE SAMLL
*
ERROR at line 1:
ORA-00959: tablespace 'SAMLL' does not exist

SQL> /
Enter value for 1: SMALL
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE SMALL
Tablespace dropped.
SQL> /
Enter value for 1: TEMPL
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE TEMPL
DROP TABLESPACE TEMPL
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

SQL> SELECT NAME FROM V$TABLESPACE;
NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
TEMPL
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPL not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> DROP TABLESPACE TEMP;
Tablespace dropped.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
PL/SQL procedure successfully completed.
SQL> CREATE TABLESPACE TESTD
2 DATAFILE 'TESTD'
3 EXTENT MANAGEMENT DICTIONARY ;
CREATE TABLESPACE TESTD
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace

SQL> SHOW PARAMETER READ
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
parallel_threads_per_cpu integer 2
read_only_open_delayed boolean FALSE
thread integer 0
SQL> CREATE TABLESPACE TESTD
2 DATAFILE 'TESTD' ;
CREATE TABLESPACE TESTD
*
ERROR at line 1:
ORA-01119: error in creating database file 'TESTD'
ORA-17610: file 'TESTD' does not exist and no size specified
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL> ED
Wrote file afiedt.buf
1 CREATE TABLESPACE TESTD
2* DATAFILE 'TESTD' SIZE 1M
3
SQL>
SQL> /
Tablespace created.
SQL> ALTER TABLESPACE TESTD READ ONLY ;
Tablespace altered.

69 comments:

Anonymous said...

generic viagra cheap viagra that works - viagra buy online nz

Anonymous said...

buy tramadol online tramadol hydrochloride online - tramadol for dogs versus humans

Anonymous said...

xanax without a perscription xanax withdrawal symptoms headache - xanax drug interactions lexapro

Anonymous said...

xanax online xanax generic - long does take 1mg xanax work

Anonymous said...

buy tramadol online buy tramadol online australia - signs tramadol overdose dogs

Anonymous said...

generic xanax xanax drug abuse effects - xanax side effects for dogs

Anonymous said...

generic xanax xanax dosage airplane - xanax side effects breastfeeding

Anonymous said...

buy tramadol online long does tramadol overdose take - generic name tramadol

Anonymous said...

buy tramadol with mastercard legal buy tramadol online usa - buy tramadol no prescription mastercard

Anonymous said...

xanax online .5 xanax effects - xanax 1mg day

Anonymous said...

buy discount tramadol tramadol 50 mg tablets for dogs - tramadol sr tabs 100mg

Anonymous said...

buy tramadol online tramadol ratiopharm 100mg nebenwirkungen - tramadol 200mg dosage

Anonymous said...

buy alprazolam xanax withdrawal last - regular dosage xanax

Anonymous said...

buy carisoprodol carisoprodol 350 mg shelf life - carisoprodol y acetaminofen

Anonymous said...

xanax cost buy xanax online overseas - xanax bars mixed with weed

Anonymous said...

buy tramadol online how to buy tramadol online - tramadol 50 mg ingredients

Anonymous said...

buy xanax bars buy xanax online overnight - alprazolam 0 5mg wirkung

Anonymous said...

xanax online xanax and alcohol long term effects - xanax bar quotes

Anonymous said...

buy tramadol online ultram brand of tramadol hydrochloride - tramadol hcl 225 mg

Anonymous said...

xanax online blue xanax pills mg - xanax side effects mayo

Anonymous said...

xanax online xanax xr overdose symptoms - buy alprazolam no prescription

Anonymous said...

buy tramadol online buy tramadol online no prescription - tramadol purchase cheap

Anonymous said...

generic xanax xanax vs sleeping pills - xanax kick in

Anonymous said...

buy tramadol online tramadol hcl er 100mg - tramadol 50mg para que serve

Anonymous said...

xanax online xanax overdose signs symptoms - drug interactions clonazepam xanax

Anonymous said...

carisoprodol 350 mg carisoprodol user reviews - 350 mg carisoprodol generic soma

Anonymous said...

xanax online generic xanax pill identification - 4mg xanax effects

Anonymous said...

generic xanax xanax side effects yahoo answers - drug test time table xanax

Anonymous said...

xanax online alprazolam 0.5 mg tab - xanax blue pill

Anonymous said...

carisoprodol 350 mg carisoprodol.drugs - buy carisoprodol europe

Anonymous said...

carisoprodol 350 mg carisoprodol uses - carisoprodol drug screen

Anonymous said...

buy tramadol no prescription tramadol hcl online pharmacy - buy tramadol online cod overnight

Anonymous said...

buy tramadol online buy 180 tramadol online - tramadol dosage weight loss

Anonymous said...

xanax online generic xanax works best - safe order xanax online

Anonymous said...

xanax online xanax 2mg r039 - xanax generic images

Anonymous said...

buy cialis viagra generic cialis good - side effects cialis daily

Anonymous said...

cialis buy online no prescription cialis online legale - buy cialis no prescription in us

Anonymous said...

buy generic xanax xanax pills wikipedia - xanax zombie

Anonymous said...

tramadol generic tramadol no prescription mastercard - buy tramadol usa pharmacy

Anonymous said...

http://landvoicelearning.com/#62431 can you buy tramadol in greece - high von tramadol

Anonymous said...

buy tramadol tramadol 50 mg medicine - best dosage tramadol

Anonymous said...

buy tramadol tramadol shipped with no prescription - tramadol for dogs diarrhea

Anonymous said...

http://landvoicelearning.com/#97734 order generic tramadol - is it legal to buy tramadol online usa

Anonymous said...

buy tramadol cash on delivery tramadol 50 mg 60 capsulas - tramadol to get high

Anonymous said...

http://landvoicelearning.com/#51438 tramadol mg get high - buy tramadol cod delivery

Anonymous said...

buy tramadol online buy tramadol 50mg - legal buy tramadol usa

Anonymous said...

buy tramadol buy tramadol usa pharmacy - tramadol hcl 50 mg generic ultram

Anonymous said...

learn how to buy tramdadol tramadol dosage instructions - buy tramadol online saturday delivery

Anonymous said...

http://reidmoody.com/#36921 ativan wiki - long ativan your system

Anonymous said...

http://reidmoody.com/#92465 ativan dosage mri - ativan withdrawal and night sweats

Anonymous said...

ways to buy ativan online ativan dosage long does last - ativan vs valium

Anonymous said...

ways to buy ativan online ativan drug class - lorazepam 1mg vs clonazepam

Anonymous said...

lorazepam drug ativan high - ativan withdrawal hair loss

Anonymous said...

ways to buy ativan online ativan dosage in elderly - ativan side effects on children

Anonymous said...

buy ativan online ativan youtube - online pharmacy for ativan

Anonymous said...

buy tramadol cod next day delivery order tramadol no prescription cheap - tramadol addiction after 1 week

Anonymous said...

buy tramadol online us tramadol online pharmacy - tramadol ingredients

Anonymous said...

http://staam.org/#90723 tramadol 50mg how many to get high - tramadol hcl 50 mg recreational use

Anonymous said...

buy tramadol online buy tramadol online by cod - buy tramadol in usa

Anonymous said...

buy tramadol online what's the tramadol high like - tramadol 50mg for dogs usa

Anonymous said...

http://bayshorechryslerjeep.com/#4038 xanax retard 1 mg bijsluiter - xanax drug test probation

Anonymous said...

Your oωn report features provеn helpful to me.
It’s very helpful and you're simply certainly very knowledgeable of this type. You have got exposed our sight in order to numerous opinion of this topic along with interesting and solid written content.
My web page - Buy Valium Online

Unknown said...

longchamp bags
michael kors handbags
kobe basketball shoes
yeezy boost
nike huarache
jordan shoes
brady jersey
lacoste online shop
timberland boots
michael kors uk

Unknown said...

air max 90
yeezy
kobe shoes
jordan shoes
chrome hearts
stephen curry shoes
adidas superstar
adidas nmd
lacoste outlet
curry 3

jeje said...

adidas eqt support adv
kobe 11
timberland shoes
true religion
air jordan 11
adidas neo online shop
yeezy sneakers
kyrie 3
cheap jordan shoes
baseball jerseys

Anonymous said...

That's an interesting blog, I think you site must rank on top of the search results, if you are looking to hire SEO Company In Delhi or any Digital Marketing Company in Delhi , no more worries, Cure SEO is here to help you out.

WordPress Development Company In Bangalore said...

Hey there, Thanks for sharing it, I was really looking for it & found your site... Thats really imprerssive, I think you must take it one step forward by developing a Mobile app for your services for which you can select a leading Mobile App Development Company in Bangalore and I even suggest you to for Digital Markting & SEO services for bsuiness. Here I'm suggesting you to opt for Digital Marketing Company in Bangalore & SEO Company in Bangalore . Only then you can get the best out of it... Thanks. Have a good day

mobileappdevelopment said...

I really appreciate your post & really it is excellent. Looking to read some more posts from your blog
Mobile App Development Company in India
Mobile App Development Company in bangalore
Mobile App Development Company in chennai
Mobile App Development Company in kolkata
mobile app development company| web app development company|cloud app development company

Software Insights said...


Thanks for sharing the info.

If you are looking for software testing services, visit the below links.

software testing services
DevOps consulting services