I am using Oracle 12c Standard Edition.  I accidentally drop the table and try to recover it, but the table does not appear in Recyclebin.  I think the tablespace is due to SYSTEM.  Create table test(  aaa varchar2(20)  .... )  TABLESPACE SYSTEM  STORAGE (   NEXT 1024K  ) /   thank!
122524
I tried dropping a table and then when i viewed the recycle bin nothing was there in it . Earlier too i dropped tables many a times but I was surprised to find no objects whatsover in it . What's the deal ? [update]: i had tried    SHOW PARAMETER RECYCLEBIN;  Its VALUE column shows it  as ON . So fine here ! Then i droppped an existing table but still the command     Select object_name from recyclebin;  and any such retrieval of columns is showing NO ROWS SELECTED !! Also using flashback command  confirms that recyclebin is not having any such dropped tables !! So where are dropped tables going ? And one more thing : This time i tried using  ALTER SYSTEM SET RECYCLEBIN=ON;    Instead of Alter session    and sqlplus throws an error : Specified initialization parameter is not modifiable with this option  So, is this because  that i am working as a SYSTEM ?? i have not created any other USER as of now .
42767
So I have a test db server that was setup on a replication stream.  Over the name an optimize came through that quickly filled up the space on the slaves datadir.  Mysql dutifully was just waiting for some more space.  This datadir is a file system used ONLY as mysql's datadir so there wasn't anything else to free up.  I had a 4 gig innodb test table that wasn't part of the replication stream so I figured I'd try something to see if it'd work, and being a test environment I wasn't too worried if things went horribly wrong.  Here's the steps I took   Flushed the table I was about to move Placed a read lock on it (even though nothing was writing to it and it wasn't in the replication stream) Copied the .frm and .ibd over to a filesystem w/ some spare room Unlocked the table Truncated that table - this freed up enough space for the optimize to finish have replication start chugging along again. Stop slaving/shutdown mysql Copy the file out of tmp back to the data dir Restart mysql   Nothing shows up in the .err log, things look good. I connect and use mydb; and see the table I was messing with in show tables.  But, if I try  select * from testtable limit 10;   I get the error  ERROR 1146 (42S02): Table 'mydb.testtable' doesn't exist   From what I can tell so far I can read from all the other tables just fine and replication started back up w/o any complaints.  Is there anything I can do to recover from this point?  I can rebuild it from scratch if need be but was curious what others thought about this venture in general.  Was there anything about the series of steps I took that would have ended up w/ more flawless results?    What if this wasn't a test server I couldn't just 'do it live' and see what happens?  What would have the best way to free up space temporarily on a production slave if I had to like that?
72530
Is there a way to determine in advance how much disk space a VACUUM FULL on a particular table would return to the OS? Therefore you can decide whether it's worth the cost in doing so. Bonus if there's a simple query to do this for every table in a database/server (rather than doing each one separately).
89940
I've been using Synapse for a while now. The  regrading the lacking ability to remember recurrent launches was fixed.  I've been launching the same documents I am working on at the moment for the past months and there was no issue. But today, for some mysterious reason, Synapse doesn't remember any of these, nor can it find them in its search. I had to browse and launch them myself! (How medieval is that! :) )  So, did something happen? Can I find out what and why? And most importantly: can it be fixed?  NB: problem also posted .
99202
The reason I ask is because I recently made the mistake of installing Oracle 12c on my sub-performance laptop. Noticing the excessive amount of resources this added up to on startup, I decided to uninstall it.  What I failed to see through, however, was the fact that it created a new Windows user account in order for the initial installation to work out. In my case, it was mandatory because I only had an administrator account and I didn't see the need to use the built-in account. So, after failing to successfully install 12c twice, I wound up creating not just one, but two Windows user accounts. This had to do with something in regards to how Oracle sets up the Oracle Home business and whatnot, but I have no idea what any of that detailed, so I just went ahead and did it.  After uninstalling what I could in regards to Oracle and the database, I decided the next thing to do would be to remove the created users.  Having Windows 10 Home installed, I couldn't use the local users &amp; groups view/tool to delete these from the GUI. SO, I did the next best thing and Googled a solution. In my case, I used the following command:  net user username /DELETE   When I executed the command, the console gave the response of having successfully deleted the account for both accounts specified.  To verify that this had worked, I went to the users directory located in %SYSTEMROOT%/Users/ and came to find that the user directories were still there.  What does this mean and if I should delete them, will anything bad happen?  Can I even delete these directories?  Regards,  --T.R.G
57312
Using MSSQL2005, can I truncate a table with a foreign key constraint if I first truncate the child table (the table with the primary key of the FK relationship)?  I know that I can either   Use a DELETE without a where clause and then RESEED the identity (or) Remove the FK, truncate the table, and recreate the FK.   I thought that as long as I truncated the child table before the parent, I'd be okay without doing either of the options above, but I'm getting this error:     Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint.
83010
We have a fairly big MS SQL 2008R2 database that resides on a SSD drive. The drive itself only has ~110Gb of space, and the database files are the only files on the drive.  The database is in "Simple" recovery mode, and only has two files, .MDF and .LDF.   The disk is now nearly full: the MDF is currently 109Gb in size. However, SSMS tells me there's nearly 18Gb of "Space Available" (in the 'General' properties page), and if I go through the motions of Shrinking the file it also tells me there's 18Gb of space free. SSMS also tells me the database size is around 132Gb, which surprises me - that wouldn't fit on the drive!  From what I've read, shrink is a really bad idea. However, I'm starting to see replication errors (could not allocate space for object). We've previously tried shrinking the database, but within a few hours the file was back to it's original size.  How should we proceed - given there's apparently 18Gb of free space, should SQL automatically use that free space? Or is it as simple as: we really need more disk space?
12713
I ran into the problem that my primary key sequence is not in sync with my table rows.   That is, when I insert a new row I get a duplicate key error because the sequence implied in the serial datatype returns a number that already exists.  It seems to be caused by import/restores not maintaining the sequence properly.
74265
This is what happened:  I was running 12.04.1 and wanted to install 12.10. upgrade, but a fresh install.  When setting up my computer I anticipated for this by dividing my 640GB HD in following partitions: 1. 60 GB for Ubuntu, boot 2. 576 GB for data, mountpoint /home 3. swap, 4GB  The idea was to manually select the correct partition in the installer but I got distracted for a moment and selected the wrong option.  Result: The installer started repartitioning the entire HD.  When I noticed this I interrupted the installer, but upon reboot it was clear that I was too late, no OS to boot to.  I booted from a Gparted Live CD to see if I could recover the data on my previous /home-partition, but it's gone.  Is there any way to recover the lost data? I searched around and read alot about Testdisk, but in all the tutorials I've seen, the set-up has been much easier than what I'm facing.  I've not only lost my partition table, it's been replaced.  Thanks in advance for any ideas that might help!  If extra info is needed, please specify and I will do my best to provide.
38392
We have a database which is set up in Merge Replication.  We accidentally updated hundreds of thousands of records in a large published table on the publisher database. We did something like this:  update LargeTable set Field = Field   So basically, every record got updated to be the same - no data was actually changed. However, every record in the table is now being delivered to every subscriber and the whole system has come to a halt. We are very worried this will take days and days to complete.  How can we safely remove data from the msmerge_contents and msmerge_genhistory table? Are there other system replication tables to remove records from? I can see the exact records in these tables that refer to the mass update. I just don't want to remove them without knowing it is safe to do so.
78656
I have a content type with 20 different fields.   Now the project requirement changed and I dont need 15 fields of them. So I removed all those unwanted 15fields from content type.   But the database is still holding all the 15 tables and also the revision table, Why? What happens if I remove it?  I feel like having these many unused tables in database might impact the performance. Will it really have impact on the performance?
87267
I am trying to check how the table size is going to be increased after adding new column with default values.  I use the following default values: NULL, ''(empty string) and some text(some random text)` but it seems the table size is not changed.   I am using the following statements:  EXEC sp_spaceused '[Table_A]';  ALTER TABLE [dbo].[Table_A] ADD [Loops] NVARCHAR(900) CONSTRAINT [DF_Loops] DEFAULT('XXX') WITH VALUES;  EXEC sp_spaceused '[Table_A]';  ALTER TABLE [dbo].[Table_A] DROP CONSTRAINT [DF_Loops]  ALTER TABLE [dbo].[Table_A] DROP COLUMN [Loops];   but the sp_spaceused always returns:  name                                               rows                 reserved           data               index_size         unused ------------------------------------------------- -------------------- ------------------ ------------------ ------------------ ------------------ Table_A                                            73540994             8701688 KB         6658544 KB         2042976 KB         168 KB   Could anyone tell what I am doing wrong?
59852
Earlier I created a user :  SQL&gt;create user suhail identified by password SQL&gt;User created. SQL&gt;Grant create session,create table to suhail; SQL&gt;Grant Succeeded.   but when I login as suhail and try to create a table I get an error saying ORA-01950: no privileges on tablespace 'SYSTEM' .  What error is it ? Why does it occur ?    SQL&gt; connect suhail Enter password: Connected. SQL&gt; create table suhail_table(name char(10)); create table suhail_table(name char(10)) * ERROR at line 1: ORA-01950: no privileges on tablespace 'SYSTEM'   I am using Oracle Database 11g Express Edition Release 11.2.0.2.0
119032
Is there a convenient way to import a schema into Oracle 11gR2 using a single new or different tablespace than where the data originated?  As an example, I have exported BLOG_DATA from OLDDB, where all user data is stored in the USERS tablespace.   On NEWDB, I would like to import the BLOG_DATA schema, but store the user objects in the BLOG_DATA tablespace, created specifically for this user.   I have created the BLOG_DATA user, created the BLOG_DATA tablespace and set it as the default tablespace for that user and added an appropriate unlimited quota.  CREATE TABLESPACE blog_data DATAFILE SIZE 1G;  CREATE USER blog_data IDENTIFIED BY secretpassword DEFAULT TABLESPACE blog_data QUOTA UNLIMITED ON blog_data;  GRANT connect,resource TO blog_data   The schema was exported from OLDDB with something like  exp blog_data/secretpassword@OLDDB file=blog_data.dmp    After reading Phil's excellent answer below, I found myself wondering:   Since data has no other place to go than the default tablespace - the only tablespace the user has a quota on - will this effectively force imp to put all user objects in that default tablespace?  imp blog_data/secretpassword@NEWDB file=blog_data.dmp   Would this then place the entire blog_data schema in the blog_data tablespace on NEWDB? Is there any reason why this wouldn't work or that I would run into problems with certain objects etc?  update:  I did a quick test and found this to be the case. Imp places objects in the default tablespace for that user, provided it can't place it in the original tablespace (eg. the tablespace does not exist). Full explanation:   Still, I suppose that using Data Pump like Phil suggests might be the preferred option.
61722
How do I dump the data, and only the data, not the schema, of some SQLite3 tables of a database (not all the tables)? The dump should be in SQL format, as it should be easily re-entered into the database later and should be done from the command line. Something like  sqlite3 db .dump   but without dumping the schema and selecting which tables to dump.
16483
I am running sql 2008 r2 and the db was working fine and fast for last 3 years untill about 3 months ago we added ntext field on very active and used table.  Now we are starting to get out of server space because of the huge expanding size of this table.  I read that shrinking ,we do not want to loose the indexing of db because it was working fast for years and we do not want to get fragmentation expending.  We decided to delete that field and all its values: Is there a way to delete the ntext field and all its values and release space without removing indexing ,without shrinking, without loosing db performance?  I am attaching the db size query output to show you size expanding of last 5 months.
73931
I have a Database nearly 1.9Gb Database in size, and MSDE2000 does not allow DBs that exceed 2.0Gb  I need to shrink this DB (and many others like this at various client locations).  I have found and deleted many 100's of 1000's of records which are considered unneeded: these records account for a large percentage of some of the main (largest) tables in the Database. Therefore it's reasonable to assume much space should now be retrievable.  So now I need to shrink the DB to account for the missing records.   I execute DBCC ShrinkDatabase('MyDB')...... No effect. I have tried the various shrink facilities provided in MSSMS.... Still no effect. I have backed up the database and restored it... Still no effect.   Still 1.9Gb  Why?  Whatever procedure I eventually find needs to be replayable on a client machine with access to nothing other than OSql or similar.